postgres-drizzle

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

PostgreSQL + Drizzle ORM

Type-safe database applications with PostgreSQL 18 and Drizzle ORM.

使用PostgreSQL 18和Drizzle ORM构建类型安全的数据库应用。

Essential Commands

核心命令

bash

npx drizzle-kit generate   # Generate migration from schema changes
npx drizzle-kit migrate    # Apply pending migrations
npx drizzle-kit push       # Push schema directly (dev only!)
npx drizzle-kit studio     # Open database browser

bash

npx drizzle-kit generate   # 根据架构变更生成迁移文件
npx drizzle-kit migrate    # 应用待处理的迁移
npx drizzle-kit push       # 直接推送架构（仅开发环境使用！）
npx drizzle-kit studio     # 打开数据库浏览器

Quick Decision Trees

快速决策树

"How do I model this relationship?"

"如何建模这种关联关系？"

Relationship type?
├─ One-to-many (user has posts)     → FK on "many" side + relations()
├─ Many-to-many (posts have tags)   → Junction table + relations()
├─ One-to-one (user has profile)    → FK with unique constraint
└─ Self-referential (comments)      → FK to same table

关联类型？
├─ 一对多（用户拥有多篇文章）     → 在“多”侧设置外键 + 使用relations()
├─ 多对多（文章拥有多个标签）   → 中间表 + 使用relations()
├─ 一对一（用户拥有一个资料）    → 带唯一约束的外键
└─ 自关联（评论）      → 指向同一张表的外键

"Why is my query slow?"

"为什么我的查询速度慢？"

Slow query?
├─ Missing index on WHERE/JOIN columns  → Add index
├─ N+1 queries in loop                  → Use relational queries API
├─ Full table scan                      → EXPLAIN ANALYZE, add index
├─ Large result set                     → Add pagination (limit/offset)
└─ Connection overhead                  → Enable connection pooling

查询速度慢？
├─ WHERE/JOIN列缺少索引  → 添加索引
├─ 循环中出现N+1查询                  → 使用关联查询API
├─ 全表扫描                      → 执行EXPLAIN ANALYZE，添加索引
├─ 结果集过大                     → 添加分页（limit/offset）
└─ 连接开销大                  → 启用连接池

"Which drizzle-kit command?"

"应该使用哪个drizzle-kit命令？"

What do I need?
├─ Schema changed, need SQL migration   → drizzle-kit generate
├─ Apply migrations to database         → drizzle-kit migrate
├─ Quick dev iteration (no migration)   → drizzle-kit push
└─ Browse/edit data visually            → drizzle-kit studio

我的需求是？
├─ 架构已变更，需要生成SQL迁移文件   → drizzle-kit generate
├─ 向数据库应用迁移         → drizzle-kit migrate
├─ 快速开发迭代（无需迁移）   → drizzle-kit push
└─ 可视化浏览/编辑数据            → drizzle-kit studio

Directory Structure

目录结构

src/db/
├── schema/
│   ├── index.ts          # Re-export all tables
│   ├── users.ts          # Table + relations
│   └── posts.ts          # Table + relations
├── db.ts                 # Connection with pooling
└── migrate.ts            # Migration runner
drizzle/
└── migrations/           # Generated SQL files
drizzle.config.ts         # drizzle-kit config

src/db/
├── schema/
│   ├── index.ts          # 导出所有表
│   ├── users.ts          # 表 + 关联关系
│   └── posts.ts          # 表 + 关联关系
├── db.ts                 # 带连接池的连接配置
└── migrate.ts            # 迁移执行器
drizzle/
└── migrations/           # 生成的SQL文件
drizzle.config.ts         # drizzle-kit配置文件

Schema Patterns

架构模式

Basic Table with Timestamps

带时间戳的基础表

typescript

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

typescript

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

Foreign Key with Index

带索引的外键

typescript

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull().references(() => users.id),
  title: varchar('title', { length: 255 }).notNull(),
}, (table) => [
  index('posts_user_id_idx').on(table.userId), // ALWAYS index FKs
]);

typescript

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull().references(() => users.id),
  title: varchar('title', { length: 255 }).notNull(),
}, (table) => [
  index('posts_user_id_idx').on(table.userId), // 始终为外键添加索引
]);

Relations

关联关系

typescript

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

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

typescript

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

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

Query Patterns

查询模式

Relational Query (Avoid N+1)

关联查询（避免N+1问题）

typescript

