Database Migrations in a Deployment Pipeline

A database migration changes the shape of your data — adding a column, renaming a table, backfilling a value — and it has to happen in lockstep with a code deploy without corrupting data or taking the application down. Unlike deploying a stateless container, a migration is often irreversible in practice: you can redeploy an old container image in seconds, but you cannot always “undeploy” a schema change once other services have written data against it. This lesson covers how to run migrations as a first-class, gated step in a GitHub Actions deployment pipeline, and how to avoid the mistakes that turn a routine schema change into an incident.

Overview / How it works

Migrations belong to the deployment stage of your pipeline, not continuous integration. CI proves the migration files are syntactically valid and that application code compiles against the new schema; deployment is where the migration actually runs against a real database, and that only happens after CI has passed. Continuous delivery means every change is proven deployable and a human approves the production release; continuous deployment means passing checks trigger production release automatically. Migrations usually push teams toward continuous delivery for the database step specifically, even when the application container deploys continuously, because schema changes carry more risk than a stateless rollout.

The core technique for doing this safely is the expand/contract pattern (also called parallel change). Instead of one migration that adds a column and removes an old one in the same release, you split the change into phases so that old and new application code can both run against the same schema during a rolling deploy.

Phase Schema change App behavior Safe to deploy alone?
Expand Add new column or table, additive only Old and new code both still work Yes
Migrate data Backfill values into the new column New code starts dual-writing old and new Yes, if the backfill is idempotent
Contract Drop the old column or table Only new code paths remain Only once every instance runs the new code

Because a migration job talks directly to production data, it needs the same protections as a production deploy job: a protected environment, required reviewers where appropriate, and credentials scoped to only what the migration tool needs — never a superuser connection string sitting in the workflow file.

Syntax or workflow structure

A typical pipeline separates test, migrate, and deploy into distinct jobs connected with needs:, so migrations only run after tests pass and application deployment only starts after the migration job succeeds. Key structural elements:

  • permissions: contents: read at the workflow level, since a migration job only needs to check out migration files and connect to a database — it does not need to push packages or write repository contents. Add id-token: write only if you use OpenID Connect to mint short-lived cloud database credentials instead of a long-lived secret.
  • environment: production on the migration job, so branch protection, required reviewers, and environment-scoped secrets apply to schema changes exactly as they do to deploys.
  • concurrency: with a shared group name across any job that touches the same database, so two runs can never migrate concurrently and race on locks.
  • timeout-minutes on the migration step, so a lock wait or a runaway backfill fails loudly instead of blocking the pipeline indefinitely.

Examples

Example 1: Gated migration before deploy

This is the minimum viable safe pipeline: tests must pass, then migrations run against the production environment, then the application deploys.

name: Deploy with Database Migration

on:
  push:
    branches: [main]

permissions:
  contents: read

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: npm ci
      - run: npm test

  migrate:
    needs: test
    runs-on: ubuntu-latest
    environment: production
    concurrency:
      group: production-migrations
      cancel-in-progress: false
    steps:
      - uses: actions/checkout@v4
      - name: Run database migrations
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: npx prisma migrate deploy

  deploy:
    needs: migrate
    runs-on: ubuntu-latest
    environment: production
    steps:
      - uses: actions/checkout@v4
      - name: Deploy application
        run: ./deploy.sh

Expected behavior: pushes to main run tests first; only a green test run triggers the migration job, which runs inside the protected production environment (so any required reviewers on that environment must approve); the deploy job only starts once the schema change has been applied. If migrate fails, deploy never runs, so the application is never started against a schema it doesn’t expect.

Example 2: Adding a safety net

The first example has no way to preview what will run and no recovery point if the migration goes wrong. This version adds a pre-migration snapshot, a dry-run preview, an explicit timeout, and a post-deploy health check.

  migrate:
    needs: test
    runs-on: ubuntu-latest
    environment: production
    timeout-minutes: 10
    concurrency:
      group: production-migrations
      cancel-in-progress: false
    steps:
      - uses: actions/checkout@v4

      - name: Snapshot database before migrating
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: ./scripts/create-db-snapshot.sh pre-migrate-${{ github.sha }}

      - name: Preview pending migrations
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: npx prisma migrate diff --from-schema-datasource ./prisma/schema.prisma --to-migrations ./prisma/migrations --script

      - name: Apply migrations
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: npx prisma migrate deploy

  deploy:
    needs: migrate
    runs-on: ubuntu-latest
    environment: production
    steps:
      - uses: actions/checkout@v4
      - name: Deploy application
        run: ./deploy.sh
      - name: Health check
        run: ./scripts/health-check.sh https://api.example.com/healthz

Expected behavior: a snapshot is captured before any schema change, so a failed migration has a known-good restore point; the diff step prints the SQL that will run so reviewers approving the production environment can see exactly what they are authorizing; if the migration hangs past ten minutes the job fails instead of stalling the pipeline; and the deploy job only reports success once the health check endpoint responds, giving you a clear signal before you consider the release complete.

Example 3: Expand/contract across two releases

For a genuinely risky change — like dropping a column that live traffic still reads during a rolling deploy — split the change into two separate workflow runs, days or weeks apart, with an explicit manual gate for the contracting phase.

name: Expand Migration - Add Column (Backward Compatible)

on:
  push:
    branches: [main]
    paths:
      - "prisma/migrations/**"

permissions:
  contents: read

