[nikodunk]

Very well written and reasoned article. I’ve struggled with a lot of the same issues with SQLite prod deployments. They appear simple, but then after you’ve ensured your file is on non-ephemeral storage, sorted out backups, and thought about vertical scaling or having separate dbs for jobs and models, a lot of the benefits over psql disappear IMO.

The main benefit over psql of course being that you don’t need to pay for a hosted db like RDS, or have a separate database server.

I’ve found a happy middle ground in simply self-hosting psql and my apps on the same VPS with something like dokploy. Local development is still easy enough, and remote deployment in containers is 1-click with Dokploy, and ends up being simpler to reason about IMO. My take below, if anyone’s interested.

https://nikodunk.com/2025-06-10-diy-serverless-(coreos-+-dok...

[graemep]

Sqlite is a bad fit for anything where ephemeral storage is the default. On the other hand is you use a simple VPS there is no problem.

There are multiple simple ways of doing SQLite backups https://sqlite.org/lang_vacuum.html#vacuuminto https://sqlite.org/rsync.html - or just lock and copy the file.

If you need to scale enough that it is a concern, then its not a good fit for your use case.

[degamad]

> If you need to scale enough that it is a concern, then its not a good fit for your use case.

If you need to scale writes.

[andersmurphy]

You can hit 40000-80000+writes/s with sqlite on a 10$ VPS just by batching transactions (i.e wrapping all inserts/updates in a single transaction every 100ms). This is easy to do at the application level, then you also avoid BUSY/LOCK.

I'd argue writes scale better wtih sqlite than postgresql.

[hu3]

I love SQLite but how would batching work in CRUD apps where you need to rollback a dozen SQL inserts/updates in case of error in a request?

Also I often need to read-after-write during the same request, using transactions.

And rails apps are often CRUDy.

[andersmurphy]

With a single writer (as it the case with sqlite). You don't need transactions and rollbacks. As all writes happen in sequence.

Each batch item can be a combination of read/write/update that happen in sequence and therefore can give you the same semantics as a traditional transaction/rollback. eg:

- read -> Does the account have enough funds?

- write -> transfer 100$ from this user to another account

This is also much simpler to write than in other databases as you don't have to worry about n+1.

[graemep]

I definitely want transactions and rollbacks even if writes happen in sequence.

To go with your example, take something like

1) add $100 to this user's account 2) add $100 to the service fees account 3) deduct $101 from the other user's account to cover these

Must all happen or none.

[czhu12]

Not totally clear to me why it’s preferable though: Postgres takes up as little as 50mb of memory as a server running on the host machine and offers way more features, with a large ecosystem.

Why jam SQLite in server side environments? Embedded and mobile devices I can see a case for

[flakeoil]

I agree. I think the reason why Rails (and Laravel) set the default to SQLite is that it makes it easier for newcomers to start. No install needed of MySQL/Postgres.

The problem with making this the default is that people then think it is the recommended way, even if it is not. It is the easiest way to fast get something up and running locally, but it is typically not the best for a production setup. Experienced users will know this, but for beginners to these frameworks, it leads them in the wrong direction.

[christophilus]

This is the way. I used to do the same way back in my ASP (not dotnet) days, only with SQL Server. Hosting the db alongside the app server turned out great, even though you’re always advised against it.

[tracker1]

Cool, I've used Dokku similarly, but I'll usually still reach for a dbms myself a lot of the time. I have used SQLite when I need something portable, just not so much for an application that I'm containerizing anyway.

Been following the Cloudflare features with interest along the way... currently have a pretty good size VPS I've been using.