# Migration Check in CI: GitHub Action and CLI

> Report what the SQL migrations in a pull request do to existing data, and the design findings they add, with no database. A free GitHub Action and CLI.

Source: https://schemity.com/doc/migration-check-ci/

**Schemity Migration Check** reports what the SQL migrations in a pull request do before they run: which statements lose data, which can fail on the rows already there, which rewrite or lock a table, which tables a change reaches through foreign keys, and which design findings the change adds, such as a foreign key with no index. It runs as a GitHub Action, [schemity/ci](https://github.com/schemity/ci), or as a `schemity` command line tool on any machine. It needs no database and no credentials: the schema a migration runs against is rebuilt from the older migration files in the repo, or from a schema dump you already commit. Nothing is executed and nothing is sent anywhere. It is free, including for commercial use, and needs no Schemity license.

It is the same engine as [Impact Analysis](https://schemity.com/doc/impact-analysis/) and [Schema Lint](https://schemity.com/doc/schema-lint/) in the desktop app, run without a connection. What it cannot know without a database is covered [below](#what-does-it-not-know-without-a-database).

## How do I add it to a GitHub repository?

Add a workflow file, such as `.github/workflows/migrations.yml`:

```yaml
name: Migrations
on: pull_request

permissions:
  contents: read
  pull-requests: read

jobs:
  check:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v6
      - uses: schemity/ci@v1
        with:
          dialect: postgres
          migrations: db/migrations
```

On each pull request the Action downloads the `schemity` binary from the repo's releases, checks it against `checksums.txt`, asks GitHub which migration files the pull request adds or changes, and checks only those, against the schema the older migrations build. Findings appear as notices on the changed lines and as a report in the job summary.

`dialect` is one of `postgres`, `mysql`, `mariadb`, `sqlserver`, `sqlite`, `snowflake` or `cockroachdb`. The job needs a Linux runner (x64 or ARM64) or an Apple silicon macOS runner; there is no Windows build.

## What does the report look like?

Take an `orders` table, and a migration that stores its total in cents, drops a note column and adds a foreign key:

```sql
-- db/migrations/0002_orders_cents.sql
ALTER TABLE orders ALTER COLUMN total TYPE integer USING (total * 100)::integer;
ALTER TABLE orders DROP COLUMN note;
ALTER TABLE orders ADD COLUMN referrer_id bigint;
ALTER TABLE orders ADD CONSTRAINT orders_referrer_id_fkey FOREIGN KEY (referrer_id) REFERENCES customers (id);
```

`schemity check` reports, measured with release v0.1.0:

```text
4 statements: 4 analysed, 0 not analysed, 0 bookkeeping
Schema before: 2 tables from 1 files

Loses data
  db/migrations/0002_orders_cents.sql:2  Drops orders.note

Can fail on existing data
  db/migrations/0002_orders_cents.sql:1  Changes the type of orders.total, fails if a value does not fit

Rewrites the table
  db/migrations/0002_orders_cents.sql:1  Rewrites orders to change total

Holds back other statements
  db/migrations/0002_orders_cents.sql:1  Changing the type of total holds back reads and writes to orders while it reads every row
  db/migrations/0002_orders_cents.sql:4  Checking the new constraint holds back writes to orders while it reads every row

Design findings this change introduces
  orders.referrer_id  Foreign key has no index on its columns.  [fk-no-covering-index]
      No index on orders starts with (referrer_id), so a join on them, and every delete or key update in customers, scans orders.
```

The impact groups are the same as in the desktop app's [Impact Analysis](https://schemity.com/doc/impact-analysis/#what-does-impact-analysis-report): loses data, can fail on existing data, storage engine, rewrites the table, holds back other statements, dependent objects, spreads through foreign keys, and changes data for `UPDATE`, `DELETE` and `INSERT`. Lint reports only what the change introduces, so a schema with old findings does not fail every pull request.

## Can it comment on the pull request?

Yes. Set `comment: true` and give the job `pull-requests: write`:

```yaml
permissions:
  contents: read
  pull-requests: write

jobs:
  check:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v6
      - uses: schemity/ci@v1
        with:
          dialect: postgres
          migrations: db/migrations
          comment: true
```

The report is posted as one comment, and each later push updates that comment instead of adding another, so reviewers always read the current state of the migration. It is posted when the job fails too. A pull request from a fork gets a read-only token, so there the comment is skipped with a warning and the report stays in the job summary.

## How do I make the job fail?

By default the job passes whatever the report says: the findings are facts to read, not verdicts. Set `fail-on` to a comma-separated list of what should fail it:

```yaml
        with:
          dialect: postgres
          migrations: db/migrations
          fail-on: loses-data,can-fail
```

| Value | Fails when the report holds |
|---|---|
| `any` | anything at all |
| `impact` | any finding about what a statement does |
| `lint` | any design finding the change introduces |
| `unread` | a statement that could not be read |
| `loses-data`, `can-fail`, `storage-engine`, `rewrites-table`, `holds-back`, `dependents`, `spreads`, `changes-data` | a finding of that kind |

## Which migration tools does it read?

Plain SQL files, read in name order: Prisma, Drizzle, Flyway, Liquibase's SQL format, golang-migrate, goose, dbmate, sqlx and Supabase. Only the up half of a goose or dbmate file is read, and down files (`.down.sql`, Diesel's `down.sql`, a Flyway `U` undo) are skipped, since they never run on the way forward. For a framework that writes migrations as code, print the SQL first and pass the result as `files`: `python manage.py sqlmigrate` for Django, `php artisan migrate --pretend` for Laravel. Both commands need a database connection of their own, such as a service container in the job, and since the older migrations are code too, start from a committed schema dump with `schema`.

A statement it cannot read, such as a PostgreSQL `DO` block, is listed under **Not analysed** with the statement's file and line, never passed over in silence. Set `fail-on: unread` to fail the job on one.

## What if the first migrations no longer match the database?

On an old database the early migrations often no longer describe what is there: a table was changed by hand, or the history was squashed. If the repo holds a schema dump (`pg_dump --schema-only`, Rails' `structure.sql`), start from it, and name only the migrations newer than the dump in `before`:

```yaml
        with:
          dialect: postgres
          migrations: db/migrations
          schema: db/structure.sql
          before: |
            db/migrations/2026
```

With `schema` set and no `before`, only the dump is read.

## What are all the inputs?

| Input | Default | |
|---|---|---|
| `dialect` | required | `postgres`, `mysql`, `mariadb`, `sqlserver`, `sqlite`, `snowflake`, `cockroachdb` |
| `migrations` | required | The folder that holds the SQL migration files |
| `command` | `check` | `analyze` (what the statements do), `lint` (design findings), `check` (both) |
| `schema` | | A schema dump to start from |
| `before` | the migrations folder | Older migrations to replay, one path per line |
| `files` | the pull request's files | The migration files to check, one per line |
| `fail-on` | | What fails the job |
| `comment` | `false` | `true` posts the report as a pull request comment |
| `version` | `latest` | The CLI release, such as `v0.1.0` |

## Can I run it without GitHub Actions?

Yes. The `schemity` binary on the [releases page](https://github.com/schemity/ci/releases) runs on Linux (x64 and ARM64) and Apple silicon macOS, so it fits GitLab CI, a pre-commit hook, or a terminal:

```bash
schemity check --dialect postgres --before db/migrations db/migrations/0042_orders.sql
schemity lint --dialect postgres --schema db/structure.sql
schemity rules --dialect postgres
```

`--format` takes `text` (the default), `json`, or `github` for workflow annotations. The exit code is 0 when the files were read, whatever they do, 1 when `--fail-on` matches, and 2 on an error. `lint` with a schema and no files reports every design finding in the whole schema, and `rules` lists the lint rules for one database.

## What does it not know without a database?

Row counts and table sizes. The desktop app reads both from a live connection, so it can say "drops `orders.note`: ~1.2M rows" and tell a 200-row table from a 40-million-row one when it reports a lock. Views are read from the `CREATE VIEW` statements in the migration history, so a type change that a PostgreSQL view blocks is reported as refused; a view someone created by hand outside the migrations is known only to a live connection, where the desktop app names it too. Migration Check knows the statements and the schema they run against, not the data. For the full price of a change, open the same migration file in Schemity against staging or production; see [Can I analyze a migration file I did not write in Schemity?](https://schemity.com/doc/impact-analysis/#can-i-analyze-a-migration-file-i-did-not-write-in-schemity)

## Is it free?

Yes. The Action's files are under the MIT License, and the `schemity` binary is free to use, including commercially and in CI, under its own binary license; its source is not public. The licences of the open source software inside it ship in each download as `THIRD_PARTY_LICENSES.txt`. Report problems at [schemity/feedback](https://github.com/schemity/feedback/issues).

## Next

Read what each finding means in [Impact Analysis](https://schemity.com/doc/impact-analysis/), or how a reviewer opens a migration from a pull request on the diagram in [Version Control with Git](https://schemity.com/doc/version-control-git/#how-do-i-review-a-schema-change-in-a-pull-request).