jobs:
  migrate-expand:
    runs-on: ubuntu-latest
    environment: production
    concurrency:
      group: production-migrations
      cancel-in-progress: false
    steps:
      - uses: actions/checkout@v4
      - name: Apply additive migration
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: npx prisma migrate deploy

  deploy-dual-write:
    needs: migrate-expand
    runs-on: ubuntu-latest
    environment: production
    steps:
      - uses: actions/checkout@v4
      - name: Deploy version that writes both old and new columns
        run: ./deploy.sh

Once every running instance is confirmed to be on the dual-write version, a separate, manually triggered workflow performs the contract phase against a stricter environment that requires reviewer approval:

name: Contract Migration - Drop Old Column

on:
  workflow_dispatch:

permissions:
  contents: read

jobs:
  migrate-contract:
    runs-on: ubuntu-latest
    environment: production-contract
    steps:
      - uses: actions/checkout@v4
      - name: Confirm no code paths read the old column
        run: ./scripts/grep-for-legacy-column-usage.sh
      - name: Apply contracting migration
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: npx prisma migrate deploy

Expected behavior: the expand workflow runs automatically whenever a new additive migration file is pushed, and old application instances keep working unmodified throughout the rollout. The contract workflow only runs when someone deliberately triggers it with workflow_dispatch, and the production-contract environment’s required reviewers give a human the chance to confirm nothing still depends on the column before it disappears.

Step by step

  1. Write the migration as a small, additive, idempotent change — prefer ADD COLUMN over RENAME COLUMN, and make destructive changes a separate, later migration.
  2. Let CI validate the migration files and run the application’s test suite against a throwaway database, catching syntax and logic errors before anything touches production.
  3. On merge to the deploy branch, run the migration job inside a protected environment with a concurrency group unique to that database.
  4. Take a snapshot or confirm a recent automated backup exists immediately before applying the migration.
  5. Apply the migration with a tool that tracks applied versions (Prisma Migrate, Flyway, Alembic, Rails’ db:migrate) so it is safe to re-run and skips migrations already applied.
  6. Only after the migration job reports success, deploy the new application version, then verify with an automated health check.
  7. If the health check fails, stop the rollout and use the pre-migration snapshot or the migration tool’s rollback command to recover, rather than pushing forward.
  8. Schedule the contract phase (dropping now-unused columns) as a separate, later, manually approved run once you’ve confirmed no instance still needs the old shape.

Common Mistakes

Mistake 1: Hardcoding the database connection string in the workflow file

Putting real connection details directly in the YAML — even temporarily for testing — means the credential lives in plaintext in your repository history forever.

jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - name: Run migrations
        run: npx prisma migrate deploy
        env:
          DATABASE_URL: postgres://admin:REPLACE_WITH_PASSWORD@your-db-host:5432/app

Correction: store the connection string as an environment-scoped secret and reference it with ${{ secrets.DATABASE_URL }}, as shown in Examples 1 and 2. Scope the environment’s secret to a database user with only the privileges the migration tool needs — schema DDL rights, not full superuser access — and rotate it if it is ever exposed in a log or a fork’s workflow run.

Mistake 2: Dropping a column in the same release that stops using it

During a rolling deploy, old and new application instances run side by side for minutes. If the migration removes a column the old code still reads, every request an old instance handles during that window fails.

# Migration file: drops a column the OLD app version still reads during a rolling deploy
ALTER TABLE orders DROP COLUMN legacy_status;

# The same release also deploys new app code that no longer references legacy_status.
# While old and new pods run side by side, old pods query legacy_status and every
# one of those requests fails with a database error until the rollout finishes.

Correction: use the expand/contract pattern from Example 3 — ship the additive change and dual-write code first, confirm the rollout is fully complete, and only drop the column in a later, separately gated release once nothing depends on it.

Best Practices

  • Treat migrations as forward-only in production; write a new migration to fix a mistake rather than editing or reordering an applied one.
  • Make every migration idempotent so re-running the job after a partial failure is safe.
  • Gate the migration job behind the same protected environment and required reviewers as your production deploy — schema changes deserve the same scrutiny.
  • Grant the migration’s database user only the privileges it needs (DDL and DML on relevant tables), not administrative access to the whole instance.
  • Always create a restorable snapshot or confirm a recent backup immediately before a migration runs, and record the snapshot identifier as a workflow log or artifact so it’s easy to find during an incident.
  • Set a concurrency group scoped to the target database so two workflow runs can never apply migrations at the same time.
  • Never trigger a migration job from pull_request_target or from a fork’s pull request — an untrusted contributor’s code should never run with credentials that can reach your production database.
  • Add a post-deploy health check step and a documented rollback command (tool-level down-migration, or snapshot restore) so recovery from a bad migration doesn’t require improvising under pressure.

Practice Exercises

  1. Take Example 1’s pipeline and add a timeout-minutes value and a concurrency group to the migrate job, then explain in your own words what each one prevents.
  2. Write an additive migration for adding a notification_preference column and a corresponding contracting migration that removes an old email_opt_in boolean column once it’s unused. Sketch the two-workflow structure from Example 3 for rolling this out safely.
  3. Identify which permissions the migrate job in Example 2 actually needs, and justify why contents: read is sufficient without adding packages: write or id-token: write.
  4. Describe, step by step, what you would do if the health check in Example 2 failed immediately after a migration ran — include which artifact from the pipeline you’d use to recover.

Summary

Database migrations are a deployment-stage concern that carries more risk than a stateless container rollout, because schema changes are hard to undo once other services depend on them. Gate migrations behind the same protected environments, minimal permissions, and secret handling you use for deploys; snapshot before you change anything; and use the expand/contract pattern to split destructive changes across releases so old and new code can coexist safely during a rollout. Done well, the migration job becomes a boring, predictable step in the pipeline rather than the riskiest one.