Adopting Versioned Schema Migrations in a Database That Already Has Data

TL;DR — A long-lived service accumulates schema changes, and the tempting way to apply them is defensive inline code: inspect the table, check whether a column exists, ALTER TABLE if it doesn't. That works until you need to know what changed, when, or how to undo it — none of which the inline approach records. A migration tool replaces the inspection with a number: each change is a numbered revision, and the database stores which revision it has reached. Adopting one on a database that already holds production data has exactly one hard step — telling the tool what is already true, which is called stamping. Stamp a specific revision after verifying that revision's tables genuinely exist; stamping the latest revision (head) asserts that every step has already run, and any step that in fact never ran is then skipped permanently, leaving a database that is structurally wrong but reports itself as current. In the harness below, stamping the correct revision runs 1 of 3 migrations and backfills correctly, while stamping head runs 0 and leaves a column missing that only surfaces as an error at the first query that reads it. This whole model assumes revisions form a single ordered line; parallel branches of schema history need explicit merge revisions, and destructive changes (dropping or narrowing a column) need a multi-phase pattern that a single revision cannot express safely.

---

1. Simple explanation

Two things can be true of a running service: the code needs a database column that doesn't exist yet, and the database contains real data you cannot throw away. Reconciling those is the whole problem of schema evolution.

The approach most systems reach for first is defensive and inline. On startup, ask the database what it looks like, and patch anything missing:

does table "members" have a column "tiers_json"?
    no  -> ALTER TABLE members ADD COLUMN tiers_json ...
    yes -> skip

This is genuinely appealing: idempotent, no tooling, and a fresh install behaves like an upgrade. Many production systems run it for years.

It fails in four ways, all about information rather than correctness:

• No history. The code tells you the schema's current target, never how it got there. "When did tiers_json appear?" has no answer short of reading git log across the whole file.
• No reverse. Each block describes how to move forward. Undoing a change means writing a new block that undoes it, which the next deploy then has to skip.
• Order is implicit. The blocks run top to bottom, so their order is whatever order someone happened to write them in. A change that must precede another is correct only by accident of line number.
• Growth is unbounded. Every schema change ever made stays in the file forever, re-checked on every single startup, because nothing records that it is already done.

A migration tool — Alembic for SQLAlchemy, Flyway or Liquibase in the JVM world, Django's built-in migrations, Rails' ActiveRecord::Migration — replaces all four with a single idea: number the changes, and let the database remember its number.

revision 1 ──► 2 ──► 3 ──► 4
                              ▲
                        "head" = newest

The database itself stores one row saying "I am at revision 3." Upgrading means: read that number, run the revisions above it, update the number. The tool no longer inspects the schema to guess what to do — it reads a number and does arithmetic.

The analogy is version control. Inline checks are a build script patching source files at compile time to whatever the code currently needs. Numbered revisions are commits: ordered, named, individually revertible, with the database holding the equivalent of a checked-out HEAD.

---

2. Diagram

The two approaches, and the adoption problem that sits between them:

  ── INLINE INSPECTION ────────────────────────────────────────────────
    every startup
        │
        ├─ has members.tiers_json?   no ─► ALTER TABLE  ─┐
        ├─ has members.active_tier?  no ─► ALTER TABLE  ─┤ runs
        ├─ has rentals.returned_at?  no ─► ALTER TABLE  ─┤ forever
        └─ ... one block per change ever made ──────────┘
      state recorded: NONE. history: NONE. reverse: NONE.


  ── NUMBERED REVISIONS ───────────────────────────────────────────────
    revisions on disk           database's own record
    ┌───┐ ┌───┐ ┌───┐ ┌───┐     ┌──────────────────┐
    │ 1 │ │ 2 │ │ 3 │ │ 4 │     │ schema_version=3 │
    └───┘ └───┘ └───┘ └───┘     └──────────────────┘
                        ▲                   │
                      head                  │ "run everything above 3"
                                            ▼
                                       runs 4 only


  ── THE ADOPTION PROBLEM ─────────────────────────────────────────────
    An existing database HAS the structure of revisions 1-3,
    but has never met the tool, so its recorded version is 0.

       upgrade from 0  ─►  tries revision 1  ─►  CREATE TABLE members
                                                 ✗ "table already exists"

    Fix: write down the version WITHOUT running anything.

       stamp 3   ─►  schema_version = 3     (nothing executed)
       upgrade   ─►  runs revision 4 only   ✓


  ── THE TRAP ─────────────────────────────────────────────────────────
    stamp head  ─►  schema_version = 4      (nothing executed)
                         │
                         │  but revision 4 never ran
                         ▼
                    upgrade runs NOTHING, reports "up to date"
                         │
                         ▼
                    column from revision 4 is absent.
                    Error appears at the first query that reads it —
                    arbitrarily far from the cause, in a different
                    file, possibly weeks later.

