Versioned Migrations with PRAGMA user_version

Learning outcomes

Once a database ships to users, there is no longer one schema—there is a sequence of schemas in the wild. A migration system answers three questions every time the application opens a database: what version is this file, what transformations are required, and who is allowed to apply them?

user_version belongs to the application

PRAGMA user_version stores a signed 32-bit integer in the database header. SQLite does not interpret it. That makes it useful as a compact application schema version such as 0, 1, 2. By contrast, schema_version is SQLite’s internal schema-change counter and is checked against prepared statements. Applications may read it for diagnostics, but should not use it as their migration number or write it casually.

PRAGMA user_version;     -- application-owned; SQLite does not use itPRAGMA schema_version;   -- SQLite-managed schema-change counter-- Application migration may deliberately advance user_version:PRAGMA user_version = 2;-- Do NOT copy recipes that manually set schema_version as app metadata.

Migration files are release artifacts

A practical repository keeps each forward transformation immutable after release. The file name says order; the application release says when it is required.

migrations/  001_create_fieldnotes.sql  002_add_device_location.sql  003_normalize_note_severity.sql  004_add_sync_request_log.sqlrelease 2.3.0 requires database user_version >= 4release 2.2.x understands versions 3..4release 2.1.x must never open a version 4 database for writes

A migration already applied to real databases should not be edited in place. Fix it with a new migration. Otherwise two files can both report user_version=3 while having different schemas.

Forward migration protocol

The runner should refuse ambiguous states. The simplest robust duplicate policy is expected-predecessor execution: migration 3 runs only if the database reports version 2; if the database is already version 3 or newer, migration 3 is skipped because the runner knows it was already applied. If the database reports an unexpected intermediate state, fail closed.

-- Connection initialization happens first.PRAGMA foreign_keys = ON;PRAGMA busy_timeout = 5000;BEGIN IMMEDIATE;              -- claim write/migration ownership early-- Verify current user_version in the host-language runner.-- Apply the exact version N -> N+1 statements.ALTER TABLE device ADD COLUMN location TEXT;PRAGMA user_version = 2;COMMIT;-- Then run structural/business verification outside or after commit.

SQL-only migration versus runner logic

SQL can contain the transformation, but the host language is better at reading version state, choosing which migration file applies, recording logs, checking expected versions, and surfacing errors. Do not try to turn a SQL script into a home-grown conditional programming language.

CREATE TABLE IF NOT EXISTS migration_device(    device_id INTEGER PRIMARY KEY,    device_code TEXT NOT NULL UNIQUE,    status TEXT NOT NULL DEFAULT 'active'           CHECK(status IN ('active','inspection_due','retired')));

ALTER TABLE migration_deviceADD COLUMN location TEXT;CREATE INDEX idx_migration_device_statusON migration_device(status);

A small Python migration runner

This runner uses a real file, turns Python’s implicit transaction behavior off, initializes connection PRAGMAs, and explicitly owns each BEGIN IMMEDIATE/COMMIT. It is intentionally small enough to audit.

from pathlib import Pathimport sqlite3MIGRATIONS = {    1: [        """CREATE TABLE migration_device(               device_id INTEGER PRIMARY KEY,               device_code TEXT NOT NULL UNIQUE,               status TEXT NOT NULL DEFAULT 'active'                      CHECK(status IN ('active','inspection_due','retired'))           )""",    ],    2: [        "ALTER TABLE migration_device ADD COLUMN location TEXT",        "CREATE INDEX idx_migration_device_status ON migration_device(status)",    ],}def user_version(con):    return con.execute("PRAGMA user_version").fetchone()[0]def migrate(path: Path, target: int) -> None:    con = sqlite3.connect(path, isolation_level=None, timeout=5.0)    try:        con.execute("PRAGMA foreign_keys=ON")        con.execute("PRAGMA busy_timeout=5000")        current = user_version(con)        if current > target:            raise RuntimeError(f"database {current} is newer than app target {target}")        for next_version in range(current + 1, target + 1):            statements = MIGRATIONS[next_version]            con.execute("BEGIN IMMEDIATE")            try:                # Recheck after owning the write transaction.                if user_version(con) != next_version - 1:                    raise RuntimeError("migration predecessor changed")                for sql in statements:                    con.execute(sql)                con.execute(f"PRAGMA user_version={next_version}")                con.commit()            except Exception:                con.rollback()                raise    finally:        con.close()

The recheck after BEGIN IMMEDIATE matters when multiple processes race. A runner that reads version 1, waits, then blindly applies “1→2” after another process already migrated can generate duplicate-object failures or worse.

Failure recovery is transaction recovery

If one statement in a migration fails, the runner explicitly rolls back the migration transaction. The database should remain at the predecessor version, not a half-upgraded version with a falsely advanced user_version.

start: user_version = 1BEGIN IMMEDIATE  ALTER TABLE ...                -- succeeds  CREATE UNIQUE INDEX ...        -- fails because legacy duplicates exist  PRAGMA user_version = 2        -- never reachedROLLBACKend: user_version = 1schema: predecessor schema restored by transaction rollback

For very large migrations that cannot fit one practical transaction, design an explicit multi-phase protocol with resumable state. Do not silently abandon atomicity halfway through because the script became slow.

user_version versus a migration history table

A production system often uses both. If you record a migration table, store a stable migration ID and checksum; refuse to proceed if a previously applied migration ID has different content than the packaged release.

Checkpoint

Bridge to rebuilds with foreign keys

The runner gives migrations ordering and ownership. Lesson 3 applies that discipline to the hardest common SQLite change: rebuilding a referenced table while preserving foreign-key correctness and transforming real data.

Authoritative references

• SQLite PRAGMA user_version and schema_version
• SQLite transactions
• SQLite ALTER TABLE
• SQLite locking and concurrency