managing-database-migrations

name	managing-database-migrations
description	Process use when you need to work with database migrations. This skill provides schema migration management with comprehensive guidance and automation. Trigger with phrases like "create migration", "run migrations", or "manage schema versions".
allowed-tools	Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*), Bash(mongosh:*)
version	1.0.0
author	Jeremy Longshore <[e-mail]>
license	MIT
compatible-with	claude-code, codex, openclaw
tags	["database","migration","database-migrations"]

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: