schema-versioning

Getting Started

1. Install the skill using the command above
2. Open your AI coding agent (Claude Code, Codex, Gemini CLI, or Cursor)
3. Reference the skill in your prompt
4. The AI will use the skill's capabilities automatically

Example Prompts

• "Review the open pull requests and summarize what needs attention"
• "Generate a changelog from the last 20 commits on the main branch"

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:

# For Knex.js
npx knex init
npx knex migrate:make initial_schema

# For Prisma
npx prisma init
npx prisma migrate dev --name initial_schema

# For Alembic (Python)
alembic init migrations
alembic revision --autogenerate -m "initial_schema"

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

# 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:

// 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:

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

# Verify the migration is reversible
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

Create a rollback script that handles both schema and data rollbacks:

#!/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
npx knex migrate:rollback --count "$ROLLBACK_COUNT" --env "$ENVIRONMENT"

# 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:

# .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).
• 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.