The shape worth internalising: stamp is the only operation in the system that writes a version without doing the work. That makes it both the tool you need to adopt migrations at all, and the only way to put the database into a state that lies about itself.

---

3. How it works

3.1 A migration tool's entire persistent state is one number

Strip away the CLI, the autogeneration, and the templating, and a migration framework maintains a single scalar: which revision this database has reached. It lives in a table the tool owns — alembic_version for Alembic, flyway_schema_history for Flyway, django_migrations for Django.

Everything follows from that number:

• upgrade — run every revision above it, in order, updating it after each.
• downgrade — reverse every revision above a target, in reverse order.
• current — print it.
• stamp — set it, run nothing.

The reason this is more robust than inspection is not that it does more work. It does less: it never asks the database what it looks like. Inspection-based migration re-derives state on every startup and can be fooled by any partial change. A recorded version is a claim made once, at the moment the work succeeded.

That is also precisely the weakness. The number is trusted absolutely. Nothing verifies it against reality.

3.2 Adopting the tool means asserting what is already true

A fresh database and an existing one need opposite treatment. Fresh: run everything. Existing: run nothing yet, but record how far it has effectively already come.

The correct procedure has two parts, and the first is the one people skip:

1. Verify. Read the revisions in order and confirm, against the live database, which ones are genuinely already applied. Revision 3 is "applied" only if every table, column and index it creates actually exists.
2. Stamp that revision — not the newest one, the one you verified.

The verification step is unglamorous and there is no tool for it. It is reading a list and checking a schema. Skipping it and stamping head is faster by several minutes and is the single most common way to corrupt a migration adoption.

3.3 Additive changes are safe; destructive ones need phases

Most revisions are additive: create a table, add a nullable column, add an index. These are safe to run against a live database because existing code neither reads nor writes the new thing.

Two complications sit on top of that.

Backfill. Adding a column is rarely the whole change. If a column replaces or generalises an existing one, existing rows need values derived from the old column. That derivation belongs in the same revision as the ALTER TABLE, because a schema where the column exists but is unpopulated is a state no code was written to handle. In the harness below, revision 3 adds tiers_json and immediately populates it from the older scalar tier column — one revision, two statements, atomic.

Destructive changes cannot be one revision. Dropping a column, narrowing a type, or renaming anything breaks any running process still using the old shape. During a rolling deploy, old and new code run simultaneously by design. The standard pattern is four phases across separate releases — add the new column; write to both; migrate reads to the new column; only then drop the old one. A single revision that drops a column is safe only if you can guarantee no old code is running, which usually means accepting downtime.

---

4. The math

Take three revisions against a database that already has the structure of revisions 1 and 2, holding one row where tier = 'annual'.

Revision inventory:

What runs under each choice of stamp:

The arithmetic of the correct case: 3 revisions exist, 2 are already satisfied, so 3 − 2 = 1 revision runs, executing 2 statements. The wrong case runs 3 − 3 = 0 revisions and executes 0 statements — and, critically, reports success, because from the tool's perspective there was nothing to do.

Why guessing is a bad strategy. With n revisions there are n + 1 possible stamp points (0 through n), exactly one of which is right for a given database. At n = 3 a guess is right 1 time in 4. At n = 12 — an ordinary count for a service two years old — it is 1 in 13. And the failure is not distributed evenly: stamping too low fails loudly and immediately ("already exists"), while stamping too high fails silently and later. The dangerous half of the guess space is the half that looks like it worked.

The detection delay. A skipped revision surfaces only when something reads the structure it should have created. If that is one endpoint among many, the error waits for the next person to use that feature — which may be well after the deploy, in a stack trace pointing at a query rather than at the migration that never ran.

---

5. Real code

A migration runner in about 25 lines, so the mechanism is visible rather than hidden behind a CLI. Then the three scenarios from §4, run for real.

import sqlite3

# --- three migrations, each a numbered step -------------------------------
def m001(c):
    c.execute("CREATE TABLE members (id INTEGER PRIMARY KEY, email TEXT, tier TEXT)")

def m002(c):
    c.execute("CREATE TABLE rentals (id INTEGER PRIMARY KEY, member_id INTEGER, station TEXT)")

