Schema Migrations

The connection pool works on the assumption that every connection sees the same schema. The assumption holds until the schema changes. When a column is added, removed, or renamed, the application’s running instances and the database’s state pull apart: which instance sees which schema depends on when each instance was last refreshed.

The SQL Fundamentals course introduced a schema change as a command run once, on its own. At the application layer, the same command becomes a migration step: versioned, applied in sequence, reversible, and recorded so that it is known which step has been applied in which environment. This lesson builds a migration runner, runs it in both directions, and changes a column without an outage.

Steps and the Version Table

Migration steps are files. Each step’s forward and backward direction lives in a separate file; the name begins with a sequence number.

mkdir -p migrations
cat > migrations/001-loan-table.up.sql <<'SQL'
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
                  email TEXT, registered_at TEXT NOT NULL);
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
                    member_id INTEGER NOT NULL REFERENCES member(member_id),
                    pickup_date TEXT NOT NULL, return_date TEXT);
SQL
cat > migrations/001-loan-table.down.sql <<'SQL'
DROP TABLE loan;
DROP TABLE member;
SQL
cat > migrations/002-overdue-fine.up.sql <<'SQL'
ALTER TABLE loan ADD COLUMN overdue_fine REAL NOT NULL DEFAULT 0;
SQL
cat > migrations/002-overdue-fine.down.sql <<'SQL'
ALTER TABLE loan DROP COLUMN overdue_fine;
SQL
cat > migrations/003-open-loan-index.up.sql <<'SQL'
CREATE INDEX loan_open_index ON loan (member_id) WHERE return_date IS NULL;
SQL
cat > migrations/003-open-loan-index.down.sql <<'SQL'
DROP INDEX loan_open_index;
SQL

The information about which step has been applied lives in the database itself. If it were kept on the file system or in a configuration file, it would not travel with the database when the database is copied.

// migrate.mjs — runs migration steps up and down by checking the version table
import { DatabaseSync } from "node:sqlite";
import { readdirSync, readFileSync } from "node:fs";

const db = new DatabaseSync(process.env.DB_PATH ?? "library.db");
db.exec(`CREATE TABLE IF NOT EXISTS schema_version (
           version INTEGER PRIMARY KEY, name TEXT NOT NULL, applied_at TEXT NOT NULL)`);

const steps = [...new Set(readdirSync("migrations").map((d) => d.replace(/\.(up|down)\.sql$/, "")))]
  .sort()
  .map((name) => ({ version: Number(name.slice(0, 3)), name }));

const appliedVersions = () => db.prepare("SELECT version FROM schema_version ORDER BY version")
                          .all().map((r) => r.version);

function runStep(step, direction) {
  const sql = readFileSync(`migrations/${step.name}.${direction}.sql`, "utf8");
  db.exec("BEGIN");
  try {
    db.exec(sql);
    if (direction === "up") {
      db.prepare("INSERT INTO schema_version VALUES (?,?,datetime('now'))").run(step.version, step.name);
    } else {
      db.prepare("DELETE FROM schema_version WHERE version = ?").run(step.version);
    }
    db.exec("COMMIT");
    console.log(`${direction === "up" ? "up  " : "down"}  ${step.name}`);
  } catch (err) {
    db.exec("ROLLBACK");
    console.log(`ERROR  ${step.name}: ${err.message}`);
    process.exit(1);
  }
}

const [command, targetArg] = process.argv.slice(2);
const target = targetArg === undefined ? null : Number(targetArg);

if (command === "status") {
  const done = new Set(appliedVersions());
  for (const step of steps) console.log(`${done.has(step.version) ? "[x]" : "[ ]"} ${step.name}`);
  console.log("current version:", Math.max(0, ...done));
} else if (command === "up") {
  const done = new Set(appliedVersions());
  for (const step of steps) {
    if (!done.has(step.version) && (target === null || step.version <= target)) runStep(step, "up");
  }
} else if (command === "down") {
  const doneVersions = appliedVersions().sort((x, y) => y - x);
  for (const v of doneVersions) {
    if (v > target) runStep(steps.find((step) => step.version === v), "down");
  }
} else {
  console.log("usage: node migrate.mjs status | up [version] | down <version>");
}

The runner makes three decisions. The step and the version record are written in the same transaction; steps are ordered by file name, so the numbering determines the sequence; and the order reverses in the backward direction.

Forward Execution

node migrate.mjs status
node migrate.mjs up 3
node migrate.mjs status

[ ] 001-loan-table
[ ] 002-overdue-fine
[ ] 003-open-loan-index
current version: 0
up    001-loan-table
up    002-overdue-fine
up    003-open-loan-index
[x] 001-loan-table
[x] 002-overdue-fine
[x] 003-open-loan-index
current version: 3

