drizzle-orm

Drizzle ORM

以 TypeScript 为先的 SQL ORM，零依赖，具备编译时类型安全和类 SQL 语法。针对边缘运行时、无服务器环境和 PostgreSQL 进行了优化。

安装

# Core ORM + PostgreSQL driver
npm install drizzle-orm pg
npm install -D drizzle-kit @types/pg

# Alternative drivers
npm install postgres          # postgres.js
npm install @neondatabase/serverless  # Neon serverless
npm install @electric-sql/pglite     # PGlite embedded

数据库连接

// Simple connection string
import { drizzle } from 'drizzle-orm/node-postgres';
const db = drizzle(process.env.DATABASE_URL!);

// With connection pool
import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL! });
const db = drizzle({ client: pool });

// With schema for relational queries
import * as schema from './schema';
const db = drizzle({ client: pool, schema });

// With SSL
const db = drizzle({
  connection: { connectionString: process.env.DATABASE_URL!, ssl: true }
});

架构定义（PostgreSQL）

表声明

import { pgTable, serial, text, varchar, integer, boolean,
  timestamp, json, jsonb, uuid } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  email: varchar({ length: 255 }).notNull().unique(),
  name: text().notNull(),
  role: text({ enum: ['admin', 'user', 'guest'] }).default('user'),
  metadata: jsonb().$type<{ theme: string; locale: string }>(),
  isActive: boolean().default(true),
  createdAt: timestamp().defaultNow().notNull(),
  updatedAt: timestamp().defaultNow().notNull(),
});

类型推断

type User = typeof users.$inferSelect;   // SELECT result type
type NewUser = typeof users.$inferInsert; // INSERT input type

PostgreSQL 列类型

类型	导入	TypeScript	说明
integer()	pg-core	number	4 字节有符号
smallint()	pg-core	number	2 字节有符号
bigint({ mode: 'number' })	pg-core	number	8 字节，mode 决定 JS 类型
serial()	pg-core	number	自动递增，4 字节
bigserial({ mode: 'number' })	pg-core	number	自动递增，8 字节
text()	pg-core	string	长度不限
varchar({ length: 255 })	pg-core	string	可变长度，带最大值
char({ length: 10 })	pg-core	string	固定长度
boolean()	pg-core	boolean
timestamp()	pg-core	Date	{ withTimezone: true }、{ mode: 'string' }
date()	pg-core	Date	原始值使用 { mode: 'string' }
time()	pg-core	string	{ withTimezone: true }
interval()	pg-core	string	{ fields: 'day' }
json()	pg-core	unknown	使用 .$type<T>()
jsonb()	pg-core	unknown	二进制 JSON，使用 .$type<T>()
uuid()	pg-core	string	使用 .defaultRandom() 自动生成
numeric({ precision, scale })	pg-core	string	使用 { mode: 'number' } 转为 JS 数字
real()	pg-core	number	4 字节浮点数
doublePrecision()	pg-core	number	8 字节浮点数
bytea()	pg-core	Buffer	二进制数据
point()	pg-core	[number, number]	对象格式使用 { mode: 'xy' }

枚举

import { pgEnum } from 'drizzle-orm/pg-core';

export const roleEnum = pgEnum('role', ['admin', 'user', 'guest']);

export const users = pgTable('users', {
  id: serial().primaryKey(),
  role: roleEnum().default('user'),
});

标识列（PostgreSQL 10+）

// Preferred over serial() for new schemas
id: integer().primaryKey().generatedAlwaysAsIdentity({ startWith: 1000 }),
id: integer().primaryKey().generatedByDefaultAsIdentity(),

索引与约束

import { pgTable, serial, text, integer, index, uniqueIndex,
  primaryKey, foreignKey, unique, check } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';

export const posts = pgTable('posts', {
  id: serial().primaryKey(),
  title: text().notNull(),
  authorId: integer().notNull().references(() => users.id, { onDelete: 'cascade' }),
  slug: text().notNull(),
  status: text({ enum: ['draft', 'published'] }),
}, (table) => [
  index('title_idx').on(table.title),
  uniqueIndex('slug_idx').on(table.slug),
  check('status_check', sql`${table.status} in ('draft', 'published')`),
]);

// Composite primary key (junction tables)
export const usersToGroups = pgTable('users_to_groups', {
  userId: integer().notNull().references(() => users.id),
  groupId: integer().notNull().references(() => groups.id),
}, (table) => [
  primaryKey({ columns: [table.userId, table.groupId] }),
]);

