迁移到关系查询版本 2

npm

yarn

pnpm

bun

npm i drizzle-orm@rc
npm i drizzle-kit@rc -D

yarn add drizzle-orm@rc
yarn add drizzle-kit@rc -D

pnpm add drizzle-orm@rc
pnpm add drizzle-kit@rc -D

bun add drizzle-orm@rc
bun add drizzle-kit@rc -D

本指南假定您已熟悉：

关系基础 - 在此阅读
drizzle-kit pull - 在此阅读

下面是目录。点击某一项可跳转到对应章节：

与 v1 的不同之处
v2 中的新功能
如何将关系定义从 v1 迁移到 v2
如何将查询从 v1 迁移到 v2
内部变化（imports、内部类型等）

API 变更

与 v1 的不同之处

最大的更新之一是在 Relations Schema 定义

第一个区别是，你不再需要在不同对象中分别为每个表单独指定 relations，然后再将它们与 schema 一起传给 drizzle()。在 Relational Queries v2 中，你现在有一个专门的位置来指定你所需的所有表的所有关系。

回调中的 r 参数提供了完整的自动补全功能——包括 schema 中的所有表，以及 one、many 和 through 等函数——基本上提供了你定义关系所需的一切。

// relations.ts
import * as schema from "./schema"
import { defineRelations } from "drizzle-orm"

export const relations = defineRelations(schema, (r) => ({
    ...
}));

// index.ts
import { relations } from "./relations"
import { drizzle } from "drizzle-orm/..."

const db = drizzle(process.env.DATABASE_URL, { relations })

有什么不同？

Schema Definition

import * as p from 'drizzle-orm/sqlite-core';

export const users = p.sqliteTable('users', {
  id: p.integer().primaryKey(),
  name: p.text(),
  invitedBy: p.integer('invited_by'),
});

export const posts = p.sqliteTable('posts', {
  id: p.integer().primaryKey(),
  content: p.text(),
  authorId: p.integer('author_id'),
});

所有关系只需在一个地方定义

❌ v1

import { relations } from "drizzle-orm/_relations";
import { users, posts } from './schema';

export const usersRelation = relations(users, ({ one, many }) => ({
  invitee: one(users, {
    fields: [users.invitedBy],
    references: [users.id],
  }),
  posts: many(posts),
}));

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

✅ v2

import { defineRelations } from "drizzle-orm";
import * as schema from "./schema";

export const relations = defineRelations(schema, (r) => ({
  users: {
    invitee: r.one.users({
      from: r.users.invitedBy,
      to: r.users.id,
    }),
    posts: r.many.posts(),
  },
  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
    }),
  },
}));

你仍然可以把它拆分成不同的 parts，并且你可以按需定义任意大小的部分

import { defineRelations, defineRelationsPart } from 'drizzle-orm';
import * as schema from "./schema";

export const relations = defineRelations(schema, (r) => ({
  users: {
    invitee: r.one.users({
      from: r.users.invitedBy,
      to: r.users.id,
    }),
    posts: r.many.posts(),
  }
}));

export const part = defineRelationsPart(schema, (r) => ({
  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
    }),
  }
}));

然后你可以把它提供给 db 实例

const db = drizzle(process.env.DB_URL, { relations: { ...relations, ...part } })

无需 one 即可定义 many

在 v1 中，如果你只想定义关系中的 many 一侧，你必须在另一端指定 one 一侧，这会带来不佳的开发体验。

在 v2 中，你可以直接使用 many 一侧，无需任何额外步骤

❌ v1

import { relations } from "drizzle-orm/_relations";
import { users, posts } from './schema';

export const usersRelation = relations(users, ({ one, many }) => ({
  posts: many(posts),
}));

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

✅ v2

import { defineRelations } from "drizzle-orm";
import * as schema from "./schema";

export const relations = defineRelations(schema, (r) => ({
  users: {
    posts: r.many.posts({
      from: r.users.id,
      to: r.posts.authorId,
    }),
  },
}));

新的 optional 选项

在类型层面上，optional: false 会使 posts 对象中的 author 键变为必需项。
当你确定这个特定实体一定存在时，应使用此项。

❌ v1

v1 中不支持

✅ v2

import { defineRelations } from "drizzle-orm";
import * as schema from "./schema";

export const relations = defineRelations(schema, (r) => ({
  users: {
    posts: r.one.posts({
      from: r.users.id,
      to: r.posts.authorId,
      optional: false,
    }),
  },
}));

drizzle() 中不再需要 mode

