It's great! However, it's only meant for local systems. Once you need to connect over a network or robustly handle simultaneous requests, you need something like postgres.
> However, it's only meant for local systems. Once you need to connect over a network or robustly handle simultaneous requests
That’s not really accurate any longer.
Mostly depends on how you layout your tables & files. If you shard the databases then multiple machines can act as writers for their shard. You can also split read requests from write requests and have read only machines scale up/down as much as you’d like. You can use multiple files in a query (there is a limit there).
So for example you can split the user table based on the first letter of the username and then depending on the rest of the database either a database file per user or per customer (organization). Of course more of everything is manual but it’s not as hard as you’d expect if you build for it.
At this point you're building your own networked database using sqlite just as a backing store. You should really reconsider if it's easier than using something designed for it.
If your entire system is sharded by username anyway for other reasons, then maybe what you've described works for you.
Once you release software to the world, the world can choose use it however it wants. Still, the author's of SQLite document their intention for it to be used locally on their "when to use" page.
> SQLite strives to provide local data storage for individual applications and devices.
I have a 100GB SQLite DB in use that gets 8+ million rows added dally.
It's on an off-the-shelf nvme SSD in a "server" that I build from 5+ year old parts.
Those are writes not hits so not directly comparable.
So about one per second (up to ten, less conservatively). I concur. But if you think your site might ever scale beyond that, do yourself a favor and use Postgres from the get-go.
The vast, vast majority of websites never see anything even close to that, so it's a safe bet unless you have some specific reason to expect it to reach that kind of traffic, or you are dealing with workloads that SQLite really does not handle well, e.g. many concurrent writes. And if your workload is mostly reads, then you probably can use a cache layer, which allows SQLite to go further still.
Adding a cache layer to keep using the wrong database is an architecture failure - if you use Postgres from the get go you will not need the cache until way way later on anyway. (Side note, I’m team MySql but the same point applies)
Use WAL (yes this should be the default, or at least explained much better) and you can have one writer, many readers.
Don't move to the network unless you have to - every single request gets massively slowed down because it has replaced local reads with network connections.
Of course if you are building a startup you must consider scaling.
I tried WAL, but like you said, it froze up during multiple writes (when creating records, not updating). I guess what I needed was a write queue - not sure if that exists.
I am curious what is your write pattern? How long do you keep the transactions open for?
There are definitely cases where a single lock does not fit. Even though you can only write one at a time, the transactions ideally should be open for a very short period. That way the throughput, as measured in writes per second, should still be really high.
If there is some other network call in there, SQLite is not a good fit for it and you should definitely move to some other database.
As for write queue, SQLite has that built in if you do transactions — it should unlock other transactions when one is closed.
If you specify IMMEDIATE as the transaction type, it should try to get an exclusive write lock and will timeout after the default time of 30 seconds.
That’s not really accurate any longer.
Mostly depends on how you layout your tables & files. If you shard the databases then multiple machines can act as writers for their shard. You can also split read requests from write requests and have read only machines scale up/down as much as you’d like. You can use multiple files in a query (there is a limit there).
So for example you can split the user table based on the first letter of the username and then depending on the rest of the database either a database file per user or per customer (organization). Of course more of everything is manual but it’s not as hard as you’d expect if you build for it.
https://rivet.dev/blog/2025-02-16-sqlite-on-the-server-is-mi...
If you need sqlite over the network you can look at https://turso.tech/ it’s a almost drop in replacement for sqlite (https://github.com/tursodatabase/turso/blob/main/COMPAT.md)