Changing a database safely

All or nothing: one transaction per migration

That's the half-changed state from the check above: the first statement applied, the version number not updated, and the next run tries the first statement again and crashes. The fix is a transaction around each migration, including the version update, so it either fully happens or not at all.

SQLite can roll back table changes too, not just data, but Python's sqlite3 normally manages transactions for you in a way that doesn't cover them. Opening the connection with isolation_level=None hands you the controls: you write BEGIN, COMMIT and ROLLBACK yourself. (Python 3.12 added an autocommit setting for the same job; isolation_level=None works on every version.)

import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)
db.execute("CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL)")

broken = (2, ["CREATE TABLE trips (id INTEGER PRIMARY KEY, name TEXT)",
              "CREATE INDEX trips_by_day ON trips (day)"])    # there's no day column: this fails

version, statements = broken
db.execute("BEGIN")
try:
    for sql in statements:
        db.execute(sql)
    db.execute(f"PRAGMA user_version = {version}")
    db.execute("COMMIT")
except sqlite3.Error as e:
    db.execute("ROLLBACK")
    print("migration", version, "failed and was undone:", e)

print("version:", db.execute("PRAGMA user_version").fetchone()[0])
print("tables:", [r[0] for r in db.execute("SELECT name FROM sqlite_master WHERE type = 'table'")])

The trips table that the first statement created is gone again, and the version is still 0, so after fixing the migration, the next run starts cleanly. (Some databases, MySQL among them, can't roll back table changes, which is one more reason to keep each migration small.)

Adding a required column to a table with rows

Here's the change that trips people up. The members table has a thousand rows, and every member now needs a region. ALTER TABLE members ADD COLUMN region TEXT NOT NULL fails: the thousand existing rows would have no value, and NOT NULL forbids exactly that. There are two safe ways through:

1. Add it with a default, ... NOT NULL DEFAULT 'unknown', when one value is a fair start for everyone.
2. Add it as nullable, backfill, then tighten: add the column allowing NULL, fill it in from data you have (a backfill), and make the code require it from then on. Tightening the column itself to NOT NULL in SQLite means rebuilding the table (create a new one, copy the rows, swap the names), so many teams enforce it in the application instead, or schedule the rebuild for a quiet moment.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT, zip_code TEXT)")
db.executemany("INSERT INTO members (name, zip_code) VALUES (?, ?)",
               [("Ana", "97201"), ("Bo", "98101"), ("Cy", "80202"), ("Dee", None)])

try:
    db.execute("ALTER TABLE members ADD COLUMN region TEXT NOT NULL")
except sqlite3.OperationalError as e:
    print("refused:", e)

db.execute("ALTER TABLE members ADD COLUMN status TEXT NOT NULL DEFAULT 'active'")   # way 1: a default
print(db.execute("SELECT name, status FROM members").fetchall())

db.execute("ALTER TABLE members ADD COLUMN region TEXT")       # way 2, step 1: allow NULL for now
db.execute("""UPDATE members SET region = CASE substr(zip_code, 1, 2)
                  WHEN '97' THEN 'oregon' WHEN '98' THEN 'washington' WHEN '80' THEN 'colorado'
                  ELSE 'unknown' END""")                       # step 2: backfill from what we know
print(db.execute("SELECT name, zip_code, region FROM members").fetchall())
print("still missing:", db.execute("SELECT COUNT(*) FROM members WHERE region IS NULL").fetchone()[0])

The last line is the check to run before tightening anything: a backfill that left gaps would make the next step fail in production. On a big table, backfill in batches, say a thousand rows per UPDATE, each in its own transaction, so the table is never locked for long.

When ALTER TABLE isn't enough: the table rebuild

SQLite's ALTER TABLE can do a few things in place: ADD COLUMN (though not a UNIQUE or primary key column, so the exercise below adds a unique index instead), RENAME COLUMN, RENAME TO for the table, and DROP COLUMN for a column no index or rule uses. Anything else, such as tightening a column to NOT NULL, changing its type, or adding a CHECK or a foreign key, means rebuilding the table, all in one transaction:

import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)
db.executescript("""
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT, region TEXT);
INSERT INTO members (name, region) VALUES ('Ana', 'oregon'), ('Bo', 'washington');
""")

db.execute("PRAGMA foreign_keys = OFF")    # must come before BEGIN: it's ignored inside a transaction
db.execute("BEGIN")
db.execute("CREATE TABLE members_new (id INTEGER PRIMARY KEY, name TEXT NOT NULL, region TEXT NOT NULL)")
db.execute("INSERT INTO members_new (id, name, region) SELECT id, name, region FROM members")
db.execute("DROP TABLE members")
db.execute("ALTER TABLE members_new RENAME TO members")
db.execute("COMMIT")
db.execute("PRAGMA foreign_keys = ON")

print(db.execute("SELECT * FROM members").fetchall())
try:
    db.execute("INSERT INTO members (name) VALUES ('Cy')")
except sqlite3.IntegrityError as e:
    print("refused:", e)

Create the new shape, copy the rows across, drop the old table and rename the new one. Indexes and triggers belong to the old table and go with it, so recreate them after the rename. PostgreSQL makes the same change in one statement, ALTER TABLE members ALTER COLUMN region SET NOT NULL, but it still checks every row, so the gap check comes first there too.

Why not just rename it?

SQLite can rename a column in one statement, ALTER TABLE members RENAME COLUMN name TO display_name. On your laptop that's fine. In production, the app runs on several servers, and a deploy updates them one at a time. For a few minutes, old code (which reads name) and new code (which reads display_name) run at the same moment against the same database. A one-step rename breaks whichever half is on the wrong side of it.

Expand and contract (the steps above) avoids that window entirely: each step is compatible with both the code before it and the code after it. It's slower, several small deploys instead of one, which is why teams save it for busy, shared tables and use the one-step change where a minute of errors doesn't matter.

The same thinking applies to every change. Adding things (a table, a nullable column, an index) is safe for the running code, though building an index on a big table can block writes while it runs (PostgreSQL's CREATE INDEX CONCURRENTLY avoids that). Removing or renaming things is only safe once no running code uses them. Ask of each migration: would the code that's running right now still work after this?

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE trips (id INTEGER PRIMARY KEY, name TEXT, day TEXT)")
db.executemany("INSERT INTO trips (name, day) VALUES (?, ?)", [("Fern Hollow loop", "2025-06-14"), ("Red Mesa climb", "2025-06-22")])

MIGRATION_4 = [
    "ALTER TABLE trips ADD COLUMN weekday INTEGER",
]
for sql in MIGRATION_4:
    db.execute(sql)
print(db.execute("SELECT name, day, weekday FROM trips").fetchall())

"""add region to members

Revision ID: 3f2a91c4e7b0
Revises: 9b1d55ae2c10
"""
from alembic import op
import sqlalchemy as sa

revision = "3f2a91c4e7b0"
down_revision = "9b1d55ae2c10"   # the migration this one follows

def upgrade():
    op.add_column("members", sa.Column("region", sa.String(), nullable=True))
    op.execute("UPDATE members SET region = 'unknown' WHERE region IS NULL")

def downgrade():
    op.drop_column("members", "region")