我们找到了一种适用于所有 MySQL 方言的相同策略，因此不需要再单独指定它们

❌ v1

import * as schema from './schema'

const db = drizzle(process.env.DATABASE_URL, { mode: "planetscale", schema });
// or
const db = drizzle(process.env.DATABASE_URL, { mode: "default", schema });

✅ v2

import { relations } from './relations'

const db = drizzle(process.env.DATABASE_URL, { relations });

from 和 to 的升级

我们将 fields 重命名为 from，将 references 重命名为 to，并且两者都支持单个值或数组

❌ v1

...
author: one(users, {
  fields: [posts.authorId],
  references: [users.id],
}),
...

✅ v2

... 
author: r.one.users({
  from: r.posts.authorId,
  to: r.users.id,
}),
...

... 
author: r.one.users({
  from: [r.posts.authorId],
  to: [r.users.id],
}),
...

relationName -> alias

❌ v1

import { relations } from "drizzle-orm/_relations";
import { users, posts } from './schema';

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

✅ v2

import { defineRelations } from "drizzle-orm";
import * as schema from "./schema";

export const relations = defineRelations(schema, (r) => ({
  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
      alias: "author_post",
    }),
  },
}));

custom types 新函数

自定义类型新增了几个函数，因此你可以控制 Relational Queries v2 中的数据映射方式：

fromJson

可选的映射函数，用于将数据库中经过转换后以 JSON 返回的数据转换为目标格式例如，当通过 RQB 或 JSON 函数查询 blob 列时，结果字段将以其十六进制字符串表示形式返回，而不是常规查询中的 Buffer 为此，我们需要一个单独的函数来处理该字段的映射：

fromJson(value: string): Buffer {
	return Buffer.from(value, 'hex');
},

它会使返回的数据从：

{
	customField: "04A8...";
}

变为：

{
	customField: Buffer([...]);
}

forJsonSelect

可选的选择修饰函数，用于修改 JSON 函数内列的选择此类场景可能需要的额外映射可以通过 fromJson 函数处理由 relational queries 使用

forJsonSelect(identifier: SQL, sql: SQLGenerator): SQL {
 	return sql`cast(${identifier} as text)`
 },

这会把查询从：

SELECT
	json_object('bigint', `t`.`bigint`)
	FROM
	(
		SELECT
		`table`.`custom_bigint` AS "bigint"
		FROM
		`table`
	) AS `t`

改为：

SELECT
	json_object('bigint', `t`.`bigint`)
	FROM
	(
		SELECT
		cast(`table`.`custom_bigint` as text) AS "bigint"
		FROM
		`table`
	) AS `t`

查询对象返回值将从：

{
	bigint: 5044565289845416000; // 由于直接转换为 JSON 格式，导致部分数据丢失
}

变为：

{
	bigint: "5044565289845416380"; // 在 JSON 化之前先将字段转换为文本，因此数据得以保留
}

✅ v2

const customBytes = customType<{
 	data: Buffer;
 	driverData: Buffer;
 	jsonData: string;
 }>({
 	dataType: () => 'custom',
 	fromJson: (value) => {
 		return Buffer.from(value.slice(2, value.length), 'hex');
 	},
 	forJsonSelect: (identifier, sql, arrayDimensions) =>
 		sql`cast(${identifier} as text)`,
 });

有什么新内容？

用于多对多关系的 through

以前，你需要通过连接表查询，然后在每个响应中手动映射出来

现在你不需要这样做了！

Schema

import * as p from 'drizzle-orm/sqlite-core';

export const users = p.sqliteTable('users', {
  id: p.integer().primaryKey(),
  name: p.text(),
  verified: p.boolean().notNull(),
});

export const groups = p.sqliteTable('groups', {
  id: p.integer().primaryKey(),
  name: p.text(),
});

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

❌ v1

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

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

export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({
  group: one(groups, {
    fields: [usersToGroups.groupId],
    references: [groups.id],
  }),
  user: one(users, {
    fields: [usersToGroups.userId],
    references: [users.id],
  }),
}));

// Query example
const response = await db.query.users.findMany({
  with: {
    usersToGroups: {
      columns: {},
      with: {
        group: true,
      },
    },
  },
});

✅ v2

import * as schema from './schema';
import { defineRelations } from 'drizzle-orm';

export const relations = defineRelations(schema, (r) => ({
  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(),
  },
}));

// Query example
const response = await db.query.users.findMany({
  with: {
    groups: true,
  },
});

预定义过滤器

❌ v1

