SQLite Footguns¶

1. Multiple Writers From Separate Processes Causes Database Locked Errors¶

Your application runs multiple worker processes (Gunicorn, Puma, etc.) all opening the same SQLite file for writes. Under any write concurrency you get SQLITE_BUSY or database is locked errors because SQLite allows only one writer at a time. Fix: Set PRAGMA busy_timeout=5000 so writers wait up to 5 seconds before giving up. Use WAL mode (PRAGMA journal_mode=WAL) to reduce contention. For sustained write concurrency from multiple processes, SQLite is the wrong tool — migrate to PostgreSQL.

2. Mounting a SQLite File on NFS or Docker Volume Causes Silent Corruption¶

You containerize your app and mount the SQLite database on an NFS share or a Docker volume backed by a remote filesystem. File locking semantics on NFS are broken; multiple nodes can write simultaneously without knowing it, silently corrupting the database. Fix: Never run SQLite on NFS, CIFS, or networked filesystems. For containerized deployments, either keep the database on local node storage with a single writer, or use Litestream to replicate to S3 and restore locally per pod. Detect NFS mounts with df -T /path/to/db | grep nfs.

War story: Vaultwarden (self-hosted Bitwarden) users running SQLite on NFS-backed Docker volumes have reported silent database corruption where the vault appears empty with "database disk image is malformed" errors. Sonarr, Plex, and other self-hosted apps have identical bug reports. The SQLite docs explicitly state: "SQLite uses POSIX advisory locks for locking. NFS locking is known to be buggy."

3. Copying a Live Database File Without Checkpointing WAL¶

You copy app.db to a backup location while the application is running in WAL mode, but forget to copy app.db-wal and app.db-shm. Your backup is incomplete — the WAL file contains committed transactions not yet checkpointed into the main file. Fix: Always backup all three files together, or better, use the .backup command or VACUUM INTO which creates a consistent point-in-time copy. For production, use Litestream which handles WAL-aware continuous replication.

4. VACUUM Blocks All Readers and Writers¶

You run VACUUM on a production database to reclaim space. SQLite VACUUM rewrites the entire database into a temporary file, swaps it in, and then deletes the old file. During this operation, all reads and writes are blocked. On a multi-GB database, this takes minutes. Fix: Schedule VACUUM during maintenance windows. For incremental space reclamation, use PRAGMA incremental_vacuum(N) which reclaims N pages at a time without a full lock. Enable PRAGMA auto_vacuum=INCREMENTAL at database creation time to avoid the problem entirely.

5. SQLite's Dynamic Typing Silently Accepts Wrong Data Types¶

You define a column as INTEGER NOT NULL but insert 'abc'. SQLite stores it as text without error because of its flexible type affinity system. Your application later reads it expecting an integer and gets a runtime error — far from where the bad data was inserted. Fix: Enforce types in your application layer, not SQLite. Use SQLite's STRICT table mode (SQLite 3.37+) to enforce strict typing: CREATE TABLE users (...) STRICT;. For existing tables, add CHECK constraints and validate on insert.

Under the hood: SQLite uses "type affinity" not strict typing. A column declared INTEGER has integer affinity but will happily store text, blob, or real values. This was a deliberate design choice for flexibility, but it means the database will never reject bad data for you unless you use STRICT tables (added in 2021).

6. Deleting Rows Doesn't Shrink the File¶

You delete millions of rows from a table. SELECT count(*) FROM users returns the expected smaller number. But ls -la app.db shows the file is the same size. SQLite marks pages as free but doesn't return space to the OS until VACUUM. Fix: Run VACUUM after large deletes if file size matters. For databases with frequent large deletes, use PRAGMA auto_vacuum=FULL (set at creation time) to automatically reclaim pages. Monitor free pages with PRAGMA freelist_count to decide when vacuum is worth the overhead.

7. Using SQLite in CI Differently Than PostgreSQL in Production¶

Your tests use SQLite because it's easy to spin up, but production runs PostgreSQL. You write queries using SQLite-specific functions (strftime, GROUP_CONCAT) or rely on SQLite's permissive behavior (inserting wrong types, no RETURNING clause in old SQLite). Tests pass; production breaks. Fix: Either use PostgreSQL in CI as well (it's easy with Docker), or rigorously restrict yourself to SQL that is standard across both. If using an ORM, test with the same database dialect as production. SQLite and PostgreSQL diverge enough that CI/prod parity matters.

8. No `busy_timeout` Set — Application Crashes on Any Write Contention¶

Two concurrent requests hit your web app simultaneously, both needing to write. The second gets SQLITE_BUSY immediately and raises an exception because the default timeout is 0ms. Your app crashes instead of retrying. Fix: Always set PRAGMA busy_timeout=5000 (or higher) immediately after opening the connection. This tells SQLite to retry the lock for up to 5 seconds before returning SQLITE_BUSY. Combined with WAL mode, this handles most normal write contention gracefully.

Default trap: The default busy_timeout is 0 milliseconds — zero. Any write contention immediately returns SQLITE_BUSY. Most ORMs and drivers do not set this automatically. The fix is a single PRAGMA that should be in every SQLite connection initialization: PRAGMA busy_timeout=5000; PRAGMA journal_mode=WAL;.