A CI job can check a database migration without a database: rebuild the schema from the older migrations in the repo, then read each new statement against it. That is enough to say which statements drop data, which can fail on rows already in the table, which rewrite or lock it, and which add a design problem such as a foreign key with no index. It is not enough to say how many rows, which is where a live connection still earns its place.

Here is the kind of pull request this is for. A customer’s name moves to a new column, the email gets a length limit, and an order records the customer who referred it. In the older migration, 0001_init.sql, customers has id bigint, email text NOT NULL UNIQUE and full_name text NOT NULL. The new one is four lines, all of them ordinary:

-- shop/migrations/0002_customer_name.sql
ALTER TABLE customers DROP COLUMN full_name;
ALTER TABLE customers ADD COLUMN display_name text NOT NULL DEFAULT '';
ALTER TABLE customers ALTER COLUMN email TYPE varchar(120);
ALTER TABLE orders ADD COLUMN referrer_id bigint REFERENCES customers (id);

The first two lines throw away every customer’s name, because the new column is added empty rather than renamed. The third rewrites the whole table. The fourth adds a foreign key that every delete from customers will now have to check by scanning orders. The drop is visible in the diff; that it is a rename, that line 3 rewrites, and that the new key has no index are not, because the diff does not know what customers looked like before.

What does a pull request review of a migration miss?

It misses everything that depends on the schema the migration runs against, because the diff shows only the new lines.

A reviewer sees ALTER TABLE customers ALTER COLUMN email TYPE varchar(120) and has to remember what email was. I ran both cases on PostgreSQL 18.3 and watched the table’s file on disk (pg_relation_filenode):

email before the migrationWhat TYPE varchar(120) does on PostgreSQL 18.3
textRewrites the table into a new file, holding an ACCESS EXCLUSIVE lock, and fails with value too long for type character varying(120) if any email is longer
varchar(100)Changes the catalogue only: same file, no rewrite, no row can fail, and the same ACCESS EXCLUSIVE lock is held only for a moment

Same statement, opposite answers. The foreign key line has the same problem: whether referrer_id needs an index depends on whether an older migration already created one, and that file is not in the pull request. A migration linter that reads only the statements in front of it is in the reviewer’s position. It can say that a type change might rewrite, but not that this one does.

How do I check database migrations in CI without a database?

Replay the migrations that are already merged into a schema in memory, then analyse only the files the pull request adds or changes against it.

That is what Schemity Migration Check does. Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. Migration Check is the same impact analysis and lint engine, run in CI as a free GitHub Action, schemity/ci, with no connection and no credentials; every input is in the Migration Check doc. The workflow:

name: Migrations
on: pull_request

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: shop/migrations
          fail-on: loses-data,can-fail
          comment: true

On the pull request above, the report it produced with release v0.1.3:

4 statements: 4 analysed, 0 not analysed, 0 bookkeeping
Schema before: 3 tables from 1 file

Loses data
  shop/migrations/0002_customer_name.sql:1  Drops customers.full_name

Can fail on existing data
  shop/migrations/0002_customer_name.sql:3  Changes the type of customers.email, fails if a value does not fit

Rewrites the table
  shop/migrations/0002_customer_name.sql:3  Rewrites customers to change email

Holds back other statements
  shop/migrations/0002_customer_name.sql:3  Changing the type of email holds back reads and writes to customers 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 Schemity migration check comment on a GitHub pull request, listing Drops customers.full_name under Loses data, the email type change under Can fail on existing data, Rewrites the table and Holds back other statements, and the fk-no-covering-index finding on orders.referrer_id, ending with what to check against a live database

With comment: true the same report lands on the pull request as one comment, updated on every later push. Its last line asks what only the database can answer for these findings: how many rows lose data, how many would make it fail, and how big the rewritten table is.

Line 2 is not in the report, and that is correct: since PostgreSQL 11 a NOT NULL column with a constant default is added without a rewrite, and the table’s file did not change when I ran it. Checked on its own against a history where email was varchar(100), line 3 produced Nothing to report. The check knew the difference because it had read 0001_init.sql.

With fail-on: loses-data,can-fail the job fails on this pull request, and each finding appears as a notice under the line that causes it in Files changed, the missing index under line 4. Without fail-on it passes and only reports, which is the default: the findings are facts for a reviewer to weigh, and dropping a column is sometimes exactly the plan. Lint reports only what the change introduces, so a schema with years of old findings does not fail every pull request.

