Migration Script Creator

# Migration Script Creator

Create safe, reversible database migration scripts with proper validation, error handling, and rollback capability.

## Migration Structure

### 1. Up Migration (Forward)
- Schema changes
- Data transformations
- Index creation
- Constraint additions

### 2. Down Migration (Rollback)
- Reverse all changes
- Restore previous state
- Handle data loss warnings
- Clean up artifacts

### 3. Safety Checks
- Backup verification
- Dry run mode
- Data validation
- Dependency checks
- Lock prevention

## Example Migration

```sql
-- Migration: 001_add_user_roles.sql
-- Description: Add roles table and link to users
-- Author: DevTeam
-- Date: 2025-10-15

-- ============================================
-- UP MIGRATION
-- ============================================

BEGIN;

-- Safety checks
DO $$
BEGIN
  -- Check if migration already applied
  IF EXISTS (SELECT 1 FROM schema_migrations WHERE version = '001') THEN
    RAISE EXCEPTION 'Migration 001 already applied';
  END IF;
  
  -- Check prerequisites
  IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'users') THEN
    RAISE EXCEPTION 'Users table not found. Run migration 000 first.';
  END IF;
END$$;

-- Create roles table
CREATE TABLE IF NOT EXISTS roles (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add role_id to users table
ALTER TABLE users 
  ADD COLUMN role_id INTEGER REFERENCES roles(id);

-- Insert default roles
INSERT INTO roles (name, description) VALUES
  ('admin', 'Administrator with full access'),
  ('user', 'Regular user with standard access'),
  ('guest', 'Guest with limited access')
ON CONFLICT (name) DO NOTHING;

-- Set default role for existing users
UPDATE users 
SET role_id = (SELECT id FROM roles WHERE name = 'user')
WHERE role_id IS NULL;

-- Make role_id required
ALTER TABLE users 
  ALTER COLUMN role_id SET NOT NULL;

-- Create indexes
CREATE INDEX idx_users_role_id ON users(role_id);
CREATE INDEX idx_roles_name ON roles(name);

-- Record migration
INSERT INTO schema_migrations (version, name, applied_at)
VALUES ('001', 'add_user_roles', CURRENT_TIMESTAMP);

COMMIT;

-- ============================================
-- DOWN MIGRATION
-- ============================================

BEGIN;

-- Remove indexes
DROP INDEX IF EXISTS idx_users_role_id;
DROP INDEX IF EXISTS idx_roles_name;

-- Remove foreign key and column
ALTER TABLE users DROP COLUMN IF EXISTS role_id;

-- Drop roles table
DROP TABLE IF EXISTS roles;

-- Remove migration record
DELETE FROM schema_migrations WHERE version = '001';

COMMIT;

Migration Best Practices

Pre-Migration Checklist

• Backup database created
• Migration tested on staging
• Rollback script tested
• Downtime window communicated
• Team notified
• Monitoring in place

During Migration

• Use transactions where possible
• Add timeouts to prevent locks
• Log all operations
• Monitor database performance
• Keep team updated

Post-Migration

• Verify data integrity
• Check application functionality
• Monitor for errors
• Update documentation
• Notify team of completion

Data Migration Example

-- Migration: 002_normalize_addresses.sql
-- Description: Split address field into components

BEGIN;

-- Add new columns
ALTER TABLE users 
  ADD COLUMN street VARCHAR(255),
  ADD COLUMN city VARCHAR(100),
  ADD COLUMN state VARCHAR(50),
  ADD COLUMN zip_code VARCHAR(20),
  ADD COLUMN country VARCHAR(100);

-- Migrate data (example parsing logic)
UPDATE users
SET 
  street = split_part(address, ',', 1),
  city = trim(split_part(address, ',', 2)),
  state = trim(split_part(address, ',', 3)),
  zip_code = trim(split_part(address, ',', 4)),
  country = 'USA'
WHERE address IS NOT NULL;

-- Validation
DO $$
DECLARE
  invalid_count INTEGER;
BEGIN
  SELECT COUNT(*) INTO invalid_count
  FROM users
  WHERE address IS NOT NULL
    AND (street IS NULL OR city IS NULL);
  
  IF invalid_count > 0 THEN
    RAISE WARNING '% users have invalid address data', invalid_count;
  END IF;
END$$;

-- After validation, can drop old column
-- ALTER TABLE users DROP COLUMN address;

COMMIT;

TypeScript Migration Runner

interface Migration {
  version: string;
  name: string;
  up: () => Promise<void>;
  down: () => Promise<void>;
}

class MigrationRunner {
  async runMigration(migration: Migration, direction: 'up' | 'down') {
    const client = await pool.connect();
    
    try {
      await client.query('BEGIN');
      
      console.log(`Running migration ${migration.version} (${direction})...`);
      
      if (direction === 'up') {
        await migration.up();
        await this.recordMigration(client, migration);
      } else {
        await migration.down();
        await this.removeMigration(client, migration);
      }
      
      await client.query('COMMIT');
      console.log(`Migration ${migration.version} completed successfully`);
      
    } catch (error) {
      await client.query('ROLLBACK');
      console.error(`Migration ${migration.version} failed:`, error);
      throw error;
    } finally {
      client.release();
    }
  }
  
  async getAppliedMigrations(): Promise<string[]> {
    const result = await pool.query(
      'SELECT version FROM schema_migrations ORDER BY applied_at'
    );
    return result.rows.map(row => row.version);
  }
}

Safety Features

Backup Before Migration

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

# MySQL
mysqldump --single-transaction mydb > backup_$(date +%Y%m%d_%H%M%S).sql

Dry Run Mode

if (process.env.DRY_RUN === 'true') {
  console.log('DRY RUN MODE: Changes will not be committed');
  // Run migration but always rollback
  await client.query('ROLLBACK');
}

Lock Timeout

-- Prevent long-running locks
SET lock_timeout = '5s';
SET statement_timeout = '30s';