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 Development32K6周前更新

Vercel React Native Skills

vercel-labs

精选

React Native 与 Expo 最佳实践规则，涵盖列表性能、动画、导航、UI 模式、状态与 monorepo 配置。

Software Development32K6周前更新

Vercel React Best Practices

vercel-labs

精选

Vercel 的 React 与 Next.js 性能指南，以 70 条规则的形式用于编写、审查和重构代码。

Software Development32K6周前更新