SQLite in Production: Lessons from Running a Store on a Single File

A surprising stack choice

Ultrathink says it runs a real e-commerce store on SQLite in production — not a toy, not a demo. Rails 8 made the choice practical: no DB server to babysit, simpler deploys, and fewer connection-pooling headaches. They split data across four SQLite files (primary, cache, queue, cable) that all live on the same Docker volume and are mounted by every app container. Simple setup. Big convenience. Also, as they admit, a latent footgun.

WAL mode to the rescue — mostly

SQLite’s default locking model won’t cut it for the web: one writer can block many readers. WAL (Write-Ahead Logging) changes that by appending writes to a -wal file so readers can keep going. Rails 8 enables WAL by default, and with a 5000ms timeout for writers the team reports smooth behavior for their largely read-heavy traffic. Reads breeze through, writes rarely collide. It’s a neat performance surprise — SQLite’s ceiling is higher than many engineers assume.

The deploy that cost two orders

But it has been reported that one afternoon of rapid-fire deploys broke that peace. Eleven commits in two hours, blue‑green rollouts that briefly left multiple containers mounting the same storage at once. Overlapping drain windows multiplied the processes with the same WAL open. The punchline? Customers were charged in Stripe, payment intents show success, but two order rows vanished from the DB. Ultrathink diagnosed it with sqlite_sequence: the sequence number implied 17 IDs had been issued while MAX(id) returned 15. Money in, records out. That moment — the stomach-drop realization that the ledger and the bank don’t match — is the emotional core of this tale.

The fix: boring, human, effective

The remedy was not a deep technical rabbit hole. They stopped deploying every ten minutes. They added a governance rule to batch related changes and avoid rapid-fire pushes (it’s now explicit in their CLAUDE.md). The takeaway is clear: SQLite can be a sane production choice for the right workload — but some risks aren’t at the DB level, they’re at your deployment rhythm and storage topology. Want simplicity? Fine. But you still need discipline.

Sources: ultrathink.art, Hacker News