Back to Details

drizzle

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Drizzle ORM

Drizzle ORM

Modern TypeScript-first ORM with zero dependencies, compile-time type safety, and SQL-like syntax. Optimized for edge runtimes and serverless environments.
现代化的TypeScript优先ORM，零依赖，编译时类型安全，类SQL语法。针对边缘运行时和无服务器环境优化。

Quick Start

快速开始

Installation

安装

bash
undefined
bash
undefined

Core ORM

Core ORM

npm install drizzle-orm
npm install drizzle-orm

Database driver (choose one)

Database driver (choose one)

npm install pg # PostgreSQL npm install mysql2 # MySQL npm install better-sqlite3 # SQLite
npm install pg # PostgreSQL npm install mysql2 # MySQL npm install better-sqlite3 # SQLite

Drizzle Kit (migrations)

Drizzle Kit (migrations)

npm install -D drizzle-kit
undefined
npm install -D drizzle-kit
undefined

Basic Setup

基础配置

typescript
// db/schema.ts
import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

// db/client.ts
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
// db/schema.ts
import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

// db/client.ts
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 });

First Query

首次查询

typescript
import { db } from './db/client';
import { users } from './db/schema';
import { eq } from 'drizzle-orm';

// Insert
const newUser = await db.insert(users).values({
  email: 'user@example.com',
  name: 'John Doe',
}).returning();

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

// Where
const user = await db.select().from(users).where(eq(users.id, 1));

// Update
await db.update(users).set({ name: 'Jane Doe' }).where(eq(users.id, 1));

// Delete
await db.delete(users).where(eq(users.id, 1));
typescript
import { db } from './db/client';
import { users } from './db/schema';
import { eq } from 'drizzle-orm';

// 插入
const newUser = await db.insert(users).values({
  email: 'user@example.com',
  name: 'John Doe',
}).returning();

// 查询所有
const allUsers = await db.select().from(users);

// 条件查询
const user = await db.select().from(users).where(eq(users.id, 1));

// 更新
await db.update(users).set({ name: 'Jane Doe' }).where(eq(users.id, 1));

// 删除
await db.delete(users).where(eq(users.id, 1));

Schema Definition

Schema定义

Column Types Reference

列类型参考

PostgreSQLMySQLSQLiteTypeScript
serial()
serial()
integer()
number
text()
text()
text()
string
integer()
int()
integer()
number
boolean()
boolean()
integer()
boolean
timestamp()
datetime()
integer()
Date
json()
json()
text()
unknown
uuid()
varchar(36)
text()
string
PostgreSQLMySQLSQLiteTypeScript
serial()
serial()
integer()
number
text()
text()
text()
string
integer()
int()
integer()
number
boolean()
boolean()
integer()
boolean
timestamp()
datetime()
integer()
Date
json()
json()
text()
unknown
uuid()
varchar(36)
text()
string

Common Schema Patterns

常见Schema模式

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  passwordHash: varchar('password_hash', { length: 255 }).notNull(),
  role: text('role', { enum: ['admin', 'user', 'guest'] }).default('user'),
  metadata: json('metadata').$type<{ theme: string; locale: string }>(),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  emailIdx: unique('email_unique_idx').on(table.email),
}));

// Infer TypeScript types
type User = typeof users.$inferSelect;
type NewUser = typeof users.$inferInsert;
typescript
import { pgTable, serial, text, varchar, integer, boolean, timestamp, json, unique } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  passwordHash: varchar('password_hash', { length: 255 }).notNull(),
  role: text('role', { enum: ['admin', 'user', 'guest'] }).default('user'),
  metadata: json('metadata').$type<{ theme: string; locale: string }>(),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  emailIdx: unique('email_unique_idx').on(table.email),
}));

// 推导TypeScript类型
type User = typeof users.$inferSelect;
type NewUser = typeof users.$inferInsert;

Relations

关联关系

One-to-Many

一对多

typescript
import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const authors = pgTable('authors', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  authorId: integer('author_id').notNull().references(() => authors.id),
});

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

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

// Query with relations
const authorsWithPosts = await db.query.authors.findMany({
  with: { posts: true },
});
typescript
import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const authors = pgTable('authors', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  authorId: integer('author_id').notNull().references(() => authors.id),
});

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

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

// 带关联关系的查询
const authorsWithPosts = await db.query.authors.findMany({
  with: { posts: true },
});

Many-to-Many

多对多

typescript
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const groups = pgTable('groups', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const usersToGroups = pgTable('users_to_groups', {
  userId: integer('user_id').notNull().references(() => users.id),
  groupId: integer('group_id').notNull().references(() => groups.id),
}, (table) => ({
  pk: primaryKey({ columns: [table.userId, table.groupId] }),
}));

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

export const groupsRelations = relations(groups, ({ many }) => ({
  users: 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] }),
}));
typescript
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const groups = pgTable('groups', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const usersToGroups = pgTable('users_to_groups', {
  userId: integer('user_id').notNull().references(() => users.id),
  groupId: integer('group_id').notNull().references(() => groups.id),
}, (table) => ({
  pk: primaryKey({ columns: [table.userId, table.groupId] }),
}));

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

export const groupsRelations = relations(groups, ({ many }) => ({
  users: 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] }),
}));

