Back to Details

database-schema

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Database Schema Awareness Skill

数据库Schema感知技能

Load with: base.md + [your database skill]
Problem: Claude forgets schema details mid-session - wrong column names, missing fields, incorrect types. TDD catches this at runtime, but we can prevent it earlier.
加载方式：base.md + [你的数据库技能]
问题： Claude在会话过程中会忘记Schema细节——列名错误、字段缺失、类型不正确。TDD会在运行时发现这些问题，但我们可以更早地预防。

Core Rule: Read Schema Before Writing Database Code

核心规则：编写数据库代码前先查阅Schema

MANDATORY: Before writing ANY code that touches the database:
┌─────────────────────────────────────────────────────────────┐
│  1. READ the schema file (see locations below)              │
│  2. VERIFY columns/types you're about to use exist          │
│  3. REFERENCE schema in your response when writing queries  │
│  4. TYPE-CHECK using generated types (Drizzle/Prisma/etc)   │
└─────────────────────────────────────────────────────────────┘
If schema file doesn't exist → CREATE IT before proceeding.
强制要求：在编写任何涉及数据库的代码前：
┌─────────────────────────────────────────────────────────────┐
│  1. 查阅Schema文件（见下方位置）                            │
│  2. 验证你要使用的列/类型是否存在                          │
│  3. 编写查询时在响应中引用Schema                          │
│  4. 使用生成的类型进行类型检查（Drizzle/Prisma等）          │
└─────────────────────────────────────────────────────────────┘
如果Schema文件不存在 → 先创建它再继续。

Schema File Locations (By Stack)

Schema文件位置（按技术栈分类）

