Drizzle ORM (MySQL) 使用指南

Drizzle ORM (MySQL) 完整使用指南

---

目录

1. Schema 定义
2. 数据库连接
3. 基础 CRUD
4. JOIN 联表查询
5. 聚合 / GROUP BY / HAVING
6. 子查询 & CTE
7. 事务
8. 动态查询构建
9. Relational Queries（关系查询 API）
10. Migration 迁移
11. 常用 Filter 操作符速查

---

1. Schema 定义

1.1 基本结构

import { mysqlTable, varchar, int, bigint, boolean, text, json, index, uniqueIndex } from "drizzle-orm/mysql-core";
import dayjs from "dayjs";

export const users = mysqlTable("users", {
  // ──────── 列定义 ────────
  id: int("id").primaryKey().autoincrement(),
  name: varchar("name", { length: 100 }).notNull(),
  age: int("age").default(0),
  amount: bigint("amount", { mode: "number" }),          // mode:"number" 让 JS 中是 number
  config: json("config").$type<{ theme: string }>(),     // JSON 列 + TS 类型
  createdAt: bigint("created_at", { mode: "number" })
    .notNull()
    .$defaultFn(() => dayjs().unix()),                   // 插入时自动填充
  updatedAt: bigint("updated_at", { mode: "number" })
    .notNull()
    .$onUpdateFn(() => dayjs().unix()),                  // 更新时自动刷新
}, (t) => ({
  // ──────── 索引定义 ────────
  nameIdx: uniqueIndex("unique_users_name").on(t.name),
  ageIdx: index("idx_users_age").on(t.age),
}));

// 自动推断类型
type User    = typeof users.$inferSelect;   // 查询返回类型
type NewUser = typeof users.$inferInsert;   // 插入参数类型

1.2 列类型速查

Drizzle 方法	SQL 类型	说明
int("id")	INT	整数
bigint("val", { mode: "number" })	BIGINT	大整数，mode 控制 JS 类型
varchar("name", { length: 100 })	VARCHAR(100)	可变长字符串
text("content")	TEXT	长文本
boolean("active")	TINYINT(1)	布尔
json("config").$type<T>()	JSON	JSON + TS 类型
mysqlEnum("status", ["A","B"])	ENUM('A','B')	枚举

1.3 约束链式调用

int("id").primaryKey().autoincrement()    // 自增主键
varchar("name", { length: 100 }).notNull() // NOT NULL
int("age").default(0)                      // DEFAULT 0
varchar("status", { length: 20, enum: EnumStatus }).notNull().default("INIT")
// ↑ 应用层枚举 + NOT NULL + DEFAULT

1.4 索引

// 在 mysqlTable 的第三个参数中定义
(t) => ({
  // 唯一索引
  phoneIdx: uniqueIndex("unique_phone").on(t.phoneNumber),
  // 普通索引（支持多列）
  orgDateIdx: index("idx_org_date").on(t.orgId, t.createdAt),
})

---

2. 数据库连接

import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";
import * as schema from "./schema";

const connection = await mysql.createConnection({
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
});

export const db = drizzle(connection, { schema, mode: "default" });

---

3. 基础 CRUD

3.1 SELECT

import { eq, and, or, gt, lt, gte, lte, like, inArray, between, isNull, desc, asc, sql } from "drizzle-orm";

// 查全部
const all = await db.select().from(users);

// 查指定字段
const partial = await db.select({ id: users.id, name: users.name }).from(users);

// WHERE 单条件
const one = await db.select().from(users).where(eq(users.id, 1));

// WHERE 多条件 AND
const list = await db.select().from(users).where(
  and(eq(users.status, "ACTIVE"), gt(users.age, 18))
);

// WHERE OR
const orList = await db.select().from(users).where(
  or(eq(users.id, 1), eq(users.id, 2))
);

// IN
const inList = await db.select().from(users).where(
  inArray(users.status, ["ACTIVE", "PENDING"])
);

// LIKE 模糊
const likeList = await db.select().from(users).where(
  like(users.name, "%张%")
);

// BETWEEN
const range = await db.select().from(users).where(
  between(users.age, 18, 30)
);

// IS NULL
const nullList = await db.select().from(users).where(isNull(users.remark));

// 排序 + 分页
const page = await db.select().from(users)
  .orderBy(desc(users.createdAt))
  .limit(10)
  .offset(0);

// DISTINCT
const distinct = await db.selectDistinct({ status: users.status }).from(users);

// 原生 SQL 表达式
const withUpper = await db.select({
  name: users.name,
  upperName: sql<string>`upper(${users.name})`,
}).from(users);

3.2 INSERT

// 单条
await db.insert(users).values({ name: "张三", age: 25 });

// 批量
await db.insert(users).values([
  { name: "张三", age: 25 },
  { name: "李四", age: 30 },
]);

// MySQL 获取自增 ID（MySQL 没有 returning）
const result = await db.insert(users).values({ name: "王五" }).$returningId();
// result = [{ id: 5 }]

