Python SQLite migration: commit schema and version as one unit

Operation contract

A receipt database begins at schema version one. The attempted second migration adds a column, then hits a conflicting table definition. Explicit BEGIN and ROLLBACK leave both the column and version unchanged. The code uses execute for each statement rather than executescript, whose pending-transaction behavior can break a caller's assumption about transaction ownership.

Failure and ownership boundary

This in-memory fixture tests rollback, not a deployment migration. Back up real data, serialize migration runners, validate the current version, and plan what happens when an old application instance sees the new schema. Some schema operations and external effects have different transaction behavior; test the exact SQLite version and operation before a release.

Working program

import sqlite3

database = sqlite3.connect(":memory:", isolation_level=None)
database.execute("BEGIN IMMEDIATE")
database.execute("CREATE TABLE receipts (receipt_id TEXT PRIMARY KEY)")
database.execute("PRAGMA user_version = 1")
database.execute("COMMIT")

try:
    database.execute("BEGIN IMMEDIATE")
    database.execute("ALTER TABLE receipts ADD COLUMN batch_id TEXT")
    database.execute("CREATE TABLE receipts (duplicate_id TEXT)")
    database.execute("PRAGMA user_version = 2")
    database.execute("COMMIT")
except sqlite3.OperationalError:
    database.execute("ROLLBACK")

version = database.execute("PRAGMA user_version").fetchone()[0]
columns = [row[1] for row in database.execute("PRAGMA table_info(receipts)")]
print("version", version)
print("columns", columns)
database.close()

Output

version 1
columns ['receipt_id']

Costs and limits

The migration lock blocks competing writes while held. A large table rewrite can be expensive; measure it with production-like size before scheduling a release.

Common Mistakes

• Do not update user_version before verifying the schema change.
• Do not assume executescript preserves a transaction opened by its caller.
• Rollback of an in-memory fixture does not prove a deployment upgrade path.

Connected lessons

• Python sqlite3 transactions: bind values and roll back failed batches
• Python SQLite savepoints: discard one substep without committing the batch
• Python SQLite backup project: copy a committed snapshot to another connection

Test this boundary.

Continue with Python SQLite migration: reopen the file after a failed upgrade.