What guarantees SQLite provides for recovery after an interrupted schema upgrade?

Question

Goal: ensure that a SQLite database can be returned to a consistent, usable state after a process crashes while a schema‑changing statement (e.g., ALTER TABLE, CREATE INDEX) is being committed.
SQLite relies on its rollback journal or Write‑Ahead Log (WAL) to undo partial writes, and the PRAGMA integrity_check procedure can verify B‑tree and index consistency after a disruptive event. However, it is unclear whether these mechanisms guarantee a full rollback of DDL changes, whether integrity_check can detect all corruptions without shutting down the database, and whether a concurrent VACUUM INTO operation can safely produce a clean copy while the journal or WAL files remain present.
Does the rollback journal/WAL guarantee that a partially applied ALTER TABLE is fully rolled back after a crash? Can PRAGMA integrity_check reliably detect corruption caused by an interrupted upgrade without requiring a shutdown? Is it safe to run VACUUM INTO on the main database file while the -wal and -shm files are present to obtain a clean copy for recovery?

Wasi · Answer

Direct answer
SQLite's rollback journal and Write-Ahead Log (WAL) guarantee atomicity for a transaction, not for an entire upgrade script. A single ALTER TABLE or CREATE INDEX is wrapped in an implicit transaction if you do not issue BEGIN, so a crash during that statement rolls it back completely. If your upgrade consists of multiple schema statements without one explicit BEGIN...COMMIT block, each statement commits independently, and a crash can leave the database partially upgraded.

Confirmed behavior

Rollback journal/WAL: uncommitted changes are discarded during recovery; committed changes survive. This applies to DDL as well as DML.
PRAGMA integrity_check: scans b-tree pages, indexes, and freelist structures without requiring a shutdown. It detects most structural corruption from an interrupted write, but it cannot detect every logical or application-level inconsistency.
VACUUM INTO: creates a new database file from the current connection's view. With -wal and -shm present, it can produce a consistent copy only when no other process is writing during the copy. A checkpoint first removes the WAL and makes the copy safer.

Steps for this case

Check the upgrade script or logs for BEGIN...COMMIT surrounding all DDL statements.
Run PRAGMA integrity_check on the live database. If it returns ok, no detectable structural corruption exists.
If the check fails or the upgrade was not one transaction, restore from backup or export and rebuild rather than manually patching DDL.
To make a clean recovery copy, run PRAGMA wal_checkpoint(TRUNCATE), then VACUUM INTO '/path/to/recovery.db' with no concurrent writers.

Verification commands
PRAGMA integrity_check;
PRAGMA wal_checkpoint(TRUNCATE);
VACUUM INTO '/path/to/recovery.db';

Run PRAGMA integrity_check on recovery.db and confirm it also returns ok before replacing the original.

Missing diagnostic detail
Was the schema upgrade executed as a single explicit transaction, or as multiple autocommitted statements? This changes whether partial DDL can remain after a crash and whether a full restore is required.

Assumptions: standard SQLite 3.x, default rollback journal or WAL mode, no custom VFS, and VACUUM INTO available (SQLite 3.27.0 or later). Behavior on a corrupted filesystem or with hardware write reordering is outside SQLite's guarantees.

What guarantees SQLite provides for recovery after an interrupted schema upgrade?

1 answer

Direct answer

Confirmed behavior

Steps for this case

Verification commands

Missing diagnostic detail

Your perspective belongs here.

0 question comments