← read more

SQLite in Production: Where the Small Database Actually Fits

SQLite handles more real web workloads than its toy reputation suggests, and fails in specific, predictable ways. When the single-file database is the right answer, where it stops working, and the setup that keeps locking and backups from surprising you.

The database that never makes the architecture diagram

Most project proposals name Postgres or MySQL by default, and for anything multi-user that's the right call. But there's a band of workloads where SQLite is genuinely the better fit, and it's wider than most teams assume: internal tools used by five people, a CMS-backed marketing site that gets a page edit twice a week, an embedded stats collector, a CLI that needs a queryable store, the test suite of a big Laravel app.

The old objection, "it's fine until two people write at once", describes an older SQLite. Modern versions with WAL mode allow readers and one writer to proceed together, and the write lock is held for the duration of a statement, not a request. For low write volumes, which covers the majority of small sites, that's plenty.

It also buys things no server database does. One file means one thing to back up, version, or copy to a laptop to debug. No connection pooler, no service to restart after a server reboot, no max_connections to tune. A whole tier of ops work simply doesn't exist.

Where it breaks, and how it breaks

The failure modes are specific, so you can check for them in advance:

  • Concurrent writers. WAL allows one writer at once. A tool with five users editing is fine. A checkout flow where dozens of orders land in the same busy minute will produce database is locked errors under load. If you can't bound the write rate, SQLite is the wrong shape for the job.
  • Multiple app servers. The database lives in a file on one machine, so a second web server can't share it. The moment your deployment story goes "two instances behind a load balancer", you've outgrown it.
  • Background workers. A queue worker on the same box works, but every extra process competing for the write lock increases locking pressure. Queues in particular want their own storage, and pretending otherwise produces jobs that fail under contention.
  • Schema changes on big tables. Without a ALTER TABLE worth the name, changing a large table means recreating it and copying rows. Fine at a hundred thousand rows, a different conversation at fifty million.

None of these are hidden. You can evaluate all four on a whiteboard before writing a line of code, which is more than can be said for most database decisions.

Getting the setup right

SQLite deployed badly is a self-inflicted wound. Three settings carry most of the weight:

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;

WAL lets reads proceed during writes. busy_timeout turns most lock contention from an instant error into a short wait, which absorbs the bursts a small tool actually sees. foreign_keys matters because SQLite ships with referential integrity switched off, a default that has quietly corrupted more projects than any bug in the engine.

Backups are the pleasant surprise. Since everything is one file, a tool like Litestream can replicate the database to object storage continuously, giving you point-in-time restore with zero server processes to babysit. For a small site, this is a simpler recovery story than most managed-database setups provide, and it costs pennies.

There's one trap worth naming for Laravel teams: the test suite runs SQLite by default while production runs MySQL, and the two differ in real ways, from case sensitivity to date functions to how strict they are about types. Tests catching a bug that only exists in the test database is its own special kind of wasted afternoon. Either test against the production engine, or know precisely which divergences you've accepted.

The honest summary

SQLite is not a scaled-down Postgres. It's a different point in the design space: excellent for one machine, low write volume, and low operational appetite; wrong for write-heavy, multi-server, or analytics-heavy work. If the workload fits, skipping the database server removes a whole category of failures at a tier of the stack where, honestly, nobody wanted to be paged about them anyway.