335 lines
9.9 KiB
Markdown
335 lines
9.9 KiB
Markdown
---
|
||
name: database-migrations
|
||
description: 資料庫遷移最佳實踐，涵蓋架構變更、資料遷移、回滾以及 PostgreSQL、MySQL 與常見 ORM (Prisma, Drizzle, Django, TypeORM, golang-migrate) 的零停機部署。
|
||
---
|
||
|
||
# 資料庫遷移模式 (Database Migration Patterns)
|
||
|
||
針對生產系統提供安全且可逆的資料庫架構變更。
|
||
|
||
## 何時啟用
|
||
|
||
- 建立或修改資料庫資料表。
|
||
- 新增/移除欄位或索引。
|
||
- 執行資料遷移 (回填 Backfill、轉換 Transform)。
|
||
- 規劃零停機 (Zero-downtime) 的架構變更。
|
||
- 為新專案設置遷移工具。
|
||
|
||
## 核心原則
|
||
|
||
1. **所有的變更都是遷移 (Everything is a migration)** — 絕不要手動修改生產環境資料庫。
|
||
2. **生產環境中的遷移僅能向前執行 (Forward-only)** — 回滾操作應使用新的「向前」遷移來達成。
|
||
3. **架構遷移與資料遷移分離** — 絕不要在同一個遷移任務中混合 DDL (架構定義) 與 DML (資料操作)。
|
||
4. **針對與生產環境規模相當的資料進行測試** — 在 100 筆資料上能運行的遷移，在 1000 萬筆資料上可能會導致鎖定。
|
||
5. **遷移一旦部署即具備不可變性** — 絕不要編輯已在生產環境執行的遷移檔案。
|
||
|
||
## 遷移安全檢核清單
|
||
|
||
在套用任何遷移之前：
|
||
|
||
- [ ] 遷移具備 UP 與 DOWN 指令（或明確標記為不可逆）。
|
||
- [ ] 大型資料表不會發生全表鎖定 (使用併發 Concurrent 操作)。
|
||
- [ ] 新欄位具備預設值或允許為 Null (絕不要在沒有預設值的情況下新增 NOT NULL)。
|
||
- [ ] 以併發方式建立索引 (針對現有資料表，不要在 CREATE TABLE 內聯建立)。
|
||
- [ ] 資料回填路徑與架構變更分離成獨立的遷移任務。
|
||
- [ ] 已針對生產環境資料的複本進行測試。
|
||
- [ ] 已編寫回滾計畫。
|
||
|
||
## PostgreSQL 模式
|
||
|
||
### 安全新增欄位
|
||
|
||
```sql
|
||
-- 推薦 (GOOD)：允許 Null 的欄位，不會產生鎖定
|
||
ALTER TABLE users ADD COLUMN avatar_url TEXT;
|
||
|
||
-- 推薦 (GOOD)：帶有預設值的欄位 (Postgres 11+ 可即時完成，無需重寫檔案)
|
||
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;
|
||
|
||
-- 錯誤 (BAD)：在現有資料表新增 NOT NULL 且無預設值 (需要重寫整張表)
|
||
ALTER TABLE users ADD COLUMN role TEXT NOT NULL;
|
||
-- 這會鎖定資料表並重寫每一列資料
|
||
```
|
||
|
||
### 零停機新增索引
|
||
|
||
```sql
|
||
-- 錯誤 (BAD)：在大型資料表執行時會阻塞寫入操作
|
||
CREATE INDEX idx_users_email ON users (email);
|
||
|
||
-- 推薦 (GOOD)：非阻塞式，允許併發寫入
|
||
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
|
||
|
||
-- 註：CONCURRENTLY 無法在交易區塊 (Transaction block) 內執行
|
||
-- 大多數遷移工具需要對此進行特殊處理
|
||
```
|
||
|
||
### 欄位重新命名 (零停機)
|
||
|
||
絕不直接在生產環境重新命名。請使用「擴展-收縮 (Expand-contract)」模式：
|
||
|
||
```sql
|
||
-- 步驟 1：新增新欄位 (遷移 001)
|
||
ALTER TABLE users ADD COLUMN display_name TEXT;
|
||
|
||
-- 步驟 2：回填資料 (遷移 002，資料遷移)
|
||
UPDATE users SET display_name = username WHERE display_name IS NULL;
|
||
|
||
-- 步驟 3：更新應用程式程式碼以同時讀取/寫入兩個欄位
|
||
-- 部署應用程式變更
|
||
|
||
-- 步驟 4：停止寫入舊欄位並移除之 (遷移 003)
|
||
ALTER TABLE users DROP COLUMN username;
|
||
```
|
||
|
||
### 安全移除欄位
|
||
|
||
```sql
|
||
-- 步驟 1：移除應用程式中對該欄位的所有參照
|
||
-- 步驟 2：部署不含該欄位參照的應用程式版本
|
||
-- 步驟 3：在下一次遷移中移除欄位
|
||
ALTER TABLE orders DROP COLUMN legacy_status;
|
||
|
||
-- 針對 Django：使用 SeparateDatabaseAndState 從模型中移除
|
||
-- 但不生成 DROP COLUMN (隨後在下一個遷移中正式移除)
|
||
```
|
||
|
||
### 大型資料遷移
|
||
|
||
```sql
|
||
-- 錯誤 (BAD)：在單一交易中更新所有列 (會鎖定資料表)
|
||
UPDATE users SET normalized_email = LOWER(email);
|
||
|
||
-- 推薦 (GOOD)：分批更新並顯示進度
|
||
DO $$
|
||
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 '已更新 % 列', rows_updated;
|
||
EXIT WHEN rows_updated = 0;
|
||
COMMIT;
|
||
END LOOP;
|
||
END $$;
|
||
```
|
||
|
||
## Prisma (TypeScript/Node.js)
|
||
|
||
### 工作流
|
||
|
||
```bash
|
||
# 根據架構變更建立遷移
|
||
npx prisma migrate dev --name add_user_avatar
|
||
|
||
# 在生產環境部署掛起的遷移
|
||
npx prisma migrate deploy
|
||
|
||
# 重置資料庫 (僅限開發環境)
|
||
npx prisma migrate reset
|
||
|
||
# 架構變更後生成用戶端
|
||
npx prisma generate
|
||
```
|
||
|
||
### Schema 範例
|
||
|
||
```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])
|
||
}
|
||
```
|
||
|
||
### 自定義 SQL 遷移
|
||
|
||
針對 Prisma 無法表達的操作 (併發索引、資料回填)：
|
||
|
||
```bash
|
||
# 建立僅含核心結構的遷移檔案，隨後手動編輯 SQL
|
||
npx prisma migrate dev --create-only --name add_email_index
|
||
```
|
||
|
||
```sql
|
||
-- migrations/20240115_add_email_index/migration.sql
|
||
-- Prisma 無法生成 CONCURRENTLY，因此我們手動撰寫
|
||
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users (email);
|
||
```
|
||
|
||
## Drizzle (TypeScript/Node.js)
|
||
|
||
### 工作流
|
||
|
||
```bash
|
||
# 根據架構變更生成遷移
|
||
npx drizzle-kit generate
|
||
|
||
# 套用遷移
|
||
npx drizzle-kit migrate
|
||
|
||
# 直接推送架構 (僅限開發環境，不生成遷移檔案)
|
||
npx drizzle-kit push
|
||
```
|
||
|
||
### Schema 範例
|
||
|
||
```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)
|
||
|
||
### 工作流
|
||
|
||
```bash
|
||
# 根據模型變更生成遷移任務
|
||
python manage.py makemigrations
|
||
|
||
# 套用遷移
|
||
python manage.py migrate
|
||
|
||
# 顯示遷移狀態
|
||
python manage.py showmigrations
|
||
|
||
# 為自定義 SQL 生成空白遷移檔案
|
||
python manage.py makemigrations --empty app_name -n 描述
|
||
```
|
||
|
||
### 資料遷移
|
||
|
||
```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 # 資料遷移，通常無需反向操作
|
||
|
||
class Migration(migrations.Migration):
|
||
dependencies = [("accounts", "0015_add_display_name")]
|
||
|
||
operations = [
|
||
migrations.RunPython(backfill_display_names, reverse_backfill),
|
||
]
|
||
```
|
||
|
||
### SeparateDatabaseAndState
|
||
|
||
從 Django 模型中移除欄位，但不立即從資料庫中刪除：
|
||
|
||
```python
|
||
class Migration(migrations.Migration):
|
||
operations = [
|
||
migrations.SeparateDatabaseAndState(
|
||
state_operations=[
|
||
migrations.RemoveField(model_name="user", name="legacy_field"),
|
||
],
|
||
database_operations=[], # 先不更動資料庫
|
||
),
|
||
]
|
||
```
|
||
|
||
## golang-migrate (Go)
|
||
|
||
### 工作流
|
||
|
||
```bash
|
||
# 建立成對的遷移檔案
|
||
migrate create -ext sql -dir migrations -seq add_user_avatar
|
||
|
||
# 套用所有掛起的遷移
|
||
migrate -path migrations -database "$DATABASE_URL" up
|
||
|
||
# 回滾上一次遷移
|
||
migrate -path migrations -database "$DATABASE_URL" down 1
|
||
|
||
# 強制更新版本 (修復 dirty 狀態)
|
||
migrate -path migrations -database "$DATABASE_URL" force 版本號
|
||
```
|
||
|
||
### 遷移檔案
|
||
|
||
```sql
|
||
-- migrations/000003_add_user_avatar.up.sql
|
||
ALTER 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.sql
|
||
DROP INDEX IF EXISTS idx_users_avatar;
|
||
ALTER TABLE users DROP COLUMN IF EXISTS avatar_url;
|
||
```
|
||
|
||
## 零停機遷移策略
|
||
|
||
對於關鍵的生產環境變更，請遵循「擴展-收縮 (expand-contract)」模式：
|
||
|
||
```
|
||
階段 1：擴展 (EXPAND)
|
||
- 新增新欄位/資料表 (設為可 null 或具備預設值)
|
||
- 部署：應用程式同時寫入「舊」與「新」欄位
|
||
- 回填現有資料
|
||
|
||
階段 2：遷移 (MIGRATE)
|
||
- 部署：應用程式從「新」欄位讀取，但維持寫入「兩者」
|
||
- 驗證資料一致性
|
||
|
||
階段 3：收縮 (CONTRACT)
|
||
- 部署：應用程式僅使用「新」欄位
|
||
- 在獨立的遷移任務中移除「舊」欄位/資料表
|
||
```
|
||
|
||
### 時間軸範例
|
||
|
||
```
|
||
第 1 天：遷移任務新增 new_status 欄位 (可為 Null)
|
||
第 1 天：部署 app v2 — 同時寫入 status 與 new_status
|
||
第 2 天：針對現有列執行回填遷移
|
||
第 3 天：部署 app v3 — 僅從 new_status 讀取
|
||
第 7 天：遷移任務移除舊的 status 欄位
|
||
```
|
||
|
||
## 反模式
|
||
|
||
| 反模式 | 失敗原因 | 更好的做法 |
|
||
|-------------|-------------|-----------------|
|
||
| 在生產環境手動執行 SQL | 無審計追蹤，不可重複執行 | 始終使用遷移檔案 |
|
||
| 編輯已部署的遷移 | 導致環境間出現差異 | 建立新的遷移任務 |
|
||
| NOT NULL 且無預設值 | 鎖定資料表，重寫所有列 | 先新增可為 Null 的欄位，回填後再加入約束 |
|
||
| 在大型資料表使用內聯索引 | 建置期間會阻塞寫入 | 使用 CREATE INDEX CONCURRENTLY |
|
||
| 架構 + 資料混在同一個遷移 | 難以回退，且交易時間過長 | 將兩者分離成獨立遷移 |
|
||
| 移除程式碼前先刪除欄位 | 應用程式因找不到欄位而報錯 | 先移除程式碼參照，在下次部署才移除欄位 |
|