database-migration

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Database Migration

数据库迁移

Safe patterns for evolving database schemas in production.

生产环境中数据库 schema 演进的安全模式。

Migration Principles

迁移原则

Backward compatible - New code works with old schema
Reversible - Can rollback if needed
Tested - Verify on staging before production
Incremental - Small changes, not big-bang
Zero downtime - No service interruption

向后兼容 - 新代码可适配旧schema
可回滚 - 必要时可回滚
经过测试 - 上线前先在预发布环境验证
增量式 - 小步变更，避免大爆炸式修改
零停机 - 无服务中断

Safe Migration Pattern

安全迁移模式

Phase 1: Add New (Compatible)

阶段1：新增兼容项

sql

-- Add new column (nullable initially)
ALTER TABLE users ADD COLUMN full_name VARCHAR(255) NULL;

-- Deploy new code that writes to both old and new
UPDATE users SET full_name = CONCAT(first_name, ' ', last_name);

sql

-- 新增字段（初始设为可空）
ALTER TABLE users ADD COLUMN full_name VARCHAR(255) NULL;

-- 部署同时读写新旧字段的新代码
UPDATE users SET full_name = CONCAT(first_name, ' ', last_name);

Phase 2: Migrate Data

阶段2：数据迁移

sql

-- Backfill existing data
UPDATE users
SET full_name = CONCAT(first_name, ' ', last_name)
WHERE full_name IS NULL;

sql

-- 回填现有数据
UPDATE users
SET full_name = CONCAT(first_name, ' ', last_name)
WHERE full_name IS NULL;

Phase 3: Make Required

阶段3：设为必填

sql

-- Make column required
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

sql

-- 将字段设为必填
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

Phase 4: Remove Old (After New Code Deployed)

阶段4：移除旧项（新代码部署完成后）

sql

-- Remove old columns
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;

sql

-- 移除旧字段
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;

Common Migrations

常见迁移操作

Adding Index

新增索引

sql

-- Create index concurrently (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

sql

-- 以并发方式创建索引（PostgreSQL）
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Renaming Column

重名字段

sql

-- Phase 1: Add new column
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);

-- Phase 2: Copy data
UPDATE users SET email_address = email;

-- Phase 3: Drop old column (after deploy)
ALTER TABLE users DROP COLUMN email;

sql

-- 阶段1：新增字段
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);

-- 阶段2：复制数据
UPDATE users SET email_address = email;

-- 阶段3：删除旧字段（部署完成后）
ALTER TABLE users DROP COLUMN email;

Changing Column Type

修改字段类型

sql

-- Phase 1: Add new column with new type
ALTER TABLE products ADD COLUMN price_cents INTEGER;

-- Phase 2: Migrate data
UPDATE products SET price_cents = CAST(price * 100 AS INTEGER);

-- Phase 3: Drop old column
ALTER TABLE products DROP COLUMN price;
ALTER TABLE products RENAME COLUMN price_cents TO price;

sql

-- 阶段1：新增对应新类型的字段
ALTER TABLE products ADD COLUMN price_cents INTEGER;

-- 阶段2：迁移数据
UPDATE products SET price_cents = CAST(price * 100 AS INTEGER);

-- 阶段3：删除旧字段
ALTER TABLE products DROP COLUMN price;
ALTER TABLE products RENAME COLUMN price_cents TO price;

Adding Foreign Key

新增外键

sql

-- Add column first
ALTER TABLE orders ADD COLUMN user_id INTEGER NULL;

-- Populate data
UPDATE orders SET user_id = (
    SELECT id FROM users WHERE users.email = orders.user_email
);

-- Add foreign key
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id) REFERENCES users(id);

sql

-- 先新增字段
ALTER TABLE orders ADD COLUMN user_id INTEGER NULL;

-- 填充数据
UPDATE orders SET user_id = (
    SELECT id FROM users WHERE users.email = orders.user_email
);

-- 新增外键约束
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id) REFERENCES users(id);

Migration Tools

迁移工具

Python (Alembic)

python

undefined

python

undefined

Generate migration

生成迁移脚本

