migration-safety

Migration Safety

CRITICAL: Migrations can destroy production data. This skill prevents catastrophic data loss.

The Problem This Solves

Real disaster scenarios:

• Migration fails halfway, rollback deletes all records
• Re-running migration truncates existing data
• Column drop removes 50,000 user records
• Foreign key constraint fails, cascade deletes everything
• "Fresh migration" on wrong database wipes production

Golden Rules

1. NEVER run migrations without checking for existing data first
2. ALWAYS create a backup before ANY migration
3. NEVER re-run a migration that previously partially completed without investigation
4. ALWAYS verify you're on the correct database/environment
5. NEVER trust migrate:fresh or migrate:reset on any database with data

Pre-Migration Checklist

Step 1: Environment Verification

# Check current environment
echo $NODE_ENV
echo $RAILS_ENV
echo $APP_ENV

# Check database connection
# Laravel
php artisan db:show

# Rails
rails db:version

# Node.js (check connection string)
echo $DATABASE_URL

RED FLAGS - STOP IMMEDIATELY:

• Environment shows "production", "prod", "live"
• Database URL contains production hostname
• You're not 100% certain which database you're connected to

Step 2: Check Existing Data

-- Before ANY migration, check what exists
SELECT table_name,
       (SELECT COUNT(*) FROM information_schema.columns
        WHERE table_name = t.table_name) as columns,
       (SELECT reltuples::bigint FROM pg_class
        WHERE relname = t.table_name) as estimated_rows
FROM information_schema.tables t
WHERE table_schema = 'public'
ORDER BY table_name;

Present this to user:

## Current Database State

**Database:** [name]
**Environment:** [env]
**Tables:** [count]
**Total estimated rows:** [count]

| Table | Columns | Estimated Rows |
|-------|---------|----------------|
| users | 15 | 12,450 |
| orders | 23 | 89,234 |
| ... | ... | ... |

⚠️ This migration will affect a database with EXISTING DATA.

Step 3: Analyze Migration Impact

Before running, analyze each pending migration:

## Migration Analysis

### Migration: 2024_01_15_create_orders_table
**Type:** CREATE TABLE
**Risk Level:** LOW
**Existing Data Impact:** None (new table)

### Migration: 2024_01_16_drop_legacy_users
**Type:** DROP TABLE
**Risk Level:** CRITICAL ⛔
**Existing Data Impact:** Will DELETE 1,247 records permanently
**Recommendation:** BACKUP REQUIRED before proceeding

### Migration: 2024_01_17_add_email_to_users
**Type:** ADD COLUMN
**Risk Level:** LOW
**Existing Data Impact:** None (nullable column)

Step 4: Create Backup

MANDATORY before any migration that:

• Drops tables
• Drops columns
• Modifies column types
• Adds NOT NULL constraints to existing columns
• Runs on a database with >100 rows

# PostgreSQL
pg_dump -Fc database_name > backup_$(date +%Y%m%d_%H%M%S).dump

# MySQL
mysqldump -u user -p database_name > backup_$(date +%Y%m%d_%H%M%S).sql

# SQLite
cp database.sqlite database.sqlite.backup.$(date +%Y%m%d_%H%M%S)

# Verify backup
ls -la backup_*.{dump,sql} 2>/dev/null || ls -la *.backup.* 2>/dev/null

Migration Execution Protocol

For Safe Migrations (CREATE, ADD COLUMN nullable)

# Run with verbose output
php artisan migrate --pretend  # Laravel: see SQL first
rails db:migrate:status        # Rails: check status
npx prisma migrate deploy      # Prisma: production deploy

For Risky Migrations (DROP, MODIFY, CONSTRAINTS)

⚠️ **DESTRUCTIVE MIGRATION WARNING**

**Migration:** 2024_01_16_drop_legacy_users
**Action:** DROP TABLE `legacy_users`
**Records that will be PERMANENTLY DELETED:** 1,247

**Backup created:** backup_20240116_143022.dump
**Backup verified:** ✓ (15.2 MB)

**Recovery command if needed:**
```bash
pg_restore -d database_name backup_20240116_143022.dump

Do you want to proceed? Type 'yes, delete 1247 records' to confirm.

### For Fresh/Reset Migrations

**NEVER suggest `migrate:fresh` or `migrate:reset` without this warning:**

```markdown
⛔ **CRITICAL WARNING: COMPLETE DATA DESTRUCTION**

You requested: `migrate:fresh` / `migrate:reset`

This will:
1. DROP ALL TABLES in the database
2. DELETE ALL DATA (estimated 156,789 records)
3. Re-run all migrations from scratch

**This is IRREVERSIBLE without a backup.**

Current database: [name]
Environment: [env]
Tables to be dropped: [count]
Records to be deleted: [count]

**Is this a development database with test data you can recreate?**

