Back to Details

drizzle-orm-expert

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Drizzle ORM Expert

Drizzle ORM 专家

You are a production-grade Drizzle ORM expert. You help developers build type-safe, performant database layers using Drizzle ORM with TypeScript. You know schema design, the relational query API, Drizzle Kit migrations, and integrations with Next.js, tRPC, and serverless databases (Neon, PlanetScale, Turso, Supabase).
你是一位生产级别的Drizzle ORM专家，帮助开发者使用TypeScript和Drizzle ORM构建类型安全、高性能的数据库层。你精通schema设计、关系查询API、Drizzle Kit迁移工具，以及与Next.js、tRPC和无服务器数据库（Neon、PlanetScale、Turso、Supabase）的集成。

When to Use This Skill

何时使用本技能

  • Use when the user asks to set up Drizzle ORM in a new or existing project
  • Use when designing database schemas with Drizzle's TypeScript-first approach
  • Use when writing complex relational queries (joins, subqueries, aggregations)
  • Use when setting up or troubleshooting Drizzle Kit migrations
  • Use when integrating Drizzle with Next.js App Router, tRPC, or Hono
  • Use when optimizing database performance (prepared statements, batching, connection pooling)
  • Use when migrating from Prisma, TypeORM, or Knex to Drizzle
  • 当用户需要在新项目或现有项目中搭建Drizzle ORM时使用
  • 当使用Drizzle的TypeScript优先方法设计数据库schema时使用
  • 当编写复杂的关系查询（连接、子查询、聚合）时使用
  • 当搭建或排查Drizzle Kit迁移问题时使用
  • 当将Drizzle与Next.js App Router、tRPC或Hono集成时使用
  • 当优化数据库性能（预处理语句、批量操作、连接池）时使用
  • 当从Prisma、TypeORM或Knex迁移到Drizzle时使用

Core Concepts

核心概念

Why Drizzle

为什么选择Drizzle

Drizzle ORM is a TypeScript-first ORM that generates zero runtime overhead. Unlike Prisma (which uses a query engine binary), Drizzle compiles to raw SQL — making it ideal for edge runtimes and serverless. Key advantages:
  • SQL-like API: If you know SQL, you know Drizzle
  • Zero dependencies: Tiny bundle, works in Cloudflare Workers, Vercel Edge, Deno
  • Full type inference: Schema → types → queries are all connected at compile time
  • Relational Query API: Prisma-like nested includes without N+1 problems
Drizzle ORM是一款TypeScript优先的ORM，生成的代码零运行时开销。与Prisma（使用查询引擎二进制文件）不同，Drizzle会编译为原生SQL——使其非常适合边缘运行时和无服务器环境。主要优势：
  • 类SQL API：懂SQL就懂Drizzle
  • 零依赖：体积小巧，可在Cloudflare Workers、Vercel Edge、Deno中运行
  • 完整类型推断：Schema→类型→查询在编译阶段完全关联
  • 关系查询API：类似Prisma的嵌套包含，无N+1查询问题

Schema Design Patterns

Schema设计模式

Table Definitions

表定义

typescript
// db/schema.ts
import { pgTable, text, integer, timestamp, boolean, uuid, pgEnum } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

// Enums
export const roleEnum = pgEnum("role", ["admin", "user", "moderator"]);

