Drizzle ORM 指南 2026：类型安全 SQL、Schema、迁移、关系与 Serverless

什么是 Drizzle ORM？

Drizzle ORM 是一个采用根本不同方法的 TypeScript ORM。它不像传统 ORM 那样用方法链隐藏 SQL，而是拥抱 SQL 语法并将其直接映射到 TypeScript。每个查询看起来像 SQL，但具有完整的类型推断、自动补全和编译时验证。

为什么选择 Drizzle？

• 类 SQL 语法：查询读起来像 SQL，而非方法链
• 类型安全：从 schema 到查询结果的完整 TypeScript 推断
• 轻量级：零依赖，最小包体积（gzip 后约 7.4KB）
• 无服务器就绪：无连接池问题，适用于边缘运行时
• 多数据库：PostgreSQL、MySQL、SQLite 使用相同 API
• Drizzle Kit：CLI 工具提供 schema 迁移、推送和内省
• Drizzle Studio：基于浏览器的数据库 GUI，用于查看和编辑数据

Drizzle vs Prisma vs TypeORM

每个 ORM 有不同的权衡。Drizzle 优先考虑 SQL 熟悉度和最小开销。Prisma 优先考虑开发者体验。TypeORM 遵循传统 ORM 模式。

Schema 定义

Drizzle schema 用纯 TypeScript 文件编写。每个表使用直接映射到 SQL 列类型的辅助函数定义。schema 同时作为数据库定义和 TypeScript 类型。

PostgreSQL Schema

// src/db/schema.ts
import {
  pgTable, serial, varchar, text, integer,
  timestamp, boolean, pgEnum, uniqueIndex
} from "drizzle-orm/pg-core";

// Define an enum
export const roleEnum = pgEnum("role", ["admin", "user", "guest"]);

// Users table
export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  role: roleEnum("role").default("user").notNull(),
  bio: text("bio"),
  isActive: boolean("is_active").default(true).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => ({
  emailIdx: uniqueIndex("email_idx").on(table.email),
}));

// Posts table
export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 500 }).notNull(),
  content: text("content").notNull(),
  published: boolean("published").default(false).notNull(),
  authorId: integer("author_id").notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

// Comments table
export const comments = pgTable("comments", {
  id: serial("id").primaryKey(),
  body: text("body").notNull(),
  postId: integer("post_id").notNull()
    .references(() => posts.id, { onDelete: "cascade" }),
  authorId: integer("author_id").notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

MySQL Schema

// src/db/schema.ts (MySQL)
import {
  mysqlTable, serial, varchar, text,
  int, timestamp, boolean, mysqlEnum
} from "drizzle-orm/mysql-core";

export const users = mysqlTable("users", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  role: mysqlEnum("role", ["admin", "user", "guest"])
    .default("user").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

export const posts = mysqlTable("posts", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 500 }).notNull(),
  content: text("content").notNull(),
  authorId: int("author_id").notNull()
    .references(() => users.id),
});

SQLite Schema

// src/db/schema.ts (SQLite)
import {
  sqliteTable, integer, text
} from "drizzle-orm/sqlite-core";

export const users = sqliteTable("users", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  role: text("role", { enum: ["admin", "user", "guest"] })
    .default("user").notNull(),
  createdAt: text("created_at")
    .default("CURRENT_TIMESTAMP").notNull(),
});

export const posts = sqliteTable("posts", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  content: text("content").notNull(),
  authorId: integer("author_id").notNull()
    .references(() => users.id),
});

项目设置和配置

设置 Drizzle 需要安装 ORM 包、数据库驱动和 drizzle-kit。配置比其他 ORM 简单得多。

# Install Drizzle ORM + PostgreSQL driver
npm install drizzle-orm postgres
npm install -D drizzle-kit

# Or with MySQL
npm install drizzle-orm mysql2
npm install -D drizzle-kit

# Or with SQLite
npm install drizzle-orm better-sqlite3
npm install -D drizzle-kit @types/better-sqlite3

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

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

// src/db/index.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

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

// For MySQL:
// import { drizzle } from "drizzle-orm/mysql2";
// import mysql from "mysql2/promise";
// const pool = mysql.createPool(process.env.DATABASE_URL!);
// export const db = drizzle(pool, { schema });