GitHub's Files changed view of shop/migrations/0002_customer_name.sql, with a Loses data notice under line 1 and Holds back other statements, Rewrites the table and Can fail on existing data notices under line 3, and an fk-no-covering-index notice for orders.referrer_id under line 4, each from the Shop migrations check

Does MySQL create a foreign key from an inline REFERENCES?

Not on MySQL 8.4: it accepts the clause, creates no key, and says nothing.

I found this while building the check. On MySQL 8.4 this statement runs cleanly:

ALTER TABLE posts ADD COLUMN author_id BIGINT REFERENCES users (id);

SHOW WARNINGS is empty, information_schema.REFERENTIAL_CONSTRAINTS lists no key, and INSERT INTO posts (title, author_id) VALUES ('orphan', 999) succeeds with no user 999. The MySQL 9.0.0 release notes confirm it from the other side: “MySQL now accepts and enforces inline foreign key specifications (these were previously accepted by the parser, but ignored).” So the same migration file builds a key on 9.x and none on 8.4, and the orphan rows that pile up on 8.4 make the real constraint fail later: adding it with FOREIGN KEY (author_id) REFERENCES users (id) stopped with error 1452 on the one orphan row.

With dialect: mysql, Migration Check reports it under Storage engine: Declares the foreign key on posts.author_id inline, which MySQL 8.4 accepts without creating a key; 9.7 creates it, and a FOREIGN KEY constraint works on both. The finding names 9.7 because that is the newest version Schemity measured; the release notes put the change at 9.0. With dialect: mariadb it reports nothing, because MariaDB creates the key.

What can a CI check not know without a database?

It cannot know row counts or table sizes, which only the database holds, or a view someone created by hand outside the migrations.

Migration Check in CISchemity desktop, connected
Schema it readsRebuilt from older migrations or a schema dumpThe live database
Drops, failing type changes, rewrites, locksYesYes
How many rows a statement touchesNoEstimated, or counted exactly
A 200-row table vs a 40-million-row oneNoYes
The view that blocks a type changeYes, from the CREATE VIEW in the historyYes, including views made outside the migrations
Where it runsEvery pull request, no credentialsOn a reviewer’s machine
PriceFreePaid desktop app

The two are meant to be used together. CI reads every pull request and flags the statements worth a second look; for the ones that matter, a reviewer opens the same migration file in Schemity against staging or production and sees the change drawn on the diagram with the row counts attached, as described in Impact Analysis. That is how a team gets to see the impact of every change before it runs, and the first pass no longer needs anyone to remember what email used to be.

Which migration tools does it work with?

Any tool that leaves plain SQL files in a folder: Prisma, Drizzle, Flyway, golang-migrate, goose, dbmate, sqlx, Supabase and Liquibase’s SQL format.

Only the up half of a goose or dbmate file is read, and down files (.down.sql, a Flyway undo) are skipped, since they never run on the way forward. Frameworks that write migrations as code, such as Django and Laravel, need their SQL printed first (sqlmigrate, migrate --pretend), and printing it needs a database of its own. If the early migrations no longer match production, start from a committed schema dump with schema instead. A statement the check cannot read, such as a PostgreSQL DO block, is listed under Not analysed with its file and line rather than skipped in silence. The same engine runs outside GitHub as the schemity command, on Linux and Apple silicon macOS, so it fits GitLab CI or a pre-commit hook too.

Lines 1 and 2 of the migration are a rename written as a drop and an add, which is what ORMs and AI agents produce when nobody answers the rename question, and the problem behind whether AI agents should write database migrations. Migration Check covers what a statement does to the data; for the design defects that sit across two tables, see schema linting vs migration linting.

Frequently asked questions

Do I need a database in CI to check a migration?

Not to know what each statement does. A check that replays the older migrations into a schema in memory knows every column's type, every index and every foreign key the new statements run against, which is enough to report dropped columns, type changes that can fail, rewrites and locks. A database is needed for how many rows a statement touches and how long it takes.

Does MySQL create a foreign key from an inline REFERENCES clause?

Not on MySQL 8.4. ALTER TABLE posts ADD COLUMN author_id BIGINT REFERENCES users (id) succeeds with no warning and creates no key, so orphan rows are accepted. MySQL 9.0 and later enforce it, and a separate FOREIGN KEY constraint works on every version.

Can the check comment on the pull request?

Yes. With comment: true and pull-requests: write, Schemity Migration Check posts the report as one comment and updates that same comment on every later push, including when the job fails. On a pull request from a fork the token is read-only, so the report stays in the job summary.