Queries

查询操作

Filtering

过滤

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

// Equality
await db.select().from(users).where(eq(users.email, 'user@example.com'));

// Comparison
await db.select().from(users).where(gt(users.id, 10));

// Pattern matching
await db.select().from(users).where(like(users.name, '%John%'));

// Multiple conditions
await db.select().from(users).where(
  and(
    eq(users.role, 'admin'),
    gt(users.createdAt, new Date('2024-01-01'))
  )
);

// IN clause
await db.select().from(users).where(inArray(users.id, [1, 2, 3]));

// NULL checks
await db.select().from(users).where(isNull(users.deletedAt));
typescript
import { eq, ne, gt, gte, lt, lte, like, ilike, inArray, isNull, isNotNull, and, or, between } from 'drizzle-orm';

// 相等匹配
await db.select().from(users).where(eq(users.email, 'user@example.com'));

// 比较匹配
await db.select().from(users).where(gt(users.id, 10));

// 模式匹配
await db.select().from(users).where(like(users.name, '%John%'));

// 多条件匹配
await db.select().from(users).where(
  and(
    eq(users.role, 'admin'),
    gt(users.createdAt, new Date('2024-01-01'))
  )
);

// IN子句
await db.select().from(users).where(inArray(users.id, [1, 2, 3]));

// NULL检查
await db.select().from(users).where(isNull(users.deletedAt));

Joins

连接查询

typescript
import { eq } from 'drizzle-orm';

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

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

// Multiple joins with aggregation
import { count, sql } from 'drizzle-orm';

const result = await db
  .select({
    authorName: authors.name,
    postCount: count(posts.id),
  })
  .from(authors)
  .leftJoin(posts, eq(authors.id, posts.authorId))
  .groupBy(authors.id);
typescript
import { eq } from 'drizzle-orm';

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

// 左连接
const result = await db
  .select({
    user: users,
    post: posts,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

// 多连接与聚合
import { count, sql } from 'drizzle-orm';

const result = await db
  .select({
    authorName: authors.name,
    postCount: count(posts.id),
  })
  .from(authors)
  .leftJoin(posts, eq(authors.id, posts.authorId))
  .groupBy(authors.id);

Pagination & Sorting

分页与排序

typescript
import { desc, asc } from 'drizzle-orm';

// Order by
await db.select().from(users).orderBy(desc(users.createdAt));

// Limit & offset
await db.select().from(users).limit(10).offset(20);

// Pagination helper
function paginate(page: number, pageSize: number = 10) {
  return db.select().from(users)
    .limit(pageSize)
    .offset(page * pageSize);
}
typescript
import { desc, asc } from 'drizzle-orm';

// 排序
await db.select().from(users).orderBy(desc(users.createdAt));

// 限制与偏移
await db.select().from(users).limit(10).offset(20);

// 分页助手函数
function paginate(page: number, pageSize: number = 10) {
  return db.select().from(users)
    .limit(pageSize)
    .offset(page * pageSize);
}

Transactions

事务

typescript
// Auto-rollback on error
await db.transaction(async (tx) => {
  await tx.insert(users).values({ email: 'user@example.com', name: 'John' });
  await tx.insert(posts).values({ title: 'First Post', authorId: 1 });
  // If any query fails, entire transaction rolls back
});

// Manual control
const tx = db.transaction(async (tx) => {
  const user = await tx.insert(users).values({ ... }).returning();

  if (!user) {
    tx.rollback();
    return;
  }

  await tx.insert(posts).values({ authorId: user.id });
});
typescript
// 出错时自动回滚
await db.transaction(async (tx) => {
  await tx.insert(users).values({ email: 'user@example.com', name: 'John' });
  await tx.insert(posts).values({ title: 'First Post', authorId: 1 });
  // 若任一查询失败，整个事务回滚
});

// 手动控制事务
const tx = db.transaction(async (tx) => {
  const user = await tx.insert(users).values({ ... }).returning();

  if (!user) {
    tx.rollback();
    return;
  }

  await tx.insert(posts).values({ authorId: user.id });
});

Migrations

迁移

Drizzle Kit Configuration

Drizzle Kit配置

typescript
// drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
  schema: './db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
} satisfies Config;
typescript
// drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
  schema: './db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
} satisfies Config;

Migration Workflow

迁移工作流

bash
undefined
bash
undefined

Generate migration

生成迁移文件

npx drizzle-kit generate
npx drizzle-kit generate

View SQL

查看SQL内容

cat drizzle/0000_migration.sql
cat drizzle/0000_migration.sql