// For SQLite:
// import { drizzle } from "drizzle-orm/better-sqlite3";
// import Database from "better-sqlite3";
// const sqlite = new Database("local.db");
// export const db = drizzle(sqlite, { schema });

类型安全查询

Drizzle 提供两种查询 API：类 SQL 核心 API 和关系查询 API。核心 API 直接映射 SQL 语法，关系 API 提供更声明式的嵌套数据获取方式。

SELECT 查询

import { eq, gt, like, and, or, desc, asc, count, sql } from "drizzle-orm";
import { db } from "./db";
import { users, posts } from "./db/schema";

// Select all users
const allUsers = await db.select().from(users);
// Type: { id: number; name: string; email: string; role: string; ... }[]

// Select specific columns
const userNames = await db.select({
  id: users.id,
  name: users.name,
}).from(users);
// Type: { id: number; name: string }[]

// WHERE clause with conditions
const activeAdmins = await db.select()
  .from(users)
  .where(
    and(
      eq(users.role, "admin"),
      eq(users.isActive, true)
    )
  );

// Complex filtering
const searchResults = await db.select()
  .from(users)
  .where(
    or(
      like(users.name, "%alice%"),
      like(users.email, "%alice%")
    )
  )
  .orderBy(desc(users.createdAt))
  .limit(10)
  .offset(0);

// Aggregation
const userCount = await db.select({
  role: users.role,
  total: count(),
}).from(users).groupBy(users.role);
// Type: { role: string; total: number }[]

INSERT 操作

// Insert a single row
const newUser = await db.insert(users).values({
  name: "Alice",
  email: "alice@example.com",
  role: "admin",
}).returning();
// Returns: { id: 1, name: "Alice", ... }[]

// Insert multiple rows
await db.insert(users).values([
  { name: "Bob", email: "bob@example.com" },
  { name: "Charlie", email: "charlie@example.com" },
]);

// Upsert (insert or update on conflict)
await db.insert(users)
  .values({ name: "Alice", email: "alice@example.com" })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: "Alice Updated" },
  });

UPDATE 操作

// Update rows matching a condition
await db.update(users)
  .set({
    role: "admin",
    updatedAt: new Date(),
  })
  .where(eq(users.email, "alice@example.com"));

// Update with returning
const updated = await db.update(users)
  .set({ isActive: false })
  .where(eq(users.id, 1))
  .returning({ id: users.id, name: users.name });

DELETE 操作

// Delete rows matching a condition
await db.delete(users)
  .where(eq(users.id, 1));

// Delete with returning
const deleted = await db.delete(posts)
  .where(
    and(
      eq(posts.published, false),
      gt(posts.createdAt, new Date("2024-01-01"))
    )
  )
  .returning();

// Delete all rows (use with caution)
// await db.delete(users);

关系和连接

Drizzle 同时支持显式 SQL 连接和关系查询 API。关系与 schema 分开定义，保持表定义整洁。

定义关系

// src/db/relations.ts
import { relations } from "drizzle-orm";
import { users, posts, comments } from "./schema";

// User has many posts and comments
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
  comments: many(comments),
}));

// Post belongs to user, has many comments
export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
  comments: many(comments),
}));

// Comment belongs to post and user
export const commentsRelations = relations(comments, ({ one }) => ({
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.id],
  }),
  author: one(users, {
    fields: [comments.authorId],
    references: [users.id],
  }),
}));

SQL 连接

// Inner join: users with their posts
const usersWithPosts = await db.select({
  userId: users.id,
  userName: users.name,
  postTitle: posts.title,
  postCreated: posts.createdAt,
}).from(users)
  .innerJoin(posts, eq(users.id, posts.authorId))
  .where(eq(posts.published, true));

// Left join: all users, including those without posts
const allUsersWithPosts = await db.select({
  userId: users.id,
  userName: users.name,
  postTitle: posts.title,
}).from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

// Multi-table join
const postWithComments = await db.select({
  postTitle: posts.title,
  commentBody: comments.body,
  authorName: users.name,
}).from(posts)
  .innerJoin(comments, eq(posts.id, comments.postId))
  .innerJoin(users, eq(comments.authorId, users.id))
  .where(eq(posts.id, 1));

关系查询

