Overview

This skill helps you establish a reliable database schema versioning workflow: generating timestamped migration files, testing them against a shadow database, integrating schema checks into CI/CD, and rolling back safely when deployments fail. It works with any migration tool (Prisma, Knex, TypeORM, Flyway, Alembic) and focuses on patterns rather than vendor lock-in.

Instructions

1. Initialize migration infrastructure

Set up the migration directory structure and configuration:

bash

# For Knex.js (creates knexfile.js; migrations are written by hand)
npm install knex pg
npx knex init
npx knex migrate:make initial_schema -x ts   # -x ts for TypeScript migrations

# For Prisma (creates prisma/schema.prisma and prisma.config.ts, which reads DATABASE_URL)
npx prisma init
npx prisma migrate dev --name initial_schema   # dev only; diffs the schema, applies via a shadow DB
npx prisma migrate deploy                      # CI/production: applies pending migrations, never generates

# For Alembic (Python)
pip install alembic
alembic init migrations
alembic revision --autogenerate -m "initial_schema"
alembic upgrade head        # roll back one step with: alembic downgrade -1

Knex does not read DATABASE_URL by itself; point the knexfile at it:

javascript

// knexfile.js
module.exports = {
  client: "pg",
  connection: process.env.DATABASE_URL,
  migrations: { directory: "./migrations" },
};

Prisma Migrate has no down migrations: roll back by writing a new forward migration, or mark a failed one with npx prisma migrate resolve --rolled-back 20250217_add_orders_table after fixing the database by hand. Check state with npx prisma migrate status.

Create a shadow database for testing migrations before applying to production:

yaml

# docker-compose.shadow-db.yml
services:
  shadow-db:
    image: postgres:16
    environment:
      POSTGRES_DB: app_shadow
      POSTGRES_PASSWORD: shadow_test
    ports:
      - "5433:5432"

2. Generate migration files from schema changes

When models change, generate the migration diff:

typescript

// Knex migration example: 20250217_add_orders_table.ts
import { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable("orders", (table) => {
    table.uuid("id").primary().defaultTo(knex.fn.uuid());
    table.uuid("user_id").notNullable().references("id").inTable("users");
    table.decimal("total", 10, 2).notNullable();
    table.enum("status", ["pending", "paid", "shipped", "cancelled"]).defaultTo("pending");
    table.timestamps(true, true);
    table.index(["user_id", "status"]);
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTableIfExists("orders");
}

3. Test migrations safely

Always test against a shadow database before production:

bash

# Apply all pending migrations to shadow database
DATABASE_URL="postgres://localhost:5433/app_shadow" npx knex migrate:latest

# Verify the migration is reversible (rollback undoes the whole last batch)
DATABASE_URL="postgres://localhost:5433/app_shadow" npx knex migrate:rollback

# Re-apply to confirm idempotency
DATABASE_URL="postgres://localhost:5433/app_shadow" npx knex migrate:latest

4. Implement rollback procedures

Knex rolls back by batch (migrate:rollback, or --all) or one file at a time (migrate:down). migrate:rollback has no --count flag, so loop over migrate:down to undo N migrations:

bash

#!/bin/bash
# rollback.sh — Roll back the last N migrations
set -euo pipefail

ROLLBACK_COUNT=${1:-1}
ENVIRONMENT=${2:-staging}

echo "Rolling back $ROLLBACK_COUNT migration(s) on $ENVIRONMENT..."

# Capture current state for audit
npx knex migrate:currentVersion --env "$ENVIRONMENT" > /tmp/pre_rollback_version.txt

# Execute rollback, one migration per call
for _ in $(seq "$ROLLBACK_COUNT"); do
  npx knex migrate:down --env "$ENVIRONMENT"
done

# Verify post-rollback state
npx knex migrate:currentVersion --env "$ENVIRONMENT" > /tmp/post_rollback_version.txt

echo "Rolled back from $(cat /tmp/pre_rollback_version.txt) to $(cat /tmp/post_rollback_version.txt)"

5. CI/CD integration

Add migration checks to your pipeline:

yaml

# .github/workflows/migration-check.yml
migration-check:
  runs-on: ubuntu-latest
  services:
    postgres:
      image: postgres:16
      env:
        POSTGRES_DB: test_db
        POSTGRES_PASSWORD: test
      ports: ["5432:5432"]
  steps:
    - uses: actions/checkout@v4
    - run: npm ci
    - name: Apply all migrations from scratch
      run: npx knex migrate:latest
      env:
        DATABASE_URL: postgres://postgres:test@localhost:5432/test_db
    - name: Verify rollback works
      run: npx knex migrate:rollback --all
      env:
        DATABASE_URL: postgres://postgres:test@localhost:5432/test_db
    - name: Re-apply to confirm clean state
      run: npx knex migrate:latest
      env:
        DATABASE_URL: postgres://postgres:test@localhost:5432/test_db

Examples

Example 1: Adding a column with backfill

Prompt: "Generate a migration that adds an email_verified boolean column to the users table, defaulting to false, and backfills it to true for all users who have logged in within the last 90 days."

Agent output:

Creates migrations/20250217143000_add_email_verified_to_users.ts
The up function adds the column with a default, then runs a data backfill query using last_login_at > NOW() - INTERVAL '90 days'
The down function drops the column
Includes a note: "Backfill runs in batches of 1,000 to avoid locking the table"

Example 2: Renaming a table safely

Prompt: "I need to rename the customers table to clients without breaking the app during deployment. Generate a zero-downtime migration strategy."

Agent output:

Migration 1: Create clients table, add trigger to sync writes from customers to clients
Migration 2: Backfill existing data from customers to clients
Migration 3: Create a view customers pointing to clients (backwards compatibility)
Migration 4: Drop the view and old table after all application code references clients
Each migration has a working down function

Guidelines

Every migration must have a working down function — untested rollbacks fail when you need them most.
Never modify a migration that has been applied to any shared environment — create a new migration instead.
Use transactions for DDL when your database supports it (PostgreSQL does, MySQL does not for most DDL).
Take a backup or snapshot before production migrations — a down function cannot restore dropped data.
Test the full sequence: migrate up, roll back, migrate up again — this catches hidden state dependencies.
Keep migrations small — one logical change per file. A 500-line migration is a red flag.
Add indexes in separate migrations from table creation to avoid long locks on large tables.
Timestamp your migration filenames — sequential integers cause merge conflicts in teams.

Schema Versioning

Validation

Prove Schema Versioning on your task

Getting Started

Example Prompts

Documentation

Overview

Instructions

1. Initialize migration infrastructure

2. Generate migration files from schema changes

3. Test migrations safely

4. Implement rollback procedures

5. CI/CD integration

Examples

Example 1: Adding a column with backfill

Example 2: Renaming a table safely

Guidelines

Information

Use Cases

Related Skills