CI/CD for Migrations — Complete Guide

# .github/workflows/ci-migrations.yml
name: CI - Migration Tests

on:
    pull_request:
        paths:
            - 'migrations/**'
            - 'models/**'
            - 'prisma/schema.prisma'

jobs:
    migration-tests:
        runs-on: ubuntu-latest
        services:
            postgres:
                image: postgres:16
                env:
                    POSTGRES_PASSWORD: postgres
                ports:
                    - 5432:5432
                options: >-
                    --health-cmd pg_isready
                    --health-interval 10s
                    --health-timeout 5s
                    --health-retries 5

        steps:
            - uses: actions/checkout@v4

            - name: Set up Python
              uses: actions/setup-python@v5
              with:
                  python-version: '3.12'

            - name: Install dependencies
              run: pip install -r requirements-dev.txt

            - name: Create test database
              run: createdb -h localhost -U postgres ci_test

            - name: Apply all migrations from scratch
              run: alembic upgrade head
              env:
                  DATABASE_URL: postgresql://postgres:postgres@localhost/ci_test

            - name: Verify schema
              run: python -m scripts.verify_schema
              env:
                  DATABASE_URL: postgresql://postgres:postgres@localhost/ci_test

            - name: Test rollback
              run: |
                  alembic downgrade -1
                  alembic upgrade head
              env:
                  DATABASE_URL: postgresql://postgres:postgres@localhost/ci_test

            - name: Run application tests
              run: pytest tests/
              env:
                  DATABASE_URL: postgresql://postgres:postgres@localhost/ci_test

# .gitlab-ci.yml with migration stages
stages:
  - build
  - migrate
  - deploy
  - verify

variables:
  MIGRATION_IMAGE: $CI_REGISTRY_IMAGE/migration:$CI_COMMIT_SHA

build-migration-image:
  stage: build
  script:
    - docker build -t $MIGRATION_IMAGE -f Dockerfile.migration .
    - docker push $MIGRATION_IMAGE
  only:
    - main

run-migrations:
  stage: migrate
  image: $MIGRATION_IMAGE
  script:
    - echo "Running migrations..."
    - alembic upgrade head
    - echo "Migrations completed"
  environment:
    name: production
  only:
    - main
  when: manual  # Require manual approval for production migrations
  allow_failure: false

deploy-application:
  stage: deploy
  script:
    - kubectl set image deployment/app app=$CI_REGISTRY_IMAGE/app:$CI_COMMIT_SHA
    - kubectl rollout status deployment/app
  environment:
    name: production
  needs: ["run-migrations"]
  only:
    - main

verify-deployment:
  stage: verify
  script:
    - python scripts/health_check.py
    - python scripts/verify_schema.py
    - python scripts/smoke_tests.py
  environment:
    name: production
  needs: ["deploy-application"]
  only:
    - main

auto-rollback:
  stage: verify
  script:
    - python scripts/rollback.py
    - kubectl rollout undo deployment/app
  environment:
    name: production
  needs: ["deploy-application"]
  when: on_failure  # Triggered when verify fails
  only:
    - main

# Environment-specific migration configuration
# .env.development
DATABASE_URL=postgresql://dev:dev@localhost/dev_db
AUTO_MIGRATE=true  # Auto-run migrations on dev startup

# .env.staging
DATABASE_URL=postgresql://staging:staging@staging-host/staging_db
AUTO_MIGRATE=true  # Auto-run migrations in staging CI

# .env.production
DATABASE_URL=postgresql://prod:prod@prod-host/prod_db
AUTO_MIGRATE=false  # Require manual approval for production

#!/bin/bash
# scripts/run_migrations.sh
set -euo pipefail

ENVIRONMENT=${1:-development}
echo "Running migrations for $ENVIRONMENT environment..."

case $ENVIRONMENT in
    development)
        # Auto-run migrations on dev startup
        alembic upgrade head
        echo "Development migrations applied"
        ;;

    staging)
        # Auto-run in staging CI/CD
        alembic upgrade head
        echo "Staging migrations applied"
        ;;

    production)
        # Require confirmation for production
        echo "WARNING: Running migrations on PRODUCTION"
        echo "Current migration state:"
        alembic current

        read -p "Continue with production migration? (yes/no): " confirmation
        if [ "$confirmation" != "yes" ]; then
            echo "Migration cancelled"
            exit 1
        fi

        # Take a backup before migrating
        echo "Taking database backup..."
        pg_dump $DATABASE_URL > "backup_$(date +%Y%m%d_%H%M%S).sql"

        # Run migrations
        alembic upgrade head
        echo "Production migrations applied"

        # Verify
        python scripts/verify_schema.py
        echo "Schema verification passed"
        ;;
esac

# scripts/monitor_migrations.py
"""Monitor migration execution and report status."""
import os
import time
import requests
from datetime import datetime

def monitor_migration():
    """Monitor migration execution and report to team."""
    webhook_url = os.environ.get('SLACK_WEBHOOK_URL')
    start_time = time.time()

    def report(message, status='info'):
        if webhook_url:
            requests.post(webhook_url, json={
                'text': f'[Migration] {message}',
                'attachments': [{
                    'color': {
                        'info': '#3498db',
                        'success': '#2ecc71',
                        'error': '#e74c3c',
                    }.get(status, '#3498db'),
                    'fields': [
                        {'title': 'Environment', 'value': os.environ.get('ENV', 'unknown'), 'short': True},
                        {'title': 'Timestamp', 'value': datetime.now().isoformat(), 'short': True},
                        {'title': 'Duration', 'value': f'{time.time() - start_time:.1f}s', 'short': True},
                    ]
                }]
            })

    try:
        report('Migration starting...')

        # Run migration
        result = subprocess.run(
            ['alembic', 'upgrade', 'head'],
            capture_output=True, text=True, check=True,
            timeout=300
        )

        report(f'Migration completed in {time.time() - start_time:.1f}s', 'success')
        return True

    except subprocess.CalledProcessError as e:
        report(f'Migration FAILED: {e.stderr[:500]}', 'error')
        return False
    except subprocess.TimeoutExpired:
        report('Migration TIMEOUT after 5 minutes', 'error')
        return False

# Deployment strategies for different migration risk levels

# Strategy 1: Blue-Green Deployment (safe for most migrations)
blue-green:
  - Run migrations on both blue and green databases
  - Switch traffic after both are migrated
  - Rollback: switch traffic back

# Strategy 2: Expand-Contract Pattern (for breaking changes)
expand-contract:
  - Phase 1: Add new columns/tables (non-breaking)
  - Phase 2: Update application to use new schema
  - Phase 3: Remove old columns/tables (in a later release)
  - Rollback: Revert application code, keep old schema

# Strategy 3: Feature Flags
feature-flags:
  - Add column with feature flag gating
  - Deploy with flag disabled
  - Enable flag after verifying migration
  - Rollback: Disable flag

# scripts/expand_contract_migration.py
"""Example of expand-contract pattern."""
def phase1_expand():
    """Add new columns alongside old ones."""
    op.add_column('users', sa.Column('email_new', sa.String(255)))
    op.add_column('users', sa.Column('email_verified_new', sa.Boolean()))

def phase2_migrate_data():
    """Backfill new columns from old ones."""
    op.execute("""
        UPDATE users
        SET email_new = email,
            email_verified_new = email_verified
    """)

def phase3_contract():
    """Remove old columns (in a future release)."""
    op.drop_column('users', 'email')
    op.drop_column('users', 'email_verified')
    op.rename_column('users', 'email_new', 'email')
    op.rename_column('users', 'email_verified_new', 'email_verified')