Database Migrations

affaan-m/ECC/pi/core/skills/database-migrations

作者 affaan-mef648e01899ba3e8dc6371642deaaf64b4477775無授權條款275K 個星標收錄於 2026年10月9日更新於 2026年10月9日儲存庫4 天前更新

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.

僅含說明Software Development DevOps & Cloud

AI 產生的概覽

提供跨多種工具撰寫安全、可回復資料庫結構與資料遷移的指引。

功能: 此技能提供在正式環境中安全變更資料庫結構與資料的遷移模式與檢查清單。內容涵蓋僅向前推進的正式環境變更、expand-contract 零停機重新命名、並行建立索引、分批回填以及回復規劃。它也提供 PostgreSQL、Prisma、Drizzle、Kysely、Django 與 golang-migrate 的分工具工作流程與範例，並列出反模式。它產出的是書面指引與範例遷移程式碼，本身不執行任何操作。
適用情境: 適用於撰寫結構或資料遷移、為大型資料表新增欄位或索引、規劃回復，或準備零停機部署時。也適合為新專案建立遷移工具鏈。
執行需求: 不需要指令碼或執行環境，僅為說明性內容。範例涉及 PostgreSQL 以及 Prisma、Drizzle、Kysely、Django、golang-migrate 等工具，但閱讀這些指引不需要安裝任何東西。

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

來源與署名

來源：affaan-m/ECC位於pi/core/skills/database-migrations提交ef648e0

授權條款：無授權條款

內容歸原作者所有。SourceWeft 從公開儲存庫中收錄這些內容。

檢舉或申請下架

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

來源與署名

更多來自 affaan-m/ECC 的技能

Unified Notifications Ops

Verification Loop

Ui To Vue

Unified Memory

Visa Doc Translate

Team Builder

更多Software Development技能

Playground

M5 Onboard

Cardputer Buddy

Web Design Guidelines

Vercel React Native Skills

Vercel React Best Practices