Migrations

Tallybook added a currency column to invoices on a developer's laptop with one command. Staging got it a week later, typed by hand with a different default. Production got it a month later, by someone else, without the index. Three databases, three slightly different schemas, and a bug that only happened in production.

Schema changes need the same discipline as code: written down, reviewed, versioned, and applied the same way everywhere. That's what migrations are.

The concept

A numbered list of schema changes, each a small SQL script, kept in version control with the code:

Version	Change
1	create the tables
2	add `currency` to invoices, default `NGN`
3	add the customer-date index
4	create `credit_notes`

The database records which version it's at. A migration tool applies every pending migration, in order, each in a transaction, and records the new version. Running it again does nothing. SQLite has a built-in slot for this: PRAGMA user_version. Tools such as Alembic (Python) or Flyway do the same with a version table.

Example

import sqlite3

MIGRATIONS = [
    # 1: the first tables
    """
    CREATE TABLE customers (customer_id TEXT PRIMARY KEY, business_name TEXT NOT NULL);
    CREATE TABLE invoices (invoice_id TEXT PRIMARY KEY, customer_id TEXT NOT NULL REFERENCES customers (customer_id),
                           issue_date TEXT NOT NULL);
    """,
    # 2: invoices get a currency, defaulting to naira for every existing row
    "ALTER TABLE invoices ADD COLUMN currency TEXT NOT NULL DEFAULT 'NGN';",
    # 3: the portal's invoice list query needs this index (lesson 6)
    "CREATE INDEX idx_invoices_customer_date ON invoices (customer_id, issue_date);",
    # 4: credit notes (lesson 2's task)
    """
    CREATE TABLE credit_notes (credit_note_id INTEGER PRIMARY KEY, invoice_id TEXT NOT NULL REFERENCES invoices (invoice_id),
                               amount_kobo INTEGER NOT NULL CONSTRAINT positive_credit CHECK (amount_kobo > 0),
                               reason TEXT NOT NULL);
    """,
]

def migrate(conn, target=len(MIGRATIONS)):
    current = conn.execute("PRAGMA user_version").fetchone()[0]
    applied = []
    for version in range(current + 1, target + 1):
        conn.execute("BEGIN")
        try:
            for statement in MIGRATIONS[version - 1].split(";"):
                if statement.strip():
                    conn.execute(statement)
            conn.execute(f"PRAGMA user_version = {version}")
            conn.execute("COMMIT")
        except Exception:
            conn.execute("ROLLBACK")
            raise
        applied.append(version)
    return applied

conn = sqlite3.connect(":memory:", isolation_level=None)
print("Applied up to 2:", migrate(conn, target=2))
conn.execute("INSERT INTO customers VALUES ('C0001', 'Ada Stores')")
conn.execute("INSERT INTO invoices (invoice_id, customer_id, issue_date) VALUES ('INV-100001', 'C0001', '2026-09-01')")
print("Applied the rest:", migrate(conn))
print("Run again:", migrate(conn))
print("Schema version:", conn.execute("PRAGMA user_version").fetchone()[0])
print(conn.execute("SELECT invoice_id, currency FROM invoices").fetchall())

Applied up to 2: [1, 2]
Applied the rest: [3, 4]
Run again: []
Schema version: 4
[('INV-100001', 'NGN')]

The second run applies nothing: every environment that runs migrate ends up at version 4 with an identical schema, whatever version it started at. The existing invoice got the default currency without anyone touching it.

Walkthrough

Practice

What schema version does the database end at?

Task

8 min

Tallybook wants to rename customers.business_name to trading_name without downtime, while old and new versions of the app run side by side during a release. Write the steps as numbered migrations and releases, at least four, so that no running version of the app ever breaks.

Your work is checked for

At least four numbered steps
Adds the new column first
Copies or backfills the data
Code writes or reads both during the change
Drops the old column last
Drop comes in the last step

import sqlite3

MIGRATIONS = [
    # 1: the first tables
    """
    CREATE TABLE customers (customer_id TEXT PRIMARY KEY, business_name TEXT NOT NULL);
    CREATE TABLE invoices (invoice_id TEXT PRIMARY KEY, customer_id TEXT NOT NULL REFERENCES customers (customer_id),
                           issue_date TEXT NOT NULL);
    """,
    # 2: invoices get a currency, defaulting to naira for every existing row
    "ALTER TABLE invoices ADD COLUMN currency TEXT NOT NULL DEFAULT 'NGN';",
    # 3: the portal's invoice list query needs this index (lesson 6)
    "CREATE INDEX idx_invoices_customer_date ON invoices (customer_id, issue_date);",
    # 4: credit notes (lesson 2's task)
    """
    CREATE TABLE credit_notes (credit_note_id INTEGER PRIMARY KEY, invoice_id TEXT NOT NULL REFERENCES invoices (invoice_id),
                               amount_kobo INTEGER NOT NULL CONSTRAINT positive_credit CHECK (amount_kobo > 0),
                               reason TEXT NOT NULL);
    """,
]

def migrate(conn, target=len(MIGRATIONS)):
    current = conn.execute("PRAGMA user_version").fetchone()[0]
    applied = []
    for version in range(current + 1, target + 1):
        conn.execute("BEGIN")
        try:
            for statement in MIGRATIONS[version - 1].split(";"):
                if statement.strip():
                    conn.execute(statement)
            conn.execute(f"PRAGMA user_version = {version}")
            conn.execute("COMMIT")
        except Exception:
            conn.execute("ROLLBACK")
            raise
        applied.append(version)
    return applied

conn = sqlite3.connect(":memory:", isolation_level=None)
print("Applied up to 2:", migrate(conn, target=2))
conn.execute("INSERT INTO customers VALUES ('C0001', 'Ada Stores')")
conn.execute("INSERT INTO invoices (invoice_id, customer_id, issue_date) VALUES ('INV-100001', 'C0001', '2026-09-01')")
print("Applied the rest:", migrate(conn))
print("Run again:", migrate(conn))
print("Schema version:", conn.execute("PRAGMA user_version").fetchone()[0])
print(conn.execute("SELECT invoice_id, currency FROM invoices").fetchall())

The problem

The concept

Example

Walkthrough

Practice

Check your understanding

Migrations

The problem

The concept

Example

Walkthrough

Practice

Check your understanding

01The problem

02The concept

03Example

04Walkthrough

05Practice

06Check your understanding

01The problem

02The concept

03Example

04Walkthrough

05Practice

06Check your understanding

The problem

The concept

Example

Walkthrough

Practice

Check your understanding

The problem

The concept

Example

Walkthrough

Practice

Check your understanding