0Pricing
SQL Academy · Lesson

CI for Schema Changes (GitHub Actions)

Run migrations, dbt build and SQLFluff lint inside GitHub Actions on every PR, with ephemeral databases.

CI for Schema Changes (GitHub Actions) is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why CI for SQL?

Manual migrations cause outages. CI catches:

  • Migration that fails to apply
  • Migration that breaks existing tests
  • Schema drift between environments
  • Lint and test regressions

A Solid SQL CI Pipeline

  1. Spin up ephemeral Postgres (Docker / GitHub Actions service)
  2. Apply all migrations from scratch
  3. Apply only the new migrations on a copy of staging schema
  4. Run dbt build / Flyway test / app tests
  5. SQLFluff lint
  6. Schema diff report

GitHub Actions: Postgres Service

Spin up Postgres alongside the job:

name: ci
on: [pull_request]
jobs:
  test:
    runs-on: ubuntu-latest
    services:
      pg:
        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: Run migrations
        env:
          DATABASE_URL: postgres://postgres:postgres@localhost:5432/postgres
        run: |
          ./scripts/migrate.sh

Lint Step

SQLFluff lint as a separate job:

jobs:
  lint:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
      - run: pip install sqlfluff && sqlfluff lint models/

dbt Build in CI

For dbt projects:

- name: dbt deps & build
  run: |
    pip install dbt-postgres
    dbt deps
    dbt build --profiles-dir profiles/ci

Schema Diff

migra (or apgdiff, schemahero) shows what changes a migration introduces:

pip install migra
migra postgres://baseline postgres://branch > diff.sql
# Comment diff.sql on the PR

Forward-Only Strategy

Always create new migrations; never edit applied ones. The CI ensures the migration sequence applies from scratch.

Backward-Compatible Schema Changes

Each PR's schema should remain compatible with the previous app version, so you can deploy app + DB in any order. CI can verify this by running OLD app tests against NEW schema.

Test Data Seed

A small fixture script in CI lets tests run against realistic data:

- name: Seed
  run: psql $DATABASE_URL < seed/dev.sql

Lock Timeouts in Migration Tests

Set lock_timeout in CI migrations to mimic production behaviour:

ALTER DATABASE postgres SET lock_timeout = '5s';

Production Apply

For prod migrations, use a one-shot job:

  • Migration runner with retries
  • Notifications on failure
  • Rollback procedure documented

Branch Database Pattern

Tools like Neon, Supabase branching let you create a copy of production for each PR — run migrations and app tests against a real-data copy.

Recap

SQL belongs in CI.

  • Spin up ephemeral PG service
  • Apply migrations + dbt build
  • Lint with SQLFluff
  • Run app tests against the migrated schema
  • Diff schema for review

Quick Check

What's the simplest way to add a Postgres database to a GitHub Actions job?

Frequently asked questions

Is the “CI for Schema Changes (GitHub Actions)” lesson free?

Yes — the full text of “CI for Schema Changes (GitHub Actions)” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “CI for Schema Changes (GitHub Actions)”?

Run migrations, dbt build and SQLFluff lint inside GitHub Actions on every PR, with ephemeral databases. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “CI for Schema Changes (GitHub Actions)” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. DBT (Data Build Tool) Fundamentals
  2. SQLFluff and Linting
  3. Testing SQL: dbt tests, Great Expectations
  4. CI for Schema Changes (GitHub Actions)
← Back to SQL Academy