claude-mods/skills/sqlite-ops/references/migration-patterns.md

SQLite Migrations

Changing a schema that already holds data. Engine-agnostic SQL, with runner examples per host. For designing a schema in the first place see schema-design.md.

Contents

• What ALTER TABLE can and cannot do
• The 12-step recreate procedure
• Versioned migrations with user_version
• A named-migration runner
• Runners per host
• Migrating large tables
• Rollback strategy
• Migration review checklist

---

What ALTER TABLE can and cannot do

Operation	Supported	Since
ADD COLUMN	Yes	Always
RENAME TO (table)	Yes	Always
RENAME COLUMN	Yes	3.25
DROP COLUMN	Yes, with conditions	3.35
Change a column type	No	—
Add/remove NOT NULL, CHECK, DEFAULT	No	—
Add/remove a foreign key	No	—
Reorder columns	No	—
Add a PRIMARY KEY / UNIQUE	No (CREATE UNIQUE INDEX is the workaround)	—

ALTER TABLE product ADD COLUMN status TEXT NOT NULL DEFAULT 'active';
ALTER TABLE product RENAME COLUMN sku TO product_code;
ALTER TABLE product DROP COLUMN legacy_flag;

ADD COLUMN constraints: a NOT NULL column must have a non-null DEFAULT (existing rows need a value), the default cannot be from the CURRENT_TIME family, and it cannot be PRIMARY KEY or UNIQUE. Adding a column is O(1) — SQLite records it in the schema and materialises the default on read.

DROP COLUMN refuses when the column is a primary key, has a UNIQUE constraint, or is referenced by an index, view, trigger, CHECK constraint, or generated column. Drop the dependent object first, or use the recreate procedure. Unlike ADD, it rewrites every row.

Everything else needs the recreate procedure below.

---

The 12-step recreate procedure

The official sequence for any change ALTER TABLE cannot express. Order matters — each step prevents a specific failure.

-- 1. If foreign keys are enabled, note that and turn them OFF.
--    Must be OUTSIDE a transaction: PRAGMA foreign_keys is a no-op inside one.
PRAGMA foreign_keys = OFF;

-- 2. Start a transaction.
BEGIN IMMEDIATE;

-- 3. Record every index, trigger and view attached to the table - you will recreate them:
--    SELECT type, name, sql FROM sqlite_master WHERE tbl_name = 'product';

-- 4. Create the new table under a temporary name, with the desired shape.
CREATE TABLE product_new (
    id         INTEGER PRIMARY KEY,
    org        TEXT NOT NULL,
    price      INTEGER NOT NULL,                   -- changed: REAL -> INTEGER (minor units)
    status     TEXT NOT NULL DEFAULT 'active'
                   CHECK (status IN ('active','archived')),  -- added constraint
    created_at TEXT NOT NULL
    -- dropped: legacy_flag
) STRICT;

-- 5. Copy the data, transforming as needed.
INSERT INTO product_new (id, org, price, status, created_at)
SELECT id, org, CAST(round(price * 100) AS INTEGER),
       coalesce(status, 'active'), created_at
FROM product;

-- 6. Drop the old table.
DROP TABLE product;

-- 7. Rename the new table into place.
ALTER TABLE product_new RENAME TO product;

-- 8. Recreate the indexes and triggers recorded in step 3.
CREATE INDEX product_org ON product(org);

-- 9. Recreate any VIEW that referenced the old table shape.

-- 10. If foreign keys were on, verify nothing broke - while you can still ROLLBACK.
PRAGMA foreign_key_check;

-- 11. Commit.
COMMIT;

-- 12. Restore the foreign-key setting (outside the transaction).
PRAGMA foreign_keys = ON;

Why each guard is there

Step	Guards against
FK off during the rebuild (1, 12)	DROP TABLE firing ON DELETE CASCADE and deleting child rows — the destructive failure this procedure exists to prevent
FK toggled outside the transaction	PRAGMA foreign_keys is silently ignored inside a transaction
Recording indexes/triggers first (3)	DROP TABLE takes them with it; they are gone before you notice
foreign_key_check before COMMIT (10)	Catching orphans while rollback is still possible
BEGIN IMMEDIATE (2)	A mid-migration upgrade deadlock — see concurrency-durability.md