// Fetch user with their posts and comments (nested)
const userWithData = await db.query.users.findFirst({
  where: eq(users.id, 1),
  with: {
    posts: {
      where: eq(posts.published, true),
      orderBy: [desc(posts.createdAt)],
      limit: 5,
      with: {
        comments: {
          with: {
            author: true,
          },
        },
      },
    },
  },
});
// Result type is fully inferred:
// {
//   id: number; name: string; email: string;
//   posts: {
//     id: number; title: string;
//     comments: { body: string; author: { name: string } }[]
//   }[]
// }

// Find many with filtering
const recentPosts = await db.query.posts.findMany({
  where: eq(posts.published, true),
  orderBy: [desc(posts.createdAt)],
  limit: 20,
  with: {
    author: {
      columns: { id: true, name: true },
    },
  },
});

使用 drizzle-kit 迁移

Drizzle Kit 通过对比 TypeScript schema 和当前数据库状态生成 SQL 迁移文件。支持 generate、push 和 introspect 工作流。

生成迁移

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

# Output: drizzle/0001_cool_migration.sql
# CREATE TABLE "users" (
#   "id" serial PRIMARY KEY NOT NULL,
#   "name" varchar(255) NOT NULL,
#   "email" varchar(255) NOT NULL,
#   ...
# );

# Apply migrations programmatically
# In your application entry point:
# import { migrate } from "drizzle-orm/postgres-js/migrator";
# await migrate(db, { migrationsFolder: "./drizzle" });

Push（开发环境）

# Push schema directly to database (development only)
# Skips migration files, applies changes immediately
npx drizzle-kit push

# Check what changes would be applied
npx drizzle-kit check

内省现有数据库

# Generate Drizzle schema from existing database
npx drizzle-kit introspect

# Output: drizzle/schema.ts
# This creates TypeScript schema files that match
# your existing database structure.
# Useful for migrating from another ORM or raw SQL.

Drizzle Studio

Drizzle Studio 是 drizzle-kit 内置的浏览器数据库 GUI。运行一个命令即可获得可视化界面，用于浏览表、编辑行、运行查询和检查 schema。

# Launch Drizzle Studio
npx drizzle-kit studio

# Opens a browser GUI at https://local.drizzle.studio
# Features:
# - Browse all tables and their data
# - Edit rows inline
# - Run custom SQL queries
# - View table schemas and relations
# - Filter and sort data
# - Export query results
#
# Uses the same drizzle.config.ts for DB connection.
# No additional setup required.

框架集成

Drizzle 适用于任何 TypeScript 运行时。以下是最流行框架的集成模式。

Next.js 集成

// src/db/index.ts (Next.js with Neon serverless)
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 });

// app/users/page.tsx (Server Component)
import { db } from "@/db";
import { users } from "@/db/schema";
import { desc } from "drizzle-orm";

export default async function UsersPage() {
  const allUsers = await db.select()
    .from(users)
    .orderBy(desc(users.createdAt))
    .limit(50);

  return (
    <ul>
      {allUsers.map(user => (
        <li key={user.id}>{user.name}</li>
      ))}
    </ul>
  );
}

// app/api/users/route.ts (Route Handler)
import { NextResponse } from "next/server";
import { db } from "@/db";
import { users } from "@/db/schema";

export async function POST(request: Request) {
  const body = await request.json();
  const newUser = await db.insert(users)
    .values(body)
    .returning();
  return NextResponse.json(newUser[0]);
}

Hono 集成

// src/index.ts (Hono with Drizzle)
import { Hono } from "hono";
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import { users } from "./db/schema";
import { eq } from "drizzle-orm";

const sql = postgres(process.env.DATABASE_URL!);
const db = drizzle(sql);

const app = new Hono();

app.get("/users", async (c) => {
  const allUsers = await db.select().from(users);
  return c.json(allUsers);
});

app.get("/users/:id", async (c) => {
  const id = Number(c.req.param("id"));
  const user = await db.select().from(users)
    .where(eq(users.id, id));
  if (!user.length) return c.json({ error: "Not found" }, 404);
  return c.json(user[0]);
});

export default app;

无服务器和边缘

// Cloudflare Workers with D1 (SQLite)
import { drizzle } from "drizzle-orm/d1";
import * as schema from "./schema";