Apply migration

应用迁移

npx drizzle-kit migrate
npx drizzle-kit migrate

Introspect existing database

反向解析现有数据库

npx drizzle-kit introspect
npx drizzle-kit introspect

Drizzle Studio (database GUI)

Drizzle Studio（数据库GUI）

npx drizzle-kit studio
undefined
npx drizzle-kit studio
undefined

Example Migration

迁移示例

sql
-- drizzle/0000_initial.sql
CREATE TABLE IF NOT EXISTS "users" (
  "id" serial PRIMARY KEY NOT NULL,
  "email" varchar(255) NOT NULL,
  "name" text NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  CONSTRAINT "users_email_unique" UNIQUE("email")
);
sql
-- drizzle/0000_initial.sql
CREATE TABLE IF NOT EXISTS "users" (
  "id" serial PRIMARY KEY NOT NULL,
  "email" varchar(255) NOT NULL,
  "name" text NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  CONSTRAINT "users_email_unique" UNIQUE("email")
);

Navigation

导航

Detailed References

详细参考

  • 🏗️ Advanced Schemas - Custom types, composite keys, indexes, constraints, multi-tenant patterns. Load when designing complex database schemas.
  • 🔍 Query Patterns - Subqueries, CTEs, raw SQL, prepared statements, batch operations. Load when optimizing queries or handling complex filtering.
  • ⚡ Performance - Connection pooling, query optimization, N+1 prevention, prepared statements, edge runtime integration. Load when scaling or optimizing database performance.
  • 🔄 vs Prisma - Feature comparison, migration guide, when to choose Drizzle over Prisma. Load when evaluating ORMs or migrating from Prisma.
  • 🏗️ 高级Schema - 自定义类型、复合键、索引、约束、多租户模式。设计复杂数据库Schema时查看。
  • 🔍 查询模式 - 子查询、CTE、原生SQL、预编译语句、批量操作。优化查询或处理复杂过滤时查看。
  • ⚡ 性能优化 - 连接池、查询优化、N+1问题预防、预编译语句、边缘运行时集成。扩展或优化数据库性能时查看。
  • 🔄 与Prisma对比 - 功能对比、迁移指南、何时选择Drizzle而非Prisma。评估ORM或从Prisma迁移时查看。

Red Flags

注意事项

Stop and reconsider if:
  • Using 
    any
    or 
    unknown
    for JSON columns without type annotation
  • Building raw SQL strings without using 
    sql
    template (SQL injection risk)
  • Not using transactions for multi-step data modifications
  • Fetching all rows without pagination in production queries
  • Missing indexes on foreign keys or frequently queried columns
  • Using 
    select()
    without specifying columns for large tables
如果出现以下情况，请停止并重新考虑：
  • 为JSON列使用
    any
    或
    unknown
    而不添加类型注解
  • 不使用
    sql
    模板构建原生SQL字符串（存在SQL注入风险）
  • 多步骤数据修改时不使用事务
  • 生产环境查询中不使用分页就获取所有行
  • 外键或频繁查询的列缺少索引
  • 针对大表使用
    select()
    时不指定列

Performance Benefits vs Prisma

与Prisma的性能对比

MetricDrizzlePrisma
Bundle Size~35KB~230KB
Cold Start~10ms~250ms
Query SpeedBaseline~2-3x slower
Memory~10MB~50MB
Type GenerationRuntime inferenceBuild-time generation
指标DrizzlePrisma
包大小~35KB~230KB
冷启动时间~10ms~250ms
查询速度基准水平慢2-3倍
内存占用~10MB~50MB
类型生成运行时推导构建时生成

Integration

集成

  • typescript-core: Type-safe schema inference with 
    satisfies
  • nextjs-core: Server Actions, Route Handlers, Middleware integration
  • Database Migration: Safe schema evolution patterns
  • typescript-core: 使用
    satisfies
    实现类型安全的Schema推导
  • nextjs-core: 与Server Actions、路由处理器、中间件集成
  • Database Migration: 安全的Schema演进模式

Related Skills

相关技能

When using Drizzle, these skills enhance your workflow:
  • prisma: Alternative ORM comparison: Drizzle vs Prisma trade-offs
  • typescript: Advanced TypeScript patterns for type-safe queries
  • nextjs: Drizzle with Next.js Server Actions and API routes
  • sqlalchemy: SQLAlchemy patterns for Python developers learning Drizzle
[Full documentation available in these skills if deployed in your bundle]
使用Drizzle时，以下技能可提升你的工作流：
  • prisma: 替代ORM对比：Drizzle与Prisma的权衡
  • typescript: 用于类型安全查询的高级TypeScript模式
  • nextjs: Drizzle与Next.js Server Actions及API路由的结合
  • sqlalchemy: 适合Python开发者学习Drizzle的SQLAlchemy模式
[完整文档可在已部署的相关技能包中查看]