--- title: 'Schema Migrations' source: 'https://academia.sh/en/courses/data-access-layer/schema-migrations' course: 'The Data Access Layer and Business Logic' language: en updated: '2026-08-19T05:19:35+00:00' license: 'CC BY-SA 4.0' --- # Schema Migrations Splitting a schema change into versioned steps: forward and backward migration scripts, the version table, the atomic rollback of a failed step, and changing a column without an outage through an expand-write-contract scheme. 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. ```sh 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. ```js // 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 "); } ``` 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 ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```js // 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", "old@example.test", "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}`); } ``` ```js // 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", "new@example.test", "new@example.test", "2025-07-20"); } else { db.prepare("INSERT INTO member (first_name, last_name, email_address, registered_at) VALUES (?,?,?,?)") .run("New", "Record", "new@example.test", "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. ```sh # 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','alice@example.test','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 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. ```sh 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 alice@example.test alice@example.test 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.