Migration scripts ship inside the service. The service reads the schema version on boot, takes a lock so instances don't race, and only ever moves the schema forward.

The migration scripts ship inside the service. “I personally like to bake in the database scripts into the service itself, and so when the service runs, the first time that it spins up, it will go and look at the database and see what version it’s at, and if it needs to be upgraded, it will upgrade on its own.” No migration job in the pipeline, no operator running a command before the deploy. The artifact that serves traffic is the artifact that owns the schema.

One sentence is what keeps that from breaking under a rolling deploy: “Obviously you have a locking mechanism so that you don’t have multiple services trying to upgrade the database at the same time, but that is a pattern that I found that works really well.”

That’s the homegrown-migrations line in ADRB, an internal assistant I built at Cigna — React and Node on ECS, SQLite underneath. “Because everything was going into a database, I did have a database versioning capability — I’d be able to add new SQL scripts for creating the schema, and on load, as the application was spinning up, it would go take a look to see what the schema version of the database was and what scripts it needed to run. Just a standard process. There were a bunch of other tools out there that would do this at enterprise scale, but just building it light and simple was great.”

The schema only moves forward

A migration in this pattern only ever adds. That rule is what makes the rest of the deploy safe, and it starts on the API side: “making sure that all your code is backwards compatible. Making sure that any new changes that you make are not breaking changes. That’s from an API perspective, that’s from a database perspective.”

Then the database side, which is the one people get wrong: “if you do create a new schema change in your database, and the code part itself needs to roll back, you’re not going to go back to the database and drop those tables or drop new columns that were created. You will just keep the database the way that it is, and then on the next push … your database migration scripts would facilitate.”

Code moves both directions. The schema moves one. That asymmetry is the whole trick: if a migration never takes anything away, the old binary still runs against the new schema, and shifting traffic back to the previous version costs nothing. It’s why I roll back at the traffic layer and not at the source layer: “definitely nothing as far as rolling back inside of GitHub or whatever. It’s just making changes to your code, running it through the pipeline, and getting it deployed again.”

The published name for this is parallel change, or expand/contract. Danilo Sato’s write-up on martinfowler.com (13 May 2014) splits it into three phases. Expand: “you augment the interface to support both the old and the new versions.” Migrate: “you update all clients using the old version to the new version.” Contract: “you perform the contract phase to remove the old version.” Baking migrations into the service and refusing to drop anything is a standing expand phase.

Which is also the honest critique of it. Sato’s own warning: “If the contract phase is not executed you might end up in a worse state than you started, therefore you need discipline to finish the transition successfully.” Forward-only accumulates. The columns nothing reads anymore are still there, and dropping them is a deliberate later migration that has to wait until no deployed version can roll back onto them — not something the pattern does for you.

GitLab runs the same discipline and says so in its migration style guide: “On GitLab production environments, if a problem occurs, a roll-forward strategy is used instead of rolling back migrations using db:rollback.” Its stated reason is the one that makes contraction hard in the first place — “Some data migrations can’t be reversed because we lose information about the state of the database before the migration.”

What I didn’t pin down

Three things about my own implementation aren’t on the record: which lock I actually used, what the other instances did while one of them migrated, and whether a failed migration failed the health check. I’m not going to reconstruct them after the fact.

So none of the code below is from that build. It’s reference, written from the current docs — where a choice is genuinely contested, it picks one and says what it costs. Docs checked 2026-09-02.

The version table and the lock

PostgreSQL ships a lock built for exactly this: an advisory lock. The documentation is clear that the database is not enforcing anything on its own — “PostgreSQL provides a means for creating locks that have application-defined meanings. These are called advisory locks, because the system does not enforce their use — it is up to the application to use them correctly.” The two you need are documented in the admin function reference: pg_advisory_lock “obtains an exclusive session-level advisory lock, waiting if necessary,” while pg_try_advisory_lock “will either obtain the lock immediately and return true, or return false without waiting if the lock cannot be acquired immediately.”

Waiting is the right default here. An instance that fails fast on a busy lock is an instance that crash-loops through a deploy for no reason.

The property that matters most for a service that migrates on boot is what happens when the process dies: “Once acquired at session level, an advisory lock is held until explicitly released or the session ends.” A container killed mid-migration ends its session, and the lock goes with it. No operator, no cleanup command.

