数据库迁移
Evolve 你的 Schema safely 使用 versioned, reversible 迁移 scripts.
你将学到
- 什么迁移是 and 为什么 manual Schema 更改是 dangerous
- 上/下模式 for reversible 迁移
- Versioning and 排序迁移 sequentially
- 运行迁移 in CI/CD and production
- 常见迁移 pitfalls and 如何 avoid 它们
概念讲解
Why Migrations?
In development, you can drop and recreate a database freely. In production, the database has real data that cannot be lost. Migrations are versioned, incremental scripts that evolve the schema over time — adding a column here, creating a table there — without losing data. They are the database equivalent of version control for your schema.
The Up/Down Pattern
Each migration has two parts: up (apply the change) and down (revert it). The up migration for "添加 email 列到用户" is ALTER TABLE 用户 ADD COLUMN email VARCHAR(255). The down migration is ALTER TABLE 用户 DROP COLUMN email. Not all migrations are reversible (you cannot un-delete a dropped table's data), but striving for reversibility lets you roll back a bad deployment.
Versioning and Ordering
Migrations are numbered sequentially or timestamped (e.g. 20250115_120000_add_email_to_users.sql). The migration tool tracks which migrations have been applied in a schema_migrations table. On deploy, it runs all pending migrations in order. This ensures every environment has the same schema state.
Migration Tools
Every major framework has a migration tool: Rails (db:migrate), Django (makemigrations / migrate), Prisma (prisma migrate), Drizzle (drizzle-kit migrate), Flyway, Liquibase. They handle versioning, ordering, and tracking. Use the tool that integrates with your stack — do not roll your own.
Pitfalls
- Long-running migrations on large tables can lock the database. Break them into smaller steps or use online schema change tools (pt-online-schema-change, gh-ost).
- Destructive changes (DROP COLUMN, DROP TABLE) should be done in two phases: first stop using the column, then remove it in a later migration after a grace period.
- Data migrations (moving or transforming data, not just schema) should be separate from schema migrations and tested carefully.
示例代码
-- Migration 001: create users table
-- File: 001_create_users.up.sql
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- File: 001_create_users.down.sql
DROP TABLE IF EXISTS users;
-- Migration 002: add email column
-- File: 002_add_email_to_users.up.sql
ALTER TABLE users ADD COLUMN email VARCHAR(255);
CREATE UNIQUE INDEX idx_users_email ON users (email) WHERE email IS NOT NULL;
-- File: 002_add_email_to_users.down.sql
DROP INDEX IF EXISTS idx_users_email;
ALTER TABLE users DROP COLUMN email;
-- Migration 003: add orders table with FK
-- File: 003_create_orders.up.sql
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
total DECIMAL(10, 2) NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_orders_user_id ON orders (user_id);
-- File: 003_create_orders.down.sql
DROP TABLE IF EXISTS orders;
Three 迁移使用上 and 下: 创建用户表, 添加 email 列使用部分唯一索引, and 创建顺序表 使用外键.
动手试一试
- SQL Formatter
格式迁移 scripts for readability and review.
- Code Diff
Compare 上 and 下迁移到 验证 reversibility.
常见错误
错误写法
编写 irreversible 迁移 (否下 script)
正确做法
总是编写下 迁移当 可能. For truly irreversible 更改 (如 dropping 表), document 为什么 and 制作下 迁移否-op 使用 comment.
错误写法
运行迁移那个 locks 大表 for 也长
正确做法
For 大表, break 迁移到 steps: 添加列 (nullable), backfill 数据 in batches, 添加 constraints, 移除旧 列. Use online Schema 更改 tools for huge 表.
练习 数据库迁移
3 个练习
查阅不认识的术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
动手做项目
通过引导式项目应用所学,含起始代码与解决方案。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。