Database Migrations

affaan-m/ECC/.kiro/skills/database-migrations

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

Database migration best practices for schema changes, data migrations, rollbacks, and zero-downtime deployments across PostgreSQL, MySQL, and common ORMs (Prisma, Drizzle, Django, TypeORM, golang-migrate). Use when planning or implementing database schema changes.

僅含說明Software DevelopmentDevOps & Cloud
AI 產生的概覽

安全、可回復的資料庫結構與資料遷移最佳實務，支援零停機部署。

功能
提供跨 PostgreSQL、MySQL 及常見 ORM 的遷移指引，涵蓋結構變更、資料回填、回復與零停機部署。內容包括安全檢查清單、並行建立索引與批次更新等 SQL 模式、Prisma、Drizzle、Django 和 golang-migrate 的工具工作流程、擴展-收縮策略以及反模式清單。
適用情境
適用於規劃或實作資料庫結構變更、新增或移除欄位與索引、執行資料遷移、建置遷移工具，或審查遷移提取要求。也適合排查遷移問題與設計零停機部署。
執行需求
不需要指令碼或工具，僅為說明性內容。套用這些模式需要可存取的資料庫，以及 Prisma、Drizzle、Django、TypeORM 或 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

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

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 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 $$;

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

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 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);

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

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(),});

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

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 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;

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_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-PatternWhy It FailsBetter Approach
Manual SQL in productionNo audit trail, unrepeatableAlways use migration files
Editing deployed migrationsCauses drift between environmentsCreate new migration instead
NOT NULL without defaultLocks table, rewrites all rowsAdd nullable, backfill, then add constraint
Inline index on large tableBlocks writes during buildCREATE INDEX CONCURRENTLY
Schema + data in one migrationHard to rollback, long transactionsSeparate migrations
Dropping column before removing codeApplication errors on missing columnRemove code first, drop column next deploy

When to Use This Skill

  • Planning database schema changes
  • Implementing zero-downtime migrations
  • Setting up migration tooling
  • Troubleshooting migration issues
  • Reviewing migration pull requests

來源與署名

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

授權條款： 無授權條款

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

檢舉或申請下架

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

Unified Notifications Ops

affaan-m

設計統一的通知策略，對來自 GitHub、Linear、鉤子與聊天工具的通知進行分類、路由、去重與升級。

Productivity & Workflow275K4 天前更新

Verification Loop

affaan-m

執行六階段程式碼驗證流程，並產出 PASS/FAIL 就緒報告。

Software Development275K4 天前更新

Ui To Vue

affaan-m

將批次 UI 設計截圖轉換為使用 Vant、Element Plus 或 Ant Design Vue 的 Vue 3 元件。

Software Development275K4 天前更新

Unified Memory

affaan-m

指導代理透過本機 ECC Memory Vault 在不同執行框架之間共享持久脈絡與交接資訊。

AI & Agents275K4 天前更新

Visa Doc Translate

affaan-m

透過 OCR 將簽證文件影像翻譯成英文，並產出原圖與譯文對照的雙語 PDF。

Documents & Office275K4 天前更新

Team Builder

affaan-m

互動式挑選器：探索可用的代理角色，讓使用者最多選五個，平行執行同一任務並彙整結果。

AI & Agents275K4 天前更新

更多Software Development技能

Playground

anthropics

精選

建立自成一體的互動式 HTML 遊樂場，包含控制項、即時預覽與可複製的提示詞輸出。

Software Development37K今天更新

M5 Onboard

anthropics

精選

透過 USB 偵測 M5Stack ESP32 開發板，燒錄 UIFlow 2.0 韌體並安裝 MicroPython 應用程式套件。

Software Development37K今天更新

Cardputer Buddy

anthropics

精選

指導迭代 Cardputer-Adv 的 MicroPython 應用程式套件：新增應用程式、透過 USB 序列埠推送檔案、查看日誌並執行 REPL 指令。

Software Development37K今天更新

Web Design Guidelines

vercel-labs

精選

依據從遠端來源取得的 Web Interface Guidelines 審查 UI 程式碼，並以 file:line 形式回報問題。

Software Development32K5 週前更新

Vercel React Native Skills

vercel-labs

精選

React Native 與 Expo 最佳實務規則，涵蓋清單效能、動畫、導覽、UI 模式、狀態與 monorepo 設定。

Software Development32K5 週前更新

Vercel React Best Practices

vercel-labs

精選

Vercel 的 React 與 Next.js 效能指南，以 70 條規則的形式用於撰寫、審查與重構程式碼。

Software Development32K5 週前更新