drizzle-orm

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Drizzle ORM Developer Guide

Drizzle ORM开发者指南

This skill provides guidelines, patterns, and best practices for working with Drizzle ORM in this project.

本skill提供在本项目中使用Drizzle ORM的指导规范、模式和最佳实践。

Quick Start

快速开始

For detailed development guidelines, patterns, and code examples, refer to references/patterns.md.

如需了解详细的开发规范、模式和代码示例，请参考references/patterns.md。

Core Philosophy

核心理念

Type-Safe by Default: Leverage TypeScript and Drizzle's type inference to catch database errors at compile time
Performance First: Use prepared statements and query optimization techniques
SQL Transparency: Keep SQL queries readable and avoid over-abstraction
Minimal Overhead: Drizzle is a thin layer - use it accordingly without unnecessary complexity

默认类型安全：利用TypeScript和Drizzle的类型推导能力，在编译阶段就发现数据库错误
性能优先：使用预编译语句和查询优化技术
SQL透明：保持SQL查询的可读性，避免过度抽象
最小开销：Drizzle是轻量封装层，使用时避免引入不必要的复杂度

Common Tasks

常见任务

Schema Definition

Schema定义

Organize schemas by domain in separate files. Use fluent constraint chaining and add indexes for frequently queried columns.

typescript

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

按领域将schema拆分到不同文件中。使用流畅的约束链式调用，为高频查询的列添加索引。

typescript

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

Type Exports

类型导出

Always export table types for use in your application:

typescript

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

始终导出表类型供应用使用：

typescript

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

Prepared Statements

预编译语句

Use prepared statements for frequently executed queries for extreme performance benefits:

typescript

export const getUserById = db
  .select()
  .from(users)
  .where(eq(users.id, sql.placeholder('id')))
  .prepare();

// Usage
const user = await getUserById.execute({ id: userId });

对高频执行的查询使用预编译语句，可大幅提升性能：

typescript

export const getUserById = db
  .select()
  .from(users)
  .where(eq(users.id, sql.placeholder('id')))
  .prepare();

// Usage
const user = await getUserById.execute({ id: userId });

Transactions

事务

Use transactions for multi-step operations to maintain data consistency:

typescript

return db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values(userData).returning();
  const [profile] = await tx.insert(profiles).values({ userId: user.id, ...profileData }).returning();
  return { user, profile };
});

对多步操作使用事务以保证数据一致性：

typescript

return db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values(userData).returning();
  const [profile] = await tx.insert(profiles).values({ userId: user.id, ...profileData }).returning();
  return { user, profile };
});

Migration Commands

迁移命令

CRITICAL: Always use package scripts for migrations. Never call

drizzle-kit

directly.

Backend:
```
pnpm run db:generate
```
(generates migration)
Backend:
```
pnpm run db:migrate
```
(applies migrations)
Landing:
```
pnpm run db:generate
```
(generates migration)
Landing:
```
pnpm run db:migrate
```
(applies migrations)

重要提示：始终使用项目的package脚本执行迁移，禁止直接调用

drizzle-kit

。

Backend:
```
pnpm run db:generate
```
(生成迁移文件)
Backend:
```
pnpm run db:migrate
```
(执行迁移)
Landing:
```
pnpm run db:generate
```
(生成迁移文件)
Landing:
```
pnpm run db:migrate
```
(执行迁移)

Code Organization

代码组织

db/
  index.ts           (Drizzle client initialization)
  schema/
    users.ts         (User table & relations)
    posts.ts         (Post table & relations)
  queries/
    users.ts         (User query functions)
    posts.ts         (Post query functions)
  migrations/        (Auto-generated migration files)

db/
  index.ts           (Drizzle客户端初始化)
  schema/
    users.ts         (用户表及关联关系)
    posts.ts         (帖子表及关联关系)
  queries/
    users.ts         (用户相关查询函数)
    posts.ts         (帖子相关查询函数)
  migrations/        (自动生成的迁移文件)

Common Pitfalls to Avoid

需要避免的常见陷阱

Missing Indexes: Always index columns used in WHERE, JOIN, and ORDER BY clauses
Unbounded Queries: Always use
```
limit()
```
and
```
offset()
```
for user-facing queries
Unsafe Raw SQL: Never concatenate user input into raw SQL - use
```
sql.placeholder()
```
N+1 Queries: Use eager loading with
```
with()
```
or batch queries
Missing Transactions: Wrap multi-step operations in transactions

缺少索引：所有用于WHERE、JOIN和ORDER BY子句的列都必须添加索引
无边界查询：面向用户的查询必须使用
```
limit()
```
和
```
offset()
```
不安全的原生SQL：禁止将用户输入拼接到原生SQL中，使用
```
sql.placeholder()
```
处理
N+1查询问题：使用
```
with()
```
做预加载或者批量查询
缺少事务：多步操作需要包裹在事务中

Validation Checklist

校验清单

Before finishing a task involving Drizzle ORM:

Check schema definitions have proper indexes for queried columns
Verify prepared statements are used for repeated queries
Ensure transactions wrap multi-step operations
Use package scripts for migrations (never call drizzle-kit directly)
Run type checks (
```
pnpm run typecheck
```
) and tests (
```
pnpm run test
```
)

For detailed rules and code examples, consult references/patterns.md.

完成涉及Drizzle ORM的任务前，请确认：

检查schema定义中，用于查询的列都有合适的索引
确认重复执行的查询使用了预编译语句
确保多步操作都包裹在事务中
使用package脚本执行迁移（禁止直接调用drizzle-kit）
执行类型检查（
```
pnpm run typecheck
```
）和测试（
```
pnpm run test
```
）

如需了解详细规则和代码示例，请查阅references/patterns.md。