Skip to main content
Agent skill

Database Migrations

Safe, reversible database migration patterns: forward-only production changes, expand-contract zero-downtime renames, concurrent indexes, batched backfills, and per-tool workflows for PostgreSQL, Prisma, Drizzle, Kysely, Django, and golang-migrate. Use when writing a schema or data migration, adding a column or index to a large table, planning a rollback, or preparing a zero-downtime deploy.

Get started

Get the complete skill folder, including its scripts and reference files.

·

When to use this skill

Use Database Migrations when an AI agent needs a reusable SKILL.md workflow for this job: Safe, reversible database migration patterns: forward-only production changes, expand-contract zero-downtime renames, concurrent indexes, batched backfills, and per-tool workflows for PostgreSQL, Prisma, Drizzle, Kysely, Django, and golang-migrate. Use when writing a schema or data migration, adding a column or index to a large table, planning a rollback, or preparing a zero-downtime deploy.

When not to use it

Skip Database Migrations when the task is outside the writing category, or when a more specific skill in this directory already covers the same workflow with clearer triggers.

How to install

Where will you use it?

Install scope
Claude Code folder setup
mkdir -p ~/.claude/skills/database-migrations
# Save SKILL.md to ~/.claude/skills/database-migrations/SKILL.md
  1. Claude Code personal install: place the skill and its bundled files at ~/.claude/skills/database-migrations/SKILL.md. For a shared project, commit the folder at .claude/skills/database-migrations/ instead.
  2. Current Claude Code detects SKILL.md changes in watched directories during the session. If you just created a top-level skills directory, run /reload-skills. Check that the skill is listed before testing its trigger.
  3. For Claude chat or Cowork in the Desktop app, manage uploaded skills in Customize > Skills; copying files into a local Claude Code folder is not a Desktop install. Other agents may use different install locations.

Full install guide for Claude, Cursor, and Codex →

Try a starter prompt

After installing the skill, paste this starter prompt into your agent to verify it triggers on a small task.

Starter prompt
Use the Database Migrations skill for this task: Use Database Migrations when this documented workflow matches the task. Confirm any required inputs or files before making changes.

What supports this recommendation?

71/100 provisional content score. This measures documentation, practical detail and source signals. It is not a measured task success rate or a security certification.

Documentation
77/100
Practical detail
68/100
Source evidence
63/100
Source signals
72/100
Model compatibility evidence
  • claudeuntested

    No model-specific signal or recorded compatibility test was found.

  • chatgptuntested

    No model-specific signal or recorded compatibility test was found.

  • geminiuntested

    No model-specific signal or recorded compatibility test was found.

  • copilotuntested

    No model-specific signal or recorded compatibility test was found.

  • llamauntested

    No model-specific signal or recorded compatibility test was found.

  • perplexityuntested

    No model-specific signal or recorded compatibility test was found.

  • mistraluntested

    No model-specific signal or recorded compatibility test was found.

  • grokuntested

    No model-specific signal or recorded compatibility test was found.

Inferred compatibility is based on content signals. Check the app, its available tools and setup requirements before running a skill.

What this skill does

# Database Migration Patterns

Safe, reversible database schema changes for production systems.

## When to Activate

- Creating or altering database tables - Adding/removing columns or indexes - Running data migrations (backfill, transform) - Planning zero-downtime schema changes - Setting up migration tooling for a new project

## Core Principles

1. **Every change is a migration** — never alter production databases manually 2. **Migrations are forward-only in production** — rollbacks use new forward migrations 3. **Schema and data migrations are separate** — never mix DDL and DML in one migration 4. **Test migrations against production-sized data** — a migration that works on 100 rows may lock on 10M 5. **Migrations are immutable once deployed** — never edit a migration that has run in production

## Migration Safety Checklist

Before applying any migration:

- [ ] Migration has both UP and DOWN (or is explicitly marked irreversible) - [ ] No full table locks on large tables (use concurrent operations) - [ ] New columns have defaults or are nullable (never add NOT NULL without default) - [ ] Indexes created concurrently (not inline with CREATE TABLE for existing tables) - [ ] Data backfill is a separate migration from schema change - [ ] Tested against a copy of production data - [ ] Rollback plan documented

## PostgreSQL Patterns

### Adding a Column Safely

```sql -- GOOD: Nullable column, no lock ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- GOOD: Column with default (Postgres 11+ is instant, no rewrite) ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

-- BAD: NOT NULL without default on existing table (requires full rewrite) ALTER TABLE users ADD COLUMN role TEXT NOT NULL; -- This locks the table and rewrites every row ```

### Adding an Index Without Downtime