v1 中不支持

✅ v2

import * as schema from './schema';
import { defineRelations } from 'drizzle-orm';

export const relations = defineRelations(schema,
  (r) => ({
    groups: {
      verifiedUsers: r.many.users({
        from: r.groups.id.through(r.usersToGroups.groupId),
        to: r.users.id.through(r.usersToGroups.userId),
        where: {
          verified: true,
        },
      }),
    },
  })
);

// 查询示例：获取所有已验证用户的群组
const response = await db.query.groups.findMany({
  with: {
    verifiedUsers: true,
  },
});

`where` 现在是对象

❌ v1

const response = db._query.users.findMany({
  where: (users, { eq }) => eq(users.id, 1),
});

✅ v2

const response = db.query.users.findMany({
  where: {
    id: 1,
  },
});

如需完整 API 参考，请查看我们的 Select Filters 文档

Complex filter example using RAW

// schema.ts
import { integer, json, sqliteTable, text, timestamp } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer().primaryKey(),
  name: text(),
  email: text().notNull(),
  age: integer(),
  createdAt: timestamp('created_at').defaultNow(),
  lastLogin: timestamp('last_login'),
  subscriptionEnd: timestamp('subscription_end'),
  lastActivity: timestamp('last_activity'),
  preferences: text({ mode: "json" }), // 用户设置/偏好设置的 JSON 列
  interests: text({ mode: "json" }).$type<string[]>(), // 用户兴趣的数组列
});

const response = await db.query.users.findMany({
    where: {
      AND: [
        {
          OR: [{ RAW: (table) => sql`${table.name} LIKE 'john%'` }, { name: { like: "jane%" } }],
        },
        { 
          RAW: (table) => sql`${table.age} BETWEEN 25 AND 35` 
        }
      ]
    }
  })

`orderBy` 现在是对象

❌ v1

const response = db._query.users.findMany({
  orderBy: (users, { asc }) => [asc(users.id)],
});

✅ v2

const response = db.query.users.findMany({
  orderBy: { id: "asc" },
});

按关系过滤

❌ v1

v1 中不支持

✅ v2

示例：获取所有 ID>10 且至少有一篇内容以 “M” 开头的帖子的用户

const usersWithPosts = await db.query.usersTable.findMany({
  where: {
    id: {
      gt: 10
    },
    posts: {
      content: {
        like: 'M%'
      }
    }
  },
});

在相关对象上使用 offset

❌ v1

v1 中不支持

✅ v2

await db.query.posts.findMany({
	limit: 5,
	offset: 2, // 正确 ✅
	with: {
		comments: {
			offset: 3, // 正确 ✅
			limit: 3,
		},
	},
});

如何将 relations schema 定义从 v1 迁移到 v2

选项 1：使用 `drizzle-kit pull`

在新版本中，drizzle-kit pull 支持以新语法拉取 relations.ts 文件：

步骤 1

npm

yarn

pnpm

bun

npx drizzle-kit pull

yarn drizzle-kit pull

pnpm drizzle-kit pull

bunx drizzle-kit pull

步骤 2

将生成的 relations 代码从 drizzle/relations.ts 转移到你用于定义 relations 的文件中

 ├ 📂 drizzle
 │ ├ 📂 meta
 │ ├ 📜 migration.sql
 │ ├ 📜 relations.ts ────────┐
 │ └ 📜 schema.ts            |
 ├ 📂 src                    │ 
 │ ├ 📂 db                   │
 │ │ ├ 📜 relations.ts <─────┘
 │ │ └ 📜 schema.ts 
 │ └ 📜 index.ts         
 └ …

drizzle/relations.ts 包含对 drizzle/schema.ts 中所有表的导入，形式如下：

import * as schema from './schema'

你可能需要将此导入改为包含你所有 schema 表的文件。

如果有多个 schema 文件，你可以这样做：

import * as schema1 from './schema1'
import * as schema2 from './schema2'
...

步骤 3

更改 drizzle 数据库实例的创建方式，并传入 relations 对象而不是 schema

Before

import * as schema from './schema'
import { drizzle } from 'drizzle-orm/...'

const db = drizzle('<url>', { schema })

After

// 应从第 2 步中的文件导入
import { relations } from './relations'
import { drizzle } from 'drizzle-orm/...'

const db = drizzle('<url>', { relations })

如果你使用的是 MySQL 方言，只要在 v2 中不需要 mode，就可以从 drizzle() 中移除它

选项 2：手动迁移

