The Stack Exchange link is incorrect about -ite being etymologically derived from lithos, as one of the commenters there noted. Maybe a misunderstanding of this wiktionary note or similar:
> But by the Hellenistic period, both the masculine -ίτης (-ítēs) and the feminine -ῖτις (-îtis) became very productive in forming technical terms for products, diseases, minerals and gems (adjectives with elliptic λίθος (líthos, “stone”)), ethnic designations and Biblical tribal names.
The meaning of that is not that -ite is etymologically derived from lithos. It’s trying to say that mineral names like “hematite” (αἱματίτης - literally “blood-red”) are originally adjectives agreeing with an implied noun lithos.
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!
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.
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
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.
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.
> 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
I think its more about the issue that databases like postgres are too much "magic". With Sqlite you see your database file, its a physical something. You want to move it to another server, its just a copy action, no dump/restore. You want to "upgrade", there is no mess like with postgres.
You want to create client separation, ... its just a file per client.
> although even there I have to be aware of the one writer only limitation.
The writer limit has not been a limit forever. You can easily do 20k writes per second, what is way beyond what most system anyway. You want 100k? A slightly different access pattern and voila. You want 1 million ... There are ways around it. If you put Sqlite vs Postgres for instance, ironically, seen Sqlite winning against postgres despite that single writer.
The whole hands on "we know what file is, where it is, and how to gain access to it" is really amazing. The whole "do not need to think about how to secure it on the network" also helps a lot.
Do i use Sqlite in production? No ... Postgres its extension support just rules. But i see the appeal for many.
> I think its more about the issue that databases like postgres are too much "magic"
The problem with that thought is that in order for sqlite to be as full featured as it is, it also has a lot of "magic". I've also had to dive into the postgres code a few times to understand how it's doing something, and I've generally found it well structured and easy to navigate, and surprisingly terse - there's not that much "magic" really.
I’ve been using SQLite for a lot of my backends since way before it’s cool. Back then I was frowning at all the people/tutorials spinning up MySQL/Postgres/heaven forbid Mongo for 1 read per second, 1 write per 10 seconds projects.
Now I’m frowning at people doing all sorts of gymnastics to beat SQLite into shape.
It’s always the same, people without a mind of their own looking at blog posts from cool kids and deciding they have to do it themselves too (not all the adopters of course, but most of them).
One reason I use SQLite in production is because of Litestream. I can setup 2 or more VPSes hosting my Django app backed by the same SQLite database behind a load balancer for redundancy.
Thanks to Litestream, it's easier to scale horizontally when using SQLite than with other databases when self-hosting.
For a serious web project it's hard to imagine picking SQLite, unless you're using a proper "serverified" SQLite service like Turso or Cloudflare D1. I'd think zero-downtime deployments alone would be a dealbreaker for many. Then there's high-availability and horizontal scaling.
I love that SQLite lets me run the app locally with no "setup". And so I actually kinda like Turso and D1 for bridging the gap. I can have the best of both worlds: no setup, AND primetime-ready deployment.
You can do zero downtime deployments with SQLite just fine if your app can gracefully handle the database file being locked. Just spin up your new version talking to the same database, be mindful about how you handle migrations, and shut down the old version.
All databases are painful in their own way. I've used all three at various times in my career, and I think SQLite behaves quite predictably, which has made it a lot easier for me personally to administrate.
If I had to start something new, I'd use SQLite until at least high 5 digit queries per second. Maybe more.
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.
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
What's new is that it's now being recommended for use in production, not just development. It's also now what they want you to use for caching, artifact storage and just about everything else that use to use "Technology X".
I can see the reasoning behind it. Simplicity. But I'm waiting for the horror stories like this one to die down before making a final judgement. It just doesn't _feel_ right to me after doing it differently for 20+ years.
Kind of but not really. Rails has defaulted to creating a project with an SQLite database in development for awhile now. But the expectation has always been you move to what you need.
SQLite has simply become "good enough" for production. So now you can take that dev default and simply deploy it for apps of a certain size and category if you want.
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.
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.
Yes. It acts exactly like WAL mode in that regard. Readers operate on a snapshot and are never blocked, writers block each other. Upgrading a reader to a writer fails immediately if there is a concurrent writer, however BEGIN IMMEDIATE transactions never do.
One of the issues I have with the mvsqlite approach (beyond not being able to convince myself that it's correct — I'm sure it is I'm just unsure I could replicate it faithfully) is that I don't think the VFS gets any peak at the BEGIN CONCURRENT syntax, so suddenly all your transactions can fail because of write-write conflicts. It's not opt-in.
> 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.
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.
I have a toy web application that accepts a very, very low rate of writes. It's almost all reads.
It is implemented like this:
- The front end uses react to draw a UI.
- It fetches data from a JSON file stored on S3.
- When we get a write request, a lamdba function reads the JSON file, parses it, adds new data, and writes back to S3.
The main selling point for me is that almost all of it is static assets, which are cheap and simple to host, and the tiny bit of "back end" logic is just one nodejs function.
The previous implementation used SQLite and a golang back end, but I eventually got tired of having to maintain a virtual machine to host it.
I ever plan do it with sqlite, loading it at memory during app start and flush data to s3 during runtime but it create more corner cases and logic to handle.
You can set concurrency limits per function on AWS, so you can apply a hard limit on your function to only have a single invocation running at the same time. That should give you a guarantee that data isn't lost without the producer noticing.
Weird title, the bulk of the article talked about the same stuff various Rails/SQLite talks have mentioned, the positivity at the end is nice I guess.
The real nice thing about SQLite in prod though is how much it simplifies development and deployment. Let's be real, a lot of apps people build are relatively small, basic CRUD apps that see modest usage or are even internal-only, SQLite is perfect for that. And SQLite can scale reasonably well on modern hardware. It follows the premise of Rails quite nicely: it gets you up and running, scaling to the point you're making a decent amount of money and then you can figure out how to scale beyond that, if you need to.
I really don't get the "use sqlite in production" thing.
What about all the important things you need in a database such as multiuser/remote access and other such things?
What do the "sqlite in production" people do when they need to examine the database/resolve an issue/do development? Also what about the limitations of sqlite in modifying database structure?
A bit off topic but:
The name doesn't really contain "lite". It's SQL-ite. So the suffix is "ite": [0]: https://english.stackexchange.com/a/34010The Stack Exchange link is incorrect about -ite being etymologically derived from lithos, as one of the commenters there noted. Maybe a misunderstanding of this wiktionary note or similar:
> But by the Hellenistic period, both the masculine -ίτης (-ítēs) and the feminine -ῖτις (-îtis) became very productive in forming technical terms for products, diseases, minerals and gems (adjectives with elliptic λίθος (líthos, “stone”)), ethnic designations and Biblical tribal names.
The meaning of that is not that -ite is etymologically derived from lithos. It’s trying to say that mineral names like “hematite” (αἱματίτης - literally “blood-red”) are originally adjectives agreeing with an implied noun lithos.
Comments like this are why I read HN ...
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!
:)
In my head, I call it "ess-kew-lite".
Yeah the first time I heard Hipp pronounce it, my brain glitched.
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...
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.
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
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.
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.
> 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
I think its more about the issue that databases like postgres are too much "magic". With Sqlite you see your database file, its a physical something. You want to move it to another server, its just a copy action, no dump/restore. You want to "upgrade", there is no mess like with postgres.
You want to create client separation, ... its just a file per client.
> although even there I have to be aware of the one writer only limitation.
The writer limit has not been a limit forever. You can easily do 20k writes per second, what is way beyond what most system anyway. You want 100k? A slightly different access pattern and voila. You want 1 million ... There are ways around it. If you put Sqlite vs Postgres for instance, ironically, seen Sqlite winning against postgres despite that single writer.
The whole hands on "we know what file is, where it is, and how to gain access to it" is really amazing. The whole "do not need to think about how to secure it on the network" also helps a lot.
Do i use Sqlite in production? No ... Postgres its extension support just rules. But i see the appeal for many.
> I think its more about the issue that databases like postgres are too much "magic"
The problem with that thought is that in order for sqlite to be as full featured as it is, it also has a lot of "magic". I've also had to dive into the postgres code a few times to understand how it's doing something, and I've generally found it well structured and easy to navigate, and surprisingly terse - there's not that much "magic" really.
> You want to "upgrade", there is no mess like with postgres.
Sure, if you don't mind downtime?
I’ve been using SQLite for a lot of my backends since way before it’s cool. Back then I was frowning at all the people/tutorials spinning up MySQL/Postgres/heaven forbid Mongo for 1 read per second, 1 write per 10 seconds projects.
Now I’m frowning at people doing all sorts of gymnastics to beat SQLite into shape.
It’s always the same, people without a mind of their own looking at blog posts from cool kids and deciding they have to do it themselves too (not all the adopters of course, but most of them).
One reason I use SQLite in production is because of Litestream. I can setup 2 or more VPSes hosting my Django app backed by the same SQLite database behind a load balancer for redundancy.
Thanks to Litestream, it's easier to scale horizontally when using SQLite than with other databases when self-hosting.
For a serious web project it's hard to imagine picking SQLite, unless you're using a proper "serverified" SQLite service like Turso or Cloudflare D1. I'd think zero-downtime deployments alone would be a dealbreaker for many. Then there's high-availability and horizontal scaling.
I love that SQLite lets me run the app locally with no "setup". And so I actually kinda like Turso and D1 for bridging the gap. I can have the best of both worlds: no setup, AND primetime-ready deployment.
You can do zero downtime deployments with SQLite just fine if your app can gracefully handle the database file being locked. Just spin up your new version talking to the same database, be mindful about how you handle migrations, and shut down the old version.
All databases are painful in their own way. I've used all three at various times in my career, and I think SQLite behaves quite predictably, which has made it a lot easier for me personally to administrate.
If I had to start something new, I'd use SQLite until at least high 5 digit queries per second. Maybe more.
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.
Yay! The SQLite posts are back :)
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
What's new is that it's now being recommended for use in production, not just development. It's also now what they want you to use for caching, artifact storage and just about everything else that use to use "Technology X".
I can see the reasoning behind it. Simplicity. But I'm waiting for the horror stories like this one to die down before making a final judgement. It just doesn't _feel_ right to me after doing it differently for 20+ years.
Sqlite has always been the default database for Rails, even though it probably hasn't been the most common deployment choice.
Kind of but not really. Rails has defaulted to creating a project with an SQLite database in development for awhile now. But the expectation has always been you move to what you need.
SQLite has simply become "good enough" for production. So now you can take that dev default and simply deploy it for apps of a certain size and category if you want.
> 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.
Definitely not just a Rails thing.
I'm sad this project fizzled: https://github.com/losfair/mvsqlite
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.
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.
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.
Does that just do Database Level Conflict Checking?
Yes. It acts exactly like WAL mode in that regard. Readers operate on a snapshot and are never blocked, writers block each other. Upgrading a reader to a writer fails immediately if there is a concurrent writer, however BEGIN IMMEDIATE transactions never do.
One of the issues I have with the mvsqlite approach (beyond not being able to convince myself that it's correct — I'm sure it is I'm just unsure I could replicate it faithfully) is that I don't think the VFS gets any peak at the BEGIN CONCURRENT syntax, so suddenly all your transactions can fail because of write-write conflicts. It's not opt-in.
> 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.
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.
Neat! From a high level what's the difference between litebase and Turso?
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.
Awesome, going to follow along the development!
I have a toy web application that accepts a very, very low rate of writes. It's almost all reads.
It is implemented like this:
- The front end uses react to draw a UI.
- It fetches data from a JSON file stored on S3.
- When we get a write request, a lamdba function reads the JSON file, parses it, adds new data, and writes back to S3.
The main selling point for me is that almost all of it is static assets, which are cheap and simple to host, and the tiny bit of "back end" logic is just one nodejs function.
The previous implementation used SQLite and a golang back end, but I eventually got tired of having to maintain a virtual machine to host it.
Easy and useful, usually the basic is better.
I ever plan do it with sqlite, loading it at memory during app start and flush data to s3 during runtime but it create more corner cases and logic to handle.
Even with very small number of requests - what happens when you have two concurrent ones?
You can set concurrency limits per function on AWS, so you can apply a hard limit on your function to only have a single invocation running at the same time. That should give you a guarantee that data isn't lost without the producer noticing.
Weird title, the bulk of the article talked about the same stuff various Rails/SQLite talks have mentioned, the positivity at the end is nice I guess.
The real nice thing about SQLite in prod though is how much it simplifies development and deployment. Let's be real, a lot of apps people build are relatively small, basic CRUD apps that see modest usage or are even internal-only, SQLite is perfect for that. And SQLite can scale reasonably well on modern hardware. It follows the premise of Rails quite nicely: it gets you up and running, scaling to the point you're making a decent amount of money and then you can figure out how to scale beyond that, if you need to.
Just use Turso
I really don't get the "use sqlite in production" thing.
What about all the important things you need in a database such as multiuser/remote access and other such things?
What do the "sqlite in production" people do when they need to examine the database/resolve an issue/do development? Also what about the limitations of sqlite in modifying database structure?