Automate PostgreSQL Schema Validation in GitHub Actions

Every pull request that touches supabase/schemas/ should prove three things before it merges: the migration renders cleanly from the declarative diff, it passes replay-safety checks, and it is idempotent when applied twice to a real PostgreSQL instance. The workflow below wires up all three checks automatically using a throwaway Postgres service container — no persistent database, no Docker Compose, no shadow database to maintain.

Full workflow

Add the workflow file

Create .github/workflows/schema-diff.yml in your repository. The workflow runs on every pull request and spins up a postgres:17 service container that is discarded when the job finishes.

name: schema-diff
on: pull_request

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

    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0
      - uses: actions/setup-node@v4
        with:
          node-version: 22
      - run: npm install --no-save supaschema
      - name: Render migration from the PR diff
        run: |
          npx supaschema diff \
            --from "git:origin/${{ github.base_ref }}" \
            --to dir:supabase/schemas \
            --out /tmp/migration.sql
      - name: Check replay safety
        run: npx supaschema check /tmp/migration.sql
      - name: Verify apply-twice and catalog parity
        run: |
          npx supaschema verify \
            --from "git:origin/${{ github.base_ref }}" \
            --to dir:supabase/schemas \
            --migration /tmp/migration.sql \
            --database-url postgresql://postgres:postgres@localhost:5432/postgres

Understand fetch-depth: 0

The git: source prefix tells supaschema to read schema files from a specific Git ref rather than the working tree. This means the runner must have the full commit history available — a shallow clone (the Actions default) will cause the diff step to fail with a ref-not-found error. Setting fetch-depth: 0 ensures the complete history is fetched.

Install supaschema

The npm install --no-save supaschema step installs the CLI into node_modules/.bin without writing to package.json. If supaschema is already a dev dependency in your project, replace this step with npm ci to use the lockfile-pinned version.

Run diff

The diff step computes the delta between the base branch schema (git:origin/${{ github.base_ref }}) and the PR’s schema directory (dir:supabase/schemas), then writes the resulting migration SQL to /tmp/migration.sql. If no schema files changed, the file is empty and subsequent steps succeed immediately.

Run check

supaschema check performs static replay-safety analysis on the generated SQL without connecting to any database. It flags:

DROP statements that are missing IF EXISTS
CREATE statements that are missing IF NOT EXISTS or CREATE OR REPLACE
Destructive operations that require an explicit hint in your config
ALTER COLUMN TYPE rewrites that may lock the table

The step exits non-zero if any diagnostic is raised, failing the PR.

Run verify

supaschema verify applies the migration to the throwaway Postgres service container, then applies it a second time to prove idempotency, and finally compares the live catalog against your declared schema to confirm parity. A mismatch on any of the three checks exits non-zero and fails the PR.

Environment variables

Variable	Required	Description
`DATABASE_URL`	Only for `verify`	Connection string for the throwaway Postgres instance. In the workflow above this is passed inline; you can also store it as a repository secret.
`SUPASCHEMA_VERIFY_ALLOW_REMOTE`	No	Set to `1` to allow `verify` to target a real remote database you intentionally treat as disposable (staging branches, ephemeral preview environments).

Never point verify at a production database. The command applies the migration and runs destructive idempotency checks. The service container in the workflow above is discarded at the end of the job.

Exit codes

Exit code	Meaning
`0`	Success — no issues found
`1`	General error (invalid arguments, unreadable config, parse failure)
`2`	Diagnostic failure — `check` or `verify` found issues
`3`	Drift detected — only raised by `diff --fail-on-diff`

Composite Action

If you prefer a one-liner, use the official composite action instead of the manual steps:

- uses: jmclaughlin724/supaschema@v0.1.0
  with:
    from: "git:origin/${{ github.base_ref }}"
    to: dir:supabase/schemas
    database-url: postgresql://postgres:postgres@localhost:5432/postgres

The composite action wraps diff, check, and verify into a single step and surfaces diagnostics as GitHub annotations.

Drift gate (simpler workflow)