def m003(c):
    c.execute("ALTER TABLE members ADD COLUMN tiers_json TEXT NOT NULL DEFAULT '[]'")
    c.execute("UPDATE members SET tiers_json = json_array(tier) WHERE tier IS NOT NULL")

MIGRATIONS = [(1, m001), (2, m002), (3, m003)]

# --- the version bookkeeping: this is all a migration tool really adds ----
def version(c):
    c.execute("CREATE TABLE IF NOT EXISTS schema_version (v INTEGER NOT NULL)")
    row = c.execute("SELECT v FROM schema_version").fetchone()
    return row[0] if row else 0

def _write(c, v):
    c.execute("DELETE FROM schema_version")
    c.execute("INSERT INTO schema_version (v) VALUES (?)", (v,))

def upgrade(c):
    at, ran = version(c), []
    for v, fn in MIGRATIONS:
        if v > at:
            fn(c); _write(c, v); ran.append(v)
    return ran

def stamp(c, v):
    version(c); _write(c, v)      # records a version WITHOUT running anything

def legacy_db():
    """A database that predates the migration tool: tables exist, no version row."""
    c = sqlite3.connect(":memory:")
    c.execute("CREATE TABLE members (id INTEGER PRIMARY KEY, email TEXT, tier TEXT)")
    c.execute("CREATE TABLE rentals (id INTEGER PRIMARY KEY, member_id INTEGER, station TEXT)")
    c.execute("INSERT INTO members (email, tier) VALUES ('a@example.org', 'annual')")
    return c

# --- A. fresh database: every step runs ----------------------------------
c = sqlite3.connect(":memory:")
print("A fresh     ran:", upgrade(c), "-> version", version(c))

# --- B. existing database: stamp what is already true, then upgrade ------
c = legacy_db()
stamp(c, 2)
print("B stamp(2)  ran:", upgrade(c), "-> version", version(c),
      "| backfilled:", c.execute("SELECT tiers_json FROM members").fetchone()[0])

# --- C. the trap: stamp head, claiming 003 ran when it never did ---------
c = legacy_db()
stamp(c, 3)
ran = upgrade(c)
try:
    c.execute("SELECT tiers_json FROM members").fetchone()
    outcome = "column present"
except sqlite3.OperationalError as e:
    outcome = f"OperationalError: {e}"
print("C stamp(3)  ran:", ran, "-> version", version(c), "|", outcome)

assert outcome.startswith("OperationalError"), "the trap must fail"
print("\nverified: stamping a step that never ran skips it permanently")

# Output:
# A fresh     ran: [1, 2, 3] -> version 3
# B stamp(2)  ran: [3] -> version 3 | backfilled: ["annual"]
# C stamp(3)  ran: [] -> version 3 | OperationalError: no such column: tiers_json
#
# verified: stamping a step that never ran skips it permanently

Three things in that output are worth reading twice.

B ran [3], not [1, 2, 3]. Stamping at 2 is what makes adoption possible at all. Without it, revision 1 immediately tries to create a table that exists.

B's backfill produced ["annual"]. The ALTER TABLE and the UPDATE are in the same revision, so there is no window in which the column exists but is empty.

C reports version 3 — the same number as B — while being structurally different. Both databases claim to be current. One is. No command in the system will tell you which, because the version number is the only state the tool has, and both have the same one. This is the whole argument for verifying before stamping: it is the last moment at which the truth is cheap to establish.

---

6. Real-world example

A service had run inline inspection-based migration for about eighteen months: a function called at startup that checked for missing columns and added them. Two columns had been added this way, one of them replacing an older scalar column, with a backfill UPDATE alongside it.

The team introduced a migration tool to get history and reversibility. Adoption went fine. What did not go fine was that the old function was left in place, on the reasoning that removing it was a separate cleanup and it was harmless because it was idempotent.

The startup path now looked like this:

  service starts
      ├─ create_all()                    ← creates any MISSING table
      ├─ apply_inline_migrations()       ← the old if-blocks
      └─ migration_tool upgrade head     ← the new system

Three mechanisms with an opinion about the schema, and no defined precedence. It worked, until a revision changed the default value of one of the columns the old function also managed. The revision set the new default; the old function, running earlier in the same startup, had already added the column with the old default when it found it missing on a fresh install. On existing databases the revision applied cleanly. On fresh installs the column got the old default and the revision saw the column already present and did nothing. Fresh and existing databases diverged, and every test ran against fresh databases.

Two details made it worse.

