Back to Details

api-database-drizzle

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Database with Drizzle ORM + Neon

使用Drizzle ORM + Neon构建数据库

Quick Guide: Use Drizzle ORM for type-safe queries, Neon serverless Postgres for edge-compatible connections. Schema-first design with automatic TypeScript types. Use RQB v2 with 
defineRelations()
and object-based 
where
syntax. Relational queries with 
.with()
avoid N+1 problems. Use transactions for atomic operations.
<critical_requirements>
快速指南： 使用Drizzle ORM实现类型安全查询，借助Neon无服务器Postgres实现边缘兼容连接。采用Schema-first设计，自动生成TypeScript类型。使用RQB v2的
defineRelations()
和基于对象的
where
语法。通过
.with()
实现关联查询，避免N+1问题。使用事务处理原子操作。
<critical_requirements>

CRITICAL: Before Using This Skill

重要提示：使用本技能前须知

All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, 
import type
, named constants)
(You MUST set 
casing: 'snake_case'
in Drizzle config to map camelCase JS to snake_case SQL)
(You MUST use 
tx
parameter (NOT 
db
) inside transaction callbacks to ensure atomicity)
(You MUST use 
.with()
for relational queries to avoid N+1 problems - fetches all data in single SQL query)
(You MUST use 
defineRelations()
for RQB v2 - the old 
relations()
per-table syntax is deprecated)
</critical_requirements>
Detailed Resources:
  • For code examples, see examples/ folder:
    • core.md - Connection setup and schema definition (always loaded)
    • queries.md - Relational queries and query builder
    • relations-v2.md - RQB v2 with defineRelations() (NEW)
    • transactions.md - Atomic operations
    • migrations.md - Drizzle Kit workflow
    • seeding.md - Development data population (includes drizzle-seed)
  • For decision frameworks and anti-patterns, see reference.md
Auto-detection: drizzle-orm, @neondatabase/serverless, neon-http, db.query, db.transaction, drizzle-kit, pgTable, defineRelations, drizzle-seed
When to use:
  • Serverless functions needing type-safe database queries
  • Schema-first development with migrations
  • Building server-rendered apps with API routes
When NOT to use:
  • Simple apps using framework server actions directly (overhead not justified)
  • Apps needing traditional TCP connection pooling only (use standard Postgres clients)
  • Non-TypeScript projects (lose primary benefit of type safety)
  • Edge functions requiring WebSocket connections (not supported in edge runtime)
<patterns>
所有代码必须遵循CLAUDE.md中的项目规范（短横线命名法、命名导出、导入顺序、
import type
、命名常量）
（必须在Drizzle配置中设置
casing: 'snake_case'
，以将驼峰式JS字段映射为下划线式SQL字段）
（事务回调中必须使用
tx
参数（而非
db
），以确保原子性）
（关联查询必须使用
.with()
，避免N+1问题——单次SQL查询获取所有数据）
（RQB v2必须使用
defineRelations()
——旧的单表
relations()
语法已废弃）
</critical_requirements>
详细资源：
  • 代码示例请查看examples/文件夹：
    • core.md - 连接配置与Schema定义（默认加载）
    • queries.md - 关联查询与查询构建器
    • relations-v2.md - 使用defineRelations()的RQB v2（新版）
    • transactions.md - 原子操作
    • migrations.md - Drizzle Kit工作流
    • seeding.md - 开发数据填充（包含drizzle-seed）
  • 决策框架与反模式请查看reference.md
自动检测标识： drizzle-orm, @neondatabase/serverless, neon-http, db.query, db.transaction, drizzle-kit, pgTable, defineRelations, drizzle-seed
适用场景：
  • 需要类型安全数据库查询的无服务器函数
  • 带迁移的Schema-first开发
  • 构建带API路由的服务端渲染应用
不适用场景：
  • 直接使用框架服务端操作的简单应用（开销得不偿失）
  • 仅需传统TCP连接池的应用（使用标准Postgres客户端）
  • 非TypeScript项目（失去类型安全核心优势）
  • 需要WebSocket连接的边缘函数（边缘运行时不支持）
<patterns>

Core Patterns

核心模式

Pattern 1: Database Connection (Neon HTTP)

模式1：数据库连接（Neon HTTP）

Configure Drizzle with Neon for serverless/edge compatibility. Key setup requirements:
typescript
export const db = drizzle(sql, {
  schema,
  casing: "snake_case", // Maps camelCase JS to snake_case SQL
});
  • Validate 
    DATABASE_URL
    before use (throw on missing)
  • Always set 
    casing: "snake_case"
    to prevent field name mismatches
  • Use 
    neon()
    for HTTP (edge-compatible) or 
    Pool
    for WebSocket (long queries)
