SQLite in Production: Lessons from Running a Store on a Single File
5 hours ago
- #Database Management
- #SQLite
- #Production Deployment
- Running a production e-commerce store on SQLite is viable, with Rails 8 supporting it as a first-class choice, simplifying deployments and eliminating database server management.
- Using four SQLite databases (primary, cache, queue, cable) in a single Docker volume enables data sharing across containers but can lead to concurrency issues during rapid deploys.
- SQLite's WAL (Write-Ahead Logging) mode allows concurrent reads and a single writer, which is sufficient for read-heavy workloads, but write contention can arise in overlapping deployment scenarios.
- A case study revealed lost orders during rapid deploys (11 pushes in 2 hours) due to WAL file contention, where Stripe payments succeeded but order records weren't saved in the database.
- The sqlite_sequence table is a valuable forensic tool for tracking auto-increment values, helping identify missing rows and count historical tasks beyond current table counts.
- SQLite has limitations compared to PostgreSQL, such as lacking ILIKE (requiring LOWER() with LIKE), json_extract returning native types (needing CAST for string comparisons), and memory constraints with containerized exec commands.
- Despite challenges, SQLite is favored for single-server deployments with moderate write volume due to reduced infrastructure complexity, easy backups, and a straightforward migration path to Postgres when scaling needs arise.