Database CI/CD with GitHub Actions: A Practical Guide

db/
  migrations/
    V001__create_customers.sql
    V002__create_orders.sql
    V003__add_orders_status_index.sql
  undo/
    U003__drop_orders_status_index.sql

name: db-ci
on:
  pull_request:
    paths: ['db/migrations/**']
 
jobs:
  lint:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: { python-version: '3.12' }
      - run: pip install sqlfluff==3.2.5
      - run: sqlfluff lint db/migrations --dialect postgres

      - name: Block unsafe migration patterns
        run: |
          set -e
          FILES=$(git diff --name-only origin/${{ github.base_ref }}... -- 'db/migrations/*.sql')
          [ -z "$FILES" ] && exit 0
          FAIL=0
          for f in $FILES; do
            # CREATE INDEX without CONCURRENTLY locks writes for the whole build
            if grep -Eiq 'CREATE +(UNIQUE +)?INDEX' "$f" && ! grep -Eiq 'CONCURRENTLY' "$f"; then
              echo "::error file=$f::CREATE INDEX must use CONCURRENTLY"; FAIL=1
            fi
            # A NOT NULL column with a volatile default rewrites the table on old versions
            if grep -Eiq 'ADD +COLUMN.*NOT +NULL' "$f" && ! grep -Eiq 'DEFAULT' "$f"; then
              echo "::error file=$f::ADD COLUMN NOT NULL needs a DEFAULT or a backfill plan"; FAIL=1
            fi
            # Destructive statements need an explicit opt-in comment
            if grep -Eiq '^\s*(DROP +(TABLE|COLUMN)|TRUNCATE)' "$f" && ! grep -q 'destructive-ok' "$f"; then
              echo "::error file=$f::destructive statement requires a '-- destructive-ok' comment"; FAIL=1
            fi
          done
          exit $FAIL

  migrate-fresh:
    runs-on: ubuntu-latest
    needs: lint
    services:
      postgres:
        image: postgres:17
        env:
          POSTGRES_PASSWORD: ci
          POSTGRES_DB: appdb
        ports: ['5432:5432']
        options: >-
          --health-cmd "pg_isready -U postgres"
          --health-interval 5s --health-timeout 5s --health-retries 10
    steps:
      - uses: actions/checkout@v4
 
      - name: Apply all migrations from empty
        run: |
          docker run --rm --network host \
            -v ${{ github.workspace }}/db/migrations:/flyway/sql \
            flyway/flyway:10 \
            -url=jdbc:postgresql://localhost:5432/appdb \
            -user=postgres -password=ci \
            -connectRetries=10 \
            migrate
 
      - name: Verify the schema matches the checked-in snapshot
        run: |
          PGPASSWORD=ci pg_dump -h localhost -U postgres -d appdb \
            --schema-only --no-owner --no-privileges > /tmp/schema.sql
          diff -u db/schema.sql /tmp/schema.sql

  migrate-against-restore:
    runs-on: ubuntu-latest
    needs: lint
    services:
      postgres:
        image: postgres:17
        env: { POSTGRES_PASSWORD: ci, POSTGRES_DB: appdb }
        ports: ['5432:5432']
        options: >-
          --health-cmd "pg_isready -U postgres"
          --health-interval 5s --health-retries 10
    steps:
      - uses: actions/checkout@v4
      - name: Restore anonymised snapshot
        env:
          PGPASSWORD: ci
        run: |
          aws s3 cp s3://db-snapshots/anon-latest.dump /tmp/anon.dump
          pg_restore -h localhost -U postgres -d appdb --no-owner -j 4 /tmp/anon.dump
 
      - name: Time the migration and fail if it locks too long
        env:
          PGPASSWORD: ci
        run: |
          START=$(date +%s)
          psql -h localhost -U postgres -d appdb \
            -v ON_ERROR_STOP=1 \
            -c "SET lock_timeout = '5s'; SET statement_timeout = '10min';" \
            -f db/migrations/$(ls db/migrations | tail -1)
          echo "took $(( $(date +%s) - START ))s"

      - name: Round-trip the migration
        run: |
          flyway migrate                       # to head
          flyway undo                          # back one version
          flyway migrate                       # forward again
          PGPASSWORD=ci pg_dump -h localhost -U postgres -d appdb \
            --schema-only --no-owner > /tmp/after.sql
          diff -u db/schema.sql /tmp/after.sql

  deploy:
    if: github.ref == 'refs/heads/main'
    runs-on: ubuntu-latest
    needs: [migrate-fresh, migrate-against-restore]
    environment: production        # requires a manual approval
    steps:
      - uses: actions/checkout@v4
 
      - name: Detect drift
        env:
          PGPASSWORD: ${{ secrets.PROD_DB_PASSWORD }}
        run: |
          pg_dump -h ${{ secrets.PROD_DB_HOST }} -U deployer -d appdb \
            --schema-only --no-owner --no-privileges > /tmp/prod-schema.sql
          if ! diff -u db/schema.sql /tmp/prod-schema.sql; then
            echo "::error::production schema has drifted from source control"
            exit 1
          fi
 
      - name: Show the plan
        run: flyway info
 
      - name: Migrate
        run: flyway -outOfOrder=false migrate

-- V010: expand — add the new column, keep the old one
ALTER TABLE customers ADD COLUMN email_address text;
 
-- V011: backfill in batches so nothing locks for long
UPDATE customers SET email_address = email
WHERE email_address IS NULL AND id BETWEEN 1 AND 100000;
 
-- V012: enforce, once the application writes both
ALTER TABLE customers
  ADD CONSTRAINT customers_email_address_not_null
  CHECK (email_address IS NOT NULL) NOT VALID;
ALTER TABLE customers VALIDATE CONSTRAINT customers_email_address_not_null;
 
-- V013: contract — only after every deployed version has stopped reading `email`
-- destructive-ok
ALTER TABLE customers DROP COLUMN email;