Sqlite for production?

Questions and Answers Database-SQL
1

Is it a bad idea to use sqlite for production?
I have 3 bigger collections, that will have appr. 800, 200 and 150 datasets. the other collections are not worth to mention (only 4 with max. up to 10 entries).
The biggest collection will increase with 10-25 entries per year.

  1. Would it be reasonable possible to use sqlite as production database or should I go for postgres?
  2. If I go with sqlite, is it possible to switch to postgres easily later?
  3. Are there any recommendations when not using sqlite for production?
2

Hi. Do you now know the answers to your questions? Or anyone else? Because I’m looking exactly for the same answers.
I’ve read that sqlite can keep up with quite a lot and I’m expecting a lot of traffic. At least not at the beggining. So I wonder if it’d be okay to use it with strapi, because making backups is as easy as copying a file. Though it is located in a .tmp folder so I suppose it’s not the way to go?
Looking forward to hearing from you, it would be a big help!

3

I got no answer on this. As I ran in an issue with sqlite. Don’t know anymore what it was. But had troubles with my data structure. I used postgres for strapi 3.x, but now tried mysql in strapi 4. With both I had now issues and using postgres or mysql is probably better for bigger data.

4

It’s a really bad idea, you can use Sqlite only on a single instance, if you scale your app horizontally it is going to break:

  • if you scale it with node cluster, pm2 or with similar libraries it will try to access the same file from different processes;

  • if you scale it with docker when you will update a database entry it will not reflect on the other instances.

If you want to have more information on this topic take a look at articles about scaling node.js applications.
Sqlite on the server is only for testing or developing purposes.

5

I’d like to ask the same question as well, considering;

I’m running a small dataset strapi with one content editor that maybe logs in once a month, and when content is changed, Gatsby will run a build. So traffic is all on Gatsbys side, Strapi will only get 1 request a month. Doesn’t this remove the “problems” for running sqlite in production? I’m never going to scale or having multiple processes accessing the db. Nor will I ever receive any form of intens traffic.

So if you remove those issues, is there actually something wrong with running sqlite in production? it would ease up the development and hosting quite a bit.

6

I know @DMehaffy never suggests running sqlite in production, but i’d like to know what I am missing when I think I could actually run it easily in production for a small Gatsby feeding installation. Would love to hear your 2 cents on the matter.

7

Indeed, forever a hard no on SQLite in production.

8

But i’d like to know why @DMehaffy , if you could be so kind. I’m in the process of migrating from Mongo and staying on SQLite seems so much easier and simpler (and also less work).

In my usecase of feeding a gatsby site, so Strapi is only connecting to the db when the editor is in the admin and when they save, the content is loaded via de API but once. There’s absolutely no need for scaling and such. why would it not be suitable?

9

I responded to a github issue on this the other day: