Ordering Migrations Across Parallel Branches

Two pull requests, both green, both adding a migration. The first merges. The second merges an hour later, and the deploy fails on a migration that references a column the other branch renamed. Neither pull request was wrong — the schema each was tested against no longer exists.

When to use this pattern

  • More than one person ships schema changes in the same week.
  • Migrations are numbered sequentially and collisions have started appearing.
  • A migration has failed on main after passing on the branch that introduced it.

Prerequisites

Why “green on the branch” is not enough

A migration is tested against the schema its branch sees. That schema is the base branch at fork time — not the base branch at merge time, and those diverge the moment someone else merges first.

Two Green Branches, One Broken Main Branch A and branch B fork from the same base schema. Branch A adds a migration renaming a column and merges first. Branch B adds a migration referencing the old column name, passes CI against the fork-time schema, and fails when applied to main after A. base schema at fork time branch A adds display_name, drops full_name branch B adds index on full_name CI green CI green merges first merges second main deploy fails column full_name does not exist Neither pull request was wrong in isolation. The bug is that neither was ever applied on top of the other.

Complete working example

#!/usr/bin/env bash
# scripts/migration-order-check.sh — required check on every pull request.
set -euo pipefail
BASE="origin/${GITHUB_BASE_REF:-main}"
DIR=migrations

# 1. Filenames must be timestamped, not sequential. Timestamps do not collide
#    between branches and sort stably regardless of merge order.
for f in $(git diff --name-only "$BASE"... -- "$DIR" | grep -E '\.sql$' || true); do
  base=$(basename "$f")
  echo "$base" | grep -qE '^[0-9]{8}_[0-9]{6}_[a-z0-9_]+\.sql$' \
    || { echo "❌ $base — expected YYYYMMDD_HHMMSS_description.sql"; exit 1; }
done

# 2. This branch's new migrations must sort AFTER everything already on the base.
newest_base=$(git ls-tree --name-only "$BASE" "$DIR/" | sort | tail -1 | xargs -r basename)
oldest_new=$(git diff --name-only --diff-filter=A "$BASE"... -- "$DIR" \
             | xargs -r -n1 basename | sort | head -1)

if [ -n "$oldest_new" ] && [ -n "$newest_base" ]; then
  # String compare works because the prefix is a fixed-width sortable timestamp.
  if [ "$oldest_new" \< "$newest_base" ]; then
    echo "❌ ${oldest_new} sorts before ${newest_base}, already on ${BASE}."
    echo "   Rebase and re-date the migration so it applies last."
    exit 1
  fi
fi

# 3. Apply the WHOLE chain from empty, in the post-merge order. This is the check
#    that would have caught the branch-A/branch-B case above.
createdb migration_probe
trap 'dropdb --if-exists migration_probe' EXIT
git merge-tree "$(git merge-base HEAD "$BASE")" HEAD "$BASE" >/dev/null   # sanity: no textual conflict
npm run migrate:deploy -- --database-url "postgres:///migration_probe"

echo "✅ migrations apply cleanly in merged order"
# .github/workflows/migrations.yml
name: Migrations
on: [pull_request]

jobs:
  order:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env: { POSTGRES_PASSWORD: probe }
        options: >-
          --health-cmd pg_isready --health-interval 5s --health-retries 10
        ports: ['5432:5432']
    steps:
      - uses: actions/checkout@v4
        with: { fetch-depth: 0 }        # the base comparison needs history
      - uses: actions/setup-node@v4
        with: { node-version: 20, cache: npm }
      - run: npm ci --ignore-scripts

      # Re-run against the MERGED result, not the branch tip: this is the whole point.
      - run: git merge --no-commit --no-ff "origin/${{ github.base_ref }}" || true
      - run: bash scripts/migration-order-check.sh
        env:
          PGHOST: localhost
          PGPASSWORD: probe
// package.json — generate timestamped names, so nobody has to remember the format.
{
  "scripts": {
    "migrate:new": "node -e \"const d=new Date().toISOString().replace(/[-:T]/g,'').slice(0,15); const n=process.argv[1]; require('fs').writeFileSync(`migrations/${d.slice(0,8)}_${d.slice(8,14)}_${n}.sql`, '-- write the migration here\\n')\" --"
  }
}

Step-by-step walkthrough

Timestamps instead of sequence numbers. Two branches created from the same base both compute “next number = 42”. Timestamps are generated at file-creation time and effectively never collide, and they sort correctly as plain strings, which makes every downstream check a string comparison rather than a graph traversal.

Ordering is checked against the base, not within the branch. A migration whose timestamp is older than something already merged will apply before it on a fresh database and after it on an existing one — which means the schema you get depends on when the database was created. That divergence is subtle, survives review, and is exactly what the second check rejects.

Apply the whole chain from empty. Applying only the new migration to a copy of the current schema tests the incremental path. Applying every migration from an empty database tests the path a new environment takes — and a preview environment created tomorrow takes exactly that path. Both matter, and only the second catches an ordering fault.

Merge the base into the branch before checking. Running the check on the branch tip re-tests what CI already tested. Running it on the merged tree tests the thing that will actually exist after merge, which is where the conflict lives.

Rebase and re-date rather than merging. If the check fails, the fix is to rebase onto the updated base and rename the migration file with a fresh timestamp. That is a rename plus a re-run, not a redesign — and it is why the timestamp lives in the filename rather than inside the file.

Verification

# 1. The check rejects an out-of-order migration.
git switch -c probe origin/main
touch migrations/20200101_000000_backdated.sql
bash scripts/migration-order-check.sh; echo "exit=$?"

Expected:

❌ 20200101_000000_backdated.sql sorts before 20260731_084500_add_display_name.sql, already on origin/main.
   Rebase and re-date the migration so it applies last.
exit=1
# 2. A fresh database and an incrementally-migrated one end up identical.
createdb from_scratch && npm run migrate:deploy -- --database-url postgres:///from_scratch
pg_dump --schema-only from_scratch | grep -v '^--' > /tmp/scratch.sql
pg_dump --schema-only production_replica | grep -v '^--' > /tmp/live.sql
diff /tmp/scratch.sql /tmp/live.sql && echo "schemas match"

# 3. No two migrations share a timestamp prefix.
ls migrations | cut -c1-15 | sort | uniq -d | head     # → no output

The second check is the one worth running on a schedule as well as on pull requests. A drift between the from-scratch schema and the live one means some migration was applied out of order at some point in the past, and finding that early is far cheaper than discovering it when a new preview environment behaves differently from production.

Filename Order Versus Applied Order On a fresh database, migrations are applied in filename order. On an existing database they are applied in the order they arrived, which for a long-lived branch can differ. The two orders producing different schemas is the failure this page exists to prevent. FRESH DATABASE — filename order 0729_add_column 0730_add_index 0731_drop_old_column EXISTING DATABASE — arrival order after a late merge 0729_add_column 0731_drop_old_column 0730_add_index — fails The index migration references a column the drop already removed. Both orders are individually reasonable; only one works.

Choosing between rebase and merge

The mechanics above assume rebase, and that assumption is worth making explicit because a merge-based workflow needs a different answer.

With rebase, a branch’s migrations are always the newest in the history at the moment it merges, so re-dating on rebase keeps filename order and applied order identical forever. The cost is that rebasing rewrites history, which some teams avoid for shared branches.

With merge commits, a branch’s migration keeps its original timestamp, so a long-lived branch can merge a migration whose timestamp predates several already-applied ones. Filename order and applied order then permanently disagree. Tools that track applied migrations by name cope — they simply record it as applied late — but a fresh database will apply them in filename order and produce a different schema. If your workflow requires merge commits, the compensating control is check 2 above running on every merge to main, so the divergence is caught the same day rather than months later when someone provisions a new environment.

The third option — squashing each pull request into one commit on main — sidesteps the question entirely for ordering purposes, because the migration lands with a single timestamp at merge time. It is the least discussed and often the simplest fit for teams that ship several schema changes a week.

Merge Strategy vs Migration Ordering Guarantee Rebase with re-dating keeps filename order and applied order identical and needs only the pull-request check. Merge commits allow the two orders to diverge and require a scheduled from-scratch comparison. Squash merging assigns the timestamp at merge time and keeps the orders aligned by construction. STRATEGY FILENAME ORDER = APPLIED ORDER? CHECK NEEDED rebase + re-date always — guaranteed by construction pull-request check merge commits can diverge permanently plus scheduled from-scratch diff squash on merge always — timestamp assigned at merge pull-request check Pick the strategy first; the ordering discipline follows from it rather than the other way round.

Common pitfalls

Sequential numbering with a “next number” script. It reads the current maximum, which is per-branch, so two branches confidently produce the same number. Timestamps remove the shared counter entirely.

Editing a migration that has already been applied anywhere. Some tools checksum applied migrations and will refuse to run; others silently skip it, leaving environments permanently different. Always add a new migration instead.

Testing only the incremental path. Applying the new migration to a copy of production says nothing about whether a fresh database produces the same schema — and a preview environment provisioned tomorrow is a fresh database, which is why environment parity validation gates catch this class of drift when the pull-request check does not.

← Back to Database Migrations & Backward-Compatible Deploys