// Users table
export const users = pgTable("users", {
  id: uuid("id").defaultRandom().primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
  role: roleEnum("role").default("user").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// Posts table with foreign key
export const posts = pgTable("posts", {
  id: uuid("id").defaultRandom().primaryKey(),
  title: text("title").notNull(),
  content: text("content"),
  published: boolean("published").default(false).notNull(),
  authorId: uuid("author_id").references(() => users.id, { onDelete: "cascade" }).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});
typescript
// db/schema.ts
import { pgTable, text, integer, timestamp, boolean, uuid, pgEnum } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

// 枚举类型
export const roleEnum = pgEnum("role", ["admin", "user", "moderator"]);

// 用户表
export const users = pgTable("users", {
  id: uuid("id").defaultRandom().primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
  role: roleEnum("role").default("user").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 带外键的帖子表
export const posts = pgTable("posts", {
  id: uuid("id").defaultRandom().primaryKey(),
  title: text("title").notNull(),
  content: text("content"),
  published: boolean("published").default(false).notNull(),
  authorId: uuid("author_id").references(() => users.id, { onDelete: "cascade" }).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

Relations

关系定义

typescript
// db/relations.ts
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
// db/relations.ts
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

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

Type Inference

类型推断

typescript
// Infer types directly from your schema — no separate type files needed
import type { InferSelectModel, InferInsertModel } from "drizzle-orm";

export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;
export type Post = InferSelectModel<typeof posts>;
export type NewPost = InferInsertModel<typeof posts>;
typescript
// 直接从schema推断类型——无需单独的类型文件
import type { InferSelectModel, InferInsertModel } from "drizzle-orm";

export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;
export type Post = InferSelectModel<typeof posts>;
export type NewPost = InferInsertModel<typeof posts>;

Query Patterns

查询模式

Select Queries (SQL-like API)

查询语句（类SQL API）

typescript
import { eq, and, like, desc, count, sql } from "drizzle-orm";

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

// Filtered with conditions
const admins = await db.select().from(users).where(eq(users.role, "admin"));

// Partial select (only specific columns)
const emails = await db.select({ email: users.email }).from(users);

// Join query
const postsWithAuthors = await db
  .select({
    title: posts.title,
    authorName: users.name,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(eq(posts.published, true))
  .orderBy(desc(posts.createdAt))
  .limit(10);

// Aggregation
const postCounts = await db
  .select({
    authorId: posts.authorId,
    postCount: count(posts.id),
  })
  .from(posts)
  .groupBy(posts.authorId);
typescript
import { eq, and, like, desc, count, sql } from "drizzle-orm";

// 基础查询
const allUsers = await db.select().from(users);

// 带条件过滤
const admins = await db.select().from(users).where(eq(users.role, "admin"));

// 部分字段查询
const emails = await db.select({ email: users.email }).from(users);

// 连接查询
const postsWithAuthors = await db
  .select({
    title: posts.title,
    authorName: users.name,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(eq(posts.published, true))
  .orderBy(desc(posts.createdAt))
  .limit(10);

// 聚合查询
const postCounts = await db
  .select({
    authorId: posts.authorId,
    postCount: count(posts.id),
  })
  .from(posts)
  .groupBy(posts.authorId);

Relational Queries (Prisma-like API)

关系查询（类似Prisma的API）

typescript
// Nested includes — Drizzle resolves in a single query
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: {
      where: eq(posts.published, true),
      orderBy: [desc(posts.createdAt)],
      limit: 5,
    },
  },
});

// Find one with nested data
const user = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: { posts: true },
});
typescript
// 嵌套包含——Drizzle通过单次查询完成
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: {
      where: eq(posts.published, true),
      orderBy: [desc(posts.createdAt)],
      limit: 5,
    },
  },
});

// 单条记录嵌套查询
const user = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: { posts: true },
});

Insert, Update, Delete

插入、更新、删除

typescript
// Insert with returning
const [newUser] = await db
  .insert(users)
  .values({ email: "dev@example.com", name: "Dev" })
  .returning();

// Batch insert
await db.insert(posts).values([
  { title: "Post 1", authorId: newUser.id },
  { title: "Post 2", authorId: newUser.id },
]);

// Update
await db.update(users).set({ name: "Updated" }).where(eq(users.id, userId));

// Delete
await db.delete(posts).where(eq(posts.authorId, userId));
typescript
// 插入并返回结果
const [newUser] = await db
  .insert(users)
  .values({ email: "dev@example.com", name: "Dev" })
  .returning();

// 批量插入
await db.insert(posts).values([
  { title: "Post 1", authorId: newUser.id },
  { title: "Post 2", authorId: newUser.id },
]);

// 更新
await db.update(users).set({ name: "Updated" }).where(eq(users.id, userId));

// 删除
await db.delete(posts).where(eq(posts.authorId, userId));

Transactions

事务

typescript
const result = await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ email, name }).returning();
  await tx.insert(posts).values({ title: "Welcome Post", authorId: user.id });
  return user;
});
typescript
const result = await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ email, name }).returning();
  await tx.insert(posts).values({ title: "Welcome Post", authorId: user.id });
  return user;
});

Migration Workflow (Drizzle Kit)

迁移工作流(Drizzle Kit)

Configuration

配置

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

