# safe-migrate v0.3.2
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.**
---
## What's New in v0.3.2
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 ... ROLLBACK` fully restores prior state, including generation counters and pending validations
- `DROP TABLE ... CASCADE` is 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
### ✅ Live Database Statistics Integration
The `sync` command reads from PostgreSQL's catalog:
- `pg_class.reltuples` — estimated row counts
- `pg_class.relpages` — page estimates for TOAST threshold crossing
- `pg_stat_user_tables.last_analyze` — staleness detection
- `pg_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.
---
## Installation
### From Binary (Recommended)
```bash
Supports:
- Linux (x86_64, ARM64, musl)
- macOS (Intel, Apple Silicon)
- Windows (x86_64, ARM64)
### From Cargo
```bash
cargo install safe-migrate
```
---
## Quick Start
### Step 1: Sync Database Statistics
```bash
export DATABASE_URL="postgres://user:password@localhost:5432/mydb"
safe-migrate sync
```
Creates `.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.
### Step 2: Lint Your Migration
```bash
safe-migrate lint --file migration.sql
```
Output:
```
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
---
## The Trust Model
### Confidence Levels
| **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)
### Version-Gating
safe-migrate detects your PostgreSQL version from the cache and applies version-specific rules:
**Example: Constant DEFAULT on ADD COLUMN**
```sql
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.
### Cache Staleness
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.
```
---
## Rules Reference
All 12 rules with examples:
### 1. **blocking-constraint** (Tier 1)
Adding a valid `CHECK` or `FOREIGN KEY` constraint scans the entire table with an `ACCESS EXCLUSIVE` lock.
```sql
ALTER TABLE orders ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id);
```
**Safe alternative:**
```sql
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;
```
### 2. **size-aware-add-column** (Tier 1)
Adding a column with a volatile `DEFAULT` requires a table rewrite, even on PG11+.
```sql
ALTER TABLE orders ADD COLUMN created_at TIMESTAMP DEFAULT NOW(); -- REWRITE
ALTER TABLE orders ADD COLUMN id UUID DEFAULT gen_random_uuid(); -- REWRITE
```
**Safe alternative (PG11+):**
```sql
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending'; -- METADATA ONLY
```
### 3. **type-change-rewrite** (Tier 1)
Changing a column type usually requires a full table rewrite with `ACCESS EXCLUSIVE` lock.
```sql
ALTER TABLE users ALTER COLUMN id TYPE BIGINT; -- REWRITE
```
**Safe alternatives:**
- Widen `varchar(10)` → `varchar(100)` (no rewrite)
- Widen `numeric(10,2)` → `numeric(20,2)` on PG12+ (no rewrite)
### 4. **concurrent-index** (Tier 2)
Synchronous index creation blocks writes. Use `CONCURRENTLY`.
```sql
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
DROP INDEX CONCURRENTLY idx_users_email;
```
### 5. **concurrent-in-transaction** (Tier 1)
PostgreSQL does not allow `CREATE/DROP INDEX CONCURRENTLY` inside an explicit transaction block.
```sql
BEGIN;
CREATE INDEX CONCURRENTLY idx ON users(id); -- ❌ ERROR
COMMIT;
```
### 6. **cascading-drop** (Tier 1)
`DROP TABLE ... CASCADE` silently destroys views, indexes, constraints without warning.
```sql
DROP TABLE users CASCADE; -- ❌ May drop dependent views
```
**Safe alternative:**
```sql
DROP VIEW dependent_view;
DROP TABLE users;
```
### 7. **blocking-mat-view-refresh** (Tier 2)
`REFRESH MATERIALIZED VIEW` (without `CONCURRENTLY`) blocks all reads during refresh.
```sql
REFRESH MATERIALIZED VIEW mv_order_totals;
```
**Safe alternative:**
```sql
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_totals;
```
### 8. **partition-lock** (Tier 1/2)
Partition operations that affect large parent tables.
### 9. **opaque-dynamic-sql** (Tier 2)
`DO` blocks and `EXECUTE` statements hide mutations. Analysis confidence degrades.
```sql
DO $$
BEGIN
```
**Recommendation:** Avoid dynamic DDL in migrations. Use explicit SQL.
### 10. **volatile-default** (Tier 3)
Using volatile functions like `random()` or `now()` as defaults can cause unexpected behavior in logical replication.
### 11. **vacuum-full** (Tier 1)
`VACUUM FULL` requires an `ACCESS EXCLUSIVE` lock and rewrites the entire table. Never in migrations.
### 12. **idempotency** (Tier 3, disabled by default)
Recommend `CREATE TABLE IF NOT EXISTS` and `CREATE INDEX IF NOT EXISTS` for safer re-runs.
---
## Configuration
Create `safe-migrate.toml` in your repo root to customize rule behavior and thresholds. All settings are optional — safe-migrate ships with sensible defaults.
### Global Settings
```toml
# 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 = 7
```
### Per-Rule Configuration
Override any rule's tier or thresholds:
```toml
[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
```
### Complete Example
```toml
# 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 Reference
| `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 |
### Version-Gating Examples
The engine reads `assume_pg_version` and applies version-specific rules:
**PG 11+: Constant defaults are safe**
```sql
-- 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**
```sql
-- With assume_pg_version < 110000, same SQL flags as Tier 1:
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';
```
### Cache Behavior
If `safe-migrate sync` hasn't been run or the cache is missing:
- Uses `assume_pg_version` for version-gated rules
- Uses `default_rows` for all unanalyzed tables
- Sets confidence to `Tainted` (since actual row counts are unknown)
If the cache exists and is fresh:
- Uses actual `pg_version_num` from PostgreSQL
- Uses actual table row counts from `pg_class.reltuples`
- Sets confidence to `Exact` (unless dynamic SQL is detected)
---
## CLI Reference
### `safe-migrate lint`
```bash
safe-migrate lint \
--file migration.sql \
--config safe-migrate.toml \
--cache .safe-migrate-stats.json \
--no-cache
```
| `-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) |
### `safe-migrate sync`
```bash
export DATABASE_URL="postgres://user:pass@localhost/db"
safe-migrate sync --out prod-stats.json
```
| `--out` | `.safe-migrate-stats.json` | Cache output path |
**Requires `DATABASE_URL` environment variable.**
---
## CI/CD Integration
### GitHub Actions
```yaml
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
done
```
### GitLab CI
```yaml
lint-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_requests
```
---
## Architecture
safe-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 ... CASCADE` against everything that actually depends on it
- Knowing a table was renamed earlier in the same file when checking a later `ALTER TABLE`
- Treating `BEGIN ... ROLLBACK` as 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.
---
## Why This Matters
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.
---
## License
Dual-licensed under [MIT](LICENSE-MIT) or [Apache 2.0](LICENSE-APACHE).
---
## Changelog
See [CHANGELOG.md](CHANGELOG.md) for release history.