// Composite unique constraint
export const example = pgTable('example', {
  a: integer(),
  b: text(),
}, (table) => [
  unique('ab_unique').on(table.a, table.b),
  unique('nulls_example').on(table.a).nullsNotDistinct(), // PG 15+
]);

关系

关系是应用层抽象，与数据库外键相互独立。它们支持关系查询 API（db.query）。

import { relations } from 'drizzle-orm';

// One-to-many
export const authorsRelations = relations(authors, ({ many }) => ({
  posts: many(posts),
}));

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

// Many-to-many (through junction table)
export const usersRelations = relations(users, ({ many }) => ({
  usersToGroups: many(usersToGroups),
}));
export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({
  user: one(users, { fields: [usersToGroups.userId], references: [users.id] }),
  group: one(groups, { fields: [usersToGroups.groupId], references: [groups.id] }),
}));

// Disambiguating multiple relations to same table
export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId], references: [users.id],
    relationName: 'author',
  }),
  reviewer: one(users, {
    fields: [posts.reviewerId], references: [users.id],
    relationName: 'reviewer',
  }),
}));

查询

SELECT

import { eq, ne, gt, gte, lt, lte, like, ilike, inArray,
  isNull, isNotNull, and, or, between, sql, desc, asc, count } from 'drizzle-orm';

// Basic
const allUsers = await db.select().from(users);

// Partial select
const names = await db.select({ id: users.id, name: users.name }).from(users);

// Filtering
await db.select().from(users).where(eq(users.email, '[email protected]'));
await db.select().from(users).where(
  and(eq(users.role, 'admin'), gt(users.createdAt, new Date('2024-01-01')))
);
await db.select().from(users).where(inArray(users.id, [1, 2, 3]));

// Sorting & pagination
await db.select().from(users).orderBy(desc(users.createdAt)).limit(10).offset(20);

// Distinct / Distinct On (PostgreSQL)
await db.selectDistinct().from(users);
await db.selectDistinctOn([users.id]).from(users).orderBy(users.id);

INSERT

// Single row with returning
const [newUser] = await db.insert(users)
  .values({ email: '[email protected]', name: 'John' })
  .returning();

// Multiple rows
await db.insert(users).values([
  { email: '[email protected]', name: 'A' },
  { email: '[email protected]', name: 'B' },
]);

// Upsert
await db.insert(users)
  .values({ id: 1, email: '[email protected]', name: 'Dan' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'Dan Updated' },
  });

await db.insert(users)
  .values({ id: 1, email: '[email protected]', name: 'Dan' })
  .onConflictDoNothing({ target: users.email });

// Insert from select
await db.insert(employees).select(
  db.select({ name: users.name }).from(users).where(eq(users.role, 'employee'))
);

UPDATE

await db.update(users).set({ name: 'Jane' }).where(eq(users.id, 1));
await db.update(users).set({ updatedAt: sql`NOW()` }).where(eq(users.id, 1));

// With returning
const updated = await db.update(users)
  .set({ name: 'Jane' })
  .where(eq(users.id, 1))
  .returning();

// UPDATE...FROM
await db.update(users)
  .set({ cityId: cities.id })
  .from(cities)
  .where(and(eq(cities.name, 'Seattle'), eq(users.name, 'John')));

DELETE

await db.delete(users).where(eq(users.id, 1));
const deleted = await db.delete(users).where(eq(users.id, 1)).returning();

联接

// Inner join
const result = await db.select({ user: users, post: posts })
  .from(users)
  .innerJoin(posts, eq(users.id, posts.authorId));

// Left join (pet fields become nullable)
await db.select().from(users).leftJoin(posts, eq(users.id, posts.authorId));

// Aggregation with join
await db.select({
  authorName: authors.name,
  postCount: count(posts.id),
}).from(authors)
  .leftJoin(posts, eq(authors.id, posts.authorId))
  .groupBy(authors.id);

// Self-join with alias
import { alias } from 'drizzle-orm/pg-core';
const parent = alias(users, 'parent');
await db.select().from(users).leftJoin(parent, eq(parent.id, users.parentId));

关系查询

// Requires schema passed to drizzle() constructor
const usersWithPosts = await db.query.users.findMany({
  with: { posts: { with: { comments: true } } },
  columns: { id: true, name: true },
  where: (users, { eq }) => eq(users.role, 'admin'),
  orderBy: (users, { desc }) => [desc(users.createdAt)],
  limit: 10,
});

const user = await db.query.users.findFirst({
  where: (users, { eq }) => eq(users.id, 1),
  with: { posts: true },
});

事务

