数据库设计和优化是开发中技术门槛最高的部分之一。Claude Code 能从业务需求直接推导出合理的 Schema 设计，生成迁移脚本，并分析性能瓶颈。本文展示完整工作流。
工作流 1：从业务需求设计 Schema
Design a PostgreSQL schema for an e-commerce platform:
Entities:
- Users: can be buyers or sellers
- Products: belong to sellers, have multiple images and variants
- Orders: placed by buyers, contain multiple order items
- Reviews: buyers review purchased products
- Categories: hierarchical (electronics > phones > iPhone)
Requirements:
- Support product variants (size, color, etc.)
- Track inventory per variant
- Order status history (created->paid->shipped->delivered)
- Soft delete for products
Output:
1. CREATE TABLE statements with proper constraints
2. Indexes for common query patterns
3. Brief explanation of design decisions
工作流 2：生成 Prisma Schema
Convert this SQL schema to Prisma schema format:
[粘贴 SQL]
Also add:
- Proper relations (one-to-many, many-to-many)
- @default values
- @updatedAt for timestamp fields
- @@index for performance-critical fields
- Cascade delete rules
生成结果示例：
prisma
model Product {
id String @id @default(cuid())
title String
slug String @unique
price Decimal @db.Decimal(10, 2)
sellerId String
seller User @relation(fields: [sellerId], references: [id])
variants Variant[]
images Image[]
reviews Review[]
categoryId String
category Category @relation(fields: [categoryId], references: [id])
deletedAt DateTime?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([sellerId])
@@index([categoryId])
@@index([slug])
}工作流 3：生成数据库迁移
Generate a safe database migration for these changes:
Current schema: [粘贴当前 schema]
Target schema: [粘贴新 schema]
Requirements:
- Zero-downtime migration (no table locks)
- Add new nullable columns before making them required
- Backfill existing rows before adding NOT NULL constraints
- Create indexes CONCURRENTLY
Output:
1. migration.sql
2. rollback.sql (undo script)
3. Estimated migration time for 10M rows
工作流 4：N+1 查询检测与修复
Review this code for N+1 query problems:
[粘贴 ORM 代码]
For each N+1 pattern found:
1. Explain why it causes N+1
2. Show the fixed version using proper joins/includes
3. Estimate queries before vs after the fix
Use Prisma syntax for the fixes.
常见 N+1 修复：
typescript
// 问题：N+1
const orders = await prisma.order.findMany()
for (const order of orders) {
const user = await prisma.user.findUnique({where: {id: order.userId}})
// 每个 order 一次查询 = N+1 次
}
// 修复：一次查询
const orders = await prisma.order.findMany({
include: { user: true } // JOIN 一次搞定
})工作流 5：分析查询性能
Analyze the performance of this PostgreSQL query:
Query:
SELECT p.*, u.name, COUNT(r.id) as review_count
FROM products p
JOIN users u ON p.seller_id = u.id
LEFT JOIN reviews r ON p.id = r.product_id
WHERE p.category_id = $1 AND p.deleted_at IS NULL
GROUP BY p.id, u.name
ORDER BY review_count DESC
LIMIT 20;
EXPLAIN ANALYZE output:
[粘贴 EXPLAIN 输出]
Please:
1. Identify bottlenecks (seq scans, high cost steps)
2. Suggest missing indexes
3. Rewrite if the query can be restructured for better performance
工作流 6：SQLAlchemy 模型生成（Python）
Generate SQLAlchemy 2.0 models for this schema:
[粘贴 SQL]
Requirements:
- Use DeclarativeBase (new SQLAlchemy 2.0 style)
- Type annotations for all columns
- relationship() with lazy='select' for small sets, lazy='dynamic' for large
- __repr__ for debugging
- save to src/models/
工作流 7：种子数据生成
Generate seed data for development/testing:
Schema: [Prisma schema or SQL]
Generate:
- 10 users (mix of buyers and sellers)
- 50 products (across 5 categories)
- 100 orders with order items
- 200 reviews
Use realistic fake data (Faker.js).
Save to prisma/seed.ts
CLAUDE.md 推荐配置（数据库项目）
markdown
## 数据库规范
- ORM: Prisma
- DB: PostgreSQL 15
- 迁移：prisma migrate dev
## 查询规范
- 禁止裸 SQL（除性能关键路径）
- 所有关联查询用 include/select 而非循环查询
- 新增字段先加 nullable，backfill 后再加约束
## 命令
- 迁移：npx prisma migrate dev
- 查看：npx prisma studio
- 重置：npx prisma migrate reset来源：Anthropic 官方文档 + Prisma 官方文档