Written against those docs — the version table, the lock, and the steps:

import type { Pool, PoolClient } from "pg";

// One fixed key for the whole application. Any bigint works; what matters
// is that every instance uses the same one.
const MIGRATION_LOCK_KEY = 8675309;

export interface Step {
  version: number;
  sql: string;
}

export async function migrate(pool: Pool, steps: Step[]): Promise<number> {
  // A session-level advisory lock lives on one connection, so the migration
  // holds a checked-out client instead of going through pool.query().
  const client: PoolClient = await pool.connect();
  try {
    await client.query("SELECT pg_advisory_lock($1)", [MIGRATION_LOCK_KEY]);
    try {
      return await runPending(client, steps);
    } finally {
      await client.query("SELECT pg_advisory_unlock($1)", [MIGRATION_LOCK_KEY]);
    }
  } finally {
    client.release();
  }
}

async function runPending(client: PoolClient, steps: Step[]): Promise<number> {
  await client.query(`
    CREATE TABLE IF NOT EXISTS schema_version (
      version    integer     PRIMARY KEY,
      applied_at timestamptz NOT NULL DEFAULT now()
    )
  `);

  const { rows } = await client.query<{ version: number | null }>(
    "SELECT max(version) AS version FROM schema_version",
  );
  let current = rows[0].version ?? 0;

  const pending = steps
    .filter((step) => step.version > current)
    .sort((a, b) => a.version - b.version);

  for (const step of pending) {
    await client.query("BEGIN");
    try {
      await client.query(step.sql);
      await client.query("INSERT INTO schema_version (version) VALUES ($1)", [
        step.version,
      ]);
      await client.query("COMMIT");
      current = step.version;
    } catch (err) {
      await client.query("ROLLBACK");
      throw err;
    }
  }

  return current;
}

Four details in that block are load-bearing.

The checked-out client is not a style preference. A session-level lock belongs to a connection, and node-postgres hands pooled connections back on release — its own pool documentation gives the same reasoning for transactions: “Do not use pool.query if you are using a transaction. Transactions within PostgreSQL are scoped to a single client and so dispatching individual queries within a single transaction across multiple, random clients will cause big problems in your app.” Take the lock through pool.query and the very next statement may run on a connection that doesn’t hold it.

The table creation sits inside the lock, not before it. CREATE TABLE IF NOT EXISTS is not a coordination primitive — PostgreSQL’s own note is that it will “not throw an error if a relation with the same name already exists,” and “that there is no guarantee that the existing relation is anything like the one that would have been created.” Bootstrapping the version table is part of the migration, so it happens under the same lock as everything else.

Each step commits with its own version row. If the third of five migrations fails, the first two are recorded as applied and the third is rolled back whole. The next instance to take the lock resumes at exactly the right place — there’s no state where the version claims work that didn’t land.

And the per-step transaction is the one place this shape has a hard edge. CREATE INDEX CONCURRENTLY is the standard way to add an index without blocking writes — PostgreSQL “will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table” — but the same page says “a regular CREATE INDEX command can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot.” A step that needs it has to run outside the BEGIN/COMMIT above, and handle the documented failure mode itself: a failed concurrent build “will fail but leave behind an ‘invalid’ index.”

One deployment-shaped caveat: if the service reaches PostgreSQL through PgBouncer in transaction pooling mode, this doesn’t work at all. PgBouncer’s feature matrix marks session-level advisory locks supported under session pooling and “Never” under transaction pooling. The migration connection has to bypass the pooler or use session pooling.

SQLite has exactly one writer

SQLite has no advisory locks, and doesn’t need them, because the database file is already the lock. Its FAQ states the guarantee directly: “Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.”

What you have to get right is when the write lock is taken. The transaction docs explain the difference: a deferred transaction “does not actually start until the database is first accessed,” so two instances can both read version four before either writes, whereas “IMMEDIATE causes the database connection to start a new write immediately, without waiting for a write statement. The BEGIN IMMEDIATE might fail with SQLITE_BUSY if another write transaction is already active on another database connection.” BEGIN IMMEDIATE is the lock.

SQLITE_BUSY is a failure, not a wait, until you ask for one. sqlite3_busy_timeout “sets a busy handler that sleeps for a specified amount of time when a table is locked,” and “after at least ‘ms’ milliseconds of sleeping, the handler returns 0 which causes sqlite3_step() to return SQLITE_BUSY.” Set it, and the second instance blocks on the first instead of dying.

Using Node’s built-in node:sqlite module — added in v22.5.0 and still marked “Stability: 1.2 - Release candidate,” which is worth knowing before you put it in a deploy path:

import { DatabaseSync } from "node:sqlite";
import type { Step } from "./steps.ts";

export function migrate(db: DatabaseSync, steps: Step[]): number {
  // Sleep and retry instead of failing on the first SQLITE_BUSY.
  db.exec("PRAGMA busy_timeout = 30000");

  // Safe outside the transaction: only one writer exists, so this either
  // wins or finds the table already there.
  db.exec(`
    CREATE TABLE IF NOT EXISTS schema_version (
      version    INTEGER PRIMARY KEY,
      applied_at TEXT NOT NULL DEFAULT (datetime('now'))
    )
  `);

  // Take the write lock now, not at the first write.
  db.exec("BEGIN IMMEDIATE");
  try {
    const row = db
      .prepare("SELECT max(version) AS version FROM schema_version")
      .get() as { version: number | null };
    let current = row.version ?? 0;

    const pending = steps
      .filter((step) => step.version > current)
      .sort((a, b) => a.version - b.version);

    for (const step of pending) {
      db.exec(step.sql);
      db.prepare("INSERT INTO schema_version (version) VALUES (?)").run(
        step.version,
      );
      current = step.version;
    }

    db.exec("COMMIT");
    return current;
  } catch (err) {
    db.exec("ROLLBACK");
    throw err;
  }
}

SQLite does carry a version slot of its own, and the reference deliberately doesn’t use it. PRAGMA user_version “will get or set the value of the user-version integer at offset 60 in the database header,” and per the pragma docs it’s “an integer that is available to applications to use however they want. SQLite makes no use of the user-version itself.” One integer, no audit trail — a table costs nothing and records when each step landed.

The bigger caveat is architectural, and it’s the reason this shape only half-applies to a SQLite service on ECS. Several tasks can only race for a lock if they can see the same file, and the SQLite FAQ is blunt about the usual way people arrange that: “this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time.” A single-task SQLite service doesn’t have a lock problem. It gets one the day it grows a second instance — or, like ADRB when the team took it over, moves to Postgres.

The health endpoint during a migration

A service that migrates on boot needs the health endpoint to answer during the migration, which means it has to be listening before the migration starts. A container that hasn’t bound its port yet can only produce a connection refusal; one that has bound it can say what it’s doing and why.

On Node’s node:http:

import { createServer } from "node:http";
import { migrate, type Step } from "./migrate.ts";

type Phase = "starting" | "migrating" | "ready" | "failed";

let phase: Phase = "starting";
let schemaVersion: number | null = null;
let failure: string | null = null;

// Listen first, migrate second — so the check has something to report.
createServer((req, res) => {
  if (req.url !== "/health") {
    res.writeHead(404).end();
    return;
  }
  res.writeHead(phase === "ready" ? 200 : 503, {
    "content-type": "application/json",
  });
  res.end(JSON.stringify({ phase, schemaVersion, failure }));
}).listen(8080);

try {
  phase = "migrating";
  schemaVersion = await migrate(pool, steps);
  phase = "ready";
} catch (err) {
  phase = "failed";
  failure = err instanceof Error ? err.message : String(err);
  // No process.exit: a 503 that names the failed migration is a better
  // artifact than a crash loop that names nothing.
}

Behind an Application Load Balancer, the 503 does the routing work. AWS’s target group health check docs put it plainly: “Each load balancer node routes requests only to the healthy targets,” and “before a target can receive requests from the load balancer, it must pass the initial health checks.”

The honest limit is on the same page, and it’s the one that decides how much this actually buys you: “If a target group contains only unhealthy registered targets, the load balancer routes requests to all those targets, regardless of their health status. This means that if all targets fail health checks at the same time in all enabled Availability Zones, the load balancer fails open.” During a rolling deploy the old tasks are healthy and the migrating ones get no traffic, which is the case that matters. On a cold start where every task is migrating at once, the ALB routes to them anyway. The endpoint is telling the truth, and nothing is listening.

The other thing to set is ECS’s own container health check, or the task holding the lock reports unhealthy while it works. Amazon’s docs describe startPeriod as “the optional grace period to provide containers time to bootstrap in before failed health checks count towards the maximum number of retries,” and the task-level rule is flat: “If the status of one essential container is UNHEALTHY, then the task status is UNHEALTHY.” Set a start period longer than the longest migration. If the task gets replaced mid-migration anyway, the design survives it — the lock dies with the session, and each step already committed its own version row — but a slow migration under a short start period turns a deploy into a loop.

Whether a failed migration should keep the container alive at 503 or exit non-zero is a real fork, and I don’t have a scar on either side of it. The reference keeps it alive because the endpoint is where I put diagnostic information anyway — the load balancer is already running the integration tests, and the health response is the first page of the incident.

How the established tools take the lock

Every established migration tool solves this, and comparing how is the fastest way to see what the choice costs.

Flyway coordinates through the database. Its FAQ answers the multi-instance question directly — “Flyway uses the locking technology of your database to coordinate multiple nodes. This ensures that even if multiple instances of your application attempt to migrate the database at the same time, it still works.” On PostgreSQL that lock is an advisory lock, and Flyway exposes the same edge the reference above ran into: flyway.postgresql.transactional.lock controls “whether transactional advisory locks should be used with PostgreSQL. If false, session-level locks will be used instead,” and the setting’s own page says it “should be set to false for statements such as CREATE INDEX CONCURRENTLY.” Transactional by default, session-level when a step can’t live in a transaction. Separately, Flyway records applied migrations in a schema history table — “a complete audit trail of all changes performed against the schema,” per the table’s documentation.

Prisma takes the same primitive. Its migration docs state that “concurrent deploys are safe: on PostgreSQL the whole apply runs inside a transaction guarded by an advisory lock, so two db migrate runs serialize instead of interleaving.”

node-pg-migrate does too, and its default runs the other way from the reference above. Its troubleshooting page explains that “by default, migration locking uses a PostgreSQL advisory lock that is scoped to the entire PostgreSQL instance. As a result, only one migration process can hold the lock at a time,” and that setting advisoryLockMode to wait means “a migration process will wait for the advisory lock to be released instead of failing immediately.” Fail-fast is the default, waiting the opt-in.

Liquibase and Knex take a different route, and it’s the one that shows the trade. Liquibase uses a lock table — “Liquibase uses the DATABASECHANGELOGLOCK (DBCLL) table to ensure only one instance of Liquibase runs at a time,” per its documentation, with a row flag “set to 1 if Liquibase is running against this database.” Knex does the same with SELECT ... FOR UPDATE against a knex_migrations_lock table: “a lock system is there to prevent multiple processes from running the same migration batch in the same time,” per the migrations guide.

Both then document the cost, in their own words. Liquibase: “If Liquibase does not exit cleanly, the lock row may be left as locked,” cleared with liquibase release-locks. Knex: “if your process unfortunately crashes, the lock will have to be manually removed with knex migrate:unlock in order to let migrations run again.”

That is the argument for the advisory lock, and it’s specifically an argument for a service that migrates on startup. A row in a table has to be cleared by somebody after a crash. A session lock is cleared by the crash. Startup is exactly where a container gets killed mid-run — by a health check, a deploy, a scale-in — so the failure mode a lock table has is the failure mode a startup migration produces most.

Worth naming the disagreement too. Prisma’s docs describe migrations as arriving through the pipeline — “in CI and production, migrations arrive via your repo, already planned, reviewed, and merged” — which is the pipeline-step model, not this one. That’s a real fork, and the tools mostly assume the other side of it.

Bake the scripts into the service, take a lock the database itself owns, and never write a migration that takes something away. What you get for it is a deploy with one artifact instead of two, and a rollback that costs a traffic shift. What you owe for it is the contract phase — the columns nobody reads, dropped on purpose later, by someone who checked first.

Related system

More field notes

Start