alembic revision --autogenerate -m "add user full_name"

Apply migration

执行迁移

alembic upgrade head

Rollback

回滚迁移

alembic downgrade -1

undefined

alembic downgrade -1

undefined

JavaScript (Knex)

javascript

// Create migration
knex migrate:make add_full_name

// Apply migrations
knex migrate:latest

// Rollback
knex migrate:rollback

javascript

// 创建迁移脚本
knex migrate:make add_full_name

// 执行所有待迁移脚本
knex migrate:latest

// 回滚迁移
knex migrate:rollback

Rails

ruby

undefined

ruby

undefined

Generate migration

生成迁移脚本

rails generate migration AddFullNameToUsers full_name:string

Run migrations

执行迁移

rails db:migrate

Rollback

回滚迁移

rails db:rollback

undefined

rails db:rollback

undefined

Testing Migrations

迁移测试

python

def test_migration_forward_backward():
    # Apply migration
    apply_migration("add_full_name")

    # Verify schema
    assert column_exists("users", "full_name")

    # Rollback
    rollback_migration()

    # Verify rollback
    assert not column_exists("users", "full_name")

python

def test_migration_forward_backward():
    # 执行迁移
    apply_migration("add_full_name")

    # 验证schema
    assert column_exists("users", "full_name")

    # 回滚迁移
    rollback_migration()

    # 验证回滚结果
    assert not column_exists("users", "full_name")

Dangerous Operations

危险操作

❌ Avoid in Production

❌ 生产环境需避免

sql

-- Locks table for long time
ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL;

-- Can't rollback
DROP TABLE old_users;

-- Breaks existing code immediately
ALTER TABLE users DROP COLUMN email;

sql

-- 会长时间锁表
ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL;

-- 无法回滚
DROP TABLE old_users;

-- 立即破坏现有代码
ALTER TABLE users DROP COLUMN email;

✅ Safe Alternatives

✅ 安全替代方案

sql

-- Add as nullable first
ALTER TABLE users ADD COLUMN email VARCHAR(255) NULL;

-- Rename instead of drop
ALTER TABLE old_users RENAME TO archived_users;

-- Keep old column until new code deployed
-- (multi-phase approach)

sql

-- 先设为可空再新增
ALTER TABLE users ADD COLUMN email VARCHAR(255) NULL;

-- 重命名而非删除
ALTER TABLE old_users RENAME TO archived_users;

-- 保留旧字段直至新代码部署完成
-- （采用多阶段迁移方式）

Rollback Strategy

回滚策略

sql

-- Every migration needs DOWN
-- UP
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- DOWN
ALTER TABLE users DROP COLUMN full_name;

sql

-- 每个迁移都需要定义回滚逻辑
-- 执行逻辑
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- 回滚逻辑
ALTER TABLE users DROP COLUMN full_name;

Decision Support

决策支持

Quick Decision Guide

快速决策指南

Making a schema change?

Breaking change (drops/modifies data) → Multi-phase migration (expand-contract)
Additive change (new columns/tables) → Single-phase migration
Large table (millions of rows) → Use CONCURRENTLY for indexes

Need zero downtime?

Schema change → Expand-contract pattern (5 phases)
Data migration (< 10k rows) → Synchronous in-migration
Data migration (> 1M rows) → Background worker pattern

Planning rollback?

Added new schema only → Simple DOWN migration
Modified/removed schema → Multi-phase rollback or fix forward
Cannot lose data → Point-in-time recovery (PITR)

Choosing migration tool?

Python/Django → Django Migrations
Python/SQLAlchemy → Alembic
Node.js/TypeScript → Prisma Migrate or Knex.js
Enterprise/multi-language → Flyway or Liquibase

→ See references/decision-trees.md for comprehensive decision frameworks

要执行schema变更？

破坏性变更（删除/修改数据）→ 多阶段迁移（扩容-收缩模式）
新增类变更（新字段/新表）→ 单阶段迁移
大表（数百万行数据）→ 对索引使用CONCURRENTLY

需要零停机？

