Deployment Order and Database Migration

All three release layouts in the previous lesson leaned on one assumption: two versions can stay up together for a while. This assumption, satisfied for processes, is not automatically satisfied for the schema underneath them. In a blue-green transition, two versions read the same database; in a canary release, this cohabitation lasts longer still. If the schema supports only one of the two versions, a release layout’s zero-downtime property means nothing.

This lesson’s subject is not the migration itself but its order: which goes first, migration or code release. Testing migration steps was established in integration/04, and the contract’s evolution across versions in M16/K06; the only question asked here is what the system does in the window between the two operations. The measure is the number of requests that error in that window.

• SD16. Migration steps run separately from the application and are not part of the same operation as the code release. The order question comes from this separation.
• SD17. The schema sits in a single database, and every version reads that same database.
• SD18. A member record’s contact field is free text: some members have an email, others a phone number. The change narrows this field to one that holds only email.

Two Versions, One Schema

The member endpoint has three versions. v1 reads and writes the old column, v2 reads the new column but writes both, v3 recognizes only the new column. All three open the same database file.

// service.mjs — a version of the member endpoint; VERSION decides which column it reads and writes
import { createServer } from 'node:http';
import { DatabaseSync } from 'node:sqlite';

const VERSION = process.env.VERSION;
const db = new DatabaseSync(process.env.DB_PATH);

const READ = {
  v1: 'SELECT member_no, name, contact AS value FROM member WHERE member_no = ?',
  v2: 'SELECT member_no, name, email AS value FROM member WHERE member_no = ?',
  v3: 'SELECT member_no, name, email AS value FROM member WHERE member_no = ?',
};
const WRITE = {
  v1: 'INSERT INTO member (member_no, name, contact) VALUES (?, ?, ?)',
  v2: 'INSERT INTO member (member_no, name, contact, email) VALUES (?, ?, ?, ?)',  // dual write
  v3: 'INSERT INTO member (member_no, name, email) VALUES (?, ?, ?)',
};

createServer((req, res) => {
  const url = new URL(req.url, 'http://local');
  const no = url.searchParams.get('no');
  const headers = { 'content-type': 'application/json' };
  try {
    if (req.method === 'POST') {
      const name = url.searchParams.get('name');
      const value = url.searchParams.get('value');
      db.prepare(WRITE[VERSION]).run(...(VERSION === 'v2' ? [no, name, value, value] : [no, name, value]));
      return res.writeHead(201, headers).end(JSON.stringify({ version: VERSION, written: no }));
    }
    const row = db.prepare(READ[VERSION]).get(no);
    res.writeHead(200, headers).end(JSON.stringify({ version: VERSION, ...row }));
  } catch (e) {
    res.writeHead(500, headers).end(JSON.stringify({ version: VERSION, error: e.message }));
  }
}).listen(Number(process.env.PORT), () => console.log(`ready ${VERSION}`));

Bringing up two versions at once against a schema with no migration applied, and asking about the same member, makes the difference visible.

node -e "const {DatabaseSync} = require('node:sqlite');
  const db = new DatabaseSync('catalog.db');
  db.exec('CREATE TABLE member (member_no TEXT PRIMARY KEY, name TEXT NOT NULL, contact TEXT)');
  db.prepare('INSERT INTO member VALUES (?, ?, ?)').run('U-0001', 'Alice Kane', '[email protected]')"
VERSION=v1 PORT=8741 DB_PATH=catalog.db node service.mjs > /dev/null &
OLD=$!
VERSION=v2 PORT=8742 DB_PATH=catalog.db node service.mjs > /dev/null &
NEW=$!
sleep 0.6
curl -s -w '\n' 'http://127.0.0.1:8741/member?no=U-0001'
curl -s -w '\n' 'http://127.0.0.1:8742/member?no=U-0001'
kill $OLD $NEW

{"version":"v1","member_no":"U-0001","name":"Alice Kane","value":"[email protected]"}
{"version":"v2","error":"no such column: email"}

The schema supports one version and not the other. Release order’s entire problem is how long this state lasts, and which version carries the load during it.

The Same Change, Three Orders

All three orders arrive at the same result: the contact field becomes an email field. The first puts migration first, the second puts the code release first, the third breaks the change into backward-compatible steps. In every order, twenty requests flow at every phase, and the error count is measured.

