There's some bad advice in the article:

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

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

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

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.

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.

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

"The limits are here too, and they are real."

You are right to push back on that.

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.

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

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?

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.

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/

[flagged]