The check command runs static analysis over one or more SQL migration files without touching a database. It enforces 20+ rules that together guarantee your migrations are safe to replay, free of lock hazards, and suitable for production deployment. Diagnostics are written to stderr so they compose cleanly with shell pipelines. check is fast enough to run on every commit in CI and as a pre-commit hook locally.
You can pass files, a glob, a directory, or - for stdin:
# Single file
npx supaschema check supabase/migrations/20240101000000_add_accounts.sql
# Glob (all migrations)
npx supaschema check supabase/migrations/*.sql
# From stdin
cat migration.sql | npx supaschema check -
When you pass a directory, supaschema scans for all *.sql files within it (non-recursively). Pass - as the sole argument to read from standard input.
Examples
# Check the latest migration
npx supaschema check supabase/migrations/20240101000000_add_accounts.sql
# Check all migrations
npx supaschema check supabase/migrations/*.sql
# Check from stdin
cat migration.sql | npx supaschema check -
# GitHub PR annotations reporter
npx supaschema check migration.sql --reporter github
# SARIF for code scanning
npx supaschema check migration.sql --reporter sarif
Flags
--reporter
text | github | sarif | json
Controls the diagnostic output format:
text — Human-readable output with file names, line numbers, rule codes, and messages. This is the default when no flag is passed.github — Emits workflow commands (::error file=...) that GitHub Actions renders as inline PR annotations on the changed files.sarif — Outputs a SARIF 2.1.0 JSON document to stdout. Upload to GitHub’s code scanning API with github/codeql-action/upload-sarif for persistent security alerts.json — Outputs a JSON array of diagnostic objects. Useful for programmatic consumption or building custom reporters.
Suppresses all output when no errors are found. Only prints diagnostics when the exit code would be non-zero. Useful in pre-commit hooks where you only want noise on failure.
Rules
Idempotency Guards
These rules ensure your DDL statements are safe to run more than once — critical for replay and rollback workflows.
| Rule | Description |
|---|
CREATE TABLE | Must include IF NOT EXISTS |
CREATE SCHEMA | Must include IF NOT EXISTS |
CREATE INDEX | Must include IF NOT EXISTS |
CREATE SEQUENCE | Must include IF NOT EXISTS |
CREATE EXTENSION | Must include IF NOT EXISTS |
CREATE TYPE / CREATE DOMAIN | Must use a catalog guard (check pg_type before creating) |
CREATE MATERIALIZED VIEW | Must include IF NOT EXISTS or a catalog guard |
ADD CONSTRAINT | Must use a catalog guard against pg_constraint |
Safe Replacement
These rules ensure objects are updated in place rather than requiring a drop-and-recreate cycle.
| Rule | Description |
|---|
CREATE VIEW | Must use OR REPLACE |
CREATE FUNCTION / CREATE PROCEDURE | Must use OR REPLACE |
CREATE TRIGGER | Must use OR REPLACE, or be preceded by DROP TRIGGER IF EXISTS |
CREATE POLICY | Must be preceded by DROP POLICY IF EXISTS |
Drop Safety
Unguarded DROP statements will fail on a first-run database where the object was never created, and CASCADE can silently wipe dependent objects.
| Rule | Description |
|---|
DROP statements | Must include IF EXISTS |
DROP ... CASCADE | Forbidden — cascading drops are not replay-safe and hide dependency errors |
DROP ... CASCADE is an unconditional error. supaschema will never approve it, regardless of hints or configuration. Explicitly enumerate the dependent objects you want to drop instead.
Lock Hazards
These operations acquire ACCESS EXCLUSIVE or table-level locks that block reads and writes. supaschema flags them so you can schedule them during a maintenance window or rewrite them using safer alternatives.
| Rule | Description |
|---|
ALTER COLUMN TYPE | May rewrite the entire table; acquires ACCESS EXCLUSIVE lock |
SET NOT NULL | Scans the full table to validate the constraint |
ADD COLUMN with volatile default | Rewrites the table (PostgreSQL < 11 behavior; flagged for safety) |
CREATE INDEX (without CONCURRENTLY) | Locks the table for the duration of the build |
CREATE INDEX CONCURRENTLY | Cannot run inside a transaction block |
REFRESH MATERIALIZED VIEW CONCURRENTLY | Cannot run inside a transaction block |
Use CREATE INDEX CONCURRENTLY to build indexes without blocking reads — just make sure the statement runs outside of a BEGIN ... COMMIT block. supaschema flags both patterns: non-concurrent index creation and concurrent index creation inside a transaction.
DML Review
Data-manipulation statements inside migrations require special care for replay safety.
| Rule | Description |
|---|
INSERT | Must include ON CONFLICT clause to handle re-runs gracefully |
UPDATE / DELETE | Flagged for explicit idempotency review — supaschema cannot prove these are safe automatically |
SET search_path | Forbidden in migrations; use fully-qualified object names instead |
SECURITY DEFINER functions | Must explicitly SET search_path to prevent search-path injection |
Exit Codes
| Code | Meaning |
|---|
0 | All checks passed — no errors found |
2 | One or more diagnostics with error severity were found |
check never exits with code 1 unless the command itself fails to parse arguments or read files. A clean exit always means 0 (pass) or 2 (rule violations found).
Reporters in Detail
Text (--reporter text)
The default human-readable format. Prints file names, line numbers, rule codes, and messages to stdout. When multiple files are checked, each file section is prefixed with its path.
GitHub Annotations (--reporter github)
Use this reporter inside a GitHub Actions job to surface violations as inline comments on the diff view of a pull request:
# .github/workflows/check.yml
- name: Check migrations
run: npx supaschema check supabase/migrations/*.sql --reporter github
::error commands automatically.
SARIF (--reporter sarif)
Use this reporter to upload results to GitHub’s code scanning dashboard, where they persist as security alerts:
- name: Check migrations (SARIF)
run: npx supaschema check supabase/migrations/*.sql --reporter sarif > results.sarif
- name: Upload SARIF
uses: github/codeql-action/upload-sarif@v3
with:
sarif_file: results.sarif
Pre-commit Hook
Catch violations before they ever reach CI by adding a Husky pre-commit hook:
# .husky/pre-commit
staged=$(git diff --cached --name-only --diff-filter=ACM -- 'supabase/migrations/*.sql')
[ -z "$staged" ] || npx supaschema check $staged