Database Migration Manager

Overview

Create, validate, and execute database schema migrations with full rollback support across PostgreSQL, MySQL, and MongoDB.

Prerequisites

• Database credentials with DDL permissions (CREATE, ALTER, DROP TABLE)
• Migration framework installed and configured (Flyway, Alembic, Prisma, Knex, or raw SQL versioning)
• Version control for migration files (Git repository)
• Access to a staging database matching production schema for testing migrations

• psql

  or

  mysql

  CLI for executing and verifying migrations
• Current schema baseline documented or captured via

  pg_dump --schema-only

Instructions

1. Capture the current schema state before making changes. Run

   pg_dump --schema-only -f schema_before.sql

   (PostgreSQL) or

   mysqldump --no-data > schema_before.sql

   (MySQL) to create a reference point.

2. Define the desired schema change clearly: specify table name, column additions/removals/modifications, constraint changes, and index updates. Document whether the change is additive (safe) or destructive (requires data migration).

3. Generate a versioned migration file following the framework's naming convention:

   • Flyway:

     V20240115_001__add_status_column_to_orders.sql

   • Alembic:

     alembic revision --autogenerate -m "add status column to orders"

   • Prisma: Edit

     schema.prisma

     then

     npx prisma migrate dev --name add_status_to_orders

   • Knex:

     npx knex migrate:make add_status_to_orders

4. Write the UP migration (forward change) with these safety practices:

   • Add new columns as nullable first, then backfill, then set NOT NULL
   • Use

     IF NOT EXISTS

     for CREATE operations to make migrations idempotent
   • Add explicit transaction wrapping:

     BEGIN; ... COMMIT;

   • Include comments explaining the business reason for each change

5. Write the DOWN migration (rollback) that exactly reverses the UP migration. For column additions, the DOWN drops the column. For table renames, the DOWN renames back. For data transformations, the DOWN must restore original data (store it in a backup column or table if needed).

6. Validate the migration on staging by running the full migration sequence:

   • Apply the UP migration and verify schema matches expectations
   • Run the application test suite against the migrated schema
   • Apply the DOWN migration and verify the schema returns to its original state
   • Re-apply the UP migration to confirm idempotency

7. For zero-downtime migrations on production, follow the expand-contract pattern:

   • Phase 1 (expand): Add new column/table without removing old ones. Deploy application code that writes to both old and new.
   • Phase 2 (migrate): Backfill new column/table from old data in batches.
   • Phase 3 (contract): Deploy application code that reads only from new. Drop old column/table in a future migration.

8. Handle large table migrations (>10M rows) with online DDL tools:

   pg_repack

   for PostgreSQL,

   pt-online-schema-change

   for MySQL, or

   gh-ost

   for MySQL. These tools create a shadow table, replicate changes, then swap atomically.

9. Update the migration history table and verify the migration version matches expectations. Run

   flyway info

   or

   alembic current

   to confirm.

10. Document the migration in a changelog with: migration version, description, tables affected, estimated execution time, rollback procedure, and any required application deployments.

Output

• Migration files (UP and DOWN) in the target framework's format
• Pre-flight validation script checking prerequisites before migration execution
• Data backfill scripts for non-nullable column additions on existing tables
• Rollback runbook with step-by-step instructions for reverting in production
• Migration changelog entry documenting the change for team reference

Error Handling

lock_timeout = '5s'

pg_repack

CASCADE

UPDATE table SET column = default_value WHERE column IS NULL

Examples

Adding a status enum column to an orders table: Generate a migration that: (1) creates the enum type

CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered')

ALTER TABLE orders ADD COLUMN status order_status

UPDATE orders SET status = 'delivered' WHERE shipped_at IS NOT NULL

ALTER TABLE orders ALTER COLUMN status SET NOT NULL

Splitting a monolith users table into users and profiles: Expand phase adds

profiles

Renaming a column without downtime: Create a migration that adds the new column, adds a trigger to sync writes between old and new columns, backfills existing data, deploys application code using the new column name, then drops the old column and trigger in a follow-up migration.

Resources

• Flyway documentation: https://documentation.red-gate.com/fd
• Alembic tutorial: https://alembic.sqlalchemy.org/en/latest/tutorial.html
• Prisma Migrate: https://www.prisma.io/docs/orm/prisma-migrate
• pg_repack (lock-free table rewrite): https://reorg.github.io/pg_repack/
• gh-ost (GitHub online schema change): https://github.com/github/gh-ost