If you only want to detect drift between your declared schema and a live database — without generating or verifying a migration — use the --fail-on-diff flag:

- name: Drift gate
  run: |
    npx supaschema diff \
      --from "db:${{ secrets.STAGING_DATABASE_URL }}" \
      --to dir:supabase/schemas \
      --fail-on-diff \
      --quiet

This exits 3 when drift is detected and 0 when the database matches your schema exactly. Use it as a scheduled gate on your staging environment.

Handling roles that don’t exist on bare PostgreSQL

Supabase projects commonly grant privileges to roles like authenticated, anon, and service_role that do not exist on a plain PostgreSQL instance. The verify command will fail when it tries to apply GRANT ... TO authenticated against the service container. Pass --ensure-roles to create stub roles before verification runs:

- name: Verify apply-twice and catalog parity
  run: |
    npx supaschema verify \
      --from "git:origin/${{ github.base_ref }}" \
      --to dir:supabase/schemas \
      --migration /tmp/migration.sql \
      --database-url postgresql://postgres:postgres@localhost:5432/postgres \
      --ensure-roles

--ensure-roles creates the roles only if they do not already exist. It is safe to use in both CI and local development.

SARIF reporter for GitHub code scanning

supaschema can emit diagnostics in SARIF format so they appear inline in the GitHub Security tab and as pull request annotations:

- name: Check replay safety (SARIF)
  run: |
    npx supaschema check /tmp/migration.sql \
      --reporter sarif \
      --output results.sarif
  continue-on-error: true

- name: Upload SARIF
  uses: github/codeql-action/upload-sarif@v3
  with:
    sarif_file: results.sarif

Use continue-on-error: true on the check step so the upload step always runs even when diagnostics are found.

Matrix over multiple PostgreSQL versions

Run verify against PostgreSQL 15, 16, and 17 to confirm your migration is compatible across versions:

jobs:
  supaschema:
    runs-on: ubuntu-latest
    strategy:
      matrix:
        postgres-version: [15, 16, 17]

    services:
      postgres:
        image: postgres:${{ matrix.postgres-version }}
        env:
          POSTGRES_PASSWORD: postgres
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready --health-interval 5s --health-timeout 5s --health-retries 10

    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0
      - uses: actions/setup-node@v4
        with:
          node-version: 22
      - run: npm install --no-save supaschema
      - name: Render migration
        run: |
          npx supaschema diff \
            --from "git:origin/${{ github.base_ref }}" \
            --to dir:supabase/schemas \
            --out /tmp/migration.sql
      - name: Check replay safety
        run: npx supaschema check /tmp/migration.sql
      - name: Verify (pg ${{ matrix.postgres-version }})
        run: |
          npx supaschema verify \
            --from "git:origin/${{ github.base_ref }}" \
            --to dir:supabase/schemas \
            --migration /tmp/migration.sql \
            --database-url postgresql://postgres:postgres@localhost:5432/postgres \
            --ensure-roles

Husky pre-commit hook

Catch issues locally before they ever reach CI. Add a pre-commit hook that runs check only on staged migration files:

# .husky/pre-commit
staged=$(git diff --cached --name-only --diff-filter=ACM -- 'supabase/migrations/*.sql')
[ -z "$staged" ] || npx supaschema check $staged

Install Husky and activate the hook:

npm install --save-dev husky
npx husky init

The hook is a no-op when no migration SQL files are staged, so it does not slow down commits that only touch application code.

​Full workflow

​Environment variables

​Exit codes

​Composite Action

​Drift gate (simpler workflow)

​Handling roles that don’t exist on bare PostgreSQL

​SARIF reporter for GitHub code scanning

​Matrix over multiple PostgreSQL versions

​Husky pre-commit hook

Full workflow

Environment variables

Exit codes

Composite Action

Drift gate (simpler workflow)

Handling roles that don’t exist on bare PostgreSQL

SARIF reporter for GitHub code scanning

Matrix over multiple PostgreSQL versions

Husky pre-commit hook