Migration Safety Checker#

Check database migrations for data loss risks, table-locking operations, backward compatibility issues, and deployment ordering problems. Works with raw SQL migrations, ORMs (Prisma, Drizzle, Django, Rails, Sequelize, TypeORM), and migration tools (Flyway, Liquibase, golang-migrate).

Usage#

text

"Check this migration for safety"
"Will this migration lock the users table?"
"Is this migration backward compatible?"
"Review the pending migrations before deployment"

How It Works#

1. Migration Discovery#

bash

find . -path "*/migrations/*.sql" -o -path "*/migrations/*.py" -o -path "*/migrate/*.sql" 2>/dev/null | head -20
ls prisma/migrations/*/migration.sql 2>/dev/null
find . -path "*/migrations/0*.py" 2>/dev/null | tail -10
ls db/migrate/*.rb 2>/dev/null | tail -10

2. Dangerous Operation Detection#

Data loss risks:

• DROP TABLE / DROP COLUMN — irreversible data deletion
• TRUNCATE TABLE — removes all rows
• Column type narrowing (VARCHAR(255) → VARCHAR(50))
• Removing NOT NULL without default (existing rows get NULL)
• Renaming columns (breaks existing queries)

Locking operations (on large tables):

• ALTER TABLE ... ADD COLUMN with DEFAULT (pre-Postgres 11, MySQL)
• ALTER TABLE ... ADD INDEX without CONCURRENTLY
• ALTER TABLE ... MODIFY COLUMN (MySQL full table rewrite)
• CREATE INDEX without CONCURRENTLY (Postgres)
• ALTER TABLE ... ADD CONSTRAINT (full table scan)

Backward compatibility:

• Adding NOT NULL column without default
• Renaming table/column
• Removing enum values
• Changing primary key
• Dropping index that queries depend on

3. Deployment Safety#

Rolling deployment compatibility:

• Can old code work with new schema? (backward compatible)
• Can new code work with old schema? (forward compatible)
• Does migration need multi-step deployment?

Safe pattern for risky changes:

text

Step 1: Add new column (nullable)
Step 2: Deploy code writing to both columns
Step 3: Backfill data
Step 4: Deploy code reading from new column
Step 5: Drop old column

4. Performance Impact#

• Estimate table size and lock duration
• Check if migration can run during traffic
• Recommend maintenance window if needed
• Suggest concurrent/online migration alternatives

5. ORM-Specific Checks#

• Prisma: prisma migrate diff for destructive changes
• Django: RunPython without reverse function
• Rails: Non-reversible migrations
• Sequelize: queryInterface.removeColumn without backup

Output#

text

## Migration Safety Report

**Migrations reviewed:** 3 pending
**Database:** PostgreSQL 15

### Migration 1: 20260430_add_preferences.sql
✅ SAFE — adds nullable column, no lock

### Migration 2: 20260430_add_email_unique.sql
🟠 CAUTION — adds unique constraint on 2M-row table
Lock time: 15-30s | Blocks writes during creation
Fix: CREATE UNIQUE INDEX CONCURRENTLY + ADD CONSTRAINT USING INDEX

### Migration 3: 20260430_remove_legacy_columns.sql
🔴 DANGEROUS — drops 3 columns with 500K non-null values
1. Verify no code references these columns
2. Backup data before dropping
3. Deploy separately from code changes

| Migration | Safety | Lock Risk | Backward Compatible |
|-----------|--------|-----------|-------------------|
| add_preferences | ✅ | None | Yes |
| add_email_unique | 🟠 | 15-30s | Yes |
| remove_legacy | 🔴 | Minimal | NO |