// Auto-rollback on error
await db.transaction(async (tx) => {
  await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100` })
    .where(eq(accounts.id, 1));
  await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100` })
    .where(eq(accounts.id, 2));
});

// Conditional rollback
await db.transaction(async (tx) => {
  const [account] = await tx.select().from(accounts).where(eq(accounts.id, 1));
  if (account.balance < 100) {
    tx.rollback();
    return;
  }
  await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100` })
    .where(eq(accounts.id, 1));
});

// Nested transactions (savepoints)
await db.transaction(async (tx) => {
  await tx.insert(users).values({ name: 'John', email: '[email protected]' });
  await tx.transaction(async (tx2) => {
    await tx2.insert(posts).values({ title: 'Post', authorId: 1 });
  });
});

// PostgreSQL isolation levels
await db.transaction(async (tx) => { /* ... */ }, {
  isolationLevel: 'serializable',
  accessMode: 'read write',
  deferrable: true,
});

使用 drizzle-kit 进行迁移

配置

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  dialect: 'postgresql',
  schema: './src/db/schema.ts',
  out: './drizzle',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

核心命令

# Generate SQL migration from schema changes
npx drizzle-kit generate

# Apply migrations to database
npx drizzle-kit migrate

# Push schema directly (no migration files — for rapid prototyping)
npx drizzle-kit push

# Pull existing database schema into TypeScript
npx drizzle-kit pull

# Generate custom empty migration
npx drizzle-kit generate --custom --name=seed-users

# Verify migration consistency
npx drizzle-kit check

# Launch visual database browser
npx drizzle-kit studio

编程式迁移

import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';

const db = drizzle(process.env.DATABASE_URL!);
await migrate(db, { migrationsFolder: './drizzle' });

完整的迁移策略、团队工作流和生产环境实践，请参阅 PostgreSQL 迁移。

PostgreSQL 架构

import { pgSchema, serial, text } from 'drizzle-orm/pg-core';

export const tenantSchema = pgSchema('tenant_1');
export const tenantUsers = tenantSchema.table('users', {
  id: serial().primaryKey(),
  name: text().notNull(),
});
// Generates: CREATE SCHEMA "tenant_1"; CREATE TABLE "tenant_1"."users" (...)

视图

import { pgView, pgMaterializedView } from 'drizzle-orm/pg-core';

export const activeUsers = pgView('active_users').as((qb) =>
  qb.select().from(users).where(eq(users.isActive, true))
);

export const userStats = pgMaterializedView('user_stats').as((qb) =>
  qb.select({
    userId: users.id,
    postCount: count(posts.id),
  }).from(users).leftJoin(posts, eq(users.id, posts.authorId)).groupBy(users.id)
);

// Refresh at runtime
await db.refreshMaterializedView(userStats);
await db.refreshMaterializedView(userStats).concurrently();

公用表表达式（CTE）

const sq = db.$with('active').as(
  db.select().from(users).where(eq(users.isActive, true))
);
const result = await db.with(sq).select().from(sq);

// CTE with INSERT
const inserted = db.$with('new_user').as(
  db.insert(users).values({ name: 'John', email: '[email protected]' }).returning()
);
await db.with(inserted).select().from(inserted);

风险提示

问题	风险
JSON 列使用 any/unknown，但未使用 .$type<T>()	类型安全性丢失
使用原始字符串拼接而非 sql 模板	SQL 注入
在 db.transaction() 外执行多步变更	部分写入
对大型表执行未使用 .limit() 的 select()	内存耗尽
外键列缺少索引	联接速度慢
经常用于筛选的列缺少索引	全表扫描
使用 serial() 而非 generatedAlwaysAsIdentity()	serial 在 PG 10+ 中已是旧式用法

参考资料

• PostgreSQL 迁移 — drizzle-kit 配置、迁移策略、团队工作流、生产环境模式、自定义迁移、程序化迁移 API。在管理数据库架构变更时加载。

• 高级架构 — 自定义类型、品牌类型、复合键、数组、多架构、视图、物化视图、pgEnum 模式。在设计复杂数据库架构时加载。

• 查询模式 — 子查询、CTE、原始 SQL、预处理语句、动态查询、批量操作、聚合、窗口函数。在优化查询或处理复杂筛选时加载。

• 性能 — 连接池、预处理语句、防止 N+1、查询优化、边缘运行时集成、无服务器模式。在扩展或优化数据库性能时加载。

• Drizzle 与 Prisma — 功能对比、迁移指南、架构差异、选择 Drizzle 的时机。在评估 ORM 或从 Prisma 迁移时加载。