```sql -- BAD: Blocks writes on large tables CREATE INDEX idx_users_email ON users (email);

-- GOOD: Non-blocking, allows concurrent writes CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- Note: CONCURRENTLY cannot run inside a transaction block -- Most migration tools need special handling for this ```

### Renaming a Column (Zero-Downtime)

Never rename directly in production. Use the expand-contract pattern:

```sql -- Step 1: Add new column (migration 001) ALTER TABLE users ADD COLUMN display_name TEXT;

-- Step 2: Backfill data (migration 002, data migration) UPDATE users SET display_name = username WHERE display_name IS NULL;

-- Step 3: Update application code to read/write both columns -- Deploy application changes

-- Step 4: Stop writing to old column, drop it (migration 003) ALTER TABLE users DROP COLUMN username; ```

### Removing a Column Safely

```sql -- Step 1: Remove all application references to the column -- Step 2: Deploy application without the column reference -- Step 3: Drop column in next migration ALTER TABLE orders DROP COLUMN legacy_status;

-- For Django: use SeparateDatabaseAndState to remove from model -- without generating DROP COLUMN (then drop in next migration) ```

### Large Data Migrations

```sql -- BAD: Updates all rows in one transaction (locks table) UPDATE users SET normalized_email = LOWER(email);

-- GOOD: Batch update with progress DO $$ DECLARE batch_size INT := 10000; rows_updated INT; BEGIN LOOP UPDATE users SET normalized_email = LOWER(email) WHERE id IN ( SELECT id FROM users WHERE normalized_email IS NULL LIMIT batch_size FOR UPDATE SKIP LOCKED ); GET DIAGNOSTICS rows_updated = ROW_COUNT; RAISE NOTICE 'Updated % rows', rows_updated; EXIT WHEN rows_updated = 0; COMMIT; END LOOP; END $$; ```

## Prisma (TypeScript/Node.js)

### Workflow

```bash # Create migration from schema changes npx prisma migrate dev --name add_user_avatar

# Apply pending migrations in production npx prisma migrate deploy

# Reset database (dev only) npx prisma migrate reset

# Generate client after schema changes npx prisma generate ```

### Schema Example

```prisma model User { id String @id @default(cuid()) email String @unique name String? avatarUrl String? @map("avatar_url") createdAt DateTime @default(now()) @map("created_at") updatedAt DateTime @updatedAt @map("updated_at") orders Order[]

@@map("users") @@index([email]) } ```

### Custom SQL Migration

For operations Prisma cannot express (concurrent indexes, data backfills):

```bash # Create empty migration, then edit the SQL manually npx prisma migrate dev --create-only --name add_email_index ```

```sql -- migrations/20240115_add_email_index/migration.sql -- Prisma cannot generate CONCURRENTLY, so we write it manually CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users (email); ```

## Drizzle (TypeScript/Node.js)

### Workflow

```bash # Generate migration from schema changes npx drizzle-kit generate

# Apply migrations npx drizzle-kit migrate

# Push schema directly (dev only, no migration file) npx drizzle-kit push ```

### Schema Example

```typescript import { pgTable, text, timestamp, uuid, boolean } from "drizzle-orm/pg-core";

export const users = pgTable("users", { id: uuid("id").primaryKey().defaultRandom(), email: text("email").notNull().unique(), name: text("name"), isActive: boolean("is_active").notNull().default(true), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }); ```

## Kysely (TypeScript/Node.js)

### Workflow (kysely-ctl)

```bash # Initialize config file (kysely.config.ts) kysely init

# Create a new migration file kysely migrate make add_user_avatar

# Apply all pending migrations kysely migrate latest

# Rollback last migration kysely migrate down

# Show migration status kysely migrate list ```

### Migration File

```typescript // migrations/2024_01_15_001_create_user_profile.ts import { type Kysely, sql } from 'kysely'

// IMPORTANT: Always use Kysely<any>, not your typed DB interface. // Migrations are frozen in time and must not depend on current schema types. export async function up(db: Kysely<any>): Promise<void> { await db.schema .createTable('user_profile') .addColumn('id', 'serial', (col) => col.primaryKey()) .addColumn('email', 'varchar(255)', (col) => col.notNull().unique()) .addColumn('avatar_url', 'text') .addColumn('created_at', 'timestamp', (col) => col.defaultTo(sql`now()`).notNull() ) .execute()

await db.schema .createIndex('idx_user_profile_avatar') .on('user_profile') .column('avatar_url') .execute() }

export async function down(db: Kysely<any>): Promise<void> { await db.schema.dropTable('user_profile').execute() } ```

### Programmatic Migrator

