---
title: 'Deployment Order and Database Migration'
source: 'https://academia.sh/en/courses/backend-production/deployment-order-with-database-migration'
course: 'Server Security and Going to Production'
language: en
updated: '2026-08-23T07:00:26+00:00'
license: 'CC BY-SA 4.0'
---

# Deployment Order and Database Migration

The same schema change runs in three separate orders, and each order's error count is measured: migration first, code first, and expand–contract. The window where two versions run against the same schema, the function of dual writing, and the irreversibility of dropping a column are all measured.

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.

```js
// 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.

```sh
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', 'alice@example.test')"
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":"alice@example.test"}
{"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.

```js
// 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', 'alice@example.test'], ['U-0002', 'Derek Voss', '0500 000 0002'],
  ['U-0003', 'Nora Bennett', 'nora@example.test'], ['U-0004', 'Marcus Reed', '0500 000 0004'],
  ['U-0005', 'Zara Wells', 'zara@example.test'], ['U-0006', 'Cole Whitman', '0500 000 0006'],
  ['U-0007', 'Dana Reyes', 'dana@example.test'], ['U-0008', 'Ben Ortiz', '0500 000 0008'],
  ['U-0009', 'Sarah Lang', 'sarah@example.test'], ['U-0010', 'Kevin Marsh', '0500 000 0010'],
  ['U-0011', 'Nina Cole', 'nina@example.test'], ['U-0012', 'Evan Duke', 'evan@example.test'],
];

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', 'thirteen@example.test');
  await write(o.v3, 'U-0014', 'Single Writer', 'fourteen@example.test');
  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', 'fifteen@example.test');
  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: thirteen@example.test
  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.