The three steps were applied in sequence and written to the version table. The second status call reads the information from the database, not from the files; if the same migration directory were run against a different database, every box there would show empty.

Atomicity of a Failed Step

If an error occurs partway through a step, that step must not be left half-applied. The step below deliberately produces an error on its second line.

cat > migrations/004-broken-example.up.sql <<'SQL'
ALTER TABLE loan ADD COLUMN branch_id INTEGER;
ALTER TABLE loan ADD COLUMN branch_id INTEGER;
SQL
cat > migrations/004-broken-example.down.sql <<'SQL'
ALTER TABLE loan DROP COLUMN branch_id;
SQL
node migrate.mjs up 4
echo "exit code: $?"
node migrate.mjs status
sqlite3 library.db "PRAGMA table_info(loan);" | grep -c branch_id

ERROR  004-broken-example: duplicate column name: branch_id
exit code: 1
[x] 001-loan-table
[x] 002-overdue-fine
[x] 003-open-loan-index
[ ] 004-broken-example
current version: 3
0

Three results appear together: the runner returned a nonzero exit code, the step was not marked in the version table, and the number on the last line shows that the branch_id column is not in the table. The first ALTER TABLE command had succeeded; when the transaction rolled back, it was rolled back too.

This behavior is engine-dependent. Not every relational database can roll back schema-changing commands inside a transaction; on engines that cannot, each step is shrunk to a single command, because being left half-applied requires manual repair.

Backward Execution

The backward direction runs the applied steps in reverse.

node migrate.mjs down 1
node migrate.mjs status

down  003-open-loan-index
down  002-overdue-fine
[x] 001-loan-table
[ ] 002-overdue-fine
[ ] 003-open-loan-index
[ ] 004-broken-example
current version: 1

The index was dropped first, then the column was removed. If the order were reversed, an index that used the column would be left dangling. If the backward direction is left unwritten, the migration becomes one-directional, and there is no way back from a faulty version.

Zero-Downtime Column Change

The real difficulty is a change made while the application keeps running. The email column on the member relation is being renamed to email_address. Renaming it in a single step is a breaking change: the moment the renaming command runs, application instances that have not yet been refreshed start failing.

The solution is to split the change into three steps. The Relational Database Administration course covered this scheme under the name expand–contract migration; a writing phase sits between the two ends.

1. Expand: the new column is added, and the existing values are copied over. The old column stays.
2. Write: the new version of the application writes to both columns and reads from the new one. The writing behavior in this phase is called dual write. The old version keeps running too.
3. Contract: once every instance has been refreshed, the old column is removed.

The steps are three migration files.

cat > migrations/005-email-expand.up.sql <<'SQL'
ALTER TABLE member ADD COLUMN email_address TEXT;
UPDATE member SET email_address = email WHERE email_address IS NULL;
SQL
cat > migrations/005-email-expand.down.sql <<'SQL'
ALTER TABLE member DROP COLUMN email_address;
SQL
cat > migrations/006-email-backfill.up.sql <<'SQL'
UPDATE member SET email_address = email WHERE email_address IS NULL AND email IS NOT NULL;
SQL
cat > migrations/006-email-backfill.down.sql <<'SQL'
SELECT 1;
SQL
cat > migrations/007-email-contract.up.sql <<'SQL'
ALTER TABLE member DROP COLUMN email;
SQL
cat > migrations/007-email-contract.down.sql <<'SQL'
ALTER TABLE member ADD COLUMN email TEXT;
UPDATE member SET email = email_address;
SQL

Two versions of the code stand in for the application’s old and new instances.

// old-code.mjs — running old version: knows only the email column
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");
try {
  db.prepare("INSERT INTO member (first_name, last_name, email, registered_at) VALUES (?,?,?,?)")
    .run("Old", "Record", "[email protected]", "2025-07-20");
  const n = db.prepare("SELECT count(*) AS n FROM member WHERE email IS NOT NULL").get().n;
  console.log(`old code: ran, rows with email = ${n}`);
} catch (err) {
  console.log(`old code: ERROR -> ${err.message}`);
}

// new-code.mjs — new version: reads the email_address column; writes to the old one too when DUAL_WRITE=1
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");
const dual = process.env.DUAL_WRITE === "1";
try {
  if (dual) {
    db.prepare("INSERT INTO member (first_name, last_name, email_address, email, registered_at) VALUES (?,?,?,?,?)")
      .run("New", "Record", "[email protected]", "[email protected]", "2025-07-20");
  } else {
    db.prepare("INSERT INTO member (first_name, last_name, email_address, registered_at) VALUES (?,?,?,?)")
      .run("New", "Record", "[email protected]", "2025-07-20");
  }
  const n = db.prepare("SELECT count(*) AS n FROM member WHERE email_address IS NOT NULL").get().n;
  console.log(`new code: ran (dual write=${dual ? "on" : "off"}), rows with email_address = ${n}`);
} catch (err) {
  console.log(`new code: ERROR -> ${err.message}`);
}

