SQLite WAL and Backups

WAL mode improves app-local concurrency, but it changes backup rules.

WAL Files

app.db      # main database pages
app.db-wal  # committed changes not yet checkpointed
app.db-shm  # shared memory coordination

In WAL mode, readers can see a consistent view while writers append frames to the WAL. A checkpoint later moves WAL frames into the main DB file.

Backup Rule

Use one of these:

• SQLite online backup API
• VACUUM INTO
• application-coordinated close/checkpoint/copy path

Do not copy only app.db while the database is open and WAL may contain committed data.

Checkpoints

PRAGMA wal_checkpoint(PASSIVE);
PRAGMA wal_checkpoint(TRUNCATE);

Use manual checkpoints when you need to control backup size or keep WAL growth bounded. Long-running readers can prevent checkpoint progress and make WAL files grow.

Validation

After backup or restore:

PRAGMA quick_check;
PRAGMA integrity_check;
PRAGMA foreign_key_check;

For large SQLite systems, test edge cases. Litestream’s testing notes call out real SQLite use, race detection, restore comparisons, integrity checks, and the SQLite lock page around the 1GB boundary.

Gotcha: WAL is persistent per database. Once enabled, future connections use WAL until changed.