Database Migrations

by affaan-mef648e01899ba3e8dc6371642deaaf64b4477775No license275K starsListed Oct 9, 2026Updated Oct 9, 2026Repository updated 4 days ago

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.

Instructions onlySoftware Development DevOps & Cloud

AI-generated overview

Guidance for writing safe, reversible database schema and data migrations across several migration tools.

What it does: This skill provides migration patterns and checklists for altering production database schemas and data safely. It covers forward-only production changes, the expand-contract pattern for zero-downtime renames, concurrent index creation, batched backfills, and rollback planning. It also gives per-tool workflows and code examples for PostgreSQL, Prisma, Drizzle, Kysely, Django, and golang-migrate. The deliverable is written guidance and example migration code rather than an executable tool.
When to use it: Use it when writing or reviewing a schema or data migration, adding a column or index to a large table, planning a rollback, or preparing a zero-downtime deployment. It is also relevant when setting up migration tooling for a new project.
Requirements: No scripts are included; the skill is instructions and examples only. Applying the examples requires a database such as PostgreSQL and the relevant migration tooling (Prisma, Drizzle, Kysely, Django, or golang-migrate) in the user's own environment.

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

Every change is a migration — never alter production databases manually
Migrations are forward-only in production — rollbacks use new forward migrations
Schema and data migrations are separate — never mix DDL and DML in one migration
Test migrations against production-sized data — a migration that works on 100 rows may lock on 10M
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 lockALTER 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

-- GOOD: Nullable column, no lockALTER 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 tablesCREATE INDEX idx_users_email ON users (email);
-- GOOD: Non-blocking, allows concurrent writesCREATE INDEX CONCURRENTLY idx_users_email ON users (email);
-- Note: CONCURRENTLY cannot run inside a transaction block-- Most migration tools need special handling for this

-- BAD: Blocks writes on large tablesCREATE INDEX idx_users_email ON users (email);
-- GOOD: Non-blocking, allows concurrent writesCREATE 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;

-- 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 migrationALTER TABLE orders DROP COLUMN legacy_status;
-- For Django: use SeparateDatabaseAndState to remove from model-- without generating DROP COLUMN (then drop in next migration)

-- Step 1: Remove all application references to the column-- Step 2: Deploy application without the column reference-- Step 3: Drop column in next migrationALTER 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 progressDO $$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 $$;

-- BAD: Updates all rows in one transaction (locks table)UPDATE users SET normalized_email = LOWER(email);
-- GOOD: Batch update with progressDO $$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 changesnpx prisma migrate dev --name add_user_avatar
# Apply pending migrations in productionnpx prisma migrate deploy
# Reset database (dev only)npx prisma migrate reset
# Generate client after schema changesnpx prisma generate

# Create migration from schema changesnpx prisma migrate dev --name add_user_avatar
# Apply pending migrations in productionnpx prisma migrate deploy
# Reset database (dev only)npx prisma migrate reset
# Generate client after schema changesnpx 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])}

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 manuallynpx prisma migrate dev --create-only --name add_email_index

# Create empty migration, then edit the SQL manuallynpx 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 manuallyCREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users (email);

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

Drizzle (TypeScript/Node.js)

Workflow

bash

# Generate migration from schema changesnpx drizzle-kit generate
# Apply migrationsnpx drizzle-kit migrate
# Push schema directly (dev only, no migration file)npx drizzle-kit push

# Generate migration from schema changesnpx drizzle-kit generate
# Apply migrationsnpx 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(),});

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 filekysely migrate make add_user_avatar
# Apply all pending migrationskysely migrate latest
# Rollback last migrationkysely migrate down
# Show migration statuskysely migrate list

# Initialize config file (kysely.config.ts)kysely init
# Create a new migration filekysely migrate make add_user_avatar
# Apply all pending migrationskysely migrate latest
# Rollback last migrationkysely migrate down
# Show migration statuskysely migrate list

Migration File

typescript

// migrations/2024_01_15_001_create_user_profile.tsimport { 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()}

// migrations/2024_01_15_001_create_user_profile.tsimport { 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 directlyimport { fileURLToPath } from 'url'const migrationFolder = path.join(  path.dirname(fileURLToPath(import.meta.url)),  './migrations',)
// `db` is your Kysely<any> database instanceconst 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)}

import { Migrator, FileMigrationProvider } from 'kysely'import { promises as fs } from 'fs'import * as path from 'path'// ESM only — CJS can use __dirname directlyimport { fileURLToPath } from 'url'const migrationFolder = path.join(  path.dirname(fileURLToPath(import.meta.url)),  './migrations',)
// `db` is your Kysely<any> database instanceconst 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 changespython manage.py makemigrations
# Apply migrationspython manage.py migrate
# Show migration statuspython manage.py showmigrations
# Generate empty migration for custom SQLpython manage.py makemigrations --empty app_name -n description

# Generate migration from model changespython manage.py makemigrations
# Apply migrationspython manage.py migrate
# Show migration statuspython manage.py showmigrations
# Generate empty migration for custom SQLpython 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),    ]

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        ),    ]

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 pairmigrate create -ext sql -dir migrations -seq add_user_avatar
# Apply all pending migrationsmigrate -path migrations -database "$DATABASE_URL" up
# Rollback last migrationmigrate -path migrations -database "$DATABASE_URL" down 1
# Force version (fix dirty state)migrate -path migrations -database "$DATABASE_URL" force VERSION

# Create migration pairmigrate create -ext sql -dir migrations -seq add_user_avatar
# Apply all pending migrationsmigrate -path migrations -database "$DATABASE_URL" up
# Rollback last migrationmigrate -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.sqlALTER 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.sqlDROP INDEX IF EXISTS idx_users_avatar;ALTER TABLE users DROP COLUMN IF EXISTS avatar_url;

-- migrations/000003_add_user_avatar.up.sqlALTER 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.sqlDROP 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

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_statusDay 2: Run backfill migration for existing rowsDay 3: Deploy app v3 — reads from new_status onlyDay 7: Migration drops old status column

Day 1: Migration adds new_status column (nullable)Day 1: Deploy app v2 — writes to both status and new_statusDay 2: Run backfill migration for existing rowsDay 3: Deploy app v3 — reads from new_status onlyDay 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

Source and attribution

Source:affaan-m/ECCinskills/database-migrationsat commitef648e0

License: No license

Content belongs to its original authors. SourceWeft indexes it from a public repository.

Report or request removal

Database Migrations

Database Migration Patterns

When to Activate

Core Principles

Migration Safety Checklist

PostgreSQL Patterns

Adding a Column Safely

Adding an Index Without Downtime

Renaming a Column (Zero-Downtime)

Removing a Column Safely

Large Data Migrations

Prisma (TypeScript/Node.js)

Workflow

Schema Example

Custom SQL Migration

Drizzle (TypeScript/Node.js)

Workflow

Schema Example

Kysely (TypeScript/Node.js)

Workflow (kysely-ctl)

Migration File

Programmatic Migrator

Django (Python)

Workflow

Data Migration

SeparateDatabaseAndState

golang-migrate (Go)

Workflow

Migration Files

Zero-Downtime Migration Strategy

Timeline Example

Anti-Patterns

Source and attribution

More from affaan-m/ECC

Unified Notifications Ops

Verification Loop

Ui To Vue

Unified Memory

Visa Doc Translate

Team Builder

More in Software Development

Playground

M5 Onboard

Cardputer Buddy

Web Design Guidelines

Vercel React Native Skills

Vercel React Best Practices