--- title: 'Coordination with Database Migrations' source: 'https://academia.sh/en/courses/ci-cd/coordination-with-database-migrations' course: 'Continuous Integration and Delivery' language: en updated: '2026-09-13T05:08:18+00:00' license: 'CC BY-SA 4.0' objectives: - 'Measures migration types on an axis of reversibility: is the content identical after the reverse operation' - 'Counts, at each expand–migrate–contract step, whether two versions can run together' - "Derives the incompatible step's cost to the release window and the rollback plan" --- # Coordination with Database Migrations Six migration types are actually applied and reversed, and reversibility is measured not by whether the command runs without error but by whether the table's content comes out identical. The expand–migrate–contract scheme runs step by step; at every step, whether the old and new versions can run together is counted, and the incompatible step's effect on the release window and the rollback plan is written down. Two of the previous lesson's three scenarios had a point of no return, and in the data scenario, that point was the reverse migration step. A rollback plan is only as reversible as every step inside it, and a schema change is the most fragile of those steps. Code comes back by selecting an artifact, configuration by writing a value. For schema, this is not always true. This lesson's axis is **reversibility**. Migration-versus-code-release ordering, and how many requests that ordering errors on, were measured in **M16/K08 Server Security and Going to Production**; the question asked here is not ordering but class: which migration types are fully reversible, which look like they work but bring the data back incomplete, and which have no way back at all. The second measure is whether the steps **carry two versions together** — this is where the rollback plan intersects with the release window. - **RS37.** The schema lives in a single database, and every version reads and writes the same table. - **RS38.** Migration steps run separately from the application release; the two are not part of the same operation. - **RS39.** Reversibility is measured by the table's content being **identical** once the reverse operation has run. The command running without error is not enough on its own. - **RS40.** Two versions are compatible when each can correctly read the record the other one wrote. The query not raising an error is not enough; the value it reads back has to be correct too. - **RS41.** The transitional versions' reads fall back: if the new column is empty, they look at the old one. - **RS42.** The fiction is the regional measurement network's meter reading table; the reading value is a single column in cubic meters, being split into a first/last index pair in liters. The figures are fictional. ## The Reversibility of Migration Types Six migration types are applied to a real table, their reverse runs immediately after, and the table's content is compared against what it started as. The measure is not whether the reverse command runs, but whether the rows come out identical. ```js // migration-types.mjs — six migration types are actually applied, reversed, and the content is compared // Regional measurement network fiction: a meter reading table. Measure: identity after the reverse operation. import { DatabaseSync } from 'node:sqlite'; import { createHash } from 'node:crypto'; const READING = [ ['R-101', 'S-0431', 8.432, 'visual'], ['R-102', 'S-0432', 17.06, 'visual'], ['R-103', 'S-0433', 34.915, 'remote'], ['R-104', 'S-0434', 52.5, 'remote'], ['R-105', 'S-0435', 6.004, 'visual'], ['R-106', 'S-0436', 21.37, 'estimated'], ]; function setUp() { const db = new DatabaseSync(':memory:'); db.exec('CREATE TABLE reading (id TEXT PRIMARY KEY, meter TEXT, m3 REAL, method TEXT)'); const insert = db.prepare('INSERT INTO reading VALUES (?, ?, ?, ?)'); for (const o of READING) insert.run(...o); return db; } const rows = (db) => db.prepare('SELECT * FROM reading ORDER BY id').all() .map((s) => Object.keys(s).sort().map((k) => `${k}=${s[k]}`).join('|')); const digest = (db) => createHash('sha256').update(rows(db).join('\n')).digest('hex').slice(0, 10); const TYPE = [ ['add column', (db) => db.exec('ALTER TABLE reading ADD COLUMN liters INTEGER'), (db) => db.exec('ALTER TABLE reading DROP COLUMN liters')], ['add index', (db) => db.exec('CREATE INDEX ix_meter ON reading(meter)'), (db) => db.exec('DROP INDEX ix_meter')], ['rename column', (db) => db.exec('ALTER TABLE reading RENAME COLUMN m3 TO consumption'), (db) => db.exec('ALTER TABLE reading RENAME COLUMN consumption TO m3')], ['backfill', (db) => { db.exec('ALTER TABLE reading ADD COLUMN liters INTEGER'); db.exec('UPDATE reading SET liters = CAST(m3 * 1000 AS INTEGER)'); }, (db) => db.exec('ALTER TABLE reading DROP COLUMN liters')], ['in-place precision narrowing', (db) => db.exec('UPDATE reading SET m3 = ROUND(m3, 1)'), (db) => db.exec('UPDATE reading SET m3 = m3')], ['drop column', (db) => db.exec('ALTER TABLE reading DROP COLUMN method'), (db) => db.exec('ALTER TABLE reading ADD COLUMN method TEXT')], ]; console.log('migration type before after after reverse identical different rows'); for (const [name, forward, reverse] of TYPE) { const db = setUp(); const o1 = rows(db), h1 = digest(db); forward(db); const h2 = digest(db); reverse(db); const o3 = rows(db), h3 = digest(db); const diff = o1.filter((s, i) => s !== o3[i]).length; console.log(`${name.padEnd(31)} ${h1} ${h2} ${h3} ` + `${(h1 === h3 ? 'yes' : 'no').padEnd(6)} ${diff}`); db.close(); } ``` ``` migration type before after after reverse identical different rows add column 7ea0a3f6ba bd4e22eb22 7ea0a3f6ba yes 0 add index 7ea0a3f6ba 7ea0a3f6ba 7ea0a3f6ba yes 0 rename column 7ea0a3f6ba 94357d3ccb 7ea0a3f6ba yes 0 backfill 7ea0a3f6ba 55cf38998f 7ea0a3f6ba yes 0 in-place precision narrowing 7ea0a3f6ba 2ddb95ed6d 2ddb95ed6d no 5 drop column 7ea0a3f6ba 6f470ab65d 75633d1eb4 no 6 ``` Four of the six types are fully reversible: the digest returns to its previous value, and not one row differs. What these four have in common is that they never lose data. Adding a column or an index does not touch existing rows. Renaming only changes a name. Backfilling writes to a new column while the old one stays in place. The last two types are not reversible, and each fails for a different reason. Precision narrowing overwrote the value **in place**: once the decimals are truncated, there is no reverse command that can bring them back. An identity operation stands in for the reverse, and five rows' values never returned to their previous state — the second and third digests came out the same. Dropping a column is the example of a type where the command runs without error but still does not restore anything: the column was re-added, and all six rows came back empty. The distinction sits right here: a rollback command existing is not the same as being reversible. In both failing rows, the reverse command ran and returned exit code zero. Building the measure on identity turns the phrase "a rollback script was written" into something that can actually be tested. The table's third row also carries a warning. Renaming a column is fully reversible — zero different rows — but that does not mean the step is safe. A version using the old name cannot find the column the moment the rename goes through. Reversibility looks at what comes **after** a step; compatibility looks at **during**. A step can be reversible and still fail to carry two versions together, or the reverse. That is why the second measure runs separately. ## Expand–Migrate–Contract, Step by Step A single reading column in cubic meters gets split into a first/last index pair in liters. The scheme has six steps: the schema expands, the data migrates, the code advances through four versions, the schema contracts. At every step, the version currently running and the one before it are tested together — one writes, the other reads, then the reverse. ```js // expand-migrate-contract.mjs — expand-migrate-contract runs step by step; two versions are tested together at each step // Fiction: a meter reading's single m3 column is split into a first/last index pair, in liters. import { DatabaseSync } from 'node:sqlite'; const M3 = 12.345, FIRST = 100000, LAST = 112345; const READ_FALLBACK = 'SELECT COALESCE((last_index - first_index) / 1000.0, m3) AS value' + ' FROM reading WHERE id = ?'; const VERSION = { v1: { field: 'm3', read: 'SELECT m3 AS value FROM reading WHERE id = ?', write: 'INSERT INTO reading (id, meter, m3) VALUES (?, ?, ?)' }, v2: { field: 'm3+index', read: READ_FALLBACK, write: 'INSERT INTO reading (id, meter, m3, first_index, last_index) VALUES (?, ?, ?, ?, ?)' }, v3: { field: 'index', read: READ_FALLBACK, write: 'INSERT INTO reading (id, meter, first_index, last_index) VALUES (?, ?, ?, ?)' }, v4: { field: 'index', read: 'SELECT (last_index - first_index) / 1000.0 AS value FROM reading WHERE id = ?', write: 'INSERT INTO reading (id, meter, first_index, last_index) VALUES (?, ?, ?, ?)' }, }; const VALUE = { m3: ['S-0440', M3], 'm3+index': ['S-0440', M3, FIRST, LAST], index: ['S-0440', FIRST, LAST] }; const SCHEMA = { expand: [(db) => db.exec('ALTER TABLE reading ADD COLUMN first_index INTEGER;' + ' ALTER TABLE reading ADD COLUMN last_index INTEGER'), true], migrate: [(db) => db.exec('UPDATE reading SET first_index = 0,' + ' last_index = CAST(m3 * 1000 AS INTEGER) WHERE last_index IS NULL'), true], contract: [(db) => db.exec('ALTER TABLE reading DROP COLUMN m3'), false], }; // [name, schema step, running version, one-version-back target, two-version-back target] const STEP = [ ['expand: index columns added', 'expand', 'v1', 'v1'], ['dual-writing version released', null, 'v2', 'v1'], ['migrate: old rows backfilled', 'migrate', 'v2', 'v1'], ['version that stops writing m3 released', null, 'v3', 'v2', 'v1'], ['version that removes the fallback released', null, 'v4', 'v3'], ['contract: m3 column dropped', 'contract', 'v4', 'v3'], ]; const db = new DatabaseSync(':memory:'); db.exec('CREATE TABLE reading (id TEXT PRIMARY KEY, meter TEXT, m3 REAL)'); db.prepare('INSERT INTO reading (id, meter, m3) VALUES (?, ?, ?)').run('R-101', 'S-0431', 8.432); function test(A, B, suffix) { const finding = []; for (const [j, [writer, reader]] of [[A, B], [B, A]].entries()) { const id = `T-${suffix}-${j}`; try { db.prepare(VERSION[writer].write).run(id, ...VALUE[VERSION[writer].field]); } catch (e) { finding.push(`${writer} failed to write: ${e.message}`); continue; } try { const d = db.prepare(VERSION[reader].read).get(id)?.value; if (Math.abs((d ?? -1) - M3) > 1e-9) finding.push(`${reader} read ${writer}'s record as ${d}`); } catch (e) { finding.push(`${reader} failed to read: ${e.message}`); } } return finding; } console.log('step schema running target compat schema reversible'); let compat = 0, reversible = 0, schemaSteps = 0; STEP.forEach(([name, schema, A, B, C], i) => { if (schema) { SCHEMA[schema][0](db); schemaSteps += 1; if (SCHEMA[schema][1]) reversible += 1; } const finding = test(A, B, i + 1); if (finding.length === 0) compat += 1; console.log(`${(i + 1)} ${name.padEnd(38)} ${(schema ?? '—').padEnd(9)} ${A} ${B} ` + `${(finding.length ? 'no' : 'yes').padEnd(7)} ${schema ? (SCHEMA[schema][1] ? 'yes' : 'no') : '—'}`); for (const b of finding) console.log(` ${b}`); if (C) { const twoBack = test(A, C, `${i + 1}x`); console.log(` two versions back (${A} with ${C}): ${twoBack.length ? 'incompatible' : 'compatible'}`); for (const b of twoBack) console.log(` ${b}`); } }); console.log(''); console.log(`steps where two versions ran together : ${compat} / ${STEP.length}`); console.log(`step where they could not : ${STEP.length - compat}`); console.log(`schema steps : ${schemaSteps}, of which reversible ${reversible}`); ``` ``` step schema running target compat schema reversible 1 expand: index columns added expand v1 v1 yes yes 2 dual-writing version released — v2 v1 yes — 3 migrate: old rows backfilled migrate v2 v1 yes yes 4 version that stops writing m3 released — v3 v2 yes — two versions back (v3 with v1): incompatible v1 read v3's record as null 5 version that removes the fallback released — v4 v3 yes — 6 contract: m3 column dropped contract v4 v3 no no v3 failed to read: no such column: m3 steps where two versions ran together : 5 / 6 step where they could not : 1 schema steps : 3, of which reversible 2 ``` In five of the six steps, two versions ran together. The one incompatible step is the last one, and it is also the one schema step that cannot be reversed. Two properties meet at the same step: dropping the old column both leaves the previous code version unable to run and cannot itself be undone. The extra row at step four shows that compatibility depends on **how many versions back** you ask. One version back — the running version and the one before it — is compatible. Two versions back is not: the version that stops writing to the m3 column has its record read as empty by the version two generations earlier. No error, the record exists, no value. That is why the rollback window is a single version; a plan that wants to go back two versions has to climb down the code steps one at a time. Two of the three schema steps are reversible. Expansion is reversed by dropping the columns, migration by clearing the new columns — in both, no loss occurs because the values keep standing in the old column. Contraction is the catalog's sixth row itself. The code ladder having four rungs also falls out of this measurement. Because the dual-writing version keeps filling the old column, the previous version can keep reading. The version that stops writing m3 is the next rung, the version that removes the fallback the one after that. Without the fourth rung, the contract step cannot run: the fallback read refers to a column that no longer exists, and whatever version is running in production at that moment breaks. If one of the rungs is skipped, the incompatible-step count goes from one to two. ## The Cost of the Incompatible Step The incompatible step charges twice. First, the release window. A step where two versions cannot run together cannot roll out in stages: every moment where some nodes are on the old version and some on the new is a moment of failure. Each of the five compatible steps can advance node by node; the sixth step has to go through in one shot, on every node at the same time. The gradual observation window staged rollout provides does not exist at this step. Second, the rollback plan. For the first five steps, rollback is the previous lesson's "code only" scenario: five steps, eight rounds, no point of no return. Past the sixth step, the plan shifts to the "code and data" scenario: eight steps, twenty unrehearsed rounds, and a way back that closes at step six. A single migration step stretches the rollback plan two and a half times and puts an irreversible point inside it. The gate here is the **compatibility check** that runs the old version's queries against the new schema before the migration step ships to production. What it catches is one step: the sixth of six. Only this gate catches this step — the migration step's own tests run against the new schema and never see the old query at all. What it misses is the catalog's fifth row: precision narrowing is compatible with both versions, the gate lets it through, but it is not reversible. Compatibility and reversibility are separate axes, and this gate looks at only one of them. The gate wrongly holding comes from its choice of target. If the check compares the running version against the oldest deployable version instead of the one before it, step four also gets stopped — as the output showed, that comparison comes out incompatible. One of the six steps ends up waiting for nothing. The time it adds is three rounds for three schema steps; what gets paid when it misses is a twenty-round rollback plan. ## Summary - Four of six migration types left the table identical after the reverse operation; precision narrowing failed to restore five rows, dropping a column failed to restore six — in both, the reverse command ran without error. - Reversibility is measured by content identity, not by a command's existence; with this measure, the phrase "a rollback script was written" becomes testable. - Expand–migrate–contract carried two versions together in five of six steps; the one incompatible step was the same step as the one irreversible schema step. - The rollback window is a single version: step four, compatible one version back, made the old version read an empty value once you went back two. - The incompatible step dropped the release window from staged to one-shot, and stretched the rollback plan from five steps and eight rounds to eight steps and twenty. ## Next Step Across this topic, where the release decision comes from was measured: what metric is watched to promote, what the rollback unit is, how many steps a plan has, whether a migration step carries two versions. What comes out of all these decisions is a desired state — this version, this flag value, this schema. But that desired state itself is not written down anywhere. What carries it to production is still a button clicked in an interface, or a command in someone's hands; the only way to learn what is actually running in production is to look at production. The next topic opens with this question: where does the desired state live, who changes it, and how is it measured whether the real state has drifted from it.