The inline function was wrapped in a try/except that logged a warning and continued. A failure inside it did not stop startup. That is a defensible choice for a best-effort patch, and a bad one once it shares responsibility with a system whose whole purpose is to guarantee the schema — it converts "the schema is wrong" from a startup failure into a line in a log nobody reads.

create_all() masked the symptom. It creates missing tables but never alters existing ones. So a table that a revision should have created appeared anyway, while a column the same revision should have added did not. Half the revision's effect happened by a different route, which is why the resulting failure looked like a data problem rather than a migration one.

The fix was ordinary — delete the inline function, let the tool own the schema, add a startup assertion that the recorded version matches the newest revision on disk. The lesson is the one the adoption should have started with: two systems that both manage the schema is not a transitional state to be tidied up later. It is the bug, already present, waiting for a revision that touches the overlap.

---

7. Interview questions companies actually ask

Why not just check whether the column exists before adding it? It works, and it is idempotent, which is why it is so common. It records nothing: no history, no ordering guarantee, no reverse. You cannot answer "what changed last Tuesday" or "put it back". And it re-checks every change on every startup forever, because nothing marks a change as done.

What does stamping do, and when do you need it? It writes the recorded revision without executing anything. You need it exactly once per database: when adopting a migration tool on a database that already has structure. Without it, the first upgrade tries to create objects that already exist.

Why is stamping the newest revision dangerous? It asserts every revision has already been applied. Any revision that in fact never ran is now permanently above the recorded version, so it will never be selected by an upgrade. The database reports itself current while missing structure, and the failure surfaces later at whatever query first needs the missing column.

How do you drop a column safely on a live system? Not in one revision. During a rolling deploy old and new code run at once, so removing a column that old code still reads breaks it. Add the replacement, write to both, move reads across, then drop the old column in a later release once no running code references it.

Where does a backfill belong? In the same revision as the schema change that requires it. A column that exists but is unpopulated is a state no code was written for. Splitting them creates a window where the schema is valid and the data is not.

Two systems both manage your schema. How do you decide which goes? Whichever cannot express reversibility and ordering — which is the inline one. The real answer is that the decision has to be made deliberately at adoption time, because the failure mode is a revision that touches the overlap, and that arrives on its own schedule.

How do you verify a migration actually applied? Compare the recorded version to the newest revision on disk at startup and fail loudly if they differ. That catches a skipped upgrade, though not a bad stamp — nothing detects a bad stamp except having verified before making it.

---

8. When to use / tradeoffs

Numbered revisions are the right default for any schema that will outlive one release. The cases below are where the model strains.

Honest limits. Versioned revisions guarantee ordering, history and reversibility of declared changes. They guarantee nothing about whether the recorded version is true, because no tool re-derives schema state from the database — that trust is the design, and it is what makes stamping both necessary and hazardous. Downgrade support is weaker in practice than it appears: reversing an additive change is mechanical, but a revision that dropped a column or transformed data cannot restore what it discarded, so "reversible" often means "reversible in structure, not in content." The model also says nothing about coordinating schema changes with application deploys; a revision can be correct in isolation and still break production by arriving one release too early. And the four-phase pattern for destructive change is a discipline, not a feature — no tool enforces it, and the pressure to collapse it into one revision is strongest exactly when the change is most dangerous.

---

9. Summary + related articles

• Inline "does this column exist?" checks are idempotent and genuinely workable, but record no history, no ordering, and no reverse — the cost is informational, and it comes due when you need to explain or undo a change.
• A migration tool's whole persistent state is one number: which revision this database has reached. Every command is arithmetic over that number.
• Adopting the tool on an existing database requires stamping, and stamping requires verifying first. Stamp the revision you confirmed, never the newest one.
• Measured: across three revisions, stamping the verified revision ran 1 migration and backfilled 1 row correctly; stamping the newest ran 0 migrations, left the column absent, and reported the same version number as the correct database.
• Boundary: the model assumes a single ordered line of revisions and non-destructive changes. Parallel branches need explicit merge revisions; dropping or narrowing a column needs four phases across separate releases, which no tool will enforce for you.

Related:

• Designing a Self-Serve Platform: One Model for Individuals and Organizations — the data model whose evolution these revisions manage
• Effective Access: Combining Two Levels of Role-Based Permissions — a concrete case of the additive-column-plus-backfill change described in §3.3
• Vector Databases: Dimensions, Footprint, and the Migration Nobody Plans For — the analogous problem in a store with no schema, where changing an embedding model forces a full reindex rather than an alterable migration