---
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 |
| 架構 + 資料混在同一個遷移 | 難以回退，且交易時間過長 | 將兩者分離成獨立遷移 |
| 移除程式碼前先刪除欄位 | 應用程式因找不到欄位而報錯 | 先移除程式碼參照，在下次部署才移除欄位 |