```typescript import { Migrator, FileMigrationProvider } from 'kysely' import { promises as fs } from 'fs' import * as path from 'path' // ESM only — CJS can use __dirname directly import { fileURLToPath } from 'url' const migrationFolder = path.join( path.dirname(fileURLToPath(import.meta.url)), './migrations', )

// `db` is your Kysely<any> database instance const migrator = new Migrator({ db, provider: new FileMigrationProvider({ fs, path, migrationFolder, }), // WARNING: Only enable in development. Disables timestamp-ordering // validation, which can cause schema drift between environments. // allowUnorderedMigrations: true, })

const { error, results } = await migrator.migrateToLatest()

results?.forEach((it) => { if (it.status === 'Success') { console.log(`migration "${it.migrationName}" executed successfully`) } else if (it.status === 'Error') { console.error(`failed to execute migration "${it.migrationName}"`) } })

if (error) { console.error('migration failed', error) process.exit(1) } ```

## Django (Python)

### Workflow

```bash # Generate migration from model changes python manage.py makemigrations

# Apply migrations python manage.py migrate

# Show migration status python manage.py showmigrations

# Generate empty migration for custom SQL python manage.py makemigrations --empty app_name -n description ```

### Data Migration

```python from django.db import migrations

def backfill_display_names(apps, schema_editor): User = apps.get_model("accounts", "User") batch_size = 5000 users = User.objects.filter(display_name="") while users.exists(): batch = list(users[:batch_size]) for user in batch: user.display_name = user.username User.objects.bulk_update(batch, ["display_name"], batch_size=batch_size)

def reverse_backfill(apps, schema_editor): pass # Data migration, no reverse needed

class Migration(migrations.Migration): dependencies = [("accounts", "0015_add_display_name")]

operations = [ migrations.RunPython(backfill_display_names, reverse_backfill), ] ```

### SeparateDatabaseAndState

Remove a column from the Django model without dropping it from the database immediately:

```python class Migration(migrations.Migration): operations = [ migrations.SeparateDatabaseAndState( state_operations=[ migrations.RemoveField(model_name="user", name="legacy_field"), ], database_operations=[], # Don't touch the DB yet ), ] ```

## golang-migrate (Go)

### Workflow

```bash # Create migration pair migrate create -ext sql -dir migrations -seq add_user_avatar

# Apply all pending migrations migrate -path migrations -database "$DATABASE_URL" up

# Rollback last migration migrate -path migrations -database "$DATABASE_URL" down 1

# Force version (fix dirty state) migrate -path migrations -database "$DATABASE_URL" force VERSION ```

### Migration Files

```sql -- migrations/000003_add_user_avatar.up.sql ALTER TABLE users ADD COLUMN avatar_url TEXT; CREATE INDEX CONCURRENTLY idx_users_avatar ON users (avatar_url) WHERE avatar_url IS NOT NULL;

-- migrations/000003_add_user_avatar.down.sql DROP INDEX IF EXISTS idx_users_avatar; ALTER TABLE users DROP COLUMN IF EXISTS avatar_url; ```

## Zero-Downtime Migration Strategy

For critical production changes, follow the expand-contract pattern:

``` Phase 1: EXPAND - Add new column/table (nullable or with default) - Deploy: app writes to BOTH old and new - Backfill existing data

Phase 2: MIGRATE - Deploy: app reads from NEW, writes to BOTH - Verify data consistency

Phase 3: CONTRACT - Deploy: app only uses NEW - Drop old column/table in separate migration ```

### Timeline Example

``` Day 1: Migration adds new_status column (nullable) Day 1: Deploy app v2 — writes to both status and new_status Day 2: Run backfill migration for existing rows Day 3: Deploy app v3 — reads from new_status only Day 7: Migration drops old status column ```

## Anti-Patterns

| Anti-Pattern | Why It Fails | Better Approach | |-------------|-------------|-----------------| | Manual SQL in production | No audit trail, unrepeatable | Always use migration files | | Editing deployed migrations | Causes drift between environments | Create new migration instead | | NOT NULL without default | Locks table, rewrites all rows | Add nullable, backfill, then add constraint | | Inline index on large table | Blocks writes during build | CREATE INDEX CONCURRENTLY | | Schema + data in one migration | Hard to rollback, long transactions | Separate migrations | | Dropping column before removing code | Application errors on missing column | Remove code first, drop column next deploy |

Intended uses

  • Use Database Migrations when this documented workflow matches the task.

Implementation notes

  • Review the source instructions and adapt inputs before running the workflow.

Try it, then tell us how it went

A copied prompt does not tell us whether the task worked. Report your result after checking the output. We collect your tool and outcome, never your files or prompt.