The script below sets up the database from scratch and runs both code versions after every step. The broken example step is deleted so that it does not block the steps that follow.

# zero-downtime.sh — the state of the two code versions across a three-step column change
rm -f library.db migrations/004-broken-example.up.sql migrations/004-broken-example.down.sql
node migrate.mjs up 3 >/dev/null
sqlite3 library.db "INSERT INTO member (first_name,last_name,email,registered_at)
  VALUES ('Alice','Kane','[email protected]','2023-02-14'),
         ('Clara','Diaz',NULL,'2024-01-09');"

echo "== version 3: only the old column exists =="
node old-code.mjs
node new-code.mjs

echo "== version 5 (expand): new column added and backfilled =="
node migrate.mjs up 5
node old-code.mjs
DUAL_WRITE=1 node new-code.mjs

echo "== version 6 (backfill): what the old code wrote moved to the new column =="
node migrate.mjs up 6
node old-code.mjs
DUAL_WRITE=1 node new-code.mjs

echo "== version 7 (contract): old column removed =="
node migrate.mjs up 7
node old-code.mjs
node new-code.mjs
echo "-- rows with no address --"
sqlite3 -header -column library.db "SELECT member_id, first_name, last_name FROM member WHERE email_address IS NULL;"

sh zero-downtime.sh

== version 3: only the old column exists ==
old code: ran, rows with email = 2
new code: ERROR -> table member has no column named email_address
== version 5 (expand): new column added and backfilled ==
up    005-email-expand
old code: ran, rows with email = 3
new code: ran (dual write=on), rows with email_address = 3
== version 6 (backfill): what the old code wrote moved to the new column ==
up    006-email-backfill
old code: ran, rows with email = 5
new code: ran (dual write=on), rows with email_address = 5
== version 7 (contract): old column removed ==
up    007-email-contract
old code: ERROR -> table member has no column named email
new code: ran (dual write=off), rows with email_address = 6
-- rows with no address --
member_id  first_name  last_name
---------  ----------  ---------
2          Clara       Diaz     
6          Old         Record   

The two middle phases are the proof of zero downtime: at versions 5 and 6, both the old and the new code ran without error. The only moment the change is breaking is version 7, and that moment can be deferred until every instance has been refreshed.

Condition of the Contract Step

The last part of the output carries a warning. Two rows are left with an empty email_address column. Of these, Clara had no address from the start; Old Record is the problematic one. The old code wrote that row after the backfill step: it wrote to the old column, the new column stayed empty, and the value was lost when the contract step removed the old column.

The condition for the contract step follows from this: no instance may still be writing to the old column. The condition is met not by repeating the backfill step, but by confirming that the old instances have been fully withdrawn. In practice, this confirmation is made in two ways: verifying that the rollout has finished, and checking, immediately before the contract step, that the number of rows left empty is zero.

The Limit of a Down Migration

Reversing the contract step is possible, but the reversal brings back the schema, not the data.

node migrate.mjs down 6
sqlite3 -header -column library.db "SELECT member_id, first_name, email, email_address FROM member WHERE member_id IN (1,2,6);"

down  007-email-contract
member_id  first_name  email               email_address     
---------  ----------  ------------------  ------------------
1          Alice       [email protected]  [email protected]
2          Clara                                             
6          Old                                               

The email column came back and was filled from the email_address values. Row six’s address is empty again: the information was lost for good when the contract step deleted it. A down migration is a rollback plan, not a backup. The backward direction of a data-losing step is meaningful only if the deleted data was saved somewhere else; otherwise, the reversal only repairs the schema.

Summary

• A migration step is a versioned, two-directional unit applied in sequence; the information about which step has been applied lives in the version table inside the database itself.
• The step itself and the version record are written in the same transaction; on the step that failed, both were rolled back together, and the version table did not change.
• Changing a column without an outage takes three phases: expand, write with dual write, contract. In the two middle phases, the old and new code versions ran together.
• The contract step cannot be applied while an instance is still writing to the old column; when the rule was not followed, the value of a row written after the backfill was lost.
• A down migration undoes the schema, not deleted data; a rollback plan does not stand in for a backup.

Next Step

The migration runner opened every step with BEGIN, closed it with COMMIT, and called ROLLBACK on error; that is what made the step and the version record get written together. The same question applies to the application’s ordinary operation: which writes does a loan request cover, must all of these writes become final together, and which layer holds the code that opens and closes the transaction? The next topic opens with this question, and its first lesson builds the transaction boundary on the definition of a unit of work: a boundary drawn too narrow produces half-finished states, and one drawn too wide produces locks held for a long time.