drizzle-orm-v0

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Skill: drizzle-orm

Skill：drizzle-orm

Scope

范围

Applies to: Drizzle ORM v0.45+ for PostgreSQL, MySQL, SQLite - schema definitions, type-safe queries, migrations
Does NOT cover: Database driver setup, connection pooling configuration, other ORMs

适用于：Drizzle ORM v0.45+ for PostgreSQL、MySQL、SQLite——schema 定义、类型安全查询、迁移
不涵盖：数据库驱动配置、连接池配置、其他 ORM

Assumptions

前提条件

Drizzle ORM v0.45+
Drizzle Kit v0.31+ (dev dependency) for migrations
PostgreSQL, MySQL, or SQLite database
TypeScript v5+ with strict mode
ESM module system

Drizzle ORM v0.45+
Drizzle Kit v0.31+（开发依赖），用于迁移
PostgreSQL、MySQL 或 SQLite 数据库
TypeScript v5+，启用 strict 模式
ESM 模块系统

Principles

原则

Schemas defined using table builders (
```
pgTable
```
,
```
mysqlTable
```
,
```
sqliteTable
```
) with typed columns
Column types match database constraints (
```
varchar
```
with length,
```
timestamp
```
with mode)
Indexes defined in table definition second parameter using
```
index()
```
helper
Text primary keys with
```
.references()
```
for foreign keys are a valid default
Query helpers (
```
eq
```
,
```
and
```
,
```
or
```
,
```
like
```
) provide type-safe SQL construction
Type inference via
```
$inferSelect
```
and
```
$inferInsert
```
eliminates manual types
Migrations generated with
```
drizzle-kit generate
```
(not
```
push
```
in production)
Prepared statements optimize frequently executed queries
Schemas organized by domain (one file per entity/table)
Transactions (
```
db.transaction
```
) ensure atomic multi-step operations

使用表构建器（
```
pgTable
```
、
```
mysqlTable
```
、
```
sqliteTable
```
）和类型化列定义 schema
列类型与数据库约束匹配（
```
varchar
```
包含长度，
```
timestamp
```
包含 mode）
在表定义的第二个参数中使用
```
index()
```
辅助函数定义索引
文本主键配合
```
.references()
```
定义外键，是有效的默认方案
查询辅助函数（
```
eq
```
、
```
and
```
、
```
or
```
、
```
like
```
）提供类型安全的 SQL 构建能力
通过
```
$inferSelect
```
和
```
$inferInsert
```
推断类型，无需手动定义类型
使用
```
drizzle-kit generate
```
生成迁移（生产环境中不要使用
```
push
```
）
预处理语句可优化频繁执行的查询
按领域组织 schema（每个实体/表一个文件）
事务（
```
db.transaction
```
）可确保多步操作的原子性

Constraints

约束

MUST

必须

Use Drizzle Kit for migrations (
```
drizzle-kit generate
```
,
```
drizzle-kit migrate
```
)
Define column types matching database constraints
Use query helpers instead of raw SQL

使用 Drizzle Kit 进行迁移（
```
drizzle-kit generate
```
、
```
drizzle-kit migrate
```
）
定义与数据库约束匹配的列类型
使用查询辅助函数，而不是原始 SQL

SHOULD

应该

Use transactions for multi-step operations
Use prepared statements for frequently executed queries
Export types via
```
$inferSelect
```
and
```
$inferInsert
```
Handle
```
DrizzleQueryError
```
for structured error handling
Organize schemas by domain (one file per entity)
Use selective field loading (not full rows)
Specify length for
```
varchar
```
columns
Use
```
index()
```
helper in table definitions
Use PGLite for testing PostgreSQL schemas
Use
```
relations()
```
and relational query builder when the project already uses them

对多步操作使用事务
对频繁执行的查询使用预处理语句
通过
```
$inferSelect
```
和
```
$inferInsert
```
导出类型
处理
```
DrizzleQueryError
```
，实现结构化错误处理
按领域组织 schema（每个实体一个文件）
使用选择性字段加载，而不是加载完整行
为
```
varchar
```
列指定长度
在表定义中使用
```
index()
```
辅助函数
使用 PGLite 测试 PostgreSQL schema
当项目已经使用
```
relations()
```
和关系查询构建器时，继续沿用

OPTIONAL

可选

Identity columns (
```
generatedAlwaysAsIdentity
```
) instead of
```
serial
```
in PostgreSQL
Relational query builder (
```
db.query.*
```
) for complex joins when relations are defined

在 PostgreSQL 中使用 Identity 列（
```
generatedAlwaysAsIdentity
```
），而不是
```
serial
```
定义 relations 后，使用关系查询构建器（
```
db.query.*
```
）处理复杂 join

AVOID

避免

Raw SQL unless necessary
Manual type assertions (use inferred types)
Skipping migration generation
```
serial
```
in new PostgreSQL tables (use identity columns)
Over-indexing (index only where queries justify)
Fetching full rows when only few columns needed
```
push
```
in production (use
```
generate
```
+
```
migrate
```
)
String-based timestamp mode when DB supports date/time types

