Claude Code数据库全链路实战：从Schema设计到零停机迁移

## Database Configuration

- Engine: PostgreSQL 16
- ORM: Drizzle ORM (drizzle-kit for migrations)
- Migration tool: drizzle-kit generate + drizzle-kit migrate
- Connection: DATABASE_URL env variable (never commit credentials)
- Schema location: src/db/schema/
- Migrations location: drizzle/
- Naming convention: snake_case for tables/columns
- Always generate both UP and DOWN migrations
- Never use SELECT * in production queries
- All timestamps use timestamptz (UTC)
- Soft delete via deleted_at column, never physical DELETE

flowchart LR
    A[业务需求描述] --> B[Claude Code分析]
    B --> C[生成ER图]
    C --> D[DDL脚本]
    D --> E[ORM Schema]
    E --> F[迁移文件]
    F --> G[测试数据]

    style A fill:#e1f5fe
    style G fill:#c8e6c9

> 我需要设计一个电商系统的数据库。包含用户、商品、订单、支付四个核心实体。
> 用户有注册信息和地址。商品有分类和库存。订单包含多个商品项。支付关联订单。
> 请生成Mermaid ER图和PostgreSQL DDL。

erDiagram
    users {
        uuid id PK
        varchar email UK
        varchar password_hash
        varchar phone
        timestamptz created_at
        timestamptz deleted_at
    }

    addresses {
        uuid id PK
        uuid user_id FK
        varchar province
        varchar city
        varchar district
        varchar detail
        boolean is_default
    }

    categories {
        uuid id PK
        varchar name
        uuid parent_id FK
        int sort_order
    }

    products {
        uuid id PK
        uuid category_id FK
        varchar name
        text description
        decimal price
        int stock
        varchar sku UK
        timestamptz created_at
    }

    orders {
        uuid id PK
        uuid user_id FK
        varchar order_no UK
        varchar status
        decimal total_amount
        timestamptz created_at
    }

    order_items {
        uuid id PK
        uuid order_id FK
        uuid product_id FK
        int quantity
        decimal unit_price
        decimal subtotal
    }

    payments {
        uuid id PK
        uuid order_id FK
        varchar payment_no UK
        varchar channel
        varchar status
        decimal amount
        timestamptz paid_at
    }

    users ||--o{ addresses : "has"
    users ||--o{ orders : "places"
    categories ||--o{ categories : "parent"
    categories ||--o{ products : "contains"
    orders ||--o{ order_items : "includes"
    products ||--o{ order_items : "referenced in"
    orders ||--o| payments : "paid via"

// src/db/schema/users.ts
import { pgTable, uuid, varchar, timestamp, boolean } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  passwordHash: varchar('password_hash', { length: 255 }).notNull(),
  phone: varchar('phone', { length: 20 }),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  deletedAt: timestamp('deleted_at', { withTimezone: true }),
});

export const addresses = pgTable('addresses', {
  id: uuid('id').defaultRandom().primaryKey(),
  userId: uuid('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
  province: varchar('province', { length: 50 }).notNull(),
  city: varchar('city', { length: 50 }).notNull(),
  district: varchar('district', { length: 50 }).notNull(),
  detail: varchar('detail', { length: 500 }).notNull(),
  isDefault: boolean('is_default').default(false).notNull(),
});

// src/db/schema/products.ts
import { pgTable, uuid, varchar, text, numeric, integer, timestamp } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { categories } from './categories';

export const products = pgTable('products', {
  id: uuid('id').defaultRandom().primaryKey(),
  categoryId: uuid('category_id').references(() => categories.id),
  name: varchar('name', { length: 255 }).notNull(),
  description: text('description'),
  price: numeric('price', { precision: 10, scale: 2 }).notNull(),
  stock: integer('stock').default(0).notNull(),
  sku: varchar('sku', { length: 50 }).notNull().unique(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
});

export const productsRelations = relations(products, ({ one, many }) => ({
  category: one(categories, {
    fields: [products.categoryId],
    references: [categories.id],
  }),
}));

> 请根据上面的DDL，生成完整的Drizzle ORM Schema文件。
> 每张表一个文件，放在src/db/schema/目录下。
> 包含所有关系定义(relations)。
> 使用snake_case映射，TypeScript属性用camelCase。

# 生成迁移文件
npx drizzle-kit generate

# 预期输出：
# 1 tables
# users 2 columns 1 indexes 1 fks
# addresses 6 columns 0 indexes 1 fks
# ...
# Your SQL migration file ➔ drizzle/0000_initial_schema.sql

> 请检查 drizzle/0000_initial_schema.sql 的内容，确认：
> 1. 所有外键都有ON DELETE策略
> 2. UUID字段使用gen_random_uuid()
> 3. created_at默认值为now()
> 4. 没有遗漏的索引（email、sku、order_no需要唯一索引）

# Claude Code可直接执行
npx drizzle-kit migrate

// .claude/settings.json
{
  "mcpServers": {
    "dbhub": {
      "command": "npx",
      "args": ["-y", "@anthropic-ai/dbhub@latest"],
      "env": {
        "DATABASE_URL": "postgresql://user:pass@localhost:5432/myapp"
      }
    }
  }
}

claude mcp add dbhub -- npx -y @anthropic-ai/dbhub@latest

> 我的订单列表页面加载很慢。查询是这样的：
> 
> SELECT o.*, u.email, COUNT(oi.id) as item_count, SUM(oi.subtotal) as total
> FROM orders o
> JOIN users u ON o.user_id = u.id
> JOIN order_items oi ON oi.order_id = o.id
> WHERE o.user_id = 'xxx' AND o.status = 'completed'
> GROUP BY o.id, u.email
> ORDER BY o.created_at DESC
> LIMIT 20;
>
> 请分析并优化这个查询。

-- 问题诊断：
-- 1. GROUP BY包含o.id但SELECT用了o.*，导致所有字段都需要分组
-- 2. JOIN users仅为了获取email，可以用子查询替代
-- 3. 缺少复合索引(user_id, status, created_at)

-- 优化步骤一：添加复合索引
CREATE INDEX CONCURRENTLY idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);

-- 优化步骤二：添加order_items的覆盖索引
CREATE INDEX CONCURRENTLY idx_order_items_order_subtotal
ON order_items (order_id, subtotal);

-- 优化步骤三：重写查询
SELECT
    o.id, o.order_no, o.status, o.total_amount, o.created_at,
    u.email,
    oi_summary.item_count,
    oi_summary.subtotal_sum
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN LATERAL (
    SELECT COUNT(*) as item_count, SUM(subtotal) as subtotal_sum
    FROM order_items WHERE order_id = o.id
) oi_summary ON TRUE
WHERE o.user_id = $1 AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 20;

> 我需要给orders表添加一个可选的coupon_code字段（varchar 50）。
> 数据库当前正在服务线上流量，必须零停机。
> 请生成完整的迁移计划，包括回滚方案。

flowchart TD
    A[阶段1: 添加可空列] --> B[部署新代码写入新列]
    B --> C[阶段2: 回填历史数据]
    C --> D[部署新代码读取新列]
    D --> E[阶段3: 添加NOT NULL约束]

    style A fill:#fff9c4
    style C fill:#fff9c4
    style E fill:#fff9c4

-- Migration: 0005_add_coupon_code_nullable.sql
-- 阶段1: 安全地添加可空列，不会锁表
ALTER TABLE orders ADD COLUMN coupon_code varchar(50);

-- 添加索引（CONCURRENTLY不锁表）
CREATE INDEX CONCURRENTLY idx_orders_coupon_code
ON orders (coupon_code)
WHERE coupon_code IS NOT NULL;

-- Down migration:
-- ALTER TABLE orders DROP COLUMN IF EXISTS coupon_code;

-- batch_backfill_coupon.sql
-- 每批1000行，避免长事务
DO $$
DECLARE
  batch_count int := 0;
  total_updated int := 0;
BEGIN
  LOOP
    UPDATE orders
    SET coupon_code = 'DEFAULT'
    WHERE id IN (
      SELECT id FROM orders
      WHERE coupon_code IS NULL
      LIMIT 1000
    );

    GET DIAGNOSTICS batch_count = ROW_COUNT;
    total_updated := total_updated + batch_count;

    EXIT WHEN batch_count = 0;

    -- 每批之间暂停100ms，让其他查询获得锁
    PERFORM pg_sleep(0.1);

    RAISE NOTICE 'Updated % rows, total: %', batch_count, total_updated;
  END LOOP;
END $$;

-- Migration: 0006_add_coupon_code_not_null.sql
-- 阶段3: 数据已回填，安全添加约束
ALTER TABLE orders
ALTER COLUMN coupon_code SET NOT NULL,
ALTER COLUMN coupon_code SET DEFAULT '';

> 请为上面的电商数据库生成测试数据：
> - 100个用户，每个有1-3个地址
> - 500个商品，分布在20个分类
> - 1000个订单，每个包含1-5个商品项
> - 80%的订单已完成支付
> 使用 INSERT ... 语句，数据要真实（中国姓名、城市、商品名）

mkdir ecommerce-db && cd ecommerce-db
npm init -y
npm install drizzle-orm pg
npm install -D drizzle-kit typescript @types/pg
npx tsc --init

# 初始化Claude Code项目
claude init

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

// src/db/index.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export const db = drizzle(pool, { schema });
export type Database = typeof db;

你: 请检查整个数据库Schema，找出可能的设计问题

Claude Code:
分析 src/db/schema/ 下所有文件后，发现以下问题：

1. orders.total_amount 应该由 order_items.subtotal 计算得出，
   不应该存储冗余数据，或者需要事务保证一致性
2. products.stock 需要并发安全机制，建议使用乐观锁或
   UPDATE ... WHERE stock >= quantity
3. payments 缺少幂等键(idempotency_key)，
   支付渠道回调可能重复
4. addresses 缺少 created_at 字段

建议修复方案：
[生成具体代码修改]

你: 采纳所有建议，请生成迁移文件

Claude Code:
已生成 drizzle/0007_fix_schema_issues.sql
包含UP和DOWN迁移...

graph TD
    CC[Claude Code] --> DH[DBHub MCP]
    CC --> PS[Prisma Studio MCP]
    CC --> AT[Atlas MCP]

    DH --> PG[PostgreSQL]
    DH --> MY[MySQL]
    DH --> SQ[SQLite]
    DH --> MS[SQL Server]

    PS --> PRISMA[Prisma Schema]
    AT --> HCL[Atlas HCL]

    style CC fill:#f3e5f5
    style DH fill:#e8f5e9

# 在Claude Code会话中
> /plugin search database

# 可用Skill列表：
# 1. database-schema-design — PostgreSQL/MySQL Schema设计模式
# 2. database-migrations — 零停机迁移策略
# 3. sql-expert — SQL查询优化专家
# 4. drizzle-orm-workflow — Drizzle ORM完整工作流
# 5. prisma-workflow — Prisma ORM工作流

> /plugin install database-schema-design

## Migration Rules
- ALL migrations must include both UP and DOWN
- DOWN migration must be tested before merging UP migration
- Add a comment at the top of each migration describing the purpose

-- 错误方式
UPDATE products SET stock = stock - 1 WHERE id = $1;

-- Claude Code建议的正确方式（乐观锁）
UPDATE products
SET stock = stock - $2,
    version = version + 1
WHERE id = $1 AND stock >= $2
RETURNING stock;

// drizzle.config.ts 中配置连接池
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                // 最大连接数
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

> 请对我的数据库执行以下性能检查：
> 1. 列出所有缺少索引的外键列
> 2. 查找使用了SELECT *的查询
> 3. 检查是否有未使用的索引
> 4. 分析最大的5张表和它们的膨胀率
> 5. 检查是否有长事务阻塞
> 6. 验证所有时间字段使用timestamptz
> 7. 检查JSON字段是否应该拆分为关联表

性能检查报告：

✅ 所有外键列均有索引
⚠️ orders表的idx_orders_created_at与idx_orders_user_status_created存在包含关系，
   建议删除idx_orders_created_at
⚠️ products表存在2个未使用的索引：idx_products_name_trgm, idx_products_created
❌ order_items表的item_count膨胀率为23%，建议执行VACUUM FULL
✅ 所有时间字段已使用timestamptz
✅ 无JSON字段需要拆分

建议执行：
DROP INDEX CONCURRENTLY idx_orders_created_at;
DROP INDEX CONCURRENTLY idx_products_name_trgm;
VACUUM FULL order_items;

flowchart TB
    DEV[开发环境<br/>drizzle-kit push] --> STAGING[预发布环境<br/>drizzle-kit migrate]
    STAGING --> REVIEW[Code Review<br/>Claude Code检查SQL]
    REVIEW --> PROD[生产环境<br/>零停机迁移]

    PROD --> ROLLBACK{成功?}
    ROLLBACK -->|否| DOWN[执行DOWN迁移]
    ROLLBACK -->|是| DONE[完成]
    DOWN --> DONE

    style DEV fill:#e3f2fd
    style STAGING fill:#fff3e0
    style PROD fill:#ffebee
    style REVIEW fill:#f3e5f5

## Production Safety
- NEVER execute migrations directly on production
- ALWAYS generate migration plan for human review
- Use CONCURRENTLY for all index operations
- Batch updates in 1000-row chunks
- Estimate migration duration before execution

flowchart LR
    subgraph 设计阶段
        A1[业务需求] --> A2[Claude Code生成ER图]
        A2 --> A3[DDL验证]
        A3 --> A4[ORM Schema]
    end

    subgraph 开发阶段
        A4 --> B1[生成迁移]
        B1 --> B2[本地测试]
        B2 --> B3[DBHub查询验证]
    end

    subgraph 优化阶段
        B3 --> C1[EXPLAIN分析]
        C1 --> C2[索引建议]
        C2 --> C3[查询重写]
    end

    subgraph 部署阶段
        C3 --> D1[三阶段迁移]
        D1 --> D2[分批回填]
        D2 --> D3[约束收敛]
        D3 --> D4[性能巡检]
    end

    style 设计阶段 fill:#e8eaf6
    style 开发阶段 fill:#e0f2f1
    style 优化阶段 fill:#fff8e1
    style 部署阶段 fill:#fce4ec