williamzujkowski/Database Migration Script Generator

Purpose & When-To-Use

Trigger conditions:

• Evolving database schema for application updates
• Performing data migrations with business logic transformations
• Implementing zero-downtime deployments requiring schema changes
• Standardizing migration workflows across teams
• Migrating between database versions or platforms
• Adding indexes, constraints, or partitioning to existing tables

Not for:

• Initial database schema creation (use ORM models or DDL scripts)
• One-off data fixes (use direct SQL with transaction safety)
• Database backups or recovery operations
• Cross-database data replication (use ETL tools)

Pre-Checks

Time normalization:

• Compute NOW_ET using NIST/time.gov semantics (America/New_York, ISO-8601)
• Use NOW_ET for all citation access dates

Input validation:

• migration_tool must be one of: liquibase, flyway, alembic
• database must be one of: postgresql, mysql, sqlserver, oracle
• migration_type must be one of: schema, data, hybrid
• downtime_allowed must be boolean (true/false)
• migration_description must be non-empty and descriptive

Source freshness:

• Liquibase docs must be accessible accessed 2025-10-26T03:51:54-04:00
• Flyway docs must be accessible accessed 2025-10-26T03:51:54-04:00
• Alembic docs must be accessible accessed 2025-10-26T03:51:54-04:00
• PostgreSQL online DDL docs must be accessible accessed 2025-10-26T03:51:54-04:00
• MySQL online DDL docs must be accessible accessed 2025-10-26T03:51:54-04:00

Procedure

T1: Basic Schema Migration (≤2k tokens)

Fast path for simple DDL changes:

1. Schema Change Identification

• Add/drop columns (with default values to avoid table rewrites)
• Create/drop tables
• Add/drop simple indexes (non-unique, single column)
• Add/drop NOT NULL constraints (with validation)

1. Tool-Specific Migration Format

Liquibase (XML/YAML) accessed 2025-10-26T03:51:54-04:00 xml <changeSet id="add-email-column" author="migration-generator"> <addColumn tableName="users"> <column name="email" type="VARCHAR(255)"/> </addColumn> <rollback> <dropColumn tableName="users" columnName="email"/> </rollback> </changeSet>

Flyway (SQL) accessed 2025-10-26T03:51:54-04:00 sql -- V1__add_email_column.sql ALTER TABLE users ADD COLUMN email VARCHAR(255);

Alembic (Python) accessed 2025-10-26T03:51:54-04:00 python def upgrade(): op.add_column('users', sa.Column('email', sa.String(255)))

def downgrade(): op.drop_column('users', 'email')

1. Basic Rollback Script

• Generate inverse operation for each forward change
• Add rollback validation comments
• Include manual rollback steps if auto-rollback unsafe

Decision: If simple schema change without data → STOP at T1; otherwise proceed to T2.

T2: Data Migrations with Rollback (≤6k tokens)

Extended migrations with data transformations:

1. Data Migration Patterns accessed 2025-10-26T03:51:54-04:00

Backfill Existing Data python # Alembic: Backfill default values for new column def upgrade(): op.add_column('users', sa.Column('status', sa.String(20), nullable=True)) # Backfill existing rows op.execute("UPDATE users SET status = 'active' WHERE status IS NULL") # Make NOT NULL after backfill op.alter_column('users', 'status', nullable=False)

Data Transformation python # Alembic: Split full_name into first_name and last_name def upgrade(): op.add_column('users', sa.Column('first_name', sa.String(100))) op.add_column('users', sa.Column('last_name', sa.String(100))) # Transform data connection = op.get_bind() users = connection.execute("SELECT id, full_name FROM users").fetchall() for user_id, full_name in users: parts = full_name.split(' ', 1) first = parts[0] last = parts[1] if len(parts) > 1 else '' connection.execute( "UPDATE users SET first_name = %s, last_name = %s WHERE id = %s", (first, last, user_id) ) op.drop_column('users', 'full_name')

1. Rollback Safety Patterns

• Idempotency: Ensure migrations can run multiple times safely
• Checkpointing: Add validation queries before destructive operations
• Backup Triggers: Create temporary backup tables for data migrations
• Dry-Run Mode: Include commented-out SELECT statements to preview changes

1. Validation Tests accessed 2025-10-26T03:51:54-04:00

sql -- Post-migration validation SELECT COUNT(*) FROM users WHERE email IS NULL; -- Should be 0 SELECT COUNT(*) FROM users WHERE status NOT IN ('active', 'inactive'); -- Should be 0

1. Database-Specific Considerations

PostgreSQL accessed 2025-10-26T03:51:54-04:00

• Use ALTER TABLE ... SET NOT NULL with CHECK constraint first
• Prefer CONCURRENTLY for index creation (zero-downtime)
• Use pg_stat_progress_create_index to monitor long operations