除非必要，避免使用原始 SQL
避免手动类型断言（使用推断类型）
避免跳过迁移生成
在新的 PostgreSQL 表中避免使用
```
serial
```
（使用 Identity 列）
避免过度创建索引（仅在查询需要时创建索引）
仅需少量字段时，避免获取完整行
在生产环境中避免使用
```
push
```
（使用
```
generate
```
+
```
migrate
```
）
当数据库支持日期/时间类型时，避免使用字符串形式的 timestamp mode

Interactions

交互关系

Works with next Server Components and API routes
Complements fastify for API development

可与 next 的 Server Components 和 API routes 配合使用
与 fastify 互补，适合 API 开发

Patterns

模式

Schema Definition

Schema 定义

typescript

import { index, pgTable, text, timestamp, varchar } from 'drizzle-orm/pg-core'

export const users = pgTable(
  'users',
  {
    id: text('id').primaryKey(),
    email: varchar('email', { length: 255 }).notNull().unique(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().notNull(),
  },
  table => [index('users_email_idx').on(table.email)],
)

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

typescript

import { index, pgTable, text, timestamp, varchar } from 'drizzle-orm/pg-core'

export const users = pgTable(
  'users',
  {
    id: text('id').primaryKey(),
    email: varchar('email', { length: 255 }).notNull().unique(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().notNull(),
  },
  table => [index('users_email_idx').on(table.email)],
)

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

Identity Columns

Identity 列

typescript

import { pgTable, integer, generatedAlwaysAsIdentity } from 'drizzle-orm/pg-core'

export const posts = pgTable('posts', {
  id: integer('id').primaryKey().generatedAlwaysAsIdentity(),
})

typescript

import { pgTable, integer, generatedAlwaysAsIdentity } from 'drizzle-orm/pg-core'

export const posts = pgTable('posts', {
  id: integer('id').primaryKey().generatedAlwaysAsIdentity(),
})

Query Builder

查询构建器

typescript

import { eq } from 'drizzle-orm'

const user = await db
  .select()
  .from(users)
  .where(eq(users.id, userId))
  .limit(1)

const userWithPosts = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: { posts: true },
})

const userEmail = await db
  .select({ email: users.email })
  .from(users)
  .where(eq(users.id, userId))

typescript

import { eq } from 'drizzle-orm'

const user = await db
  .select()
  .from(users)
  .where(eq(users.id, userId))
  .limit(1)

const userWithPosts = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: { posts: true },
})

const userEmail = await db
  .select({ email: users.email })
  .from(users)
  .where(eq(users.id, userId))

Transactions

事务

typescript

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

typescript

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

Prepared Statements

预处理语句

typescript

import { placeholder } from 'drizzle-orm'

const getUserByEmail = db
  .select()
  .from(users)
  .where(eq(users.email, placeholder('email')))
  .prepare('get_user_by_email')

const user = await getUserByEmail.execute({ email: 'user@example.com' })

typescript

import { placeholder } from 'drizzle-orm'

const getUserByEmail = db
  .select()
  .from(users)
  .where(eq(users.email, placeholder('email')))
  .prepare('get_user_by_email')

const user = await getUserByEmail.execute({ email: 'user@example.com' })

Error Handling

错误处理

typescript

import { DrizzleQueryError } from 'drizzle-orm'

try {
  const user = await db.select().from(users).where(eq(users.id, userId))
} catch (error) {
  if (error instanceof DrizzleQueryError) {
    if (error.cause?.code === '23505') {
      throw new Error('User already exists')
    }
  }
  throw error
}

typescript

import { DrizzleQueryError } from 'drizzle-orm'

try {
  const user = await db.select().from(users).where(eq(users.id, userId))
} catch (error) {
  if (error instanceof DrizzleQueryError) {
    if (error.cause?.code === '23505') {
      throw new Error('User already exists')
    }
  }
  throw error
}

Relations

关系

typescript

import { relations } from 'drizzle-orm'

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}))

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}))

typescript

import { relations } from 'drizzle-orm'

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}))

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}))

Database Connection

数据库连接

typescript

import { drizzle } from 'drizzle-orm/node-postgres'
import { Pool } from 'pg'
import * as schema from './schema'

const pool = new Pool({ connectionString: process.env.DATABASE_URL })
export const db = drizzle(pool, { schema })

typescript

import { drizzle } from 'drizzle-orm/node-postgres'
import { Pool } from 'pg'
import * as schema from './schema'

const pool = new Pool({ connectionString: process.env.DATABASE_URL })
export const db = drizzle(pool, { schema })

Drizzle Kit Config

Drizzle Kit 配置

typescript

import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  dialect: 'postgresql',
  schema: './src/db/schema/index.ts',
  out: './src/db/migrations',
  dbCredentials: { url: process.env.DATABASE_URL! },
  migrations: {
    table: '__drizzle_migrations',
    schema: 'public',
  },
  verbose: true,
  strict: true,
})

typescript

import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  dialect: 'postgresql',
  schema: './src/db/schema/index.ts',
  out: './src/db/migrations',
  dbCredentials: { url: process.env.DATABASE_URL! },
  migrations: {
    table: '__drizzle_migrations',
    schema: 'public',
  },
  verbose: true,
  strict: true,
})

References

参考资料

Query Patterns - CRUD operations, joins, aggregations
PostgreSQL Patterns - PostgreSQL-specific patterns

查询模式——CRUD 操作、join、聚合
PostgreSQL 模式——PostgreSQL 专用模式