Hacker News Reader

Comment by nikodunk | Hacker News Reader

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...

View on HN

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.

> 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.

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.

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.

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.

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.

The batch is still atomic (as it's wrapped in a database transaction). So you batch items will never partially happen (say in the case of a crash).

You do have to write your batch items so that they check their own constraints though. I.e check the accounts have funds etc.

But then rolling back the entire batch would potentially rollback inserts/updates/deletes from multiple independent requests.

I need to bne able to rollback just the queries of a single request.

You don't need to rollback, because you have already checked the invariants and the system is a single writer.

Ah you're doing request response? sqlite/single writer fits much better with a CQRS still approach, so in my experience you move away from request/response to something push based. That being said even in a request/response model wrapping a request in a transaction is not a great idea. The minute you have anything that takes some time (slow third party) that transaction is going to sit open and cause trouble.

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

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.

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.

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.

Reply on news.ycombinator.com