// ON DUPLICATE KEY UPDATE（MySQL 专有）
await db.insert(users).values({ id: 1, name: "赵六", age: 20 })
  .onDuplicateKeyUpdate({ set: { name: "赵六", age: 20 } });

// INSERT ... SELECT（从另一张表导入）
await db.insert(usersArchive)
  .select(db.select({ name: users.name, age: users.age }).from(users).where(gt(users.age, 50)));

3.3 UPDATE

// 基本
await db.update(users)
  .set({ name: "新名字", age: 26 })
  .where(eq(users.id, 1));

// 用 SQL 表达式（如金额自增）
await db.update(users)
  .set({ amount: sql`${users.amount} + 100` })
  .where(eq(users.id, 1));

// 设为 NULL
await db.update(users)
  .set({ remark: null })
  .where(eq(users.id, 1));

3.4 DELETE

await db.delete(users).where(eq(users.id, 1));
await db.delete(users); // 删全部（慎用）

---

4. JOIN 联表查询

4.1 基本用法

// LEFT JOIN —— 右表可能为 null
const left = await db.select()
  .from(users)
  .leftJoin(orders, eq(users.id, orders.userId));
// 类型: { users: User; orders: Order | null }[]

// INNER JOIN —— 两边都有值
const inner = await db.select()
  .from(users)
  .innerJoin(orders, eq(users.id, orders.userId));
// 类型: { users: User; orders: Order }[]

// RIGHT JOIN
const right = await db.select()
  .from(users)
  .rightJoin(orders, eq(users.id, orders.userId));

// 多表 JOIN
const multi = await db.select()
  .from(users)
  .leftJoin(orders, eq(users.id, orders.userId))
  .leftJoin(orderItems, eq(orders.id, orderItems.orderId));

4.2 指定字段 / 嵌套对象

// 选取部分字段
const partial = await db.select({
  userName: users.name,
  orderAmount: orders.amount,
}).from(users).leftJoin(orders, eq(users.id, orders.userId));

// 嵌套对象 —— 整个 order 要么全有要么 null，避免每个字段都 nullable
const nested = await db.select({
  user: users,
  order: orders,
}).from(users).leftJoin(orders, eq(users.id, orders.userId));

4.3 自连接（alias）

import { alias } from "drizzle-orm";

const parent = alias(users, "parent");
const result = await db.select()
  .from(users)
  .leftJoin(parent, eq(parent.id, users.parentId));

4.4 聚合映射（多对一手动合并）

type User = typeof users.$inferSelect;
type Order = typeof orders.$inferSelect;

const rows = await db.select({
  user: users,
  order: orders,
}).from(users).leftJoin(orders, eq(users.id, orders.userId));

// 手动合并成 user -> orders[] 结构
const grouped = rows.reduce<Record<number, { user: User; orders: Order[] }>>(
  (acc, row) => {
    if (!acc[row.user.id]) acc[row.user.id] = { user: row.user, orders: [] };
    if (row.order) acc[row.user.id].orders.push(row.order);
    return acc;
  },
  {}
);

---

5. 聚合 / GROUP BY / HAVING

import { sql, count, sum, avg, max, min } from "drizzle-orm";

// COUNT
const total = await db.select({ total: count() }).from(users);

// GROUP BY
const stats = await db.select({
  status: users.status,
  total: sql<number>`cast(count(*) as unsigned)`,
  avgAge: sql<number>`cast(avg(${users.age}) as decimal(10,2))`,
}).from(users).groupBy(users.status);

// HAVING
const filtered = await db.select({
  status: users.status,
  total: sql<number>`cast(count(*) as unsigned)`,
}).from(users)
  .groupBy(users.status)
  .having(({ total }) => gt(total, 5));

// SUM
const revenue = await db.select({
  total: sql<number>`cast(sum(${orders.amount}) as unsigned)`,
}).from(orders);

// $count 快捷方式（推荐）
const userCount = await db.$count(users);
const activeCount = await db.$count(users, eq(users.status, "ACTIVE"));

// $count 作为子查询（超实用）
const usersWithPostCount = await db.select({
  ...getColumns(users),
  postCount: db.$count(posts, eq(posts.authorId, users.id)),
}).from(users);

---

6. 子查询 & CTE

6.1 子查询

// FROM 子查询
const sq = db.select({ id: users.id, name: users.name })
  .from(users)
  .where(gt(users.age, 18))
  .as("sq");

const result = await db.select().from(sq);

// 子查询用于 JOIN
const result2 = await db.select()
  .from(orders)
  .innerJoin(sq, eq(orders.userId, sq.id));

6.2 CTE (WITH 子句)

const activeUsers = db.$with("active_users").as(
  db.select().from(users).where(eq(users.status, "ACTIVE"))
);

const result = await db.with(activeUsers)
  .select()
  .from(activeUsers)
  .limit(10);