Legacy ordering to avoid: the shorter "rename old, create new, copy, drop old" sequence (ALTER TABLE t RENAME TO t_old first) interacts badly with legacy_alter_table settings — references from other objects get rewritten to point at t_old. Use the order above.

---

Versioned migrations with user_version

SQLite reserves a 32-bit integer in the file header for exactly this. It costs no table and cannot drift from the file it describes.

PRAGMA user_version;        -- read (0 on a fresh database)
PRAGMA user_version = 3;    -- write (cannot be parameterised - interpolate an integer)

-- migrations/001_initial.sql
BEGIN IMMEDIATE;
CREATE TABLE product (
    id  INTEGER PRIMARY KEY,
    org TEXT NOT NULL,
    sku TEXT NOT NULL UNIQUE
) STRICT;
CREATE INDEX product_org ON product(org);
PRAGMA user_version = 1;
COMMIT;

sqlite3 app.db < migrations/001_initial.sql
sqlite3 app.db 'PRAGMA user_version;'    # -> 1

Approach	Pros	Cons
PRAGMA user_version	No extra table; atomic with the schema; trivially readable	Integer only — no names, timestamps, or audit trail
A schema_migrations table	Names, applied-at timestamps, per-migration audit	An extra table; created by migration zero

Use user_version for embedded/single-app databases; use a table when several people or services need to see what ran and when.

---

A named-migration runner

CREATE TABLE IF NOT EXISTS schema_migrations (
    name       TEXT PRIMARY KEY,
    applied_at TEXT NOT NULL DEFAULT (datetime('now')),
    checksum   TEXT
) STRICT;

Recording a checksum catches the nastiest migration bug: someone editing a migration that has already run somewhere, so environments silently diverge. Compare on startup and refuse to proceed on a mismatch.

import hashlib, pathlib, sqlite3

def migrate(conn: sqlite3.Connection, directory: str) -> list[str]:
    """Apply pending .sql migrations in filename order. Returns names applied."""
    conn.execute("""
        CREATE TABLE IF NOT EXISTS schema_migrations (
            name TEXT PRIMARY KEY,
            applied_at TEXT NOT NULL DEFAULT (datetime('now')),
            checksum TEXT
        ) STRICT
    """)
    applied = {r[0]: r[1] for r in
               conn.execute("SELECT name, checksum FROM schema_migrations")}

    done = []
    for path in sorted(pathlib.Path(directory).glob("*.sql")):
        sql = path.read_text(encoding="utf-8")
        checksum = hashlib.sha256(sql.encode()).hexdigest()

        if path.name in applied:
            if applied[path.name] != checksum:
                raise RuntimeError(
                    f"{path.name} changed after it was applied "
                    f"(recorded {applied[path.name][:12]}, now {checksum[:12]}). "
                    "Add a new migration instead of editing an applied one.")
            continue

        # Each file supplies its own BEGIN/COMMIT so a failure rolls that file back cleanly.
        conn.executescript(sql)
        conn.execute("INSERT INTO schema_migrations (name, checksum) VALUES (?, ?)",
                     (path.name, checksum))
        done.append(path.name)
    return done

Never edit an applied migration. Add a new one. The checksum turns that convention into an enforced invariant.

---

Runners per host

// better-sqlite3 / node:sqlite - synchronous, so the loop reads naturally
import Database from "better-sqlite3";
import { readFileSync, readdirSync } from "node:fs";

const db = new Database("app.db");
db.pragma("foreign_keys = ON");
db.exec(`CREATE TABLE IF NOT EXISTS schema_migrations (
           name TEXT PRIMARY KEY,
           applied_at TEXT NOT NULL DEFAULT (datetime('now'))) STRICT`);

const applied = new Set(db.prepare("SELECT name FROM schema_migrations").pluck().all());
const record  = db.prepare("INSERT INTO schema_migrations (name) VALUES (?)");

for (const file of readdirSync("migrations").filter(f => f.endsWith(".sql")).sort()) {
  if (applied.has(file)) continue;
  db.exec(readFileSync(`migrations/${file}`, "utf8"));   // file supplies BEGIN/COMMIT
  record.run(file);
}

# Cloudflare D1 - migrations are a first-class wrangler feature
wrangler d1 migrations create atdw-mirror add_covering_index
wrangler d1 migrations list   atdw-mirror --remote
wrangler d1 migrations apply  atdw-mirror --local     # test locally first
wrangler d1 migrations apply  atdw-mirror --remote    # MAINTAINER-GATED: this is a deploy

