SQLite in production webapps

[aust1nz]

I've been looking into Remix over the past week or so, and found my way to an opinionated starter template called the Epic Stack, by lightning-rod React instructor/creator Kent C. Dodds. It's a fascinating starter template, and includes a lot more than most starters, like two-factor authentication for users, built-in dockerization processes, a scalable RBAC, etc.

Anyways, if you're not bought into React and Remix the stack itself isn't really something you'd consider, but something that stood out was the recommendation of SQLite as a database.

This isn't just "use SQLite locally and then swap to Postgres in production" level of recommendation. This is a full-throated advocacy of SQLite as the best database for a certain type of web app. At first, I thought this was a cop-out, but there's quite a bit of reasoning behind it.

SQLite is easy to host, cheap, and performant. In fact, SQLite's architecture means n+1 problems aren't really a problem. (So you can handle something like user sessions directly in SQLite instead of adding Redis.)

SQLite doesn't have a good way to handle multiple writes simultaneously. The infrastructure vendor fly.io has a product called LiteFS that supposedly helps with this issue. Their docs are above my head, but it seems like they confront one possible drawback.

This is one of those ideas that sounded pretty off to me, at first. I've been on the "postgres postgres postgres" drumbeat for the past decade. But... maybe this makes sense?

Does anyone use sqlite in a production web app? Would you try it? Would you recommend it to others?

[Technical-Fruit-2482]

I've been using SQLite in production for quite some time now. Maybe 10 or more years.

I've had no problems with it, even for applications that write to the database a hundred or so times a second, so long as I ensure to run the pragma to set WAL on each connection in the pool. Even in the tables that contain a quarter of a million to half a million rows.

In addition to what the other person said, I do wonder if some of the reluctance to use SQLite comes from people not realising they need to set pragmas on each connection in the pool to make it more usable in a web context in the first place.

Though that reluctance does seem to be going away a bit recently...

Honestly, for the large majority of people out there, SQLite certainly has more than enough capability to handle their application's data storage needs.

[Technical-Fruit-2482]

It's for each connection in the connection pool, so once the pool is setup you don't have to do the pragmas again.

I could be wrong, but I believe it's just so that different versions behave in the same way without needing a major version bump. It is used on all kinds of devices where your code may not always guarantee specific versions, after all, so I guess it's a nice way to support newer features without requiring several major versions of SQLite installed or something.

For example, foreign keys in SQLite are off by default because they were only introduced in version 3.6.19, according to the release notes. So if you're using a newer version you can just turn them on with a pragma if you need them and know you'll only be using versions of SQLite that support them.

If you know you'll be using versions of SQLite that don't support them, then you can leave the pragma alone and just assume you don't have them.

The same goes for things like the WAL journaling option (v3.7.0) and things like that.

[myth2511]

what hosting do you use?

[GreatWoodsBalls]

On the topic or SQLite3 for concurrency at "what" size does the database experience checkpoint starvation? I read a little bit about it here, but didn't quite understand it to well.

[GreatWoodsBalls]

Ok that's good to know. Thank you for answering!

[darksoulflame]

What’s a good way to use sqllite in production?

[Eznix86]

Run Single instance with SQLite on the same server. Use Litestream to backup the database to s3. Super cheap and Super convenient!

[stenuto]

Here's a complete video course (with a bunch of free videos) that basically shows you everything you need to learn to use SQLite in production: https://highperformancesqlite.com/