如果你想手动迁移，可以查看我们的 Drizzle Relations 部分，其中包含完整的 API 参考以及一对一、一对多和多对多关系的示例。

如何将查询从 v1 迁移到 v2

迁移 `where` 语句

你可以查看我们的 Select Filters 文档以了解示例和完整 API 参考。

使用新语法后，你可以使用 AND、OR、NOT 和 RAW，以及此前在 Relations v1 中可用的所有过滤运算符。

示例

simple eq

using AND

using OR

using NOT

complex example using RAW

const response = await db.query.users.findMany({
  where: {
    age: 15,
  },
});

select
  "d0"."id" as "id",
  "d0"."name" as "name",
  "d0"."age" as "age"
from
  "users" as "d0"
where
  "d0"."age" = 15

const response = await db.query.users.findMany({
  where: {
    age: 15,
    name: 'John'
  },
});

select
  "d0"."id" as "id",
  "d0"."name" as "name",
  "d0"."age" as "age"
from
  "users" as "d0"
where
  (
    ("d0"."age" = 15)
    and ("d0"."name" = 'John')
  )

const response = await db.query.users.findMany({
  where: {
    OR: [
      {
        id: {
          gt: 10,
        },
      },
      {
        name: {
          like: "John%",
        },
      },
    ],
  },
});

select
  "d0"."id" as "id",
  "d0"."name" as "name",
  "d0"."age" as "age"
from
  "users" as "d0"
where
  (
    ("d0"."id" > 10)
    or ("d0"."name" like 'John%')
  )

const response = await db.query.users.findMany({
  where: {
    NOT: {
      id: {
        gt: 10,
      },
    },
    name: {
      like: "John%",
    },
  },
});

select
  "d0"."id" as "id",
  "d0"."name" as "name",
  "d0"."age" as "age"
from
  "users" as "d0"
where
  (
    (not ("d0"."id" > 10))
    and ("d0"."name" like 'John%')
  )

// schema.ts
import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";
import { sql } from "drizzle-orm";

export const users = sqliteTable("users", {
  id: integer().primaryKey(),
  name: text(),
  email: text().notNull(),
  age: integer(),
  createdAt: text("created_at"),
  lastLogin: text("last_login"),
  subscriptionEnd: text("subscription_end"),
  lastActivity: text("last_activity"),
  preferences: text({ mode: "json" }), // 用户设置/偏好设置的 JSON 列
  interests: text(),                   // 以逗号分隔的用户兴趣
});

const response = await db.query.users.findMany({
  where: {
    AND: [
      {
        OR: [
          { RAW: (table) => sql`LOWER(${table.name}) LIKE 'john%'` },
          { name: { like: "jane%" } },
        ],
      },
      { RAW: (table) => sql`${table.age} BETWEEN 25 AND 35` },
    ],
  },
});

SELECT
	"d0"."id" AS "id",
	"d0"."name" AS "name",
	"d0"."email" AS "email",
	"d0"."age" AS "age",
	"d0"."created_at" AS "createdAt",
	"d0"."last_login" AS "lastLogin",
	"d0"."subscription_end" AS "subscriptionEnd",
	"d0"."last_activity" AS "lastActivity",
	"d0"."preferences" AS "preferences",
	"d0"."interests" AS "interests"
FROM
	"users" AS "d0"
WHERE
	(
		(
			(
				(LOWER("d0"."name") LIKE 'john%')
				OR ("d0"."name" LIKE 'jane%')
			)
		)
		AND ("d0"."age" BETWEEN 25 AND 35)
	)

迁移 `orderBy` 语句

排序已被简化为单个对象，你可以在其中指定列和排序方向（asc 或 desc）

❌ v1

const response = db._query.users.findMany({
  orderBy: (users, { asc }) => [asc(users.id)],
});

✅ v2

const response = db.query.users.findMany({
  orderBy: { id: "asc" },
});

迁移 `many-to-many` 查询

Relational Queries v1 在管理多对多查询方面非常复杂。你必须使用连接表显式查询，再将这些表映射出来，如下所示：

const response = await db.query.users.findMany({
  with: {
    usersToGroups: {
      columns: {},
      with: {
        group: true,
      },
    },
  },
});

升级到 Relational Queries v2 后，你的多对多关系将如下所示：

import * as schema from './schema';
import { defineRelations } from 'drizzle-orm';

export const relations = defineRelations(schema, (r) => ({
  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(),
  },
}));

当你迁移查询后，它将变为：

// Query example
const response = await db.query.users.findMany({
  with: {
    groups: true,
  },
});

