SQLite troubleshooting
database is locked
Another connection holds a write lock. Common with multiple processes or
threads writing without coordination. Enable PRAGMA busy_timeout,
switch to journal_mode=WAL, and serialize writes in the application.
Find holders: lsof app.db or fuser -v app.db.
database disk image is malformed
Corruption — often from copying a live file, full disk mid-write, or NFS issues.
Run PRAGMA integrity_check;. Restore from
.backup, Litestream, or last good copy. Do not delete WAL files
blindly — they may contain uncheckpointed commits.
Disk full / cannot write
SQLite needs free space for WAL growth and temp files. Check
df -h on the volume holding the database. WAL files can grow
large under heavy write load — checkpoint with
PRAGMA wal_checkpoint(TRUNCATE); after freeing space.
WAL file growing without bound
Long-running read transactions prevent WAL checkpointing. Find stale readers;
checkpoint manually: PRAGMA wal_checkpoint(TRUNCATE);. Tune
PRAGMA wal_autocheckpoint (default 1000 pages). Ensure readers
close connections promptly.
Permission denied
The app user needs read/write on the database file and write on the directory
(SQLite creates temp and WAL files alongside the db). Check
ls -la app.db and directory permissions.
Foreign key violations silently ignored
Foreign keys are off by default. Enable per connection:
PRAGMA foreign_keys = ON; — must be set on every connection,
including in the application pool.
Unsafe backup caused corruption
Use the backup API instead of cp on a live database:
sqlite3 app.db ".backup backup.db"
# Or: VACUUM INTO 'backup.db';
# Production: litestream.io for continuous replicationLitestream restore
When recovering from Litestream-backed storage:
litestream restore -o app.db s3://bucket/path
# Stop app before swapping db file, then restartDebugging workflow
1. Integrity and journal mode
sqlite3 app.db "PRAGMA integrity_check; PRAGMA journal_mode;"2. Who has the file open?
lsof app.db
ls -la app.db app.db-wal app.db-shm3. Consistent backup before experiments
sqlite3 app.db ".backup /tmp/app-backup.db"Practice scenarios
Hands-on SQLite scenarios on live Linux VMs: sqlite