SQLite Is All You Need(dbpro.app)
dbpro.app
SQLite Is All You Need
https://www.dbpro.app/blog/sqlite-is-all-you-need
64 comments
At some point I thought I would be clever and just backup the file while no transaction is active (or manual WAL checkpointing and no WAL checkpointing active).
Ran into 2.2. https://www.sqlite.org/howtocorrupt.html -- the backup was close()ing the database file descriptor and canceling the SQlite locks.
Ran into 2.2. https://www.sqlite.org/howtocorrupt.html -- the backup was close()ing the database file descriptor and canceling the SQlite locks.
Good call.
I use Litestream for offsite backups. SQLite also has a .backup command:
sqlite3 /path/to/db '.backup /path/to/backup'
or
sqlite3 /path/to/db "VACUUM INTO '/path/to/backup'"
I use Litestream for offsite backups. SQLite also has a .backup command:
sqlite3 /path/to/db '.backup /path/to/backup'
or
sqlite3 /path/to/db "VACUUM INTO '/path/to/backup'"
> the Postgres container you spun up out of habit was never needed
These posts need to stop comparing their contrived use-cases for SQLite to Postgres. Sure, SQLite is all you need if it really is all you need. But Postgres does so much more than just act as a data dump with an SQL engine on top.
Dr. Hipp himself even said that SQLite does not, and will never, compete with the likes of Postgres. It competes with fopen.
These posts need to stop comparing their contrived use-cases for SQLite to Postgres. Sure, SQLite is all you need if it really is all you need. But Postgres does so much more than just act as a data dump with an SQL engine on top.
Dr. Hipp himself even said that SQLite does not, and will never, compete with the likes of Postgres. It competes with fopen.
> SQLite is all you need if it really is all you need.
most use cases are really capable of being satisfied by sqlite, but the "architect" imagines they need more (or is preparing for the potential).
most use cases are really capable of being satisfied by sqlite, but the "architect" imagines they need more (or is preparing for the potential).
> the "architect" imagines they need more (or is preparing for the potential)
I guess I am one of those "architects" that imagines they need an actual date/time storage class instead of some stringly-typed text column that I hope will contain a parsable ISO8601 datetime string when I try to read it back.
Hipp said that it will never be added because it will bloat the size of the embedded object. Because that is what SQLite was designed for: single-user embedded databases. Like the address book on your phone.
I guess I am one of those "architects" that imagines they need an actual date/time storage class instead of some stringly-typed text column that I hope will contain a parsable ISO8601 datetime string when I try to read it back.
Hipp said that it will never be added because it will bloat the size of the embedded object. Because that is what SQLite was designed for: single-user embedded databases. Like the address book on your phone.
> I guess I am one of those "architects" that imagines they need an actual date/time storage class instead of some stringly-typed text column that I hope will contain a parsable ISO8601 datetime string when I try to read it back.
To be honest if you're using JSON at any point in your stack you have the same issue.
To be honest if you're using JSON at any point in your stack you have the same issue.
In fairness, sqlite is perfectly happy with Julian dates or Unix timestamps (that’s the affinity of a column typed “datetime” in non-strict mode) and timestamp(tz) are nothing to write home about except in complaint.
so now we've traded a text column for an int column that still can't validate that the number is actually externally consistent with the real world.
Is there an invalid unix timestamp? What is there to validate?
Well for one we should probably validate that the number is smaller than the total life of the physical universe.
SQLite will gladly store a u64::MAX as a "unix timestamp" despite it being about 300x larger than the number of seconds that the universe and everything in it has existed.
Try reading that back in any application date/time code and your app probably crashes immediately.
SQLite will gladly store a u64::MAX as a "unix timestamp" despite it being about 300x larger than the number of seconds that the universe and everything in it has existed.
Try reading that back in any application date/time code and your app probably crashes immediately.
> SQLite will gladly store a u64::MAX as a "unix timestamp"
SQLite does not support unsigned integers.
> Try reading that back in any application date/time code and your app probably crashes immediately.
Postgres will happily ingest and produce dates in the 280th millenium, which will also crash your application if its datetime type can’t handle that shrug.
You can add a check constraint that your field passes through SQLite’s datetime functions and it’ll be clamped between the years 0 and 9999. Or you can put in your own limits matching your application layer, or your application’s business logic. That’s what check constrains are for (amongst other things).
SQLite does not support unsigned integers.
> Try reading that back in any application date/time code and your app probably crashes immediately.
Postgres will happily ingest and produce dates in the 280th millenium, which will also crash your application if its datetime type can’t handle that shrug.
You can add a check constraint that your field passes through SQLite’s datetime functions and it’ll be clamped between the years 0 and 9999. Or you can put in your own limits matching your application layer, or your application’s business logic. That’s what check constrains are for (amongst other things).
> SQLite does not support unsigned integers.
omg you're right. Another example of SQLite's type-flimsyness.
> You can add a check constraint
So now we're just layering on our own brittle validation and runtime checks to make up for sqlite's woeful deficiency supporting even basic domain data types.
omg you're right. Another example of SQLite's type-flimsyness.
> You can add a check constraint
So now we're just layering on our own brittle validation and runtime checks to make up for sqlite's woeful deficiency supporting even basic domain data types.
> omg you're right. Another example of SQLite's type-flimsyness.
You're just firing on all the bullshit cylinders in your little motorised gish gallop aren't you?
The only RDBMS in widespread use which supports unsigned integers is mysql. Not postgres, not oracle, not sql server, not db2, not snowflake, not databricks.
> So now we're just layering on our own brittle validation and runtime checks to make up for sqlite's woeful deficiency supporting even basic domain data types.
Ah yes, the "brittle validation" of actually validating the thing you purported to care about two comments ago. Amazing.
You're just firing on all the bullshit cylinders in your little motorised gish gallop aren't you?
The only RDBMS in widespread use which supports unsigned integers is mysql. Not postgres, not oracle, not sql server, not db2, not snowflake, not databricks.
> So now we're just layering on our own brittle validation and runtime checks to make up for sqlite's woeful deficiency supporting even basic domain data types.
Ah yes, the "brittle validation" of actually validating the thing you purported to care about two comments ago. Amazing.
And how would you validate that? Which arbitrary cutoff do you find reasonable?
And if your app crashes on an input like that, you should pick a different date Library or stop and rethink your coding skills.
And if your app crashes on an input like that, you should pick a different date Library or stop and rethink your coding skills.
I was such a big sqlite fan that I used it for my data-intensive startup. Once I actually started scaling it up I ran into crippling file system race conditions. This was because we were hosted on a distributed file system in the cloud which I learned is very very bad for sqlite.
So I had to migrate the production db under live load from sqlite to mysql which was a quite ...intense week. I still like sqlite but I'd be wary of using it again for a usecase like mine.
So I had to migrate the production db under live load from sqlite to mysql which was a quite ...intense week. I still like sqlite but I'd be wary of using it again for a usecase like mine.
SQLite creators explicitly warn against using SQLite on a networked file system btw.
If you could conceivably use multiple processes at once to access the database, and not just as an edge case, you need something more than sqlite.
Sqlite does support multi-process access correctly, but the performance is abysmal as it locks the entire file for any write transaction. Client/server databases have much smarter concurrency.
Sqlite does support multi-process access correctly, but the performance is abysmal as it locks the entire file for any write transaction. Client/server databases have much smarter concurrency.
It’s perfectly fine if your write throughput is low, especially in wal mode. Multiprocess does not actually change much if anything, even in multithreaded mode you want every thread to have its own connection and to have a good handle on who is writing when.
Regardless, it's a sign sqlite is not right for you. You should start with postgres if you aren't sure your application will always be good with sqlite. It'll save you a migration, and doesn't really cost anything to start your project with postgres instead of sqlite.
There is an interesting pattern SyncLite (https://github.com/syncliteio/SyncLite) attempts to solve to bring the best of both SQLite and Postgres with one or more SQLite databases directly serving the application while SyncLite replicating/consolidating data from all those SQLite databases into a centralized PostgreSQL database..
It sounds like a solution trying to sell itself. While you probably can do something like that and it might even be useful for some applications, I can't imagine it's a very good generic solution - you probably want to do it yourself if you want to do it.
> It competes with fopen.
No, it actually competes with Excel.
At least in the finance world, there are still a million small processes driven by an Excel spreadsheet put together in an afternoon by an intern 20 years ago. If it is a really business critical process, then the input is usually a csv file, read by an Excel macro.
Because Excel is user friendly, and "is good enough". Mostly...
No, it actually competes with Excel.
At least in the finance world, there are still a million small processes driven by an Excel spreadsheet put together in an afternoon by an intern 20 years ago. If it is a really business critical process, then the input is usually a csv file, read by an Excel macro.
Because Excel is user friendly, and "is good enough". Mostly...
and for that matter, fopen is all you need, you do not need sqlite
For example you cannot have concurrent access. As soon as you need a worker process and a web process SQLite is out. Or if you are trying to use it as a vector db all of those vector searches will block a node event loop.
Article mentions WAL and how this sentiment is about 16 years out of date. But also, you can duplicate databases for out-of-order processes.
But also also, if you have higher concurrency requirements - e.g. multiple servers, one database - or a more write-heavy use case, sqlite is no longer the right choice.
But also also, if you have higher concurrency requirements - e.g. multiple servers, one database - or a more write-heavy use case, sqlite is no longer the right choice.
Yeah it blocks for the 0.001ms it takes for 99.99% of queries to come back. Or you can enable WAL and allow readers to read at the same time as somebody is writing.
WAL mode allows concurrency with reads and writes fwiw
That site is hard on my eyes
The SQLite documentation says that
"SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.
The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time."
"SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.
The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time."
I remember this section for the 200 queries per page thing, which is a huge difference between sqlite and most databases. Most databases run in a separate process and use a network boundary to separate it from the server processes (socket connection or even http), which alone exacerbates the n+1 problem, and / or puts pressure on the amount of queries one should trigger per page view. SQLite doesn't care because it runs as part of the server process.
I use a in-memory database per unit test with both rocksdb and sqlite, it is a game-changer to get better quality tests.
Overall premise is wrong though. Moving the database out of process will change performance characteristics and data architecture too much and will cause massive headaches at exactly the time when you are trying to scale with success. You should have out of process performance tests early to catch these issues, even if you do deploy a single node.
If success can be satisfied with a single node and you are satisfied with availability and recovery that gives you then great, but it isn't all I need.
Overall premise is wrong though. Moving the database out of process will change performance characteristics and data architecture too much and will cause massive headaches at exactly the time when you are trying to scale with success. You should have out of process performance tests early to catch these issues, even if you do deploy a single node.
If success can be satisfied with a single node and you are satisfied with availability and recovery that gives you then great, but it isn't all I need.
lobster.rs also started using sqlite, i've been using exclusively using sqlite for most of my projects.
apps appear much faster because db is on the same server
apps appear much faster because db is on the same server
How are lobster.rs handling writes? Are they queuing them synchronously?
https://sqlite.org/wal.html presumably.
But like any message board it will get peak time hits
So lunchtime in America there will be multiple people posting comments at the same time
And Sqllite only allows one writer, so what happens if multiple people send their comments at once
So are they using ultra fast storage on the host or queues or other
So lunchtime in America there will be multiple people posting comments at the same time
And Sqllite only allows one writer, so what happens if multiple people send their comments at once
So are they using ultra fast storage on the host or queues or other
SQLite with WAL on a standard NVMe drive will easily do thousand of writes per second.
Yuck, the LLM writing style is obvious, and strongly suggests you didn't do the work you claim. Why should we read this?
I could not agree more KISS is always the way to go.
For those people that are interested you can have a sqld instance on a different VM with S3 backup.
On my case I use k8s and the backend pod use rust libsqld crate with local first sql database file with remote sync to sqld.
When pod start if db file is not available libsqld will try recover it from slqd otherwise it will just load local db file and sync.
I love SQLite, but my clients expect minimal data loss and downtime when one of my servers goes down.
How are people solving that issue with SQLite? Everytime I’ve investigated it, it seems like the state of the art is not very battle tested WAL shipping solutions.
If I’m setting something like that up, suddenly running Postgres with its battle tested replication starts to look not so much more complicated in comparison.
How are people solving that issue with SQLite? Everytime I’ve investigated it, it seems like the state of the art is not very battle tested WAL shipping solutions.
If I’m setting something like that up, suddenly running Postgres with its battle tested replication starts to look not so much more complicated in comparison.
litestream is good for this.
I use it in several projects. You're still open to a few seconds of possible downtime (depending how often you flush the WAL to your remote location, and how bursty your writes are), and you need to have all of your db on disk on the process doing the reads, but it's been great.
We serve multi million MAU on sqlite orchestrated through durable objects. It's not the most complex thing in the world but it goes further than CRUD. It costs us such a small amount of money for what it does. Our PG cluster was orders of magnitude more expensive.
The tests and the numbers are interesting. The LLM writing style? Insufferable.
It really wouldn’t have taken much effort to cut out the worst of the AI fluff, and maybe add some human touches
It really wouldn’t have taken much effort to cut out the worst of the AI fluff, and maybe add some human touches
"No version upgrades, no connection limits, no pooler, no failover drill, no separate thing to monitor, no separate thing to pay for"
Inspired me to finally build (well, vibe code) a cliché detector https://tools.simonwillison.net/llm-cliche-highlighter
Inspired me to finally build (well, vibe code) a cliché detector https://tools.simonwillison.net/llm-cliche-highlighter
SQLite is an incredible database engine. I've built my entire AI harness entirely on top of it.
And what do you do when you need more than one server, for redundancy or scaling to more servers?
Someone pointed to moving to postgres or comparable, but another option is e.g. rqlite (https://github.com/rqlite/rqlite).
You move to postgres at that point. Though if you can't scale to $1marr with sqllite then ehhh
If you think this might happen then you should start with postgres - it'll save you a migration, and doesn't cost much.
So you don't have a redundant server until $1marr?
That means you also don't apply updates to the server/kernel that require a reboot until then? Or you accept the downtime? And what happens if it doesn't boot properly?
That means you also don't apply updates to the server/kernel that require a reboot until then? Or you accept the downtime? And what happens if it doesn't boot properly?
None of that is an issue depending on what your software actually does. Now sure if you're doing something like logging, or you're cloudflare, or meta or some shit then yeah it's not going to cut it.
Generally I target enterprise b2b customers, they use the application from 9-5 generally. Weekly scheduled downtime is not the end of the world.
If it doesn't boot properly, which has never happened, I build a new server - takes 10 minutes max. Litestream keeps database relatively fresh, so it gets pulled on boot. Floating IP points at new server. We do this in dev regularly, the only difference in prod is a few variables.
Even if your target is higher uptime or bigger scale, you can go far further than you think on sqlite - it's multitudes of magnitude quicker than postgres for most general use cases.
Most software projects go nowhere. YAGNI. Build something valuable and people will put up with bullshit for a while.
Generally I target enterprise b2b customers, they use the application from 9-5 generally. Weekly scheduled downtime is not the end of the world.
If it doesn't boot properly, which has never happened, I build a new server - takes 10 minutes max. Litestream keeps database relatively fresh, so it gets pulled on boot. Floating IP points at new server. We do this in dev regularly, the only difference in prod is a few variables.
Even if your target is higher uptime or bigger scale, you can go far further than you think on sqlite - it's multitudes of magnitude quicker than postgres for most general use cases.
Most software projects go nowhere. YAGNI. Build something valuable and people will put up with bullshit for a while.
"The limits are here too, and they are real."
You are right to push back on that.
You are right to push back on that.
There’s also the article “Consider SQLite”: https://blog.wesleyac.com/posts/consider-sqlite
The above article was what convinced me to use SQLite in my new business. 5 years later, serving 120+ million requests per month and still working great. To be fair, most of those requests are served out of redis, but I’m still running off of cheap digital ocean droplets.
The above article was what convinced me to use SQLite in my new business. 5 years later, serving 120+ million requests per month and still working great. To be fair, most of those requests are served out of redis, but I’m still running off of cheap digital ocean droplets.
Author here. This started because I read Evan Hahn's STRICT tables post [1] last week and got curious how far "just use SQLite" actually holds up under real load, not toy benchmarks.
So I built a small social app (Chirp: 50k users, 1M posts, ~2.5M follows) in one SQLite file, put it behind a plain Node server, and load tested it properly: real HTTP, real JSON serialization, autocannon hammering it over sockets. The worst query in the app (home timeline, which joins follows against posts, counts likes, sorts by time) still did 3,654 req/s on an M1 laptop, which works out to 315M requests/day.
The part I didn't expect going in: WAL vs the old rollback journal isn't a minor tuning knob, it's the whole story. Same query, same data, one pragma changed, and p99 read latency goes from 4.4ms to 133ms once you add a writer. That's the "SQLite locks and blocks everyone" reputation, and it's from a database mode most people don't even use anymore.
I also tried to be honest about where it falls over: reads stop scaling once anything writes (page cache invalidation, not lock contention), there's one write lock for the whole DB, and there's no failover if the box dies. Those are real constraints, not disclaimers.
Also benchmarked Node+better-sqlite3 vs Bun+bun:sqlite since I had the harness built anyway. Bun wins on cheap queries, Node wins on the expensive ones. Wasn't expecting a split.
Happy to answer questions on methodology, the STRICT table stuff, or why we ended up building this the way we did.
[1] https://evanhahn.com/prefer-strict-tables-in-sqlite/
So I built a small social app (Chirp: 50k users, 1M posts, ~2.5M follows) in one SQLite file, put it behind a plain Node server, and load tested it properly: real HTTP, real JSON serialization, autocannon hammering it over sockets. The worst query in the app (home timeline, which joins follows against posts, counts likes, sorts by time) still did 3,654 req/s on an M1 laptop, which works out to 315M requests/day.
The part I didn't expect going in: WAL vs the old rollback journal isn't a minor tuning knob, it's the whole story. Same query, same data, one pragma changed, and p99 read latency goes from 4.4ms to 133ms once you add a writer. That's the "SQLite locks and blocks everyone" reputation, and it's from a database mode most people don't even use anymore.
I also tried to be honest about where it falls over: reads stop scaling once anything writes (page cache invalidation, not lock contention), there's one write lock for the whole DB, and there's no failover if the box dies. Those are real constraints, not disclaimers.
Also benchmarked Node+better-sqlite3 vs Bun+bun:sqlite since I had the harness built anyway. Bun wins on cheap queries, Node wins on the expensive ones. Wasn't expecting a split.
Happy to answer questions on methodology, the STRICT table stuff, or why we ended up building this the way we did.
[1] https://evanhahn.com/prefer-strict-tables-in-sqlite/
Have you read https://www.sqlite.org/howtocorrupt.html? Section 1.2 addresses the exact scenario you wrote about in "Backups are a file copy". You got lucky with your testing, and didn't manage to copy the database in the middle of writing a transaction to the WAL file. Backing up an SQLite database with `cp` can produce corrupt backups if you lose the race condition, an unlikely but possible scenario,
Your later advice about "VACUUM INTO (backupfile)" is good, though: the SQLite manual guarantees that that's safe. But it's not safe to back up with `cp` if there are transactions currently writing to the DB: that has a chance of copying files in an inconsistent state, resulting in a corrupt DB and data loss you don't realize has happened until you restore the backup and find out there are some rows missing, or some rows have an invalid mix of old and new data.
Your later advice about "VACUUM INTO (backupfile)" is good, though: the SQLite manual guarantees that that's safe. But it's not safe to back up with `cp` if there are transactions currently writing to the DB: that has a chance of copying files in an inconsistent state, resulting in a corrupt DB and data loss you don't realize has happened until you restore the backup and find out there are some rows missing, or some rows have an invalid mix of old and new data.
Lots of interesting stuff in your post, thank you for sharing it. But please just write in your own voice - LLMs just make it worse.
This comment was shadowbanned because an LLM wrote it, and HN uses an LLM detector.
> WAL vs the old rollback journal isn't a minor tuning knob, it's the whole story.
That gave it away for me. I still appreciate the information contained in the reply but it was obvious. FWIW I don’t mind LLM content as long as I’m learning something.
That gave it away for me. I still appreciate the information contained in the reply but it was obvious. FWIW I don’t mind LLM content as long as I’m learning something.
"Backups are a file copy. [...] you can back up a live SQLite database, under write load, without stopping anything."
This is straight out of section 1.2 of https://www.sqlite.org/howtocorrupt.html. Yes, you can do that, and sometimes you will end up with a valid, non-corrupt backup. But it's timing-dependent: lose the race and you'll end up backing up a partially written transaction, making the backup corrupt. They didn't end up losing that race when they wrote the article, but that doesn't mean it is safe 100% of the time.
The section later on about running "VACUUM INTO backup-$(date +%F).db" is 100% safe, though: SQLite guarantees that you'll get consistent state if you do that.