A PostgreSQL migration linter that executes a bi-directional state machine simulation over your SQL, combining static typed AST analysis with live database statistics to prevent blocking locks before they reach production.
The Problem: ALTER TABLE users ADD COLUMN status TEXT is safe on 500 rows. On 50M rows, it acquires an ACCESS EXCLUSIVE lock that takes down your app. Standard linters only look at the SQL. safe-migrate looks at the SQL AND the size of the tables it affects.
v0.3.2 is a complete internal rewrite. Earlier versions parsed migrations with regex and substring matching, which broke on quoted identifiers, schemas, and anything non-trivial. safe-migrate now walks a typed PostgreSQL AST and runs a full state machine simulation of the migration — including transaction rollbacks, cascading drops, and partition hierarchies — before evaluating any rule.
Highlights:
- Typed AST parsing via
squawk_syntax(no more string matching) - Transactions are simulated correctly:
BEGIN ... ROLLBACKfully restores prior state, including generation counters and pending validations DROP TABLE ... CASCADEis evaluated against the actual dependency graph (foreign keys, views, partitions), not just the table named in the statement- Table renames are tracked so foreign key and index references stay correct
- Search path resolution checks that a schema actually exists before using it
- 79 passing tests covering the simulator, rule engine, and CLI
The sync command reads from PostgreSQL's catalog:
pg_class.reltuples— estimated row countspg_class.relpages— page estimates for TOAST threshold crossingpg_stat_user_tables.last_analyze— staleness detectionpg_attribute.avg_width— column width for compression decisions- Foreign key graph, index mappings, partition hierarchies
No application credentials needed — sync only requires SELECT on catalog tables.
curl -fsSL https://raw.githubusercontent.com/dsecurity49/safe-migrate/main/install.sh | bashSupports:
- Linux (x86_64, ARM64, musl)
- macOS (Intel, Apple Silicon)
- Windows (x86_64, ARM64)
cargo install safe-migrateexport DATABASE_URL="postgres://user:password@localhost:5432/mydb"
safe-migrate syncCreates .safe-migrate-stats.json with table sizes, column info, constraints, and indexes. Safe to commit to source control — contains no secrets, only statistics.
Cache freshness: Warnings if older than 7 days (configurable). Stale stats are flagged in the report.
safe-migrate lint --file migration.sqlOutput:
Analyzing migration: migration.sql
--------------------------------------------------------------------------------
[FAIL] [TIER 1 - DANGER ] Synchronous FOREIGN KEY constraint addition locks
public.orders and public.auth_users
Rule: blocking-constraint
Recipe: Adding a valid CHECK or FOREIGN KEY constraint
takes an ACCESS EXCLUSIVE lock. Add it as NOT VALID
first, then VALIDATE it in a separate transaction.
--------------------------------------------------------------------------------
[WARN] [TIER 2 - WARNING] Synchronous index creation on public.orders
Rule: require-concurrent-index
Recipe: Index operations block writes. Add the CONCURRENTLY
keyword.
--------------------------------------------------------------------------------
==================================================
Analysis Complete.
Analysis Confidence : Exact
Tier 1 (Halt Build) : 1
Tier 2 (Warning) : 1
Tier 3 (Info) : 0
==================================================
Error: Migration halted: Tier 1 lock detected.
Exit code: 1 (Tier 1 violation) → CI build fails
| Level | Meaning | When It Happens |
|---|---|---|
| Exact | Analysis is mathematically sound | Pure DDL, no opaque SQL |
| Tainted | Some DDL is hidden in opaque statements | DO blocks, EXECUTE statements, dynamic SQL |
When confidence is Tainted, the engine:
- Still evaluates all visible DDL
- Warns that hidden mutations may exist
- Does not suppress violations (conservative)
safe-migrate detects your PostgreSQL version from the cache and applies version-specific rules:
Example: Constant DEFAULT on ADD COLUMN
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';- PG 11+: Metadata-only, no rewrite → ✅ Safe (Tier 3)
- PG <11: Table rewrite →
⚠️ Warning (Tier 2 for small tables, Tier 1 for large)
The rule reads pg_version_num from the cache and applies the correct threshold.
Tables without recent ANALYZE:
- Flagged as
[WARNING: Based on stale statistics] - Treated conservatively (assume Tier 2+ severity)
- Still evaluated (not suppressed)
Example:
[WARN] [TIER 2 - WARNING] Table statistics are stale. Lock evaluations may be
inaccurate.
Rule: blocking-constraint
Recipe: Run ANALYZE to ensure accurate row estimates.
All 12 rules with examples:
Adding a valid CHECK or FOREIGN KEY constraint scans the entire table with an ACCESS EXCLUSIVE lock.
ALTER TABLE orders ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id);Safe alternative:
ALTER TABLE orders ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
-- Later, in a separate migration:
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;Adding a column with a volatile DEFAULT requires a table rewrite, even on PG11+.
ALTER TABLE orders ADD COLUMN created_at TIMESTAMP DEFAULT NOW(); -- REWRITE
ALTER TABLE orders ADD COLUMN id UUID DEFAULT gen_random_uuid(); -- REWRITESafe alternative (PG11+):
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending'; -- METADATA ONLYChanging a column type usually requires a full table rewrite with ACCESS EXCLUSIVE lock.
ALTER TABLE users ALTER COLUMN id TYPE BIGINT; -- REWRITESafe alternatives:
- Widen
varchar(10)→varchar(100)(no rewrite) - Widen
numeric(10,2)→numeric(20,2)on PG12+ (no rewrite)
Synchronous index creation blocks writes. Use CONCURRENTLY.
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
DROP INDEX CONCURRENTLY idx_users_email;PostgreSQL does not allow CREATE/DROP INDEX CONCURRENTLY inside an explicit transaction block.
BEGIN;
CREATE INDEX CONCURRENTLY idx ON users(id); -- ❌ ERROR
COMMIT;DROP TABLE ... CASCADE silently destroys views, indexes, constraints without warning.
DROP TABLE users CASCADE; -- ❌ May drop dependent viewsSafe alternative:
DROP VIEW dependent_view;
DROP TABLE users;REFRESH MATERIALIZED VIEW (without CONCURRENTLY) blocks all reads during refresh.
REFRESH MATERIALIZED VIEW mv_order_totals;Safe alternative:
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_totals;Partition operations that affect large parent tables.
DO blocks and EXECUTE statements hide mutations. Analysis confidence degrades.
DO $$
BEGIN
EXECUTE 'ALTER TABLE ' || table_name || ' ADD COLUMN id int';
END $$;Recommendation: Avoid dynamic DDL in migrations. Use explicit SQL.
Using volatile functions like random() or now() as defaults can cause unexpected behavior in logical replication.
VACUUM FULL requires an ACCESS EXCLUSIVE lock and rewrites the entire table. Never in migrations.
Recommend CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS for safer re-runs.
Create safe-migrate.toml in your repo root to customize rule behavior and thresholds. All settings are optional — safe-migrate ships with sensible defaults.
# Row count threshold for Tier 1 (default: 100,000)
# Tables with >= this many rows trigger Tier 1 for dangerous operations
tier1_threshold_rows = 100000
# Row count threshold for Tier 2 (default: 10,000)
# Tables with >= this many rows trigger Tier 2 for dangerous operations
tier2_threshold_rows = 10000
# PostgreSQL version to assume when database is offline (default: 100000)
# Format: XXYYZZ (e.g., 100000 = PG 10.0, 110000 = PG 11.0, 170010 = PG 17.0.10)
# Used for version-gated rules like constant DEFAULT on ADD COLUMN (safe on PG11+)
assume_pg_version = 100000
# TOAST column width threshold in bytes (default: 2048)
# Columns wider than this are flagged for TOAST overflow risk
toast_width_threshold_bytes = 2048
# Default row count for unanalyzed tables (default: 10,000)
# Tables with unknown size are treated as having this many rows
default_rows = 10000
# Cache freshness threshold in days (default: 7)
# Warns if .safe-migrate-stats.json is older than this
stale_stats_days = 7Override any rule's tier or thresholds:
[rules.blocking-constraint]
# Stricter thresholds for foreign key constraints specifically
tier1_threshold_rows = 5000
tier2_threshold_rows = 1000
[rules.size-aware-add-column]
# Escalate all table rewrites to Tier 1 regardless of size
tier1_threshold_rows = 0
[rules.missing-idempotency]
# Disable the idempotency rule (don't warn about missing IF NOT EXISTS)
disabled = true# Global defaults for the whole team
tier1_threshold_rows = 100000
tier2_threshold_rows = 10000
assume_pg_version = 170000 # Assume PG 17 for new staging envs
toast_width_threshold_bytes = 2048
default_rows = 10000
stale_stats_days = 7
# Stricter rules for high-traffic tables
[rules.blocking-constraint]
tier1_threshold_rows = 1000 # Flag FKs on tables >1K rows
tier2_threshold_rows = 100
[rules.concurrent-index]
tier1_threshold_rows = 50000 # Flag non-concurrent indexes on tables >50K rows
# Relax some rules for safer operations
[rules.blocking-mat-view-refresh]
tier1_threshold_rows = 500000 # Only flag materialized view refresh on huge tables
# Disable rules that don't apply to your workflow
[rules.vacuum-full]
disabled = true| Rule ID | What It Does | Default Tier |
|---|---|---|
destructive-cascade |
Flags DROP TABLE ... CASCADE operations that affect baseline schema | Tier 1 |
size-aware-add-column |
Flags table rewrites for ADD COLUMN with volatile defaults or PG<11 constant defaults | Tier 1 |
type-change-rewrite |
Flags type changes that force ACCESS EXCLUSIVE table rewrites | Tier 1 |
blocking-constraint |
Flags synchronous CHECK or FOREIGN KEY constraint additions | Tier 1 |
blocking-index-constraint |
Flags synchronous PRIMARY KEY or UNIQUE constraint additions via index | Tier 1 |
require-concurrent-index |
Flags synchronous index creation | Tier 2 |
require-concurrent-drop-index |
Flags synchronous index dropping | Tier 2 |
blocking-mat-view-refresh |
Flags synchronous REFRESH MATERIALIZED VIEW (without CONCURRENTLY) | Tier 2 |
partition-lock |
Flags partition attach/detach operations on large tables | Tier 1/2 |
concurrent-in-transaction |
Blocks CONCURRENTLY index operations inside explicit transaction blocks | Tier 1 |
vacuum-full |
Flags VACUUM FULL usage (requires ACCESS EXCLUSIVE lock) | Tier 1 |
opaque-dynamic-sql |
Detects dynamic SQL (DO blocks, EXECUTE) that hides mutations | Tier 2 |
volatile-default |
Notes volatile functions like NOW() or gen_random_uuid() in defaults |
Tier 3 |
missing-idempotency |
Recommends IF NOT EXISTS on CREATE statements (disabled by default) | Tier 3 |
table-rewrite-storage |
Flags table rewrites caused by column storage parameter changes | Tier 1 |
table-rewrite-access-method |
Flags table rewrites caused by access method changes | Tier 1 |
The engine reads assume_pg_version and applies version-specific rules:
PG 11+: Constant defaults are safe
-- With assume_pg_version >= 110000, this is metadata-only (safe):
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';PG <11: Constant defaults require rewrite
-- With assume_pg_version < 110000, same SQL flags as Tier 1:
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';If safe-migrate sync hasn't been run or the cache is missing:
- Uses
assume_pg_versionfor version-gated rules - Uses
default_rowsfor all unanalyzed tables - Sets confidence to
Tainted(since actual row counts are unknown)
If the cache exists and is fresh:
- Uses actual
pg_version_numfrom PostgreSQL - Uses actual table row counts from
pg_class.reltuples - Sets confidence to
Exact(unless dynamic SQL is detected)
safe-migrate lint \
--file migration.sql \
--config safe-migrate.toml \
--cache .safe-migrate-stats.json \
--no-cache| Flag | Default | Description |
|---|---|---|
-f, --file |
required | SQL migration file |
--config |
safe-migrate.toml |
Config overrides |
--cache |
.safe-migrate-stats.json |
Stats cache |
--no-cache |
false | Use worst-case assumptions (offline mode) |
export DATABASE_URL="postgres://user:pass@localhost/db"
safe-migrate sync --out prod-stats.json| Flag | Default | Description |
|---|---|---|
--out |
.safe-migrate-stats.json |
Cache output path |
Requires DATABASE_URL environment variable.
name: Safe Migrate
on:
pull_request:
branches: [main]
jobs:
lint-migrations:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
with:
fetch-depth: 0
- name: Install safe-migrate
run: |
curl -fsSL https://raw.githubusercontent.com/dsecurity49/safe-migrate/main/install.sh | bash
- name: Sync database stats
env:
DATABASE_URL: ${{ secrets.DATABASE_URL }}
run: safe-migrate sync --out prod-cache.json
- name: Lint changed migrations
run: |
FILES=$(git diff --name-only origin/main...HEAD -- '*.sql')
if [ -z "$FILES" ]; then
echo "No migrations changed."
exit 0
fi
for f in $FILES; do
echo "Linting $f..."
safe-migrate lint --file "$f" --cache prod-cache.json
donelint-migrations:
image: ubuntu:latest
script:
- curl -fsSL https://raw.githubusercontent.com/dsecurity49/safe-migrate/main/install.sh | bash
- safe-migrate sync --out prod-cache.json
- |
git diff --name-only origin/main...HEAD -- '*.sql' | while read f; do
safe-migrate lint --file "$f" --cache prod-cache.json
done
only:
- merge_requestssafe-migrate parses your migration into a typed AST, then simulates it statement-by-statement against an in-memory model of your schema (tables, columns, indexes, foreign keys, views, partitions). That model starts from your synced database statistics and is updated as each statement is applied — so by the time a rule runs, it's checking against the schema as it would actually look at that point in the migration, not just the raw SQL text.
This is what allows things like:
- Correctly evaluating a
DROP TABLE ... CASCADEagainst everything that actually depends on it - Knowing a table was renamed earlier in the same file when checking a later
ALTER TABLE - Treating
BEGIN ... ROLLBACKas a no-op on the schema, rather than analyzing the in-transaction state as if it persisted
DML statements (INSERT, UPDATE, DELETE, SELECT) are ignored. Dynamic SQL (DO blocks, EXECUTE) is detected and flagged, since it can hide schema changes the simulator can't see.
PostgreSQL lock behavior is invisible in the SQL itself. The same ALTER TABLE statement is a no-op on one table and an outage on another, depending entirely on size, version, and what else depends on it. safe-migrate makes that visible before you deploy, not after.
Dual-licensed under MIT or Apache 2.0.
See CHANGELOG.md for release history.