export default {
  async fetch(request: Request, env: Env) {
    const db = drizzle(env.DB, { schema });
    const allUsers = await db.select().from(schema.users);
    return Response.json(allUsers);
  },
};

// Vercel Edge with Neon
import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";

export const runtime = "edge";

export async function GET() {
  const sql = neon(process.env.DATABASE_URL!);
  const db = drizzle(sql);
  const result = await db.select().from(users);
  return Response.json(result);
}

// AWS Lambda with PlanetScale
import { drizzle } from "drizzle-orm/planetscale-serverless";
import { Client } from "@planetscale/database";

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

事务和预处理语句

Drizzle 支持数据库事务用于原子操作，预处理语句用于提高重复查询的性能。

使用事务

// Basic transaction
await db.transaction(async (tx) => {
  // All operations inside share the same transaction
  const [newUser] = await tx.insert(users)
    .values({ name: "Alice", email: "alice@example.com" })
    .returning();

  await tx.insert(posts).values({
    title: "First Post",
    content: "Hello world!",
    authorId: newUser.id,
  });

  // If any operation fails, all changes are rolled back
});

// Nested transaction with savepoints
await db.transaction(async (tx) => {
  await tx.insert(users)
    .values({ name: "Bob", email: "bob@example.com" });

  try {
    await tx.transaction(async (nestedTx) => {
      await nestedTx.insert(posts).values({
        title: "Draft",
        content: "...",
        authorId: 999, // invalid FK
      });
    });
  } catch (e) {
    // Nested transaction rolled back,
    // outer transaction continues
    console.log("Nested transaction failed:", e);
  }
  // Bob is still inserted
});

预处理语句

import { sql, placeholder } from "drizzle-orm";

// Prepared statement with placeholder
const getUserById = db.select()
  .from(users)
  .where(eq(users.id, placeholder("id")))
  .prepare("get_user_by_id");

// Execute with different values (reuses query plan)
const user1 = await getUserById.execute({ id: 1 });
const user2 = await getUserById.execute({ id: 2 });

// Prepared statement with multiple placeholders
const searchUsers = db.select()
  .from(users)
  .where(
    and(
      eq(users.role, placeholder("role")),
      eq(users.isActive, placeholder("active"))
    )
  )
  .limit(placeholder("limit"))
  .prepare("search_users");

const admins = await searchUsers.execute({
  role: "admin",
  active: true,
  limit: 10,
});

// Raw SQL when needed
const result = await db.execute(
  sql`SELECT * FROM users WHERE name ILIKE ${"%" + search + "%"}`
);

性能优化

Drizzle 由于薄抽象层已经是最快的 TypeScript ORM 之一。这些模式帮助你在生产中获得更好的性能。

• 使用 select() 指定列而非查询所有列以减少数据传输
• 为 WHERE、ORDER BY 和 JOIN 条件中使用的列创建数据库索引
• 对频繁执行的查询使用预处理语句以跳过查询规划
• 在事务中批量处理相关写入以减少往返
• 在无服务器环境中使用连接池
• 在开发中启用查询日志以尽早发现 N+1 查询
• 使用关系查询 API 获取嵌套数据，而非多次顺序查询
• 对所有返回列表的查询添加 .limit() 以防止无限结果集

// Performance patterns

// 1. Select only needed columns
const names = await db.select({
  id: users.id,
  name: users.name,
}).from(users); // faster than db.select().from(users)

// 2. Add indexes in schema
import { index } from "drizzle-orm/pg-core";

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 500 }).notNull(),
  authorId: integer("author_id").notNull(),
  published: boolean("published").default(false),
  createdAt: timestamp("created_at").defaultNow(),
}, (table) => ({
  authorIdx: index("author_idx").on(table.authorId),
  publishedIdx: index("published_idx")
    .on(table.published, table.createdAt),
}));

// 3. Batch operations in transactions
await db.transaction(async (tx) => {
  await tx.insert(posts).values(batchOfPosts);
  await tx.update(users)
    .set({ updatedAt: new Date() })
    .where(eq(users.id, authorId));
});

// 4. Always use .limit()
const recent = await db.select().from(posts)
  .orderBy(desc(posts.createdAt))
  .limit(20);  // never fetch unbounded result sets

// 5. Enable query logging
const db = drizzle(connection, {
  schema,
  logger: true,  // logs all queries to console
});

常见问题