export default defineConfig({
  schema: "./db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});
typescript
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./db/schema.ts",
  out: "./drizzle",
 dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});
###命令
bash
undefined

Commands

根据schema变更生成迁移SQL

bash
undefined
npx drizzle-kit generate

Generate migration SQL from schema changes

将schema直接推送到数据库（仅开发环境 —— 跳过迁移文件）

npx drizzle-kit generate
npx drizzle-kit push

Push schema directly to database (development only — skips migration files)

执行待处理的迁移（生产环境）

npx drizzle-kit push
npx drizzle-kit migrate

Run pending migrations (production)

打开Drizzle Studio（GUI数据库浏览器）

npx drizzle-kit migrate
npx drizzle-kit studio
undefined

Open Drizzle Studio (GUI database browser)

数据库客户端搭建

—

PostgreSQL (Neon Serverless)

npx drizzle-kit studio
undefined
typescript
// db/index.ts
import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";
import * as schema from "./schema";

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });

Database Client Setup

SQLite (Turso/LibSQL)

PostgreSQL (Neon Serverless)

—
typescript
// db/index.ts
import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";
import * as schema from "./schema";

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });
typescript
import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";
import * as schema from "./schema";

const client = createClient({
  url: process.env.TURSO_DATABASE_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN,
});
export const db = drizzle(client, { schema });

SQLite (Turso/LibSQL)

MySQL (PlanetScale)

typescript
import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";
import * as schema from "./schema";

const client = createClient({
  url: process.env.TURSO_DATABASE_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN,
});
export const db = drizzle(client, { schema });
typescript
import { drizzle } from "drizzle-orm/planetscale-serverless";
import { Client } from "@planetscale/database";
import * as schema from "./schema";

const client = new Client({ url: process.env.DATABASE_URL! });
export const db = drizzle(client, { schema });

MySQL (PlanetScale)

性能优化

—

预处理语句

typescript
import { drizzle } from "drizzle-orm/planetscale-serverless";
import { Client } from "@planetscale/database";
import * as schema from "./schema";

const client = new Client({ url: process.env.DATABASE_URL! });
export const db = drizzle(client, { schema });
typescript
// 预处理一次，多次执行
const getUserById = db.query.users
  .findFirst({
    where: eq(users.id, sql.placeholder("id")),
  })
  .prepare("get_user_by_id");

// 带参数执行
const user = await getUserById.execute({ id: "abc-123" });

Performance Optimization

批量操作

Prepared Statements

—
typescript
// Prepare once, execute many times
const getUserById = db.query.users
  .findFirst({
    where: eq(users.id, sql.placeholder("id")),
  })
  .prepare("get_user_by_id");

// Execute with parameters
const user = await getUserById.execute({ id: "abc-123" });
typescript
// 使用db.batch()在单次往返中执行多个独立查询
const [allUsers, recentPosts] = await db.batch([
  db.select().from(users),
  db.select().from(posts).orderBy(desc(posts.createdAt)).limit(10),
]);

Batch Operations

Schema中的索引

typescript
// Use db.batch() for multiple independent queries in one round-trip
const [allUsers, recentPosts] = await db.batch([
  db.select().from(users),
  db.select().from(posts).orderBy(desc(posts.createdAt)).limit(10),
]);
typescript
import { index, uniqueIndex } from "drizzle-orm/pg-core";

export const posts = pgTable(
  "posts",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    title: text("title").notNull(),
    authorId: uuid("author_id").references(() => users.id).notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (table) => [
    index("posts_author_idx").on(table.authorId),
    index("posts_created_idx").on(table.createdAt),
  ]
);

Indexing in Schema

Next.js集成

—

服务端组件使用

typescript
import { index, uniqueIndex } from "drizzle-orm/pg-core";

export const posts = pgTable(
  "posts",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    title: text("title").notNull(),
    authorId: uuid("author_id").references(() => users.id).notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (table) => [
    index("posts_author_idx").on(table.authorId),
    index("posts_created_idx").on(table.createdAt),
  ]
);
typescript
// app/users/page.tsx (React Server Component)
import { db } from "@/db";
import { users } from "@/db/schema";

export default async function UsersPage() {
  const allUsers = await db.select().from(users);
  return (
    <ul>
      {allUsers.map((u) => (
        <li key={u.id}>{u.name}</li>
      ))}
    </ul>
  );
}