// ✓ Single query with nested data
const usersWithPosts = await db.query.users.findMany({
  with: { posts: true },
});

typescript

// ✓ 单次查询获取嵌套数据
const usersWithPosts = await db.query.users.findMany({
  with: { posts: true },
});

Filtered Query

过滤查询

typescript

const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.status, 'active'));

typescript

const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.status, 'active'));

Transaction

事务

typescript

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

typescript

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

Performance Checklist

性能检查清单

Priority	Check	Impact
CRITICAL	Index all foreign keys	Prevents full table scans on JOINs
CRITICAL	Use relational queries for nested data	Avoids N+1
HIGH	Connection pooling in production	Reduces connection overhead
HIGH	`EXPLAIN ANALYZE` slow queries	Identifies missing indexes
MEDIUM	Partial indexes for filtered subsets	Smaller, faster indexes
MEDIUM	UUIDv7 for PKs (PG18+)	Better index locality

优先级	检查项	影响
关键	为所有外键添加索引	避免JOIN时的全表扫描
关键	对嵌套数据使用关联查询	避免N+1问题
高	生产环境启用连接池	减少连接开销
高	对慢查询执行 `EXPLAIN ANALYZE`	识别缺失的索引
中	为过滤子集创建部分索引	索引更小、速度更快
中	使用UUIDv7作为主键（PG18+）	更好的索引局部性

Anti-Patterns (CRITICAL)

反模式（关键）

Anti-Pattern	Problem	Fix
No FK index	Slow JOINs, full scans	Add index on every FK column
N+1 in loops	Query per row	Use `with:` relational queries
No pooling	Connection per request	Use `@neondatabase/serverless` or similar
`push` in prod	Data loss risk	Always use `generate` + `migrate`
Storing JSON as text	No validation, bad queries	Use `jsonb()` column type

反模式	问题	修复方案
外键未加索引	JOIN速度慢、全表扫描	为每个外键列添加索引
循环中的N+1查询	逐行查询	使用 `with:` 关联查询
未使用连接池	每个请求创建连接	使用 `@neondatabase/serverless` 或类似工具
生产环境使用 `push`	数据丢失风险	始终使用 `generate` + `migrate`
将JSON存储为文本	无验证、查询性能差	使用 `jsonb()` 列类型

Reference Documentation

参考文档

File	Purpose
references/SCHEMA.md	Column types, constraints
references/QUERIES.md	Operators, joins, aggregations
references/RELATIONS.md	One-to-many, many-to-many
references/MIGRATIONS.md	drizzle-kit workflows
references/POSTGRES.md	PG18 features, RLS, partitioning
references/PERFORMANCE.md	Indexing, optimization
references/CHEATSHEET.md	Quick reference

文件	用途
references/SCHEMA.md	列类型、约束
references/QUERIES.md	操作符、连接、聚合
references/RELATIONS.md	一对多、多对多
references/MIGRATIONS.md	drizzle-kit工作流
references/POSTGRES.md	PG18特性、RLS、分区
references/PERFORMANCE.md	索引、优化
references/CHEATSHEET.md	快速参考

Resources

资源

Drizzle ORM

Official Documentation: https://orm.drizzle.team
GitHub Repository: https://github.com/drizzle-team/drizzle-orm
Drizzle Kit (Migrations): https://orm.drizzle.team/kit-docs/overview

官方文档: https://orm.drizzle.team
GitHub仓库: https://github.com/drizzle-team/drizzle-orm
Drizzle Kit（迁移工具）: https://orm.drizzle.team/kit-docs/overview

PostgreSQL

Official Documentation: https://www.postgresql.org/docs/
SQL Commands Reference: https://www.postgresql.org/docs/current/sql-commands.html
Performance Tips: https://www.postgresql.org/docs/current/performance-tips.html
Index Types: https://www.postgresql.org/docs/current/indexes-types.html
JSON Functions: https://www.postgresql.org/docs/current/functions-json.html
Row Level Security: https://www.postgresql.org/docs/current/ddl-rowsecurity.html

官方文档: https://www.postgresql.org/docs/
SQL命令参考: https://www.postgresql.org/docs/current/sql-commands.html
性能优化技巧: https://www.postgresql.org/docs/current/performance-tips.html
索引类型: https://www.postgresql.org/docs/current/indexes-types.html
JSON函数: https://www.postgresql.org/docs/current/functions-json.html
行级安全: https://www.postgresql.org/docs/current/ddl-rowsecurity.html