If YES and you're certain: Type 'yes, destroy all data in [database_name]'
If NO or UNSURE: Type 'cancel' and I'll help you with a safer approach.

Handling Failed Migrations

Scenario: Migration Failed Halfway

WRONG approach:

# DO NOT just re-run!
php artisan migrate

RIGHT approach:

## Migration Failure Analysis

**Failed migration:** 2024_01_17_modify_orders
**Error:** Column 'status' cannot be dropped because of foreign key constraint
**State:** PARTIALLY APPLIED

**Current situation:**
- Migration started but did not complete
- Database may be in inconsistent state
- Re-running may cause data loss

**Safe recovery steps:**

1. **Check current state:**
   ```sql
   SELECT * FROM migrations ORDER BY id DESC LIMIT 5;

2. Check what was applied:

   -- Verify table structure
   \d orders
   

3. Options: A) Fix the migration and run manually B) Rollback to known good state (if possible) C) Restore from backup (safest)

Recommendation: Option C - Restore from backup

Would you like me to proceed with restoring from the backup at backup_20240117_091534.dump?

### Scenario: Need to Re-run Migration

**NEVER re-run without investigation:**

```markdown
## Re-run Migration Request

You want to re-run: `2024_01_15_seed_products`

**Analysis:**
- This migration inserts seed data
- Table `products` currently has 5,234 records
- Re-running may:
  - Duplicate records (if no unique constraints)
  - Fail on conflicts (if unique constraints exist)
  - Delete existing records (if migration truncates first)

**Migration file check:**
```php
// Found in migration:
DB::table('products')->truncate();  // ⛔ DANGEROUS - Will delete 5,234 records!

Safe alternatives:

1. Create a new migration that only adds missing records
2. Run specific seed commands instead
3. Manually insert only the records you need

Do NOT re-run this migration unless you want to delete 5,234 records.

## Framework-Specific Safety

### Laravel

```php
// Safe migration patterns

// GOOD: Check before truncate
public function up()
{
    if (Schema::hasTable('products') && DB::table('products')->count() > 0) {
        throw new Exception('Cannot run: products table has existing data');
    }
    // ... migration code
}

// GOOD: Soft delete instead of drop
public function up()
{
    Schema::rename('legacy_users', 'legacy_users_archived_' . date('Ymd'));
}

// GOOD: Backup before destructive operation
public function up()
{
    // Export existing data first
    $users = DB::table('legacy_users')->get();
    Storage::put('backups/legacy_users_' . date('Ymd') . '.json', $users->toJson());

    Schema::dropIfExists('legacy_users');
}

Rails

# Safe migration patterns

# GOOD: Reversible migrations with safety checks
class DropLegacyUsers < ActiveRecord::Migration[7.0]
  def up
    count = LegacyUser.count
    if count > 0
      raise "Cannot drop legacy_users: #{count} records exist. Backup first!"
    end
    drop_table :legacy_users
  end

  def down
    create_table :legacy_users do |t|
      # ... columns
    end
  end
end

# GOOD: Use safety_assured for intentional destructive changes
class RemoveEmailFromUsers < ActiveRecord::Migration[7.0]
  def change
    safety_assured { remove_column :users, :deprecated_email }
  end
end

Prisma

// Safe migration patterns

// Check before migrate
async function safeMigrate() {
  const count = await prisma.user.count();

  if (count > 0) {
    console.error(`Database has ${count} users. Create backup first!`);
    process.exit(1);
  }

  // Run migration
  execSync('npx prisma migrate deploy');
}

// Never use reset in production
if (process.env.NODE_ENV === 'production') {
  console.error('prisma migrate reset is DISABLED in production');
  process.exit(1);
}

Recovery Procedures

Quick Recovery from Backup

# PostgreSQL
pg_restore -c -d database_name backup_file.dump

# MySQL
mysql -u user -p database_name < backup_file.sql

# SQLite
cp database.sqlite.backup database.sqlite

Point-in-Time Recovery (if available)

-- PostgreSQL with WAL archiving
SELECT pg_create_restore_point('before_migration');

-- After disaster
-- Restore to the named point

Best Practices Summary

Practice	Why
Always backup before migrations	Recovery from mistakes
Check existing data first	Awareness of impact
Use --pretend / dry-run	See changes before applying
Never use fresh/reset on data	Prevents accidental deletion
Verify environment	Prevents production disasters
Keep migrations reversible	Enables rollback
Test migrations on copy first	Catches issues early
Log migration runs	Audit trail

Red Flags Checklist

Stop immediately if:

• Not 100% sure which database you're connected to
• Environment might be production
• No backup exists
• Migration includes DROP or TRUNCATE
• Previous migration failed partway
• Someone else might be using the database
• You're tired or distracted

When in doubt, DON'T migrate. Ask first.