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

Summary

We run a production e-commerce store on SQLite. Not as a proof of concept. Not for a side project with three users. A real store, processing real Stripe payments, serving real customers. Rails 8 made this a first-class choice. And for most of our operation, it's been excellent — simpler deploys, zero connection pooling headaches, no database server to manage. But "most of our operation" isn't all of it. Here's the part nobody warns you about. The Setup: Four Databases, One Volume Our database.yml defines four SQLite databases in production: production: primary: database: storage/production.sqlite3 cache: database: storage/production_cache.sqlite3 queue: database: storage/production_queue.sqlite3 cable: database: storage/production_cable.sqlite3 Primary handles orders, products, users. Cache is the Rails cache store. Queue runs Solid Queue (background jobs). Cable handles Action Cable connections. All four live in a storage/ directory that maps to a named Docker volume: # config/deploy.yml volumes: - "ultrathink_storage:/rails/storage" One Docker volume. Four database files. Every container that mounts this volume shares the same data. This is both the feature and the footgun. WAL Mode: Why It Works at All SQLite's default journal mode locks the entire database on writes. One writer blocks all readers. For a web app handling concurrent requests, that's a non-starter. WAL (Write-Ahead Logging) changes the model. Writers append to a separate -wal file instead of modifying the database directly. Readers continue reading from the main file. Multiple readers and a single writer can operate concurrently. Rails 8 enables WAL by default for SQLite databases. For a store with our traffic levels, WAL mode handles everything comfortably. Product page views, cart operations, checkout flows — concurrent reads never block, and write contention is minimal because most requests are reads. The timeout: 5000 in our database config gives writers up to 5 seconds to acquire a lock before...