Schema变更 → 扩容-收缩模式（5阶段）
数据迁移（<1万行）→ 同步内存迁移
数据迁移（>100万行）→ 后台任务模式

规划回滚？

仅新增了schema → 简单回滚迁移
修改/删除了schema → 多阶段回滚或向前修复
不能丢失数据 → 时间点恢复（PITR）

选择迁移工具？

Python/Django → Django Migrations
Python/SQLAlchemy → Alembic
Node.js/TypeScript → Prisma Migrate 或 Knex.js
企业级/多语言环境 → Flyway 或 Liquibase

→ 详见 references/decision-trees.md 获取完整决策框架

Troubleshooting

故障排查

Common Issues Quick Reference

常见问题速查

Migration failed halfway → Check database state, fix forward with repair migration

Schema drift detected → Use autogenerate to create reconciliation migration

Cannot rollback (no downgrade) → Create reverse migration or fix forward

Foreign key violation → Clean data before adding constraint, or add as NOT VALID

Migration locks table too long → Use CONCURRENTLY, add columns in phases, batch updates

Circular dependency → Create merge migration or reorder dependencies

→ See references/troubleshooting.md for detailed solutions with examples

迁移执行到一半失败 → 检查数据库状态，通过修复迁移脚本向前推进

检测到schema漂移 → 使用自动生成功能创建一致性迁移脚本

无法回滚（无降级逻辑） → 创建反向迁移脚本或向前修复

外键约束冲突 → 添加约束前清理数据，或设为NOT VALID

迁移锁表时间过长 → 使用CONCURRENTLY、分阶段新增字段、批量更新

循环依赖 → 创建合并迁移脚本或调整依赖顺序

→ 详见 references/troubleshooting.md 获取带示例的详细解决方案

Navigation

🌳 Decision Trees - Schema migration strategies, zero-downtime patterns, rollback strategies, migration tool selection, and data migration approaches. Load when planning migrations or choosing strategies.
🔧 Troubleshooting - Failed migration recovery, schema drift detection, migration conflicts, rollback failures, data integrity issues, and performance problems. Load when debugging migration issues.

🌳 决策树 - Schema迁移策略、零停机模式、回滚策略、迁移工具选择、数据迁移方案。规划迁移或选择策略时可查看。
🔧 故障排查 - 失败迁移恢复、schema漂移检测、迁移冲突、回滚失败、数据完整性问题、性能问题。调试迁移问题时可查看。

Remember

注意事项

Test migrations on copy of production data
Have rollback plan ready
Monitor during deployment
Communicate with team about schema changes
Keep migrations small and focused

在生产数据的副本上测试迁移
提前准备好回滚方案
部署过程中监控状态
与团队同步schema变更信息
保持迁移脚本小而聚焦

database-migration

Original

Translation

Database Migration

数据库迁移

Migration Principles

迁移原则

Safe Migration Pattern

安全迁移模式

Phase 1: Add New (Compatible)

阶段1：新增兼容项

Phase 2: Migrate Data

阶段2：数据迁移

Phase 3: Make Required

阶段3：设为必填

Phase 4: Remove Old (After New Code Deployed)

阶段4：移除旧项（新代码部署完成后）

Common Migrations

常见迁移操作

Adding Index

新增索引

Renaming Column

重名字段

Changing Column Type

修改字段类型

Adding Foreign Key

新增外键

Migration Tools

迁移工具

Python (Alembic)

Python (Alembic)

Generate migration

生成迁移脚本

Apply migration

执行迁移

Rollback

回滚迁移

JavaScript (Knex)

JavaScript (Knex)

Rails

Rails

Generate migration

生成迁移脚本

Run migrations

执行迁移

Rollback

回滚迁移

Testing Migrations

迁移测试

Dangerous Operations

危险操作

❌ Avoid in Production

❌ 生产环境需避免

✅ Safe Alternatives

✅ 安全替代方案

Rollback Strategy

回滚策略

Decision Support

决策支持

Quick Decision Guide

快速决策指南

Troubleshooting

故障排查

Common Issues Quick Reference

常见问题速查

Navigation

导航

Detailed References

详细参考资料

Remember

注意事项