with one click
drizzle-v1
// Drizzle ORM v1 关系查询指南。当需要定义 Relations v2、编写关系查询、使用 through 多对多、预定义过滤器、或从旧版 Drizzle 迁移时使用
// Drizzle ORM v1 关系查询指南。当需要定义 Relations v2、编写关系查询、使用 through 多对多、预定义过滤器、或从旧版 Drizzle 迁移时使用
Effect v4 模式指南。当需要创建 Effect 服务、定义错误类型、编写 Effect 程序、管理 Layer 组合、或使用 Effect 封装异步操作时使用
创建或修改 BullMQ 队列任务。当需要创建新队列、添加任务类型、注册 Worker、设置定时任务、或用户请求"添加后台任务/队列处理"时使用
创建或修改一个新的 API tier。当用户请求“新增 tier / 创建 partner tier / 新增 merchant 端 / 新增 tenant 端 / 新增 API 端 / 新增一套路由层”时使用。目标是在不修改框架核心的前提下,为新 tier 补齐配置、中间件、类型别名、路由入口和测试。
创建或修改 CRUD 模块。当需要创建新的增删改查 API、修改现有路由模块、添加新字段、新增接口、或用户请求"创建/修改 XX 管理"时使用
创建或修改数据库 Schema。当需要创建新表、修改表结构、定义字段、设置索引约束、或涉及 Drizzle ORM / drizzle-zod 操作时使用
| name | drizzle-v1 |
| description | Drizzle ORM v1 关系查询指南。当需要定义 Relations v2、编写关系查询、使用 through 多对多、预定义过滤器、或从旧版 Drizzle 迁移时使用 |
| argument-hint | 关系定义/查询写法/迁移指导 |
drizzle-orm@1.0.0-beta.21(已进入 RC 阶段)传入 relations 而非 schema:
import { drizzle } from "drizzle-orm/postgres-js";
import { relations } from "./relations";
const db = drizzle({
client: getQueryClient(),
relations,
casing: "snake_case",
});
使用 defineRelations 在一处定义所有关系:
// src/db/relations/index.ts
import { defineRelations } from "drizzle-orm";
import * as schema from "@/db/schema";
export const relations = defineRelations(schema, (r) => ({
// 每个表的关系定义
users: {
posts: r.many.posts(),
},
posts: {
author: r.one.users({
from: r.posts.authorId,
to: r.users.id,
}),
},
}));
本项目使用 defineRelationsPart + spread 方式拆分关系定义:
// src/db/relations/index.ts
import { defineRelations } from "drizzle-orm";
import * as schema from "@/db/schema";
import { userRolesRelations } from "./admin/user-roles";
export const relations = defineRelations(schema, (r) => ({
...userRolesRelations(r),
}));
// src/db/relations/admin/user-roles.ts
import type { ExtractTablesFromSchema, RelationsBuilder } from "drizzle-orm";
import type * as schema from "@/db/schema";
type Schema = ExtractTablesFromSchema<typeof schema>;
export const userRolesRelations = (r: RelationsBuilder<Schema>) => ({
systemUsers: {
roles: r.many.systemRoles({
from: r.systemUsers.id.through(r.systemUserRoles.userId),
to: r.systemRoles.id.through(r.systemUserRoles.roleId),
}),
enabledRoles: r.many.systemRoles({
from: r.systemUsers.id.through(r.systemUserRoles.userId),
to: r.systemRoles.id.through(r.systemUserRoles.roleId),
where: { status: Status.ENABLED },
}),
},
systemRoles: {
users: r.many.systemUsers(),
},
});
关键模式:
ExtractTablesFromSchema<typeof schema> + RelationsBuilder<Schema>defineRelations 中src/db/relations/{tier}/{feature}.ts 组织文件// one: 指定 from → to 映射
posts: {
author: r.one.users({
from: r.posts.authorId,
to: r.users.id,
}),
},
// many: 可以只定义 many 侧(无需在对端定义 one)
users: {
posts: r.many.posts({
from: r.users.id,
to: r.posts.authorId,
}),
},
使用 .through() 指定连接表,无需手动查询连接表再映射:
users: {
groups: r.many.groups({
from: r.users.id.through(r.usersToGroups.userId),
to: r.groups.id.through(r.usersToGroups.groupId),
}),
},
groups: {
participants: r.many.users(), // 反向可省略 from/to
},
在关系定义中预设过滤条件,查询时直接使用:
systemUsers: {
enabledRoles: r.many.systemRoles({
from: r.systemUsers.id.through(r.systemUserRoles.userId),
to: r.systemRoles.id.through(r.systemUserRoles.roleId),
where: { status: Status.ENABLED },
}),
},
optional: false 使关系成为必填(TypeScript 类型非 nullable):
posts: {
author: r.one.users({
from: r.posts.authorId,
to: r.users.id,
optional: false, // author 不会是 null
}),
},
自引用或同表多关系时使用 alias 区分:
posts: {
author: r.one.users({
from: r.posts.authorId,
to: r.users.id,
alias: "author_post",
}),
reviewer: r.one.users({
from: r.posts.reviewerId,
to: r.users.id,
alias: "reviewer_post",
}),
},
// 简单等值
db.query.users.findFirst({
where: { id: userId },
});
// 多条件(AND)
db.query.users.findMany({
where: { status: Status.ENABLED, username: "admin" },
});
// 操作符
db.query.users.findMany({
where: {
id: { gt: 10 },
name: { like: "M%" },
},
});
// 按关系过滤(v1 新增!)
db.query.users.findMany({
where: {
id: { gt: 10 },
posts: {
content: { like: "M%" },
},
},
});
// 关系存在性过滤:获取至少有 1 篇 post 的用户
db.query.users.findMany({
with: { posts: true },
where: { posts: true },
});
where: {
// 逻辑组合
OR: [], // 或
AND: [], // 与
NOT: {}, // 非
// RAW SQL
RAW: (table) => sql`${table.id} = 1`,
// 按关系过滤
[relation]: {}, // 如 posts: { content: { like: "M%" } }
// 列操作符
[column]: {
eq: 1, // =
ne: 1, // !=
gt: 1, // >
gte: 1, // >=
lt: 1, // <
lte: 1, // <=
in: [1, 2], // IN
notIn: [1, 2], // NOT IN
like: "M%", // LIKE
ilike: "m%", // ILIKE(不区分大小写)
notLike: "M%", // NOT LIKE
notIlike: "m%", // NOT ILIKE
isNull: true, // IS NULL
isNotNull: true, // IS NOT NULL
arrayOverlaps: [1, 2], // 数组重叠
arrayContained: [1, 2], // 数组被包含
arrayContains: [1, 2], // 数组包含
// 列级逻辑组合
OR: [],
AND: [],
NOT: {},
},
}
db.query.users.findMany({
orderBy: { id: "asc" },
});
// 多字段排序
db.query.users.findMany({
orderBy: { createdAt: "desc", name: "asc" },
});
// 自定义 SQL 排序
db.query.posts.findMany({
orderBy: (t) => sql`${t.id} asc`,
});
// 关系内排序
db.query.posts.findMany({
orderBy: { id: "asc" },
with: {
comments: {
orderBy: { id: "desc" },
},
},
});
// 加载全部列
db.query.users.findFirst({
where: { id: userId },
with: { roles: true },
});
// 选择部分列
db.query.users.findFirst({
where: { id: userId },
with: {
roles: {
columns: { id: true, name: true },
},
},
});
// 预定义过滤关系
db.query.users.findFirst({
where: { id: userId },
with: { enabledRoles: { columns: { id: true } } },
});
db.query.users.findFirst({
where: { id: userId },
columns: { id: true, username: true, avatar: true },
});
// v1 新增:关系内也支持 offset
db.query.posts.findMany({
limit: 5,
offset: 2,
with: {
comments: {
offset: 3,
limit: 3,
},
},
});
在查询中添加自定义 SQL 计算字段:
import { sql } from "drizzle-orm";
// 简单 extras
db.query.users.findMany({
extras: {
loweredName: sql`lower(${users.name})`,
},
});
// 回调语法
db.query.users.findMany({
extras: {
loweredName: (users, { sql }) => sql`lower(${users.name})`,
},
});
// 嵌套关系中也支持 extras
db.query.posts.findMany({
extras: {
contentLength: (table, { sql }) => sql<number>`length(${table.content})`,
},
with: {
comments: {
extras: {
commentSize: (table, { sql }) => sql<number>`length(${table.content})`,
},
},
},
});
注意:extras 目前不支持聚合函数,需要聚合请使用 core queries。
import { posts } from "./schema";
import { eq } from "drizzle-orm";
// 获取用户及其 post 总数
db.query.users.findMany({
with: { posts: true },
extras: {
totalPostsCount: (table) => db.$count(posts, eq(posts.authorId, table.id)),
},
});
使用 placeholder 提升重复查询性能:
import { sql } from "drizzle-orm";
// where 中使用 placeholder
const prepared = db.query.users.findMany({
where: { id: { eq: sql.placeholder("id") } },
with: {
posts: {
where: { id: 1 },
},
},
}).prepare("query_name");
const result = await prepared.execute({ id: 1 });
// limit/offset 中使用 placeholder
const prepared2 = db.query.users.findMany({
limit: sql.placeholder("uLimit"),
offset: sql.placeholder("uOffset"),
where: {
OR: [{ id: { eq: sql.placeholder("id") } }, { id: 3 }],
},
with: {
posts: {
where: { id: { eq: sql.placeholder("pid") } },
limit: sql.placeholder("pLimit"),
},
},
}).prepare("query_name");
const result2 = await prepared2.execute({
pLimit: 1, uLimit: 3, uOffset: 1, id: 2, pid: 6,
});
使用 defineRelationsPart 拆分关系定义时的重要规则:
规则 1:spread 时主 relations 必须在前:
// ✅ 正确
const db = drizzle(url, { relations: { ...relations, ...part } })
// ❌ 错误
const db = drizzle(url, { relations: { ...part, ...relations } })
规则 2:必须有一个 main relations(使用 defineRelations),让 drizzle 能推断所有表。如果只想用 parts,可以创建一个空的 main:
export const mainPart = defineRelationsPart(schema); // 空 main,用于推断所有表
// 旧版:必须嵌套查询连接表再映射
const response = await db.query.users.findMany({
with: {
usersToGroups: {
columns: {},
with: { group: true },
},
},
});
// 还需要手动 map: response.map(u => ({ ...u, groups: u.usersToGroups.map(utg => utg.group) }))
// 新版:直接查多对多,无需映射
const response = await db.query.users.findMany({
with: { groups: true },
});
src/db/relations/{tier}/{feature}.tssrc/db/relations/index.ts 注册:spread 到 defineRelations 中// 1. src/db/relations/{tier}/{feature}.ts
import type { ExtractTablesFromSchema, RelationsBuilder } from "drizzle-orm";
import type * as schema from "@/db/schema";
type Schema = ExtractTablesFromSchema<typeof schema>;
export const {feature}Relations = (r: RelationsBuilder<Schema>) => ({
{parentTable}: {
{relName}: r.many.{childTable}({
from: r.{parentTable}.id,
to: r.{childTable}.{parentTable}Id,
}),
},
{childTable}: {
{parentRef}: r.one.{parentTable}({
from: r.{childTable}.{parentTable}Id,
to: r.{parentTable}.id,
}),
},
});
// 2. src/db/relations/index.ts
import { {feature}Relations } from "./{tier}/{feature}";
export const relations = defineRelations(schema, (r) => ({
...userRolesRelations(r),
...{feature}Relations(r), // 新增
}));
注意:同一个表的关系在多个 part 中定义时,后 spread 的会覆盖前面同名的 key。确保不同 part 中同一表的关系 key 名不冲突,或在 index.ts 中手动合并。
| v1 旧版 | v1 新版 (Relations v2) |
|---|---|
import { relations } from "drizzle-orm" | import { relations } from "drizzle-orm/_relations" ← 旧版移到这 |
fields: [posts.authorId] | from: r.posts.authorId |
references: [users.id] | to: r.users.id |
relationName: "xxx" | alias: "xxx" |
每表单独 relations() | 统一 defineRelations() |
db.query.xxx (旧语法) | db._query.xxx (可继续用旧语法) |
where: (t, { eq }) => eq(t.id, 1) | where: { id: 1 } |
orderBy: (t, { asc }) => [asc(t.id)] | orderBy: { id: "asc" } |
drizzle(url, { schema }) | drizzle(url, { relations }) |
MySQL mode: "planetscale" | 不再需要 mode |
如果需要逐步迁移而非一次性切换:
import { relations } from "drizzle-orm" 改为 import { relations } from "drizzle-orm/_relations"db._query.xxx 替代 db.query.xxxdb.query.xxx(新语法)pnpm drizzle-kit pull
会在 drizzle/relations.ts 生成新语法的关系定义,可直接复制到项目的 src/db/relations/ 中使用。注意修改导入路径。
defineRelations (v2) 和旧版 relations() (v1) 不兼容from: r.posts.authorId,多列时才用数组 from: [r.posts.a, r.posts.b]r.usersToGroups.userId 前提是 usersToGroups 表已导出where 子句只能包含 to 端表的列.as() 别名:drizzle 会忽略 extras 字段上的 .as("<alias>")pnpm drizzle-kit up:更新 migrations 文件夹结构(移除 journal.json,按文件夹分组)drizzle-zod → drizzle-orm/zoddrizzle-valibot → drizzle-orm/valibotdrizzle-typebox → drizzle-orm/typeboxdrizzle-arktype → drizzle-orm/arktype