// order.mjs — the same schema change in three separate orders: breaking migration first, code first, expand-contract
import { spawn } from 'node:child_process';
import { DatabaseSync } from 'node:sqlite';
import { mkdtempSync, rmSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';

const root = mkdtempSync(join(tmpdir(), 'migration-order-'));
const LOAD = 20;
const MEMBERS = [
  ['U-0001', 'Alice Kane', '[email protected]'], ['U-0002', 'Derek Voss', '0500 000 0002'],
  ['U-0003', 'Nora Bennett', '[email protected]'], ['U-0004', 'Marcus Reed', '0500 000 0004'],
  ['U-0005', 'Zara Wells', '[email protected]'], ['U-0006', 'Cole Whitman', '0500 000 0006'],
  ['U-0007', 'Dana Reyes', '[email protected]'], ['U-0008', 'Ben Ortiz', '0500 000 0008'],
  ['U-0009', 'Sarah Lang', '[email protected]'], ['U-0010', 'Kevin Marsh', '0500 000 0010'],
  ['U-0011', 'Nina Cole', '[email protected]'], ['U-0012', 'Evan Duke', '[email protected]'],
];

function setupDatabase(path) {
  const db = new DatabaseSync(path);
  db.exec('CREATE TABLE member (member_no TEXT PRIMARY KEY, name TEXT NOT NULL, contact TEXT)');
  const insert = db.prepare('INSERT INTO member VALUES (?, ?, ?)');
  for (const u of MEMBERS) insert.run(...u);
  return db;
}

const MIGRATE = {
  breaking: (db) => db.exec('ALTER TABLE member RENAME COLUMN contact TO email'),
  expand: (db) => {
    db.exec('ALTER TABLE member ADD COLUMN email TEXT');
    db.exec("UPDATE member SET email = contact WHERE instr(contact, '@') > 0");
  },
  contract: (db) => db.exec('ALTER TABLE member DROP COLUMN contact'),
};

const children = [];
async function start(version, port, path) {
  const child = spawn(process.execPath, ['service.mjs'], { stdio: ['ignore', 'pipe', 'inherit'],
    env: { ...process.env, VERSION: version, PORT: String(port), DB_PATH: path } });
  await new Promise((c) => child.stdout.once('data', c));
  children.push(child);
  return port;
}
const teardown = () => { children.forEach((c) => c.kill('SIGKILL')); children.length = 0; };

async function flow(ports, n) {
  const tally = { ok: 0, error: 0 };
  for (let i = 0; i < n; i += 1) {
    const y = await fetch(`http://127.0.0.1:${ports[i % ports.length]}/member?no=${MEMBERS[i % MEMBERS.length][0]}`);
    tally[y.ok ? 'ok' : 'error'] += 1;
    await y.text();
  }
  return tally;
}
const write = (port, no, name, value) =>
  fetch(`http://127.0.0.1:${port}/member?no=${no}&name=${name}&value=${value}`, { method: 'POST' }).then((y) => y.json());
const read = (port, no) => fetch(`http://127.0.0.1:${port}/member?no=${no}`).then((y) => y.json());

async function setup(name) {
  const path = join(root, `${name}.db`);
  const db = setupDatabase(path);
  const [v1, v2, v3] = [await start('v1', 8741, path), await start('v2', 8742, path),
                        await start('v3', 8743, path)];
  return { db, v1, v2, v3, phases: [], steps: [] };
}

async function migrationFirst() {
  const o = await setup('migration-first');
  o.phases.push(['old version, no migration', await flow([o.v1], LOAD)]);
  o.steps.push('breaking migration'); MIGRATE.breaking(o.db);
  o.phases.push(['migration ran, code still old', await flow([o.v1], LOAD)]);
  o.steps.push('release of new version');
  o.phases.push(['new version released', await flow([o.v2], LOAD)]);
  teardown();
  return { name: 'migration then code', ...o, together: 0 };
}

async function codeFirst() {
  const o = await setup('code-first');
  o.phases.push(['old version, no migration', await flow([o.v1], LOAD)]);
  o.steps.push('release of new version');
  o.phases.push(['code new, column missing', await flow([o.v2], LOAD)]);
  o.steps.push('breaking migration'); MIGRATE.breaking(o.db);
  o.phases.push(['migration ran', await flow([o.v2], LOAD)]);
  teardown();
  return { name: 'code then migration', ...o, together: 0 };
}

async function expandContract() {
  const o = await setup('expand-contract');
  o.phases.push(['old version, no migration', await flow([o.v1], LOAD)]);
  o.steps.push('expand: column added and backfilled'); MIGRATE.expand(o.db);
  o.phases.push(['schema wide, code still old', await flow([o.v1], LOAD)]);
  o.steps.push('release of the dual-writing version');
  o.phases.push(['two versions together', await flow([o.v1, o.v2], LOAD)]);
  await write(o.v2, 'U-0013', 'Dual Writer', '[email protected]');
  await write(o.v3, 'U-0014', 'Single Writer', '[email protected]');
  const window = [await read(o.v1, 'U-0013'), await read(o.v1, 'U-0014')];
  o.steps.push('old version withdrawn');
  o.phases.push(['dual-writing version only', await flow([o.v2], LOAD)]);
  o.steps.push('dual write removed');
  o.phases.push(['dual write removed', await flow([o.v3], LOAD)]);
  o.steps.push('contract: old column dropped'); MIGRATE.contract(o.db);
  o.phases.push(['schema narrow, code new', await flow([o.v3], LOAD)]);
  const premature = await write(o.v2, 'U-0015', 'Early Contract', '[email protected]');
  teardown();
  return { name: 'expand-contract', ...o, together: 1, window, premature };
}

const results = [await migrationFirst(), await codeFirst(), await expandContract()];
for (const s of results) {
  console.log(`${s.name} — ${s.steps.length} steps`);
  for (const [label, tally] of s.phases) {
    console.log(`  ${label.padEnd(32)} ok=${String(tally.ok).padEnd(3)} error=${tally.error}`);
  }
}
console.log('');
console.log('order                 steps  sent  errored  phase with two versions together');
for (const s of results) {
  const g = s.phases.reduce((t, [, y]) => t + y.ok + y.error, 0);
  const h = s.phases.reduce((t, [, y]) => t + y.error, 0);
  console.log(`${s.name.padEnd(21)} ${String(s.steps.length).padEnd(5)} ${String(g).padEnd(11)} ` +
    `${String(h).padEnd(10)} ${s.together}`);
}
console.log('');
const ec = results.at(-1);
console.log('during the window, the new version wrote, the old version read');
for (const p of ec.window) console.log(`  ${p.member_no} ${p.name.padEnd(14)} value the old version saw: ${p.value}`);
console.log('');
console.log('if the dual-writing version tries to write after contraction completes');
console.log(`  ${ec.premature.error}`);

console.log('');
const db = ec.db;
const tally = (condition) => db.prepare(`SELECT COUNT(*) AS n FROM member WHERE ${condition}`).get().n;
db.exec('ALTER TABLE member ADD COLUMN contact TEXT');
db.exec('UPDATE member SET contact = email WHERE email IS NOT NULL');
console.log('rollback after contraction: column re-added and backfilled from email');
console.log(`  total members             : ${tally('1 = 1')}`);
console.log(`  contact recovered         : ${tally('contact IS NOT NULL')}`);
console.log(`  contact not recoverable   : ${tally('contact IS NULL')}`);
rmSync(root, { recursive: true, force: true });

migration then code — 2 steps
  old version, no migration        ok=20  error=0
  migration ran, code still old    ok=0   error=20
  new version released             ok=20  error=0
code then migration — 2 steps
  old version, no migration        ok=20  error=0
  code new, column missing         ok=0   error=20
  migration ran                    ok=20  error=0
expand-contract — 5 steps
  old version, no migration        ok=20  error=0
  schema wide, code still old      ok=20  error=0
  two versions together            ok=20  error=0
  dual-writing version only        ok=20  error=0
  dual write removed               ok=20  error=0
  schema narrow, code new          ok=20  error=0

order                 steps  sent  errored  phase with two versions together
migration then code   2     60          20         0
code then migration   2     60          20         0
expand-contract       5     120         0          1

during the window, the new version wrote, the old version read
  U-0013 Dual Writer    value the old version saw: [email protected]
  U-0014 Single Writer  value the old version saw: null

if the dual-writing version tries to write after contraction completes
  table member has no column named contact

rollback after contraction: column re-added and backfilled from email
  total members             : 14
  contact recovered         : 9
  contact not recoverable   : 5

The Measure of the Wrong Order

The first two orders run the same migration and produce the same number: twenty erroring requests. Their directions differ, not their outcomes. If migration goes first, the old version cannot find its column; if code goes first, the new version looks for a column that does not exist yet. In both, the error’s duration is the gap between the two operations — if migration runs at night and release happens in the morning, that gap is a night.

This number’s meaning appears when it is set next to the previous lesson’s number. There, the blue-green layout measured zero dropped requests. The same release, if the schema underneath supports only one version, produces twenty errors. A release layout’s zero-downtime property depends on schema compatibility; measured separately, one’s zero hides the other’s twenty.

The expand–contract order handled all one hundred twenty requests error-free. Its cost shows up in the step count: five instead of two. Each of the five steps is a separate release or a separate migration run, with a wait between them — without those waits, the order carries no meaning. The change’s timeline stretches accordingly: a two-step rename becomes a five-step piece of work spread across several releases.

The Window and Dual Writing

The third phase is the only one where two versions run together against the same schema. Twenty requests went alternately to the old and new versions, and all completed. This window’s survival depends on one condition: the new version keeps writing to the old column too.

Two members were written inside the window. U-0013 arrived through the dual-writing version, and the old version could read it. U-0014 arrived through the version that writes only the new column, and the value the old version saw was null. There is no error in the second row: the request returned 200, the record exists, the field is empty. Skipping dual writing produces no error, it produces missing data — and that gap is visible only where the old version is looking.

When dual writing gets removed is also part of the order. Once the contraction step completes, the dual-writing version trying to write gets table member has no column named contact. This is why the fourth step — removing dual writing — has to come before the fifth. The order is constrained at both ends: expansion comes before the code release, contraction comes after it.

The Step That Cannot Be Undone

Four of the five steps are reversible. Expansion is undone by dropping the column, code releases by returning to the previous version, and as the previous lesson measured, that is a one-step job. Contraction is not like that.

The column can be added back; the output does this and backfills it from the email field. Nine of fourteen members got their contact information back, five did not. The five who lost it are the members whose contact field held a phone number: the expand step only copied entries that looked like email, so those five values never crossed to the new column, and the contract step deleted the one place holding them. The rollback command ran successfully; the result is missing data anyway.

Two rules follow from this. First, the contraction step sits outside the rollback window: after it, the rollback path is not a release layout, it is a restore from backup. Second, the expansion step’s backfill has to count the rows it misses; in this measurement that number is five, and it is the only warning knowable before contraction.

Where the Order Lives

Migration steps sit in the data layer, release steps in the deployment layout; no setting ties the two together. The only thing holding the order is a written list of steps, and that list is verified nowhere. The dual-write setting, on the other hand, sits in application code and exists on every node: whether dual writing is on can vary node by node while one runs the old version and another the new.

The silence of a wrong setting has two levels here. A wrong order is loud: twenty requests get a 500, visible in the first minute. Wrong dual writing is silent: requests return 200, records get created, only one field stays empty. Noticed after contraction, this gap can no longer be fixed, because the column holding the value has been dropped. The number worth measuring, then, is not the error count — it is the row count the backfill misses.

Summary

• The same schema change produced twenty erroring requests under both migration-first and code-first order; under expand–contract, all one hundred twenty requests completed.
• Expand–contract needs five steps instead of two, and the waits between them spread the change’s timeline across several releases.
• Dual writing is the condition for the window where two versions run against the same schema: the old version could read a record written by the dual-writing version, while a record from the version writing only the new column showed an empty field, and no request errored.
• Dual writing gets removed before contraction; after contraction, the old write path cannot find the column.
• Dropping a column is irreversible: the column was re-added and backfilled from the new field, nine of fourteen members were recovered, five were not — even though the rollback command ran without error.

Next Step

Every decision measured across this topic assumed the resource already existed: the nodes standing behind the reverse proxy, the processes doubling during the release window, the store the session moved to, the database the migration steps ran against. No measurement asked how much of that resource should exist. The course closes on that question: how much load a node carries, how many nodes are needed, what release and failure headroom add on top of that number, and how the gap between estimate and reality gets measured.