Full connection setup, WebSocket config, and Drizzle Kit config in examples/core.md.
配置Drizzle与Neon，实现无服务器/边缘环境兼容。关键配置要求：
typescript
export const db = drizzle(sql, {
  schema,
  casing: "snake_case", // 将驼峰式JS字段映射为下划线式SQL字段
});
  • 使用前验证
    DATABASE_URL
    （缺失时抛出错误）
  • 必须设置
    casing: "snake_case"
    ，避免字段名称不匹配
  • HTTP连接（边缘兼容）使用
    neon()
    ，长查询WebSocket连接使用
    Pool
完整连接配置、WebSocket配置与Drizzle Kit配置请查看examples/core.md。

Pattern 2: Schema Definition

模式2：Schema定义

Define tables with TypeScript types using Drizzle's schema builder:
typescript
export const companies = pgTable("companies", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: varchar("name", { length: 255 }).notNull(),
  slug: varchar("slug", { length: 255 }).unique(),
  deletedAt: timestamp("deleted_at"), // Soft delete
  createdAt: timestamp("created_at").defaultNow(),
});
  • Use 
    pgEnum()
    for constrained values instead of varchar
  • Always include 
    createdAt
    /
    updatedAt
    timestamps
  • Add 
    deletedAt
    for soft deletes
  • Set 
    onDelete: "cascade"
    on foreign keys to prevent orphaned records
  • Use 
    uuid().defaultRandom()
    or 
    integer().generatedAlwaysAsIdentity()
    for primary keys
Full schema examples (enums, relations, junction tables, identity columns) in examples/core.md.
使用Drizzle的Schema构建器，通过TypeScript类型定义表结构：
typescript
export const companies = pgTable("companies", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: varchar("name", { length: 255 }).notNull(),
  slug: varchar("slug", { length: 255 }).unique(),
  deletedAt: timestamp("deleted_at"), // 软删除
  createdAt: timestamp("created_at").defaultNow(),
});
  • 受限值使用
    pgEnum()
    而非varchar
  • 始终包含
    createdAt
    /
    updatedAt
    时间戳
  • 添加
    deletedAt
    实现软删除
  • 外键设置
    onDelete: "cascade"
    ，避免孤立记录
  • 主键使用
    uuid().defaultRandom()
    或
    integer().generatedAlwaysAsIdentity()
完整Schema示例（枚举、关联、连接表、自增列）请查看examples/core.md。

Pattern 3: Relational Queries with 
.with()

模式3：使用
.with()
实现关联查询

Fetch related data efficiently in a single SQL query using 
.with()
:
typescript
const job = await db.query.jobs.findFirst({
  where: and(eq(jobs.id, jobId), isNull(jobs.deletedAt)),
  with: {
    company: { with: { locations: true } },
    jobSkills: { with: { skill: true } },
  },
});
// Result is fully typed: job.company.name, job.jobSkills[0].skill.name
  • Use 
    db.query
    with 
    .with()
     when fetching related data -- single SQL query, no N+1
  • Use query builder (
    db.select()
    )     for custom column selection, complex JOINs, aggregations
  • Always include 
    isNull(deletedAt)
    in WHERE conditions for soft-deleted tables
Full relational query examples, N+1 anti-patterns, and dynamic filtering in examples/queries.md.
</patterns>
通过
.with()
在单次SQL查询中高效获取关联数据：
typescript
const job = await db.query.jobs.findFirst({
  where: and(eq(jobs.id, jobId), isNull(jobs.deletedAt)),
  with: {
    company: { with: { locations: true } },
    jobSkills: { with: { skill: true } },
  },
});
// 返回结果完全类型化：job.company.name, job.jobSkills[0].skill.name
  • 获取关联数据时使用
    db.query
    配合
    .with()
    ——单次SQL查询，无N+1问题
  • 自定义列选择、复杂JOIN、聚合操作使用查询构建器(
    db.select()
    )
  • 软删除表的WHERE条件中始终包含
    isNull(deletedAt)
完整关联查询示例、N+1反模式与动态过滤请查看examples/queries.md。
</patterns>

Additional Patterns

其他模式

