SQLite in Production - WAL Mode, Single-Writer Patterns, and Litestream

Status: Active
Last Updated: 2026-08-26
Category: Databases - Operations
Prerequisites: database-selection, docker-volumes
Time: 2 hours
Tags: sqlite, wal, litestream, backups, single-writer, embedded-database

Summary

How to run SQLite safely in production for fogserv.cloud-scale services: enabling WAL mode and busy_timeout, structuring the app around a single writer, streaming replicas with Litestream, and the failure modes that tell you SQLite has stopped being enough.

🎯 What You'll Learn

By the end of this article, you'll be able to:

• ✅ Enable and verify WAL mode plus busy_timeout on a Prisma-managed SQLite file
• ✅ Design a write path that respects SQLite's single-writer model
• ✅ Run Litestream for continuous replication to S3-compatible storage
• ✅ Recognize the concrete signals that it's time to move to Postgres

---

Context / Why This Matters

SQLite is our development default and remains legitimate for production on small, mostly-read services (database-selection). Its appeal is operational: one file, no server, trivial backups. But "no server" doesn't mean "no operations" — an untuned SQLite file behind a containerized web app will eventually produce SQLITE_BUSY errors at the worst possible moment. This article is the playbook for doing it right, and for knowing when to stop.

The website stack currently uses libsql/Prisma against a local dev.db file, so everything here applies directly.

Implementation / Core Content

WAL Mode: The Non-Negotiable First Step

Default journal mode (DELETE) blocks readers while a writer holds the lock. Write-Ahead Logging lets readers proceed concurrently with one writer — essential for any web workload.

# One-time (persistent setting stored in the DB file)
sqlite3 /data/app.db "PRAGMA journal_mode=WAL;"

-- Verify
PRAGMA journal_mode;  -- wal
PRAGMA synchronous;   -- NORMAL recommended under WAL

With Prisma + libsql driver, set pragmas via connection URL or init SQL where supported:

DATABASE_URL=file:/data/app.db?connection_limit=1

connection_limit=1 is deliberate: it serializes all writes from this app process through one connection, which sidesteps most SQLITE_BUSY scenarios entirely (see below).

Also set busy_timeout so writers wait briefly instead of erroring instantly:

PRAGMA busy_timeout = 5000;  -- ms

Note: journal_mode=WAL persists in the database file, but busy_timeout and synchronous are per-connection — they must be applied by every process that opens the DB, ideally in your client factory code.

The Single-Writer Pattern

SQLite allows many concurrent readers but only one writer across the whole file. Architect accordingly:

1. All writes go through one process. If you have app + background worker + admin scripts writing directly, you have three writers contending. Route worker writes through the app's API, or run a dedicated writer service.
2. Keep write transactions short. Long transactions hold the write lock. Batch inserts inside one transaction are fine (fast); holding a transaction open across a network call is not.
3. Never put the DB file on NFS or network volumes (including some distributed CSI volumes). WAL relies on shared-memory files (-shm, -wal) that require POSIX locking semantics network filesystems don't honor. Local volume or local SSD only — see docker-volumes.
4. One app replica. Horizontal scaling of direct-SQLite writers breaks the model. Scale reads by caching, not replicas.

Backups: Litestream

You cannot reliably cp a live SQLite file (you may catch it mid-write). Litestream streams the WAL to object storage continuously and supports point-in-time restore — pair it with MinIO per minio-setup.

# /etc/litestream.yml
dbs:
  - path: /data/app.db
    replicas:
      - type: s3
        endpoint: http://minio.internal:9000
        bucket: sqlite-backups
        path: appdb
        access-key-id: ${LITESTREAM_ACCESS_KEY_ID}
        secret-access-key: ${LITESTREAM_SECRET_ACCESS_KEY}
        retention: 720h        # 30 days
        sync-interval: 1s

litestream replicate -config /etc/litestream.yml   # continuous
litestream restore -o /data/restored.db s3://sqlite-backups/appdb  # recovery

Litestream can also run as a container sharing the same volume as the app (read-only access to the DB file suffices for replication).

When It Breaks: Migration Triggers

Move to Postgres when any of these become true:

Signal	Threshold	Why
Sustained write contention	SQLITE_BUSY after busy_timeout expires more than occasionally	Single-writer model exhausted
Multiple nodes need write access	Any	File can't be shared safely
Database size	> ~10 GB, or heavy concurrent analytical queries	Query latency degrades without server-side planning
Team size	> ~5 developers deploying schema changes casually	Coordination cost exceeds Postgres ops cost

Migration out is straightforward with Prisma: change provider, use migrate diff to generate initial SQL, follow prisma-migrations-guide baselining steps against the new Postgres instance.

Practical Examples

Example 1: Hardened docker-compose service

services:
  app:
    image: fogserv/app:latest
    environment:
      DATABASE_URL: file:/data/app.db?connection_limit=1
    volumes:
      - app-data:/data

  litestream:
    image: litestream/litestream:latest
    command: replicate -config /etc/litestream.yml
    volumes:
      - app-data:/data          # same volume, read-only usage
      - ./litestream.yml:/etc/litestream.yml:ro

volumes:
  app-data:

Health check that verifies WAL is active:

docker exec app sh -c 'sqlite3 /data/app.db "PRAGMA journal_mode;"'
# expected output: wal

Example 2: Safe manual backup without Litestream

# Uses the backup API → consistent snapshot even mid-write
sqlite3 /data/app.db ".backup '/backups/app-$(date +%F).db'"
gzip "/backups/app-$(date +%F).db"

Schedule it via cron/systemd timer and push to MinIO with mc cp (minio-setup).

Example 3: Point-in-time restore drill step

litestream restore -timestamp 2026-08-26T03:00:00Z \
  -o /tmp/drill.db s3://sqlite-backups/appdb
sqlite3 /tmp/drill.db 'PRAGMA integrity_check; SELECT count(*) FROM Subscriber;'
# integrity_check must return ok before the drill passes

Common Pitfalls & Troubleshooting

Problem	Cause	Fix
SQLITE_BUSY: database is locked	Second writer process, or missing busy_timeout	Serialize writes (connection_limit=1, one writer service); set busy_timeout everywhere
disk I/O error under WAL	DB on NFS/network volume	Move to local volume immediately; see docker-volumes
Corrupt backup file	Copied live DB file without .backup or stopping writes	Restore from Litestream instead; fix backup job to use .backup
WAL file grows unbounded	No checkpointing because a reader never disconnects	Ensure connections close/reuse; run PRAGMA wal_checkpoint(TRUNCATE); periodically
Lost hours of data after crash	synchronous=OFF	Set synchronous=NORMAL under WAL on every connection
Litestream replicating but restore fails	Retention shorter than incident detection time	Raise retention; test restores quarterly (backup-recovery-drill)

Next Steps / Ops Actions

• Apply WAL + busy_timeout to every SQLite database in service today.
• Deploy Litestream (or .backup cron) targeting MinIO: minio-setup.
• Add the restore drill to the quarterly calendar: backup-recovery-drill.
• Re-evaluate the Postgres migration triggers each quarter using database-selection.

Sources & Related

External references consulted:

• https://www.sqlite.org/wal.html
• https://www.sqlite.org/pragma.html
• https://litestream.io/guides/

Related knowledge-base articles:

• database-selection
• postgres-tuning-basics
• backup-recovery-drill
• restic-backups

Change Log

2026-08-26

• Initial creation covering WAL, single-writer patterns, Litestream replication, and migration triggers.