---

7. 事务

7.1 基本事务

await db.transaction(async (tx) => {
  await tx.update(accounts)
    .set({ balance: sql`${accounts.balance} - 100` })
    .where(eq(accounts.userId, 1));

  await tx.update(accounts)
    .set({ balance: sql`${accounts.balance} + 100` })
    .where(eq(accounts.userId, 2));
});
// 两个 update 要么全部成功，要么全部回滚

7.2 嵌套事务（Savepoint）

await db.transaction(async (tx) => {
  await tx.insert(users).values({ name: "A" });

  await tx.transaction(async (tx2) => {
    // 这里是 SAVEPOINT
    await tx2.insert(users).values({ name: "B" });
  });
});

7.3 手动回滚

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

7.4 事务返回值

const newBalance: number = await db.transaction(async (tx) => {
  await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100` });
  const [row] = await tx.select({ balance: accounts.balance }).from(accounts);
  return row.balance;
});

7.5 MySQL 事务配置

await db.transaction(
  async (tx) => {
    // ... 操作
  },
  {
    isolationLevel: "read committed",   // 隔离级别
    accessMode: "read write",           // 读写模式
    withConsistentSnapshot: true,       // 一致性快照
  }
);

---

8. 动态查询构建

8.1 动态过滤条件

async function searchUsers(filters: {
  name?: string;
  status?: string;
  minAge?: number;
}) {
  const conditions: SQL[] = [];

  if (filters.name) conditions.push(like(users.name, `%${filters.name}%`));
  if (filters.status) conditions.push(eq(users.status, filters.status));
  if (filters.minAge) conditions.push(gte(users.age, filters.minAge));

  return db.select().from(users).where(and(...conditions));
}

8.2 动态字段选择

async function selectUsers(withAge: boolean) {
  return db.select({
    id: users.id,
    name: users.name,
    ...(withAge ? { age: users.age } : {}),
  }).from(users);
}

8.3 动态排序

async function listUsers(sortField: "name" | "age", sortDir: "asc" | "desc") {
  const column = sortField === "name" ? users.name : users.age;
  const orderFn = sortDir === "asc" ? asc : desc;

  return db.select().from(users).orderBy(orderFn(column));
}

---

9. Relational Queries（关系查询 API）

需要先定义 relations，然后用 db.query 进行关系查询。这种方式更简洁，但需要额外配置。

9.1 定义 Relations

import { relations } from "drizzle-orm";

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

export const ordersRelations = relations(orders, ({ one }) => ({
  user: one(users, { fields: [orders.userId], references: [users.id] }),
}));

9.2 使用 db.query

// 查用户及其所有订单（自动 JOIN）
const result = await db.query.users.findMany({
  with: {
    orders: true,
  },
});
// 返回: { id, name, orders: [...] }[]

// 带条件 + 分页
const result2 = await db.query.users.findMany({
  where: eq(users.status, "ACTIVE"),
  with: { orders: true },
  limit: 10,
  offset: 0,
  orderBy: desc(users.createdAt),
});

// 只取部分字段
const result3 = await db.query.users.findMany({
  columns: { id: true, name: true },
  with: { orders: { columns: { id: true, amount: true } } },
});

// 查单个
const user = await db.query.users.findFirst({
  where: eq(users.id, 1),
  with: { orders: true },
});

// 深层嵌套
const result4 = await db.query.users.findMany({
  with: {
    orders: {
      with: {
        items: true,
      },
    },
  },
});

---

10. Migration 迁移

10.1 生成迁移文件

npx drizzle-kit generate

会根据 schema 变化自动生成 SQL 迁移文件到 drizzle/ 目录。

10.2 执行迁移

npx drizzle-kit migrate

10.3 推送 schema（开发环境快速同步）

npx drizzle-kit push

10.4 可视化查看

npx drizzle-kit studio

---

11. 常用 Filter 操作符速查

import {
  eq,         // =
  ne,         // <>
  gt,         // >
  gte,        // >=
  lt,         // <
  lte,        // <=
  like,       // LIKE '%xxx%'
  ilike,      // ILIKE (不区分大小写，MySQL 不支持)
  inArray,    // IN (...)
  notInArray, // NOT IN (...)
  between,    // BETWEEN a AND b
  isNull,     // IS NULL
  isNotNull,  // IS NOT NULL
  and,        // AND
  or,         // OR
  not,        // NOT
  exists,     // EXISTS
  sql,        // 原生 SQL
} from "drizzle-orm";

---

官方文档

• 官网: orm.drizzle.team
• Select: orm.drizzle.team/docs/select
• Insert: orm.drizzle.team/docs/insert
• Update: orm.drizzle.team/docs/update
• Delete: orm.drizzle.team/docs/delete
• Joins: orm.drizzle.team/docs/joins
• Transactions: orm.drizzle.team/docs/transa…
• Filters: orm.drizzle.team/docs/operat…