database-migrations_skill

Overview

This skill provides pragmatic best practices for safe, reversible database migrations across PostgreSQL, MySQL, and common ORMs (Prisma, Drizzle, Django, TypeORM, golang-migrate). It focuses on zero-downtime patterns, separating schema and data work, and producing migration operations that are testable, auditable, and production-safe. The guidance is concise and actionable for teams shipping schema changes on high-volume systems.

How this skill works

The skill inspects migration workflows and recommends patterns: always treat schema changes as migrations, avoid mixing DDL and DML in one run, and prefer forward-only reversible steps. It outlines concrete SQL and ORM-specific commands for safe adds, index creation, backfills, and rollbacks. It prescribes the expand-contract deploy pattern and a pre-apply safety checklist to avoid long locks and destructive edits.

When to use it

• Creating or altering tables, columns, or indexes in production
• Planning or running data backfills and large data transforms
• Implementing zero-downtime schema changes for high-traffic services
• Onboarding migration tooling for a new project or tech stack
• Preparing rollbacks and disaster recovery plans for schema changes

Best practices

• Treat every change as a migration and never modify production schema outside migration files
• Keep schema and data migrations separate; backfills should be independent steps
• Make migrations immutable after deployment; create new migrations for fixes or rollbacks
• Test migrations on production-sized data and a copy of production before deploying
• Use non-blocking operations (CONCURRENTLY indexes, nullable columns, batched updates)
• Document a rollback plan and include UP/DOWN or mark migrations as deliberately irreversible

Example use cases

• Add a new nullable column, backfill in a separate migration, then add NOT NULL constraint later
• Create an email index using CREATE INDEX CONCURRENTLY with a custom SQL migration for Prisma or ORM gaps
• Perform a large normalize-email backfill in small batches with SKIP LOCKED to avoid long locks
• Use SeparateDatabaseAndState in Django to remove a field from models without immediately dropping the DB column
• Follow expand-contract to rename a column: add new column, backfill, switch app to dual-write, then drop old column