StackSchema LocationType Generation
Drizzle
src/db/schema.ts
or 
drizzle/schema.ts
Built-in TypeScript
Prisma
prisma/schema.prisma
npx prisma generate
Supabase
supabase/migrations/*.sql
+ types
supabase gen types typescript
SQLAlchemy
app/models/*.py
or 
src/models.py
Pydantic models
TypeORM
src/entities/*.ts
Decorators = types
Raw SQL
schema.sql
or 
migrations/
Manual types required
技术栈Schema位置类型生成
Drizzle
src/db/schema.ts
或 
drizzle/schema.ts
内置TypeScript类型
Prisma
prisma/schema.prisma
npx prisma generate
Supabase
supabase/migrations/*.sql
+ 类型
supabase gen types typescript
SQLAlchemy
app/models/*.py
或 
src/models.py
Pydantic模型
TypeORM
src/entities/*.ts
装饰器定义类型
原生SQL
schema.sql
或 
migrations/
需要手动定义类型

Schema Reference File (Recommended)

Schema参考文件（推荐）

Create 
_project_specs/schema-reference.md
for quick lookup:
markdown
undefined
创建
_project_specs/schema-reference.md
以便快速查阅：
markdown
undefined

Database Schema Reference

数据库Schema参考

Auto-generated or manually maintained. Claude: READ THIS before database work.
自动生成或手动维护。Claude：进行数据库操作前请阅读此文件。

Tables

表

users

users

ColumnTypeNullableDefaultNotes
iduuidNOgen_random_uuid()PK
emailtextNO-Unique
nametextYES-Display name
created_attimestamptzNOnow()-
updated_attimestamptzNOnow()-
列类型是否可为空默认值说明
iduuid否gen_random_uuid()主键
emailtext否-唯一
nametext是-显示名称
created_attimestamptz否now()-
updated_attimestamptz否now()-

orders

orders

ColumnTypeNullableDefaultNotes
iduuidNOgen_random_uuid()PK
user_iduuidNO-FK → users.id
statustextNO'pending'enum: pending/paid/shipped/delivered
total_centsintegerNO-Amount in cents
created_attimestamptzNOnow()-
列类型是否可为空默认值说明
iduuid否gen_random_uuid()主键
user_iduuid否-外键 → users.id
statustext否'pending'枚举：pending/paid/shipped/delivered
total_centsinteger否-金额（单位：分）
created_attimestamptz否now()-

Relationships

关系

  • users 1:N orders (user_id)
  • users 一对多 orders（通过user_id）

Enums

枚举

  • order_status: pending, paid, shipped, delivered

---
  • order_status: pending, paid, shipped, delivered

---

Pre-Code Checklist (Database Work)

编码前检查清单（数据库操作）

Before writing any database code, Claude MUST:
markdown
undefined
在编写任何数据库代码前，Claude必须：
markdown
undefined

Schema Verification Checklist

Schema验证检查清单

  • Read schema file: 
    [path to schema]
  • Columns I'm using exist: [list columns]
  • Types match my code: [list type mappings]
  • Relationships are correct: [list FKs]
  • Nullable fields handled: [list nullable columns]

**Example in practice:**

```markdown
  • 已查阅Schema文件：
    [Schema路径]
  • 我要使用的列存在：[列出列名]
  • 类型与我的代码匹配：[列出类型映射]
  • 关系设置正确：[列出外键]
  • 可为空字段已处理：[列出可为空列]

**实际示例：**

```markdown

Schema Verification for TODO-042 (Add order history endpoint)

TODO-042（添加订单历史接口）的Schema验证

  • Read schema: 
    src/db/schema.ts
  • Columns exist: orders.id, orders.user_id, orders.status, orders.total_cents, orders.created_at
  • Types: id=uuid→string, total_cents=integer→number, status=text→OrderStatus enum
  • Relationships: orders.user_id → users.id (many-to-one)
  • Nullable: none of these columns are nullable

---
  • 已查阅Schema：
    src/db/schema.ts
  • 列存在：orders.id, orders.user_id, orders.status, orders.total_cents, orders.created_at
  • 类型：id=uuid→string, total_cents=integer→number, status=text→OrderStatus枚举
  • 关系：orders.user_id → users.id（多对一）
  • 可为空：这些列均不可为空

---

Type Generation Commands

类型生成命令

Drizzle (TypeScript)

Drizzle（TypeScript）

typescript
// Schema defines types automatically
// src/db/schema.ts
import { pgTable, uuid, text, integer, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});

export const orders = pgTable('orders', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull().references(() => users.id),
  status: text('status').notNull().default('pending'),
  totalCents: integer('total_cents').notNull(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});

// Inferred types - USE THESE
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Order = typeof orders.$inferSelect;
export type NewOrder = typeof orders.$inferInsert;
typescript
// Schema自动定义类型
// src/db/schema.ts
import { pgTable, uuid, text, integer, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});

export const orders = pgTable('orders', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull().references(() => users.id),
  status: text('status').notNull().default('pending'),
  totalCents: integer('total_cents').notNull(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});

// 推断类型 - 请使用这些类型
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Order = typeof orders.$inferSelect;
export type NewOrder = typeof orders.$inferInsert;

Prisma

Prisma

prisma
// prisma/schema.prisma
model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String?
  orders    Order[]
  createdAt DateTime @default(now()) @map("created_at")

  @@map("users")
}

model Order {
  id         String   @id @default(uuid())
  userId     String   @map("user_id")
  user       User     @relation(fields: [userId], references: [id])
  status     String   @default("pending")
  totalCents Int      @map("total_cents")
  createdAt  DateTime @default(now()) @map("created_at")

  @@map("orders")
}
bash
undefined
prisma
// prisma/schema.prisma
model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String?
  orders    Order[]
  createdAt DateTime @default(now()) @map("created_at")

  @@map("users")
}

model Order {
  id         String   @id @default(uuid())
  userId     String   @map("user_id")
  user       User     @relation(fields: [userId], references: [id])
  status     String   @default("pending")
  totalCents Int      @map("total_cents")
  createdAt  DateTime @default(now()) @map("created_at")

  @@map("orders")
}
bash
undefined

Generate types after schema changes

Schema变更后生成类型

npx prisma generate
undefined
npx prisma generate
undefined

Supabase

Supabase

bash
undefined
bash
undefined

Generate TypeScript types from live database

从本地数据库生成TypeScript类型

supabase gen types typescript --local > src/types/database.ts
supabase gen types typescript --local > src/types/database.ts

Or from remote

或从远程数据库生成

supabase gen types typescript --project-id your-project-id > src/types/database.ts

```typescript
// Use generated types
import { Database } from '@/types/database';

type User = Database['public']['Tables']['users']['Row'];
type NewUser = Database['public']['Tables']['users']['Insert'];
type Order = Database['public']['Tables']['orders']['Row'];
supabase gen types typescript --project-id your-project-id > src/types/database.ts

```typescript
// 使用生成的类型
import { Database } from '@/types/database';

type User = Database['public']['Tables']['users']['Row'];
type NewUser = Database['public']['Tables']['users']['Insert'];
type Order = Database['public']['Tables']['orders']['Row'];

SQLAlchemy (Python)

SQLAlchemy（Python）

python
undefined
python
undefined

app/models/user.py

app/models/user.py

from sqlalchemy import Column, String, DateTime from sqlalchemy.dialects.postgresql import UUID from sqlalchemy.sql import func from app.db import Base import uuid
class User(Base): tablename = "users"
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
email = Column(String, nullable=False, unique=True)
name = Column(String, nullable=True)
created_at = Column(DateTime(timezone=True), server_default=func.now())

# Relationships
orders = relationship("Order", back_populates="user")

```python
from sqlalchemy import Column, String, DateTime from sqlalchemy.dialects.postgresql import UUID from sqlalchemy.sql import func from app.db import Base import uuid
class User(Base): tablename = "users"
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
email = Column(String, nullable=False, unique=True)
name = Column(String, nullable=True)
created_at = Column(DateTime(timezone=True), server_default=func.now())

# 关系
orders = relationship("Order", back_populates="user")

```python

app/schemas/user.py - Pydantic for API validation

app/schemas/user.py - 用于API验证的Pydantic模型

from pydantic import BaseModel, EmailStr from uuid import UUID from datetime import datetime
class UserBase(BaseModel): email: EmailStr name: str | None = None
class UserCreate(UserBase): pass
class User(UserBase): id: UUID created_at: datetime
class Config:
    from_attributes = True

---
from pydantic import BaseModel, EmailStr from uuid import UUID from datetime import datetime
class UserBase(BaseModel): email: EmailStr name: str | None = None
class UserCreate(UserBase): pass
class User(UserBase): id: UUID created_at: datetime
class Config:
    from_attributes = True

---

Schema-Aware TDD Workflow

支持Schema感知的TDD工作流

Extend the standard TDD workflow for database work:
┌─────────────────────────────────────────────────────────────┐
│  0. SCHEMA: Read and verify schema before anything else     │
│     └─ Read schema file                                     │
│     └─ Complete Schema Verification Checklist               │
│     └─ Note any missing columns/tables needed               │
├─────────────────────────────────────────────────────────────┤
│  1. RED: Write tests that use correct column names          │
│     └─ Import generated types                               │
│     └─ Use type-safe queries in tests                       │
│     └─ Tests should fail on logic, NOT schema errors        │
├─────────────────────────────────────────────────────────────┤
│  2. GREEN: Implement with type-safe queries                 │
│     └─ Use ORM types, not raw strings                       │
│     └─ TypeScript/mypy catches column mismatches            │
├─────────────────────────────────────────────────────────────┤
│  3. VALIDATE: Type check catches schema drift               │
│     └─ tsc --noEmit / mypy catches wrong columns            │
│     └─ Tests validate runtime behavior                      │
└─────────────────────────────────────────────────────────────┘
为数据库操作扩展标准TDD工作流：
┌─────────────────────────────────────────────────────────────┐
│  0. SCHEMA：先查阅并验证Schema，再进行其他操作              │
│     └─ 查阅Schema文件                                       │
│     └─ 完成Schema验证检查清单                               │
│     └─ 记录所需的缺失列/表                                   │
├─────────────────────────────────────────────────────────────┤
│  1. 红：编写使用正确列名的测试                              │
│     └─ 导入生成的类型                                       │
│     └─ 在测试中使用类型安全的查询                           │
│     └─ 测试应因逻辑失败，而非Schema错误                     │
├─────────────────────────────────────────────────────────────┤
│  2. 绿：使用类型安全的查询实现功能                          │
│     └─ 使用ORM类型，而非原生字符串                           │
│     └─ TypeScript/mypy会捕获列不匹配问题                    │
├─────────────────────────────────────────────────────────────┤
│  3. 验证：类型检查捕获Schema偏移                            │
│     └─ tsc --noEmit / mypy会捕获错误的列名                  │
│     └─ 测试验证运行时行为                                  │
└─────────────────────────────────────────────────────────────┘

Common Schema Mistakes (And How to Prevent)

常见Schema错误（及预防方法）

MistakeExamplePrevention
Wrong column name
user.userName
vs 
user.name
Read schema, use generated types
Wrong type
totalCents
as string		Type generation catches this
Missing nullable check
user.name!
when nullable		Schema shows nullable fields
Wrong FK relationship
order.userId
vs 
order.user_id
Check schema column names
Missing columnUsing 
user.avatar
that doesn't exist		Read schema before coding
Wrong enum value
status: 'complete'
vs 
'completed'
Document enums in schema reference
错误示例预防方法
列名错误
user.userName
而非 
user.name
查阅Schema，使用生成的类型
类型错误将
totalCents
定义为字符串		类型生成会捕获此问题
忽略可为空检查当
user.name
可为空时使用
user.name!
Schema会显示可为空字段
外键关系错误
order.userId
而非 
order.user_id
检查Schema中的列名
使用不存在的列使用不存在的
user.avatar
编码前查阅Schema
枚举值错误
status: 'complete'
而非 
'completed'
在Schema参考文件中记录枚举

Type-Safe Query Examples

类型安全查询示例

Drizzle (catches errors at compile time):
typescript
// ✅ Correct - uses schema-defined columns
const user = await db.select().from(users).where(eq(users.email, email));

// ❌ Wrong - TypeScript error: 'userName' doesn't exist
const user = await db.select().from(users).where(eq(users.userName, email));
Prisma (catches errors at compile time):
typescript
// ✅ Correct
const user = await prisma.user.findUnique({ where: { email } });

// ❌ Wrong - TypeScript error
const user = await prisma.user.findUnique({ where: { userName: email } });
Raw SQL (NO protection - avoid):
typescript
// ❌ Dangerous - no type checking, easy to get wrong
const result = await db.query('SELECT * FROM users WHERE user_name = $1', [email]);
// Should be 'email' not 'user_name' - won't catch until runtime
Drizzle（编译时捕获错误）：
typescript
// ✅ 正确 - 使用Schema定义的列
const user = await db.select().from(users).where(eq(users.email, email));

// ❌ 错误 - TypeScript报错：'userName'不存在
const user = await db.select().from(users).where(eq(users.userName, email));
Prisma（编译时捕获错误）：
typescript
// ✅ 正确
const user = await prisma.user.findUnique({ where: { email } });

// ❌ 错误 - TypeScript报错
const user = await prisma.user.findUnique({ where: { userName: email } });
原生SQL（无保护 - 避免使用）：
typescript
// ❌ 危险 - 无类型检查，容易出错
const result = await db.query('SELECT * FROM users WHERE user_name = $1', [email]);
// 应为'email'而非'user_name' - 运行时才会发现错误

Migration Workflow

迁移工作流

When schema changes are needed:
┌─────────────────────────────────────────────────────────────┐
│  1. Update schema file (Drizzle/Prisma/SQLAlchemy)          │
├─────────────────────────────────────────────────────────────┤
│  2. Generate migration                                       │
│     └─ Drizzle: npx drizzle-kit generate                    │
│     └─ Prisma: npx prisma migrate dev --name add_column     │
│     └─ Supabase: supabase migration new add_column          │
├─────────────────────────────────────────────────────────────┤
│  3. Regenerate types                                         │
│     └─ Prisma: npx prisma generate                          │
│     └─ Supabase: supabase gen types typescript              │
├─────────────────────────────────────────────────────────────┤
│  4. Update schema-reference.md                               │
├─────────────────────────────────────────────────────────────┤
│  5. Run type check - find all broken code                    │
│     └─ npm run typecheck                                    │
├─────────────────────────────────────────────────────────────┤
│  6. Fix type errors, update tests, run full validation       │
└─────────────────────────────────────────────────────────────┘
当需要变更Schema时：
┌─────────────────────────────────────────────────────────────┐
│  1. 更新Schema文件（Drizzle/Prisma/SQLAlchemy）              │
├─────────────────────────────────────────────────────────────┤
│  2. 生成迁移文件                                           │
│     └─ Drizzle: npx drizzle-kit generate                    │
│     └─ Prisma: npx prisma migrate dev --name add_column     │
│     └─ Supabase: supabase migration new add_column          │
├─────────────────────────────────────────────────────────────┤
│  3. 重新生成类型                                           │
│     └─ Prisma: npx prisma generate                          │
│     └─ Supabase: supabase gen types typescript              │
├─────────────────────────────────────────────────────────────┤
│  4. 更新schema-reference.md                               │
├─────────────────────────────────────────────────────────────┤
│  5. 运行类型检查 - 找出所有失效代码                        │
│     └─ npm run typecheck                                    │
├─────────────────────────────────────────────────────────────┤
│  6. 修复类型错误，更新测试，运行完整验证                    │
└─────────────────────────────────────────────────────────────┘

Session Start Protocol

会话启动协议

When starting a session that involves database work:
  1. Read schema file immediately
  2. Read 
    _project_specs/schema-reference.md
    if exists
  3. Note in session state what tables/columns are relevant
  4. Reference schema explicitly when writing code
Session state example:
markdown
undefined
当启动涉及数据库操作的会话时：
  1. 立即查阅Schema文件
  2. 如果存在
    _project_specs/schema-reference.md
    则查阅它
  3. 在会话状态中记录相关的表/列
  4. 编写代码时明确引用Schema
会话状态示例：
markdown
undefined

Current Session - Database Context

当前会话 - 数据库上下文

Schema read: ✓ src/db/schema.ts Tables in scope: users, orders, order_items Key columns:
  • users: id, email, name, created_at
  • orders: id, user_id, status, total_cents
  • order_items: id, order_id, product_id, quantity, price_cents

---
已查阅Schema：✓ src/db/schema.ts 涉及的表：users, orders, order_items 关键列：
  • users: id, email, name, created_at
  • orders: id, user_id, status, total_cents
  • order_items: id, order_id, product_id, quantity, price_cents

---

Anti-Patterns

反模式

  • ❌ Guessing column names - Always read schema first
  • ❌ Using raw SQL strings - Use ORM with type generation
  • ❌ Hardcoding without verification - Check schema before using any column
  • ❌ Ignoring type errors - Schema drift shows up as type errors
  • ❌ Not regenerating types - After migration, always regenerate
  • ❌ Assuming nullable - Check schema for nullable columns
  • ❌ 猜测列名 - 始终先查阅Schema
  • ❌ 使用原生SQL字符串 - 使用带类型生成的ORM
  • ❌ 未验证就硬编码 - 使用任何列前都要检查Schema
  • ❌ 忽略类型错误 - Schema偏移会表现为类型错误
  • ❌ 不重新生成类型 - 迁移后务必重新生成类型
  • ❌ 默认认为字段可为空 - 检查Schema中的可为空列

Checklist

检查清单

Setup

配置

  • Schema file exists in standard location
  • Type generation configured
  • _project_specs/schema-reference.md
    created
  • Types regenerate on schema change
  • Schema文件存在于标准位置
  • 已配置类型生成
  • 已创建
    _project_specs/schema-reference.md
  • Schema变更时会重新生成类型

Per-Task

每个任务

  • Schema read before writing database code
  • Schema Verification Checklist completed
  • Using generated types (not raw strings)
  • Type check passes (catches column errors)
  • Tests use correct schema
  • 编写数据库代码前已查阅Schema
  • 已完成Schema验证检查清单
  • 使用生成的类型（而非原生字符串）
  • 类型检查通过（捕获列错误）
  • 测试使用正确的Schema