drizzle-orm

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Drizzle ORM

Overview

概述

Drizzle ORM is a lightweight, type-safe TypeScript ORM that maps directly to SQL for PostgreSQL, MySQL, and SQLite. It provides both a SQL-like query builder and a relational queries API, with zero dependencies and full serverless compatibility. Use Drizzle when you need compile-time type safety with SQL-level control; avoid it when you need a full active-record ORM with automatic migrations (use Prisma) or when working with MongoDB/NoSQL databases.

Drizzle ORM是一款轻量、类型安全的TypeScript ORM，可直接映射至PostgreSQL、MySQL和SQLite的SQL语句。它提供类SQL查询构建器和关系查询API，无依赖且完全兼容无服务器环境。当你需要编译时类型安全并拥有SQL级别的控制能力时选择Drizzle；若你需要带自动迁移功能的完整活动记录ORM（推荐使用Prisma），或处理MongoDB/NoSQL数据库时，则不建议使用。

Quick Reference

快速参考

Pattern	API	Key Points
Schema definition	`pgTable('name', { columns }, (t) => [indexes])`	Third arg returns array of indexes/constraints
Column types	`text()` , `integer()` , `boolean()` , `timestamp()`	Import from `drizzle-orm/pg-core`
Type inference	`typeof table.$inferSelect` , `$inferInsert`	Derive TS types directly from schema
Relational queries	`db.query.table.findMany({ with, where })`	Requires schema passed to `drizzle()` client
SQL-like queries	`db.select().from(table).where()`	Chainable, returns array of rows
Insert	`db.insert(table).values({}).returning()`	`.returning()` for getting inserted rows
Update	`db.update(table).set({}).where().returning()`	Always include `.where()` to avoid full-table updates
Delete	`db.delete(table).where()`	Always include `.where()` to avoid full-table deletes
Upsert	`.onConflictDoUpdate({ target, set })`	Chain after `.insert().values()`
Transactions	`db.transaction(async (tx) => { ... })`	Auto-rollback on thrown errors
Filters	`eq()` , `and()` , `or()` , `inArray()` , `sql\` ``	Import operators from `drizzle-orm`
Relations	`relations(table, ({ one, many }) => ({}))`	Declares logical relations for relational queries
Generate migrations	`drizzle-kit generate`	Creates SQL migration files from schema diff
Apply migrations	`drizzle-kit migrate` or `migrate()` in code	Applies pending migrations to database
Push schema	`drizzle-kit push`	Direct schema push without migration files
Prepared statements	`db.select().from(t).where(eq(t.id, sql.placeholder('id'))).prepare()`	Reusable parameterized queries
Views	`pgView('name').as(qb => ...)`	Regular and materialized views
$count utility	`db.$count(table, filter?)`	Shorthand count, usable as subquery
Generated columns	`text().generatedAlwaysAs(() => sql\` ...`)`	Computed columns (virtual or stored)
Check constraints	`check('name', sql\` condition`)`	Row-level validation at database level

模式	API	关键要点
模式定义	`pgTable('name', { columns }, (t) => [indexes])`	第三个参数返回索引/约束数组
列类型	`text()` , `integer()` , `boolean()` , `timestamp()`	从 `drizzle-orm/pg-core` 导入
类型推断	`typeof table.$inferSelect` , `$inferInsert`	直接从模式派生TypeScript类型
关系查询	`db.query.table.findMany({ with, where })`	需要将模式传入 `drizzle()` 客户端
类SQL查询	`db.select().from(table).where()`	可链式调用，返回行数组
插入	`db.insert(table).values({}).returning()`	使用 `.returning()` 获取插入的行
更新	`db.update(table).set({}).where().returning()`	务必包含 `.where()` 以避免全表更新
删除	`db.delete(table).where()`	务必包含 `.where()` 以避免全表删除
插入或更新	`.onConflictDoUpdate({ target, set })`	在 `.insert().values()` 后链式调用
事务	`db.transaction(async (tx) => { ... })`	抛出错误时自动回滚
过滤器	`eq()` , `and()` , `or()` , `inArray()` , `sql\` ``	从 `drizzle-orm` 导入操作符
关系	`relations(table, ({ one, many }) => ({}))`	为关系查询声明逻辑关系
生成迁移	`drizzle-kit generate`	根据模式差异创建SQL迁移文件
应用迁移	`drizzle-kit migrate` 或代码中的 `migrate()`	将待处理迁移应用到数据库
推送模式	`drizzle-kit push`	无需迁移文件直接推送模式
预编译语句	`db.select().from(t).where(eq(t.id, sql.placeholder('id'))).prepare()`	可复用的参数化查询
视图	`pgView('name').as(qb => ...)`	普通视图和物化视图
$count工具	`db.$count(table, filter?)`	简写统计方法，可用作子查询
生成列	`text().generatedAlwaysAs(() => sql\` ...`)`	计算列（虚拟或存储）
检查约束	`check('name', sql\` condition`)`	数据库层面的行级验证

Common Mistakes

常见错误

Mistake	Correct Pattern
Missing `.returning()` on insert/update	Chain `.returning()` to get back inserted/updated rows
Omitting `.where()` on update/delete	Always provide `.where()` to avoid affecting all rows
Using `any` for query result types	Use `$inferSelect` / `$inferInsert` for type-safe results
Not passing `schema` to `drizzle()` client	Pass `{ schema }` to enable `db.query` relational API
Forgetting indexes on foreign key columns	Add explicit indexes for columns used in joins/lookups
Wrapping related writes without a transaction	Use `db.transaction()` for multi-table mutations
Using raw column names with `casing: 'snake_case'`	With casing option, define columns in camelCase without name arg
Declaring relations but missing the inverse	Both sides of a relation need declarations for relational queries
Using `serial()` in new PostgreSQL schemas	Prefer `integer().generatedAlwaysAsIdentity()` (modern approach)
Not handling `findFirst` returning `undefined`	Check for `undefined` before accessing properties
Importing column types from wrong dialect module	Use `drizzle-orm/pg-core` for PostgreSQL, `mysql-core` , `sqlite-core`
Using `.references()` instead of `relations()`	`.references()` is SQL foreign key; `relations()` is for query API

错误	正确用法
插入/更新时遗漏 `.returning()`	链式调用 `.returning()` 以获取插入/更新后的行
更新/删除时省略 `.where()`	务必提供 `.where()` 以避免影响所有行
为查询结果类型使用 `any`	使用 `$inferSelect` / `$inferInsert` 获取类型安全的结果
未将 `schema` 传入 `drizzle()` 客户端	传入 `{ schema }` 以启用 `db.query` 关系API
忘记在外键列上创建索引	为用于连接/查找的列添加显式索引
未使用事务包裹关联写入操作	对多表变更使用 `db.transaction()`
启用 `casing: 'snake_case'` 时使用原始列名	启用该选项时，无需指定名称参数，直接用驼峰式定义列
声明了关系但缺少反向关联	关系的两端都需要声明才能使用关系查询
在新PostgreSQL模式中使用 `serial()`	推荐使用 `integer().generatedAlwaysAsIdentity()` （现代方案）
未处理 `findFirst` 返回 `undefined` 的情况	访问属性前先检查是否为 `undefined`
从错误的方言模块导入列类型	PostgreSQL使用 `drizzle-orm/pg-core` ，MySQL使用 `mysql-core` ，SQLite使用 `sqlite-core`
使用 `.references()` 替代 `relations()`	`.references()` 是SQL外键； `relations()` 用于查询API

Delegation

委托建议

If the
tanstack-query
skill is available, delegate client-side caching and server state management to it. Otherwise, recommend:
npx skills add oakoss/agent-skills --skill tanstack-query
If the
tanstack-start
skill is available, delegate server function patterns for database access to it. Otherwise, recommend:
npx skills add oakoss/agent-skills --skill tanstack-start
If the
electricsql
skill is available, delegate Electric setup, shape configuration, and write patterns to it. Otherwise, recommend:
pnpm dlx skills add oakoss/agent-skills -s electricsql -a claude-code -y
If the
pglite
skill is available, delegate PGlite-specific configuration, extensions, and browser storage patterns to it. Otherwise, recommend:
pnpm dlx skills add oakoss/agent-skills -s pglite -a claude-code -y

Schema validation: Use
```
drizzle-zod
```
to generate Zod schemas from Drizzle tables for input validation
Query pattern discovery: Use
```
Explore
```
agent
Code review: Delegate to
```
code-reviewer
```
agent

如果
tanstack-query
技能可用，将客户端缓存和服务器状态管理委托给它。否则，推荐执行：
npx skills add oakoss/agent-skills --skill tanstack-query
如果
tanstack-start
技能可用，将数据库访问的服务器函数模式委托给它。否则，推荐执行：
npx skills add oakoss/agent-skills --skill tanstack-start
如果
electricsql
技能可用，将Electric的设置、形状配置和写入模式委托给它。否则，推荐执行：
pnpm dlx skills add oakoss/agent-skills -s electricsql -a claude-code -y
如果
pglite
技能可用，将PGlite特定的配置、扩展和浏览器存储模式委托给它。否则，推荐执行：
pnpm dlx skills add oakoss/agent-skills -s pglite -a claude-code -y

模式验证：使用
```
drizzle-zod
```
从Drizzle表生成Zod模式以进行输入验证
查询模式发现：使用
```
Explore
```
agent
代码审查：委托给
```
code-reviewer
```
agent

References

参考资料

Schema definition, column types, constraints, indexes, and type inference
Relational queries, SQL-like API, joins, subqueries, and aggregations
Insert, update, delete, upsert, and transactions
Relations: one, many, nested with clauses, self-referencing
Migrations: drizzle-kit generate, migrate, push, pull, studio
Filter operators: eq, ne, gt, lt, like, inArray, sql template
Views, materialized views, generated columns, check constraints, $count, batch API
ElectricSQL + PGlite integration: driver setup, schema-to-shape mapping, type inference, local sync

模式定义、列类型、约束、索引和类型推断
关系查询、类SQL API、连接、子查询和聚合
插入、更新、删除、插入或更新和事务
关系：一对一、一对多、嵌套with子句、自引用
迁移：drizzle-kit generate、migrate、push、pull、studio
过滤操作符：eq、ne、gt、lt、like、inArray、sql模板
视图、物化视图、生成列、检查约束、$count、批量API
ElectricSQL + PGlite集成：驱动设置、模式到形状映射、类型推断、本地同步