Corruption Prevention and Detection

SQLite is incredibly reliable — when used correctly

SQLite is one of the most-tested pieces of software on Earth. Corruption almost always comes from outside the engine. The classic causes:

NFS / SMB / FUSE — broken file locking. Never put a SQLite file on network storage with a writer.
Killed mid-write without WAL — pre-WAL journal modes can corrupt on hard kill. WAL recovers cleanly.
Two processes both with the same file open via different paths — symlinks, bind mounts.
Hardware — bad disk, bad RAM, lying SSDs that ack writes before persisting them.
Restoring backups while writers are active — partial overwrite of an in-use database.

Detect corruption with PRAGMA integrity_check (full scan; slow on large DBs) or PRAGMA quick_check (faster, weaker guarantees). Run after every backup and on a schedule.

Self-reference: Pippa's SQLite + JSONL design exists partly because corruption recovery is bounded: if SQLite ever shows damage, purge the affected conversation's rows and replay from JSONL. The JSONL ground truth is the disaster-recovery mechanism, not just an audit log.

#!/bin/bash DB=/var/lib/myapp/myapp.db result=$(sqlite3 "$DB" 'PRAGMA integrity_check') if [ "$result" != "ok" ]; then echo "Corruption detected: $result" | mail -s 'SQLite corruption' admin@example.com exit 1 fi

Corruption Prevention and Detection

SQLite is incredibly reliable — when used correctly

Code

External links

Exercise

Progress

Comments 0