内部变更

每个 drizzle 数据库、session、migrator 和 transaction 实例都已更新，为 RQB v2 查询添加了新的泛型参数

示例

migrator

之前

export function migrate<
  TSchema extends Record<string, unknown>
>(
	db: BetterSQLite3Database<TSchema>,
	config: MigrationConfig,
) {
	...
}

现在

export function migrate<
  TSchema extends Record<string, unknown>,
  TRelations extends AnyRelations
>(
	db: BetterSQLite3Database<TSchema, TRelations>, // SQLiteBunDatabase, NodeSQLiteDatabase, DrizzleSqliteDODatabase, ...
	config: MigrationConfig,
) {
	...
}

session

之前

export class BetterSQLiteSession<
	TFullSchema extends Record<string, unknown>,
	TSchema extends TablesRelationalConfig,
> extends SQLiteSession<'sync', RunResult, TFullSchema, TSchema>

现在

export class BetterSQLiteSession< // SQLiteBunSession, NodeSQLiteSession, ...
	TFullSchema extends Record<string, unknown>,
	TRelations extends AnyRelations,
	TSchema extends V1.TablesRelationalConfig,
> extends SQLiteSession<'sync', RunResult, TFullSchema, TRelations, TSchema>

transaction

之前

export class BetterSQLiteTransaction<
	TFullSchema extends Record<string, unknown>,
	TSchema extends TablesRelationalConfig,
> extends SQLiteTransaction<'sync', RunResult, TFullSchema, TSchema>

现在

export class BetterSQLiteTransaction< // BunSQLiteTransaction, NodeSQLiteTransaction, ...
	TFullSchema extends Record<string, unknown>,
	TRelations extends AnyRelations,
	TSchema extends V1.TablesRelationalConfig,
> extends SQLiteTransaction<'sync', RunResult, TFullSchema, TRelations, TSchema>

driver

之前

export class BetterSQLite3Database<
  TSchema extends Record<string, unknown> = Record<string, never>
> extends BaseSQLiteDatabase<'sync', RunResult, TSchema>

现在

export class BetterSQLite3Database<
	TSchema extends Record<string, unknown> = Record<string, never>,
	TRelations extends AnyRelations = EmptyRelations,
> extends BaseSQLiteDatabase<'sync', RunResult, TSchema, TRelations>

DrizzleConfig 泛型已更新，新增 TRelations 参数和 relations: TRelations 字段

示例

之前

export interface DrizzleConfig<
  TSchema extends Record<string, unknown> = Record<string, never>
> {
  logger?: boolean | Logger;
  schema?: TSchema;
  casing?: Casing;
}

现在

export interface DrizzleConfig<
	TRelationConfigs extends AnyRelations = EmptyRelations,
> {
	logger?: boolean | Logger | undefined;
	relations?: TRelationConfigs | undefined;
	cache?: Cache | undefined;
	jit?: boolean | undefined;
}

以下实体已从 drizzle-orm 和 drizzle-orm/relations 中移除。原始导入现在包含了 Relational Queries v2 使用的新类型，因此如果你打算使用旧类型，请务必更新你的导入：

已移除实体列表

Relations
TableRelationsKeysOnly
ExtractTableRelationsFromSchema
ExtractRelationsFromTableExtraConfigSchema
getOperators
FindTableByDBName
RelationalSchemaConfig
RelationConfig
extractTablesRelationalConfig
relations
createOne
createMany
NormalizedRelation
normalizeRelation
createTableRelationsHelpers
TableRelationsHelpers

同样地，${dialect}-core/query-builders/query 文件已更新为 RQB v2 的替代项

示例

import { RelationalQueryBuilder, SQLiteRelationalQuery } from 'drizzle-orm/sqlite-core/query-builders/query';

迁移到关系查询版本 2

API 变更

与 v1 的不同之处

有什么不同？

有什么新内容？

where 现在是对象

orderBy 现在是对象

按关系过滤

在相关对象上使用 offset

如何将 relations schema 定义从 v1 迁移到 v2

选项 1：使用 drizzle-kit pull

步骤 1

步骤 2

步骤 3

选项 2：手动迁移

如何将查询从 v1 迁移到 v2

迁移 where 语句

迁移 orderBy 语句

迁移 many-to-many 查询

内部变更

`where` 现在是对象

`orderBy` 现在是对象

选项 1：使用 `drizzle-kit pull`

迁移 `where` 语句

迁移 `orderBy` 语句

迁移 `many-to-many` 查询