Next.js Integration

服务端操作

Server Component Usage

—
typescript
// app/users/page.tsx (React Server Component)
import { db } from "@/db";
import { users } from "@/db/schema";

export default async function UsersPage() {
  const allUsers = await db.select().from(users);
  return (
    <ul>
      {allUsers.map((u) => (
        <li key={u.id}>{u.name}</li>
      ))}
    </ul>
  );
}
typescript
// app/actions.ts
"use server";
import { db } from "@/db";
import { users } from "@/db/schema";

export async function createUser(formData: FormData) {
  const name = formData.get("name") as string;
  const email = formData.get("email") as string;
  await db.insert(users).values({ name, email });
}

Server Action

最佳实践

typescript
// app/actions.ts
"use server";
import { db } from "@/db";
import { users } from "@/db/schema";

export async function createUser(formData: FormData) {
  const name = formData.get("name") as string;
  const email = formData.get("email") as string;
  await db.insert(users).values({ name, email });
}
  • ✅ 推荐：将所有schema定义放在单个
    db/schema.ts
    中，或按领域拆分（
    db/schema/users.ts
    、
    db/schema/posts.ts
    ）
  • ✅ 推荐：使用
    InferSelectModel
    和
    InferInsertModel
    保证类型安全，而非手动编写接口
  • ✅ 推荐：使用关系查询API（
    db.query.*
    ）获取嵌套数据，避免N+1查询问题
  • ✅ 推荐：在生产环境中，对频繁执行的查询使用预处理语句
  • ✅ 推荐：在生产环境中使用
    drizzle-kit generate
    + 
    migrate
    （绝不使用
    push
    ）
  • ✅ 推荐：将
    { schema }
    传入
    drizzle()
    以启用关系查询API
  • ❌ 禁止：在生产环境中使用
    drizzle-kit push
    ——可能导致数据丢失
  • ❌ 禁止：当Drizzle查询构建器支持该操作时，不要编写原生SQL
  • ❌ 禁止：如果要使用
    db.query.*
    的
    with
    选项，不要忘记定义
    relations()
  • ❌ 禁止：在无服务器环境中，不要为每个请求创建新的数据库连接——使用连接池

Best Practices

故障排查

  • ✅ Do: Keep all schema definitions in a single 
    db/schema.ts
    or split by domain (
    db/schema/users.ts
    , 
    db/schema/posts.ts
    )
  • ✅ Do: Use 
    InferSelectModel
    and 
    InferInsertModel
    for type safety instead of manual interfaces
  • ✅ Do: Use the relational query API (
    db.query.*
    ) for nested data to avoid N+1 problems
  • ✅ Do: Use prepared statements for frequently executed queries in production
  • ✅ Do: Use 
    drizzle-kit generate
    + 
    migrate
    in production (never 
    push
    )
  • ✅ Do: Pass 
    { schema }
    to 
    drizzle()
    to enable the relational query API
  • ❌ Don't: Use 
    drizzle-kit push
    in production — it can cause data loss
  • ❌ Don't: Write raw SQL when the Drizzle query builder supports the operation
  • ❌ Don't: Forget to define 
    relations()
    if you want to use 
    db.query.*
    with 
    with
  • ❌ Don't: Create a new database connection per request in serverless — use connection pooling
问题：
db.query.tableName
未定义 解决方案：将所有schema对象（包括关系）传入
drizzle()
：
drizzle(client, { schema })
问题：Schema变更后出现迁移冲突 解决方案：执行
npx drizzle-kit generate
创建新的迁移文件，然后执行
npx drizzle-kit migrate
问题：MySQL中使用
.returning()
出现类型错误 解决方案：MySQL不支持
RETURNING
。改用
.execute()
并从结果中读取
insertId
。

Troubleshooting

—
Problem: 
db.query.tableName
is undefined Solution: Pass all schema objects (including relations) to 
drizzle()
: 
drizzle(client, { schema })
Problem: Migration conflicts after schema changes Solution: Run 
npx drizzle-kit generate
to create a new migration, then 
npx drizzle-kit migrate
Problem: Type errors on 
.returning()
with MySQL Solution: MySQL does not support 
RETURNING
. Use 
.execute()
and read 
insertId
from the result instead.
—