Database CI/CD: Versioned Migrations, GitHub Actions, and Schema Testing

Database CI/CD should prove that a versioned change can build and upgrade a representative schema, preserve required contracts, and be deployed once through an authorized path. A pull-request job validates migration files against an ephemeral database; it does not deploy production. Production migration is a separate, protected job with its own credentials, approvals, backups, observation window, and post-deploy checks.

Give Each Stage One Responsibility

Keep migrations in an append-only directory such as db/migration/V001__create_customer.sql. Once a versioned migration has run in a shared environment, changing its contents changes the checksum and destroys reproducibility. Add a later corrective migration instead. Repeatable migrations suit replaceable objects such as views, but their rerun behavior still needs tests and review.

A Safe Pull-Request Workflow

The following Linux-runner example uses MySQL 8.4 as a GitHub Actions service and Flyway 13.1.0's official container. Pin all third-party actions and containers to versions or reviewed digests under your dependency policy. Secrets are referenced through GitHub Actions; they are not written into the repository or command line. The job applies migrations only to the ephemeral service database.

name: database-ci
on:
  pull_request:
    paths:
      - 'db/migration/**'
      - 'db/test/**'

permissions:
  contents: read

jobs:
  migration-test:
    runs-on: ubuntu-latest
    services:
      mysql:
        image: mysql:8.4
        env:
          MYSQL_DATABASE: app_ci
          MYSQL_USER: app_ci
          MYSQL_PASSWORD: ${{ secrets.CI_DB_PASSWORD }}
          MYSQL_ROOT_PASSWORD: ${{ secrets.CI_DB_ROOT_PASSWORD }}
        ports:
          - 3306:3306
        options: >-
          --health-cmd='mysqladmin ping --silent'
          --health-interval=5s
          --health-timeout=3s
          --health-retries=30

    env:
      FLYWAY_URL: jdbc:mysql://127.0.0.1:3306/app_ci
      FLYWAY_USER: app_ci
      FLYWAY_PASSWORD: ${{ secrets.CI_DB_PASSWORD }}

    steps:
      - uses: actions/checkout@v6

      - name: Build schema from migrations
        run: >-
          docker run --rm --network host
          -e FLYWAY_URL -e FLYWAY_USER -e FLYWAY_PASSWORD
          -v "${GITHUB_WORKSPACE}/db/migration:/flyway/sql:ro"
          redgate/flyway:13.1.0
          -connectRetries=60 migrate

      - name: Validate migration history
        run: >-
          docker run --rm --network host
          -e FLYWAY_URL -e FLYWAY_USER -e FLYWAY_PASSWORD
          -v "${GITHUB_WORKSPACE}/db/migration:/flyway/sql:ro"
          redgate/flyway:13.1.0 validate

      - name: Run schema assertions
        run: ./db/test/assert-schema.sh

GitHub requires a Linux runner for Docker service containers. A runner job reaches the mapped service port on 127.0.0.1; a containerized job uses the service label and container port instead. Confirm this networking model before copying the workflow. Fork pull requests normally do not receive repository secrets, so either use a non-secret ephemeral test credential generated inside the job or skip privileged paths for untrusted contributors. Never weaken secret policy merely to make a fork workflow pass.

Test the Contract, Not Only Object Existence

Schema assertions should query the database catalog and fail with a nonzero exit status. Verify column type and nullability, index column order and uniqueness, foreign keys and check constraints, default expressions, grants, view definitions, and migration history. Test representative application reads and writes using both the currently deployed application contract and the new contract during a compatibility rollout.

SELECT COUNT(*) = 1 AS orders_table_exists
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name = 'orders';

SELECT index_name, non_unique, seq_in_index, column_name
FROM information_schema.statistics
WHERE table_schema = DATABASE()
  AND table_name = 'orders'
  AND index_name = 'ix_orders_customer_created'
ORDER BY seq_in_index;

Make the test harness assert exact expected rows rather than printing them. Add fixtures for existing data, nulls, duplicates, long values, concurrent writes, and the largest credible table shape. A fresh database finds missing dependencies and invalid SQL; a sanitized baseline finds upgrade defects such as incompatible existing values, long locks, unexpected backfill cost, and checksum history differences.

Use Expand and Contract for Compatibility

A database migration and application deployment rarely become visible atomically. For a rename or type change, first expand: add the new nullable structure, preserve the old interface, and deploy application code that can tolerate both. Backfill by deterministic key ranges with small commits and observable progress. Reconcile old and new values, then switch reads and writers gradually. Only after every deployed version no longer depends on the old contract should a later migration contract by removing it.

Test online DDL on the exact engine version and a production-shaped copy. Metadata locks, table rebuilds, storage headroom, replication delay, and transaction duration determine operational risk. A statement being syntactically valid does not make it safe at peak traffic. The zero-downtime expand-and-contract guide covers the rollout pattern in depth, while the Flyway versioning guide covers tool-specific project structure.

Separate Validation From Production Deployment

Use a dedicated workflow triggered from a protected branch, release, or manual approval. Attach a GitHub environment with required reviewers and environment-scoped secrets. Grant the deployment identity only the DDL and data privileges required for the approved change, and restrict network access to the controlled runner. Prevent concurrent database deployments with a single concurrency group per environment.

1. Confirm the artifact or commit SHA, migration list, owner, maintenance expectations, abort conditions, and current database identity.
2. Run flyway info and flyway validate against the target without modifying it. Investigate missing, changed, failed, or unexpected migrations.
3. Verify a recent restorable backup and measure available storage, replication health, long transactions, and blocking sessions.
4. Apply flyway migrate once from the deployment job. Application instances must not race to migrate on startup.
5. Run catalog assertions, application canaries, and business invariants. Observe errors, latency, locks, storage, and replicas through the agreed window.
6. Record the Flyway output, schema-history state, commit, approver, start/end times, and validation evidence.

Do not promise a down migration for every change. Dropping data, narrowing a type, or transforming values can be irreversible after new writes. Prefer a forward fix, feature flag, application rollback that remains compatible with the expanded schema, or restore/reconcile plan. A reverse script is useful only when its preconditions, data effect, and rehearsal are explicit.

Primary Documentation

• Redgate Flyway: validate command
• Redgate Flyway: migrate command
• Redgate Flyway: official Docker usage
• GitHub Actions: communicating with service containers
• GitHub Actions: using secrets safely
• GitHub Actions: deployment environments and protection rules