The following patterns are documented with full examples in examples/:
  • Query Builder - Complex filters, dynamic conditions, custom JOINs - see queries.md
  • Transactions - Atomic operations, error handling, rollback - see transactions.md
  • Database Migrations - Drizzle Kit workflow, 
    generate
    vs 
    push
    - see migrations.md
  • Database Seeding - Development data, safe cleanup - see seeding.md
Performance optimization (indexes, prepared statements, pagination) is documented in reference.md.
<red_flags>
以下模式的完整示例请查看examples/：
  • 查询构建器 - 复杂过滤、动态条件、自定义JOIN - 查看queries.md
  • 事务 - 原子操作、错误处理、回滚 - 查看transactions.md
  • 数据库迁移 - Drizzle Kit工作流、
    generate
    vs 
    push
    - 查看migrations.md
  • 数据库填充 - 开发数据、安全清理 - 查看seeding.md
性能优化（索引、预编译语句、分页）请查看reference.md。
<red_flags>

RED FLAGS

注意事项

  • ❌ Using 
    db
    instead of 
    tx
    inside transactions     - Bypasses transaction context, breaking atomicity
  • ❌ N+1 queries with relations - Use 
    .with()
    to fetch in one query
  • ❌ Not setting 
    casing: 'snake_case'
     - Field name mismatches between JS and SQL
  • ❌ Using v1 
    relations()
    per-table syntax     - Deprecated, use 
    defineRelations()
  • ❌ Using callback-based 
    where
    /
    orderBy
     - v1 syntax deprecated, use object-based syntax
  • ⚠️ Queries without soft delete checks (
    isNull(deletedAt)
    )
  • ⚠️ No pagination limits on list queries
Gotchas & Edge Cases:
  • Neon HTTP has 30-second query timeout - long queries need WebSocket
  • Prepared statements created outside transactions cannot be used inside transactions
  • enableRLS()
    deprecated in v1.0.0-beta.1 - use 
    pgTable.withRLS()
    instead
  • Validator packages consolidated: 
    drizzle-zod
    is now 
    drizzle-orm/zod
    (since v1 beta)
For the complete list of anti-patterns and gotchas, see reference.md.
</red_flags>
<critical_reminders>
  • ❌ 事务中使用
    db
    而非
    tx
     - 绕过事务上下文，破坏原子性
  • ❌ 关联查询出现N+1问题 - 使用
    .with()
    单次查询获取数据
  • ❌ 未设置
    casing: 'snake_case'
     - JS与SQL字段名称不匹配
  • ❌ 使用v1版本的单表
    relations()
    语法     - 已废弃，使用
    defineRelations()
  • ❌ 使用回调式
    where
    /
    orderBy
     - v1语法已废弃，使用基于对象的语法
  • ⚠️ 查询未包含软删除检查(
    isNull(deletedAt)
    )
  • ⚠️ 列表查询未设置分页限制
易犯错误与边缘情况：
  • Neon HTTP查询超时时间为30秒——长查询需使用WebSocket
  • 事务外创建的预编译语句无法在事务内使用
  • v1.0.0-beta.1中
    enableRLS()
    已废弃——使用
    pgTable.withRLS()
    替代
  • 验证器包已整合：
    drizzle-zod
    现已改为
    drizzle-orm/zod
    （自v1 beta版起）
完整反模式与易犯错误列表请查看reference.md。
</red_flags>
<critical_reminders>

CRITICAL REMINDERS

重要提醒

All code must follow project conventions in CLAUDE.md
(You MUST set 
casing: 'snake_case'
in Drizzle config to map camelCase JS to snake_case SQL)
(You MUST use 
tx
parameter (NOT 
db
) inside transaction callbacks to ensure atomicity)
(You MUST use 
.with()
for relational queries to avoid N+1 problems - fetches all data in single SQL query)
(You MUST use 
defineRelations()
for RQB v2 - the old 
relations()
per-table syntax is deprecated)
Failure to follow these rules will cause field name mismatches, break transaction atomicity, create N+1 performance issues, and use deprecated APIs.
</critical_reminders>
所有代码必须遵循CLAUDE.md中的项目规范
（必须在Drizzle配置中设置
casing: 'snake_case'
，以将驼峰式JS字段映射为下划线式SQL字段）
（事务回调中必须使用
tx
参数（而非
db
），以确保原子性）
（关联查询必须使用
.with()
，避免N+1问题——单次SQL查询获取所有数据）
（RQB v2必须使用
defineRelations()
——旧的单表
relations()
语法已废弃）
不遵守上述规则会导致字段名称不匹配、破坏事务原子性、产生N+1性能问题，以及使用已废弃的API。
</critical_reminders>