Chapter 17 · Schema Evolution, Migrations, Testing, and Release Compatibility
Safe Table-Rebuild Migrations and Foreign-Key Handling
Apply SQLite’s documented general schema-change procedure, including foreign-key handling, data transformation, dependent-object reconstruction, validation, and release rollback planning.
Learning outcomes
A table rebuild is not “copy rows into a new table and hope.” It is a controlled replacement of one schema object and every contract around it. SQLite’s official general procedure is intentionally conservative because foreign keys, triggers, indexes, and views can all refer to the table being changed.
Follow SQLite’s documented general schema-change sequence in the correct order.
Disable/re-enable foreign-key enforcement only at the documented transaction boundary and always validate afterward.
Transform and validate legacy values while copying into the new schema.
Recreate indexes, triggers, and affected views intentionally.
Compare row counts and domain fingerprints before and after migration.
Design release rollback/recovery around a pre-migration verified backup rather than a reverse-SQL fantasy.
The general rebuild procedure as a release checklist
The documented procedure begins by observing whether foreign-key enforcement is enabled. If it is, PRAGMA foreign_keys=OFF must be issued before the transaction; changing that PRAGMA inside a transaction is ineffective. Then the migration owns a transaction, records dependent object definitions, creates new_X, copies data, drops any affected views whose references would be temporarily invalid, drops X, renames new_X to X, rebuilds dependent objects, runs foreign_key_check, commits, and restores foreign-key enforcement.
-- 0. Verified backup already exists.PRAGMA foreign_keys; -- remember original statePRAGMA foreign_keys = OFF; -- only before BEGINBEGIN IMMEDIATE;-- 1. Snapshot definitions of indexes/triggers/views that must survive.SELECT type, name, sqlFROM sqlite_schemaWHERE tbl_name='device_migrate' OR sql LIKE '%device_migrate%';-- 2. CREATE TABLE new_device_migrate(...desired schema...)-- 3. INSERT INTO new_device_migrate(...) SELECT ... FROM device_migrate-- 4. DROP TABLE device_migrate-- 5. ALTER TABLE new_device_migrate RENAME TO device_migrate-- 6. Recreate indexes/triggers/views-- 7. PRAGMA foreign_key_check-- 8. Business/data invariantsCOMMIT;PRAGMA foreign_keys = ON; -- restore policyPRAGMA foreign_key_check; -- verify again after release boundaryFieldNotes-style migration scenario
A legacy device table stores a loose status vocabulary. The new schema wants a constrained status set, an installed_at timestamp, and a UNIQUE device code. A child table references device_id, an index supports active-device queries, a trigger records status changes, and a view exposes the current fleet.
PRAGMA foreign_keys = ON;CREATE TABLE site_migrate( site_id INTEGER PRIMARY KEY, site_name TEXT NOT NULL);CREATE TABLE device_migrate( device_id INTEGER PRIMARY KEY, site_id INTEGER NOT NULL REFERENCES site_migrate(site_id), device_code TEXT NOT NULL, status TEXT NOT NULL);CREATE TABLE note_migrate( note_id INTEGER PRIMARY KEY, device_id INTEGER NOT NULL REFERENCES device_migrate(device_id), note_text TEXT NOT NULL);CREATE INDEX idx_device_migrate_site_statusON device_migrate(site_id, status);CREATE TABLE device_status_audit( device_id INTEGER NOT NULL, old_status TEXT, new_status TEXT NOT NULL);CREATE TRIGGER trg_device_migrate_statusAFTER UPDATE OF status ON device_migrateBEGIN INSERT INTO device_status_audit(device_id, old_status, new_status) VALUES(OLD.device_id, OLD.status, NEW.status);END;CREATE VIEW active_device_migrate ASSELECT device_id, device_codeFROM device_migrateWHERE status='active';Measure the predecessor before touching it
A migration needs observable invariants. Row count is necessary but weak; also record key counts, orphan count, and a simple deterministic content fingerprint. The following is not a cryptographic checksum—it is a domain sanity signal that helps detect accidental loss or transformation outside the intended columns.
SELECT count(*) AS device_rows, count(DISTINCT device_id) AS distinct_ids, count(DISTINCT device_code) AS distinct_codes, sum(length(device_code)) AS code_length_sumFROM device_migrate;SELECT status, count(*)FROM device_migrateGROUP BY statusORDER BY status;PRAGMA foreign_key_check;PRAGMA integrity_check;If count(DISTINCT device_code) is smaller than row count, the planned UNIQUE constraint cannot succeed. That is a data migration problem to resolve explicitly before dropping the old table.
Backfill and normalize inside the copy
The transformation below accepts known legacy spellings and refuses the rest by relying on the destination CHECK constraint. A migration should not silently map every unknown value to a convenient default because that hides data quality problems.
PRAGMA foreign_keys = OFF;BEGIN IMMEDIATE;CREATE TABLE new_device_migrate( device_id INTEGER PRIMARY KEY, site_id INTEGER NOT NULL REFERENCES site_migrate(site_id), device_code TEXT NOT NULL UNIQUE, status TEXT NOT NULL CHECK(status IN ('active','inspection_due','retired')), installed_at TEXT);INSERT INTO new_device_migrate (device_id, site_id, device_code, status, installed_at)SELECT device_id, site_id, device_code, CASE status WHEN 'inspection' THEN 'inspection_due' ELSE status END, NULLFROM device_migrate;-- An affected persistent view would be invalid while the old table name is absent.-- Its SQL was saved earlier, so drop it before replacing the table.DROP VIEW active_device_migrate;DROP TABLE device_migrate;ALTER TABLE new_device_migrate RENAME TO device_migrate;CREATE INDEX idx_device_migrate_site_statusON device_migrate(site_id, status);CREATE TRIGGER trg_device_migrate_statusAFTER UPDATE OF status ON device_migrateBEGIN INSERT INTO device_status_audit(device_id, old_status, new_status) VALUES(OLD.device_id, OLD.status, NEW.status);END;CREATE VIEW active_device_migrate ASSELECT device_id, device_code, installed_atFROM device_migrateWHERE status='active';PRAGMA foreign_key_check;COMMIT;PRAGMA foreign_keys = ON;If an unexpected status such as 'broken' exists, the INSERT into new_device_migrate fails and the migration transaction can roll back. That failure is preferable to quietly inventing a semantic mapping.
Why disabling foreign_keys is not “turn off safety”
The documented rebuild temporarily disables enforcement because dropping/replacing a referenced table would otherwise interact badly with foreign-key actions while the schema is in its intermediate state. Safety comes from the whole protocol: disable before BEGIN, keep the replacement atomic in one transaction, run PRAGMA foreign_key_check before commit, commit only a valid state, and restore enforcement immediately afterward.
A helper that says “foreign_keys=OFF; run arbitrary SQL; foreign_keys=ON” can mask broken migrations. Use the exception only for a reviewed rebuild, prove foreign_key_check is empty, and restore the connection policy even when errors occur.
Dependent objects are code and must be reconstructed
| Object | Why it matters after rebuild | Verification |
|---|---|---|
| Indexes | Performance and sometimes uniqueness semantics. | PRAGMA index_list plus expected query plan where important. |
| Triggers | Audit/derived behavior can disappear if forgotten. | Inspect sqlite_schema and execute a behavior test. |
| Views | Compatibility/report interfaces may reference changed columns. | Prepare/query the view and inspect output columns. |
| Foreign keys | Child relationships can become invalid during data translation. | PRAGMA foreign_key_check must return no rows. |
| STRICT/generated features | New schema semantics may require a newer runtime. | Capability test before starting migration. |
Post-migration invariants
PRAGMA foreign_keys = ON;PRAGMA foreign_key_check;PRAGMA integrity_check;SELECT count(*) AS device_rows, count(DISTINCT device_id) AS distinct_ids, count(DISTINCT device_code) AS distinct_codes, sum(length(device_code)) AS code_length_sumFROM device_migrate;SELECT status, count(*)FROM device_migrateGROUP BY statusORDER BY status;SELECT * FROM active_device_migrate ORDER BY device_id;-- Trigger behavior testUPDATE device_migrateSET status='inspection_due'WHERE device_id=1;SELECT * FROM device_status_audit WHERE device_id=1;Rollback and release recovery
Within the migration transaction, an error should trigger SQL rollback. After a successful commit, “rollback” becomes a release-management problem because older application code may no longer understand the new schema. The safest recovery plan is usually: stop the incompatible application version, preserve the failed/new database for diagnosis, restore the verified pre-migration backup, deploy the known-compatible application, and investigate offline.
BEFORE release: verified backup B0 application A16 + schema V16DEPLOY A17: migrate V16 -> V17 atomically run migration + application smoke testsIF migration transaction fails: ROLLBACK; remain at V16IF migration commits but release is operationally bad: stop writers preserve V17 copy for diagnosis restore verified B0 / V16 redeploy compatible A16 validate restored stateDropping a new column is easy only when no post-upgrade data depends on it. A restore plan is often safer than pretending every semantic transformation has a lossless inverse.
Checkpoint
Review the rebuild boundary
Answer using the documented procedure.
- When must PRAGMA foreign_keys=OFF be issued?
- Why create new_X before dropping X?
- What should happen if legacy data violates a new CHECK?
- Why must indexes/triggers/views be treated as migration inputs?
- What does foreign_key_check prove that a row-count comparison does not?
- After a committed migration, why might restoring a backup be safer than a reverse migration?
Review the answers
foreign_keys must be changed before BEGIN because the pragma is ineffective inside a transaction. new_X lets the successor be validated while the original still exists and avoids unsafe rename-first propagation. Bad legacy data should stop the migration unless an explicit transformation policy handles it. Dependent objects carry behavior/performance/interface contracts. foreign_key_check finds orphaned/mismatched relationships. A restore returns the whole database to a known predecessor state when semantic changes are not cleanly reversible.
Bridge to database tests
Migration verification is already a form of testing. Lesson 4 generalizes that idea into a repeatable local suite that checks schema, constraints, queries, transactions, and real file-backed concurrency before a database change reaches users.