MySQL accessed 2025-10-26T03:51:54-04:00

• Check online DDL support: ALGORITHM=INPLACE, LOCK=NONE
• Avoid ALTER TABLE that requires table copy (pre-8.0)
• Use pt-online-schema-change for large tables (Percona Toolkit)

SQL Server

• Use WITH (ONLINE = ON) for index operations
• Leverage SSMS execution plan analysis
• Consider SCHEMA_ONLY copies for large data migrations

T3: Zero-Downtime Patterns (≤12k tokens)

Advanced patterns for production systems:

1. Expand/Contract Pattern accessed 2025-10-26T03:51:54-04:00

Phase 1: Expand (Add new schema) python # Migration 001: Add new column, keep old column def upgrade(): op.add_column('users', sa.Column('email_new', sa.String(255))) # Trigger to sync old → new during transition op.execute(""" CREATE TRIGGER sync_email_new BEFORE UPDATE ON users FOR EACH ROW BEGIN SET NEW.email_new = NEW.email; END; """)

Phase 2: Migrate Data python # Migration 002: Backfill new column def upgrade(): op.execute("UPDATE users SET email_new = email WHERE email_new IS NULL")

Phase 3: Contract (Remove old schema) python # Migration 003: Drop old column (after application updated) def upgrade(): op.execute("DROP TRIGGER IF EXISTS sync_email_new") op.drop_column('users', 'email') op.alter_column('users', 'email_new', new_column_name='email')

1. Online Index Creation accessed 2025-10-26T03:51:54-04:00

PostgreSQL CONCURRENTLY sql -- Flyway: V5__add_email_index.sql CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Validation SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE indexname = 'idx_users_email';

MySQL Online DDL sql -- Flyway: V6__add_composite_index.sql ALTER TABLE users ADD INDEX idx_email_status (email, status) ALGORITHM=INPLACE, LOCK=NONE;

1. Shadow Table Pattern (for complex transformations)

python # Migration 010: Create shadow table with new schema def upgrade(): op.create_table( 'users_new', sa.Column('id', sa.Integer, primary_key=True), sa.Column('email', sa.String(255), nullable=False, index=True), sa.Column('status', sa.String(20), nullable=False) ) # Stream data from old → new table op.execute(""" INSERT INTO users_new (id, email, status) SELECT id, email, COALESCE(status, 'active') FROM users """) # Atomic rename (downtime: milliseconds) op.rename_table('users', 'users_old') op.rename_table('users_new', 'users')

1. Deployment Guide Template

markdown ## Deployment Steps

### Pre-Migration

1. Verify database backup completed (last 24h)
2. Check application connection pool settings (timeout ≥ 30s)
3. Review query performance baseline (pg_stat_statements)

### Migration Execution

1. Run migration in transaction (if supported)
2. Monitor lock waits: SELECT * FROM pg_locks WHERE NOT granted
3. Validate row counts: SELECT COUNT(*) FROM users

### Post-Migration

1. Run ANALYZE to update statistics
2. Verify application logs (no constraint violations)
3. Monitor query performance (compare to baseline)

### Rollback Procedure (if needed)

1. Stop application traffic (or use feature flag)
2. Run rollback script: flyway undo or alembic downgrade -1
3. Verify data integrity: SELECT * FROM users LIMIT 10
4. Restore from backup if rollback fails

1. Blue-Green Database Migrations accessed 2025-10-26T03:51:54-04:00

• Duplicate database instance (Blue = old schema, Green = new schema)
• Run migrations on Green instance
• Dual-write pattern during transition (application writes to both)
• Cutover: Update connection string to Green
• Validation period: Keep Blue online for 24-48h

Decision Rules

Migration Tool Selection:

• Liquibase: Best for multi-database support, XML/YAML declarative changes, complex rollback
• Flyway: Best for SQL-first teams, simple versioning, Java/Spring ecosystems
• Alembic: Best for Python applications using SQLAlchemy, programmatic migrations

Migration Strategy by Downtime Allowance:

• downtime_allowed = true: Use direct ALTER TABLE, faster execution, simpler scripts
• downtime_allowed = false: Use expand/contract, CONCURRENTLY, shadow tables, longer timeline

Database-Specific Patterns:

• PostgreSQL: Prefer CONCURRENTLY for indexes, use CHECK constraints before NOT NULL
• MySQL: Validate ALGORITHM=INPLACE support, use pt-online-schema-change for InnoDB
• SQL Server: Use ONLINE=ON, consider columnstore indexes for analytics workloads
• Oracle: Use Oracle Data Redefinition (DBMS_REDEFINITION) for zero-downtime

Abort Conditions:

