[Glench]

copying and pasting from a different thread because this keeps coming up and I want to share my experience in case it's helpful:

I use SQLite/Litestream for https://extensionpay.com! Serves about 120m requests per month (most of those are cached and don't hit the db), but it's been great!

I was convinced that SQLite could be a viable db option from this great post about it called Consider SQLite: https://blog.wesleyac.com/posts/consider-sqlite

Using SQLite with Litestream helped me to launch the site quickly without having to pay for or configure/manage a db server, especially when I didn't know if the site would make any money and didn't have any personal experience with running production databases. Litestream streams to blackblaze b2 for literally $0 per month which is great. I already had a backblaze account for personal backups and it was easy to just add b2 storage. I've never had to restore from backup so far.

There's a pleasing operational simplicity in this setup — one $14 DigitalOcean droplet serves my entire app (single-threaded still!) and it's been easy to scale vertically by just upgrading the server to the next tier when I started pushing the limits of a droplet (or doing some obvious SQLite config optimizations). DigitalOcean's "premium" intel and amd droplets use NVMe drives which seem to be especially good with SQLite.

One downside of using SQLite is that there's just not as much community knowledge about using and tuning it for web applications. For example, I'm using it with SvelteKit and there's not much written online about deploying multi-threaded SvelteKit apps with SQLite. Also, not many example configs to learn from. By far the biggest performance improvement I found was turning on memory mapping for SQLite.

Happy to answer any questions you might have!

[_xnmw]

I've been struggling with the Postgres vs. SQLite decision lately, especially since my app runs on a single server (and will likely continue that way for a long time) but the real reason I am still leaning towards Postgres is for the ecosystem of monitoring tools and plugins. For example a big downside of SQLite is that I don't have easily available tools that can give me reports on slow queries, full table scans, and other poorly written SQL "gotchas" which inevitably happen. I have to invent and implement monitoring on the application level.

[Glench]

Yeah I hear you. I recently started logging slow queries to a text file. It was really easy to do but yeah I didn’t really look into more complex monitoring. I wonder if there’s anything out there!

[ixxie]

As someone considering a SvelteKit + SQLite stack, I would love to hear more about the setup. Have you considered writing a blog post on the topic? If this is too much, perhaps you can drop a few words here...

[Glench]

Is there something you want to know specifically? It’s pretty standard basically. I used sequelize (might have chosen something different if starting today) and I build for node running on a single machine. I use pm2 to deploy with nginx in front.