D1 tracks applied migrations in its own table and applies files in name order. Three differences from a local database: statements are subject to platform limits (100 KB per statement, 100 bound parameters — see d1-edge.md); a remote apply is a production deploy, so a working session writes the migration file and stops; and Time Travel gives you a 30-day undo that local SQLite does not, so take a bookmark before applying.

One more remote-apply trap: --remote can report a timeout while the migration actually landed, and blindly re-running is the mistake — verify the schema state read-only first (d1-production-patterns.md).

# aiosqlite - same logic, awaited. Run migrations at startup, before serving.
async def migrate(conn, directory: str) -> None:
    await conn.execute("""CREATE TABLE IF NOT EXISTS schema_migrations (
                            name TEXT PRIMARY KEY,
                            applied_at TEXT NOT NULL DEFAULT (datetime('now'))) STRICT""")
    cur = await conn.execute("SELECT name FROM schema_migrations")
    applied = {r[0] for r in await cur.fetchall()}
    for path in sorted(pathlib.Path(directory).glob("*.sql")):
        if path.name in applied:
            continue
        await conn.executescript(path.read_text(encoding="utf-8"))
        await conn.execute("INSERT INTO schema_migrations (name) VALUES (?)", (path.name,))
        await conn.commit()

---

Migrating large tables

The recreate procedure holds a write lock throughout and needs room for a second copy of the table. On a large table that is real downtime.

Technique	Detail
Prefer ADD COLUMN	O(1) and lock-free — model changes as additive whenever possible
Backfill in batches	New nullable column → backfill in ~10k-row chunks in separate transactions → add the constraint later via a recreate
Expand/contract	Add the new column, dual-write from the app, backfill, switch reads, drop the old column in a later release
Measure first	SELECT count(*), and confirm free disk ≥ 2x the table size
Schedule it	A recreate on a multi-GB table is minutes, not seconds

-- Batched backfill: bounded transactions, resumable, no long lock
UPDATE product SET price_cents = CAST(round(price * 100) AS INTEGER)
WHERE price_cents IS NULL
  AND id IN (SELECT id FROM product WHERE price_cents IS NULL LIMIT 10000);
-- repeat until 0 rows changed

On D1 this pattern is mandatory rather than optional: a statement that scans too much is killed by the 30-second query-duration limit or the isolate's CPU/memory limits.

---

Rollback strategy

Down-migrations are frequently more dangerous than the change they undo — a DROP COLUMN rollback destroys everything written since the migration ran.

Situation	Preferred response
Additive change (new column/table/index)	Roll forward — old code ignores the addition
Destructive change	Restore from backup (or D1 Time Travel); snapshot immediately before
Constraint tightened, data now violates it	Roll forward with a repair migration
Genuine need to reverse	Write an explicit down-migration and test it against a copy of production

# The rollback plan that always works: snapshot first
sqlite3 app.db "VACUUM INTO '/backup/pre-migration-$(date +%F-%H%M).db'"
sqlite3 app.db < migrations/007_recreate_product.sql

# On D1, capture a restore point instead
wrangler d1 time-travel info atdw-mirror     # record the bookmark BEFORE applying

Design migrations to be backwards-compatible for one release: deploy the schema change first, the code that depends on it second. Then a code rollback never needs a schema rollback.

---

Migration review checklist

• Wrapped in BEGIN IMMEDIATE … COMMIT
• PRAGMA foreign_keys = OFF around a table recreate — outside the transaction
• Every index, trigger, and view on a recreated table is recreated afterwards
• PRAGMA foreign_key_check before COMMIT
• Version recorded (user_version or a schema_migrations row) in the same transaction
• Tested against a copy of production data, not just an empty schema
• Backup (or D1 bookmark) captured immediately before a destructive change
• Free disk ≥ 2x the table size for a recreate
• ANALYZE after a change that alters data distribution or adds an index
• Query plans for affected statements re-checked afterwards
• No edits to an already-applied migration file
• For D1: the remote apply is left to the maintainer, not run from a working session

---

See also

• schema-design.md — designing the shape you are migrating to
• schema-patterns.md — ready-made table designs
• testing.md — idempotency, data-preservation, and integrity tests
• operations.md — backups before destructive changes
• d1-edge.md — wrangler migrations, Time Travel, and the deploy gate