Switching all my projects from psql to SQLite was a bit of a PITA but has absolutely been worth it. Keeping with the “do it the rails way” has kept mental overhead low, and seems to jive well with using LLMs.
polyrand · 1h ago
A bit off topic but:
The reason for the "lite" in the name is that it doesn’t run a separate process, it doesn’t listen on a port or a socket, and you can’t connect to it.
The name doesn't really contain "lite". It's SQL-ite. So the suffix is "ite":
The suffix "ite" is derived from the Greek word lithos (from its adjectival form -ites), meaning rock or stone [0]
From the horse's mouth (Hipp's): "I wrote SQLite, and I think it should be pronounced "S-Q-L-ite". Like a mineral. But I'm cool with y'all pronouncing it any way you want. :-)"
Me, I say "sequeLITE" with the emphasis on the last syllable, but now I'm thinking of switching to "SEQuelite". You'll never catch me pronouncing it "ess-cue-ell" either way dammit!
:)
christophilus · 49m ago
Yeah the first time I heard Hipp pronounce it, my brain glitched.
nikodunk · 1h ago
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.
If you need to scale enough that it is a concern, then its not a good fit for your use case.
degamad · 16m ago
> 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.
christophilus · 47m ago
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.
jemmyw · 29m ago
I don't really understand the current trend of using sqlite in place of a traditional database. It has it's place, I use it in a client side project and it's really great, although even there I have to be aware of the one writer only limitation.
20 years ago it was MySQL and now it's usually postgres, and these are both still fine. Easier than ever to get setup and going in production. Outside of quite niche needs, I can't understand why one would put up with the pain of using sqlite. Plus it's setting up an even more painful migration later if you do grow.
pawelduda · 1h ago
So that's the new default for Rails? Seems like a new way for people to shot themselves in foot, for those who are used to the usual postgres + separate services setup. Especially that it's advertised as simpler and therefore is tempting to try out without considering such edge cases. I mean, it's the usual route (from my experience) for successful Rails apps to scale horizontally
sosborn · 2m ago
Sqlite has always been the default database for Rails, even though it probably hasn't been the most common deployment choice.
giveita · 1h ago
> I’ve been deploying Rails applications to production since 2004
> I was still caught out by some of the ways things are different.
I feel this is the problem with frameworks, they have to keep evolving and growing and even people who have been doing it 20 years can get caught out.
But the architecture of the Web has basically not changed that much. We are collectively shooting ourselves in the foot.
It had the fascinating property of being a full multi-writer SQL engine when using Page Level Conflict Checking. Even more fascinating: every single client itself becomes a writer.
ncruces · 32m ago
The way they got multi-writer working is a bit too adventurous for me.
It involves fiddling the database header to ensure you can keep track of the read-set from the VFS layer, by convincing SQLite to drop its page cache at the start of every transaction.
I haven't settled on what's a good storage layer for something like this.
tlaverdure · 1h ago
> Another implication of having just one single server: there is only one place for network requests to go.
> In the dream SQLite plus LiteFS world, you have all the advantages of SQLite and all the advantages of a fully replicated multi-writer database setup. Any individual server can go down without causing any downtime for the application as a whole, and every user has a full copy of the application and all its data, running extremely close to them.
These are some of the problems I’m working on solving with Litebase (alpha), which runs on SQLite and distributed storage. Most SQLite solutions rely on replicating data between nodes, but I’m taking a simpler approach by using distributed storage for durability, availability, and replication, combined with a lightweight consensus system between primary and replica nodes.
I’m working on getting a public alpha ready, but you can check out the project so far. I’ll create a thread on Hacker News once things are ready.
nop_slide · 1h ago
Neat! From a high level what's the difference between litebase and Turso?
tlaverdure · 1h ago
A lot of similarities but I believe Turso replicates data between nodes.
Litebase writes data to a tiered storage system (local, network, and object) for easier distribution. Using SQLite's VFS API I've also implemented a different type of storage that allows the primary to write data without full consensus with replicas. It's kind of like a multi-versioned log structured merge tree.
Me, I say "sequeLITE" with the emphasis on the last syllable, but now I'm thinking of switching to "SEQuelite". You'll never catch me pronouncing it "ess-cue-ell" either way dammit!
:)
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...
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 writes.
20 years ago it was MySQL and now it's usually postgres, and these are both still fine. Easier than ever to get setup and going in production. Outside of quite niche needs, I can't understand why one would put up with the pain of using sqlite. Plus it's setting up an even more painful migration later if you do grow.
> I was still caught out by some of the ways things are different.
I feel this is the problem with frameworks, they have to keep evolving and growing and even people who have been doing it 20 years can get caught out.
But the architecture of the Web has basically not changed that much. We are collectively shooting ourselves in the foot.
Definitely not just a Rails thing.
It had the fascinating property of being a full multi-writer SQL engine when using Page Level Conflict Checking. Even more fascinating: every single client itself becomes a writer.
It involves fiddling the database header to ensure you can keep track of the read-set from the VFS layer, by convincing SQLite to drop its page cache at the start of every transaction.
Other than that, a MVCC VFS is relatively straightforward. Here's an in-memory experiment if a few hundred lines of Go: https://github.com/ncruces/go-sqlite3/blob/v0.29.0/vfs/mvcc/...
I haven't settled on what's a good storage layer for something like this.
> In the dream SQLite plus LiteFS world, you have all the advantages of SQLite and all the advantages of a fully replicated multi-writer database setup. Any individual server can go down without causing any downtime for the application as a whole, and every user has a full copy of the application and all its data, running extremely close to them.
These are some of the problems I’m working on solving with Litebase (alpha), which runs on SQLite and distributed storage. Most SQLite solutions rely on replicating data between nodes, but I’m taking a simpler approach by using distributed storage for durability, availability, and replication, combined with a lightweight consensus system between primary and replica nodes.
https://litebase.com
I’m working on getting a public alpha ready, but you can check out the project so far. I’ll create a thread on Hacker News once things are ready.
Litebase writes data to a tiered storage system (local, network, and object) for easier distribution. Using SQLite's VFS API I've also implemented a different type of storage that allows the primary to write data without full consensus with replicas. It's kind of like a multi-versioned log structured merge tree.