Database migrations are the only code changes that can lock tables, truncate data, and break production with a single missing keyword. Yet most teams review migrations the same way they review JavaScript: a quick glance at the diff and an approval. That works until someone drops a column that a background worker still references, or adds NOT NULL to a column with ten million NULL rows.
The fix isn't more manual review. It's automated schema diffing in your CI/CD pipeline — running on every pull request that touches a .sql file, posting a clear report of what changed, and optionally failing the build when a dangerous change slips through.
This post shows exactly how to set it up with SchemaLens, GitHub Actions, and GitLab CI.
By the end of this guide, your pipeline will:
SchemaLens includes a zero-dependency Node.js CLI at ci/schemalens-diff.js. It embeds the full parser and diff engine — no npm install required. This means it runs anywhere Node.js is available: GitHub Actions, GitLab CI, Docker containers, and local machines.
node ci/schemalens-diff.js \
schema_old.sql schema_new.sql \
--dialect postgres \
--format markdown \
--output diff-report.mdSupported flags:
--dialect: postgres, mysql, sqlite, or mssql--format: json or markdown--output: write to a file instead of stdoutExit codes:
0 — no differences found1 — differences found2 — parsing errorCreate .github/workflows/schema-diff.yml with the following workflow:
name: Schema Diff
on:
pull_request:
paths:
- '**/*.sql'
- 'db/migrations/**'
jobs:
diff:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
with:
fetch-depth: 0
- name: Get base branch schema
run: |
git show origin/${{ github.base_ref }}:schema.sql > schema_base.sql || true
- name: Run SchemaLens diff
run: |
node ci/schemalens-diff.js schema_base.sql schema.sql \
--dialect postgres --format markdown --output report.md
continue-on-error: true
- name: Comment PR with diff report
uses: actions/github-script@v7
with:
script: |
const fs = require('fs');
const report = fs.readFileSync('report.md', 'utf8');
github.rest.issues.createComment({
issue_number: context.issue.number,
owner: context.repo.owner,
repo: context.repo.repo,
body: '## Schema Diff Report\n\n' + report
});This workflow triggers only when .sql files change, checks out both branches, extracts the base schema, runs the diff, and posts the report as a PR comment.
To make the build fail when specific changes are detected, parse the JSON output and inspect the diff structure:
- name: Check for breaking changes
run: |
node ci/schemalens-diff.js schema_base.sql schema.sql \
--dialect postgres --format json --output diff.json
node -e "
const diff = require('./diff.json');
const breaking = diff.tablesRemoved.length > 0 ||
diff.tablesModified.some(t =>
t.columnsRemoved.length > 0 ||
t.constraintsRemoved.some(c => c.type === 'FOREIGN KEY')
);
if (breaking) {
console.error('Breaking schema changes detected');
process.exit(1);
}
"Customize the breaking-change heuristic to match your team's risk tolerance. Some teams treat any column drop as breaking; others only care about foreign key removals.
For GitLab, add this job to your .gitlab-ci.yml:
schema_diff:
image: node:20-alpine
stage: test
rules:
- if: $CI_PIPELINE_SOURCE == "merge_request_event"
changes:
- "**/*.sql"
script:
- |
git show origin/$CI_MERGE_REQUEST_TARGET_BRANCH_NAME:schema.sql \
> schema_base.sql || true
- |
node ci/schemalens-diff.js schema_base.sql schema.sql \
--dialect postgres --format markdown --output diff-report.md
artifacts:
paths:
- diff-report.md
expire_in: 1 weekThe report is attached to the pipeline as an artifact. Team members can download it from the merge request page or integrate it with GitLab's code quality report format.
If your project supports multiple databases (for example, PostgreSQL in production and SQLite for local testing), run the diff for each dialect:
strategy:
matrix:
dialect: [postgres, mysql, sqlite]
steps:
- name: Diff ${{ matrix.dialect }}
run: |
node ci/schemalens-diff.js schema_base.sql schema.sql \
--dialect ${{ matrix.dialect }} \
--format markdown \
--output report-${{ matrix.dialect }}.mdThis catches dialect-specific issues early — like using ALTER COLUMN ... TYPE in a file that gets run against MySQL in some environments.
The pipeline needs two schema files: the base (from the target branch) and the new (from the PR). There are three common patterns for this:
Commit a schema.sql file to the repo. The CI diffs the PR's version against the base branch's version. This is the simplest approach and guarantees the diff is always available.
Run your migration tool (Flyway, Liquibase, Prisma Migrate) against an empty database in CI to produce the schema, then diff the result. This is more accurate but slower.
Download the latest staging schema from a known URL or S3 bucket at the start of the pipeline. This diffs against the real expected production schema, not just the last committed version.
Recommendation: Start with Pattern 1. A committed schema.sql is fast, deterministic, and easy to review. Upgrade to Pattern 2 or 3 when you need higher fidelity.
A typical markdown report from the CLI includes:
CREATE TABLE with columns and constraintsALTER TABLE statements ready to runHere's an example of what gets posted to a pull request:
## Schema Diff Report
**Summary:** 1 table modified, 2 columns added, 1 constraint added
### Modified: users
| Change | Detail |
|--------|--------|
| Column added | `email_verified_at TIMESTAMP` |
| Column added | `login_count INT DEFAULT 0` |
| Constraint added | `UNIQUE (email)` |
### Migration SQL
```sql
ALTER TABLE users ADD COLUMN email_verified_at TIMESTAMP;
ALTER TABLE users ADD COLUMN login_count INT DEFAULT 0;
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
```Because the SchemaLens CLI is zero-dependency and runs entirely in your CI environment, your schema never leaves your infrastructure. There's no API key, no cloud service, no data upload. The diff happens inside your runner.
For teams in regulated industries (healthcare, finance), this is often a requirement. You get automated schema review without adding a new vendor to your security questionnaire.
Once schema diffing works in one repo, you can scale it:
schema.sql per service in a central repo. The registry diffs all services nightly and alerts on drift.The CLI is already in your repository if you're using SchemaLens. Copy the workflow files above, adjust the dialect and schema path, and open your next pull request. You'll have automated schema review in under ten minutes.
Download the SchemaLens CLI or open the browser app to validate your schemas before adding them to CI.
Open SchemaLensschema.sql file; upgrade to generated snapshots when needed.SchemaLens is a browser-based SQL schema diff tool with a zero-dependency CLI for CI/CD. No signup, no upload, no backend. Your schema never leaves your machine. Try it free →