• Invalid tool/database combination → error "Tool X does not support database Y"
• Destructive operation without rollback → error "Cannot generate safe rollback for DROP TABLE"
• Zero-downtime requested for non-supported operation → error "Zero-downtime not possible for operation X"

Data Preservation Checks:

• Dropping columns: Warn if column contains non-NULL data
• Changing types: Validate data fits in new type (VARCHAR(50) → VARCHAR(20))
• Adding NOT NULL: Require default value or backfill strategy

Output Contract

Schema (JSON):

json
{
  "migration_tool": "liquibase | flyway | alembic",
  "database": "postgresql | mysql | sqlserver | oracle",
  "migration_type": "schema | data | hybrid",
  "downtime_allowed": "boolean",
  "migration_script": {
    "filename": "string (e.g., V5__add_email_column.sql)",
    "content": "string (tool-specific migration code)"
  },
  "rollback_script": {
    "filename": "string (e.g., U5__undo_email_column.sql)",
    "content": "string (inverse migration code)",
    "manual_steps": ["string (if auto-rollback unsafe)"]
  },
  "validation_tests": [
    {
      "description": "string",
      "query": "string (SQL validation query)",
      "expected_result": "string"
    }
  ],
  "deployment_guide": {
    "pre_migration_steps": ["string"],
    "execution_steps": ["string"],
    "post_migration_steps": ["string"],
    "rollback_procedure": ["string"],
    "estimated_duration": "string (e.g., '5 minutes', '2 hours')"
  },
  "warnings": ["string (potential issues or breaking changes)"],
  "timestamp": "ISO-8601 string (NOW_ET)"
}

Required Fields:

• migration_tool, database, migration_type, downtime_allowed, migration_script, rollback_script, validation_tests, deployment_guide, timestamp

Safety Guarantees:

• All DDL changes must have explicit rollback (or manual rollback steps)
• Data migrations must include row count validation
• Zero-downtime migrations must specify lock duration estimates

Examples

Example 1: Simple Column Addition (Alembic + PostgreSQL)

python
"""Add email column to users table with NOT NULL constraint

Revision ID: a1b2c3d4e5f6
Revises: previous_revision
Create Date: 2025-10-26 03:51:54.000000

"""
from alembic import op
import sqlalchemy as sa

def upgrade():
    # Add column as nullable first
    op.add_column('users', sa.Column('email', sa.String(255), nullable=True))

    # Backfill with placeholder (application will update)
    op.execute("UPDATE users SET email = CONCAT('user', id, '@example.com') WHERE email IS NULL")

    # Add NOT NULL constraint
    op.alter_column('users', 'email', nullable=False)

    # Add index for performance
    op.create_index('idx_users_email', 'users', ['email'], unique=True)

def downgrade():
    op.drop_index('idx_users_email', table_name='users')
    op.drop_column('users', 'email')

Quality Gates

Token Budgets:

• T1: ≤2k tokens (simple schema change, basic rollback)
• T2: ≤6k tokens (data migration, validation tests, database-specific optimizations)
• T3: ≤12k tokens (zero-downtime patterns, deployment guide, multi-phase migrations)

Safety:

• No plaintext credentials in migration scripts (use environment variables)
• All destructive operations require explicit confirmation comments
• Rollback scripts tested against sample data

Auditability:

• Migration IDs/versions follow tool conventions (Flyway: V1__description.sql, Alembic: revision IDs)
• All migrations include author, timestamp, and description
• Database-specific syntax validated against official documentation

Determinism:

• Same inputs → identical migration scripts
• Idempotent migrations (can run multiple times safely)
• Predictable rollback behavior

Performance:

• Estimate lock duration for DDL operations
• Include EXPLAIN ANALYZE for data migrations affecting >10k rows
• Recommend batch size for large table transformations (e.g., 1000 rows/batch)

Resources

Official Documentation (accessed 2025-10-26T03:51:54-04:00):

1. Liquibase Change Types - DDL/DML operations
2. Flyway SQL Migrations - Versioned migrations
3. Alembic Operations Reference - Python migration API
4. PostgreSQL ALTER TABLE - DDL syntax
5. MySQL Online DDL - Zero-downtime operations
6. SQL Server Online Index Operations - Online DDL

Migration Patterns:

• Expand/Contract Pattern - Zero-downtime schema evolution
• Blue-Green Deployments - Database migration strategies
• Database Refactoring - Catalog of database refactoring patterns

Best Practices:

• Flyway Best Practices - Migration versioning and naming
• Alembic Tutorial - Auto-generate vs manual migrations
• PostgreSQL Wiki: Don't Do This - Anti-patterns to avoid

Tool Comparisons:

• Liquibase vs Flyway - Feature comparison
• Schema Migration Tools Comparison - Multi-tool benchmarks