Drizzle ORM关联查询报错：TypeError: 无法读取relation.referencedTable

问题场景 #

基于Elysia框架开发项目，使用Drizzle ORM操作CockroachDB，已定义多张数据表及其关联关系，但执行关联查询时抛出以下错误：

TypeError: undefined is not an object (evaluating 'relation.referencedTable')

相关代码片段 #

1. relations.ts（存在语法错误）

export { userRelations } from "./relations/users.relations";
export { workspaceRelations } from "./relations/workspaces.relations";
export { workspaceMemberRelations } from "./relations/workspaceMembers.relations";s"; // 多余的`s";`导致语法错误

2. tables.ts

export * from "./schema/user";
export * from "./schema/workspace";
export * from "./schema/workspaceMember";

3. workspaceMember表结构

import {
  cockroachTable,
  uuid,
  int4,
  index,
  text,
  timestamp,
} from "drizzle-orm/cockroach-core";
import { baseColumns } from "../../common/database/shared";
import { users } from "./user";
import { workspaces } from "./workspace";
import { InferSelectModel } from "drizzle-orm";

export const workspaceMembers = cockroachTable(
  "workspace_members",
  {
    id: int4("id").primaryKey().generatedByDefaultAsIdentity(),
    workspaceId: uuid("workspace_id")
      .references(() => workspaces.id, { onDelete: "cascade" })
      .notNull(),
    userId: uuid("user_id")
      .references(() => users.id, { onDelete: "cascade" })
      .notNull(),
    role: text("role")
      .default("DEVELOPER")
      .notNull()
      .$type<"OWNER" | "ADMIN" | "DEVELOPER" | "BILLING">(),
    ...baseColumns,
    deletedAt: timestamp("deleted_at"),
  },
  (table) => [index("workspace_members_user_idx").on(table.userId)]
);

export type WorkspaceMemberSchema = InferSelectModel<typeof workspaceMembers>;

4. 原workspaceMember关联关系定义

import { defineRelations } from "drizzle-orm";
import { users, workspaces, workspaceMembers } from "../tables";

export const workspaceMemberRelations = defineRelations(
  { users, workspaces, workspaceMembers },
  (r) => ({
    workspaceMembers: { // 错误：嵌套同名键导致关联映射异常
      workspace: r.one.workspaces({
        from: r.workspaceMembers.workspaceId,
        to: r.workspaces.id,
      }),
      user: r.one.users({
        from: r.workspaceMembers.userId,
        to: r.users.id,
      }),
    },
  })
);

5. Drizzle实例初始化

import { drizzle } from "drizzle-orm/cockroach";
import * as schema from "../database/tables"
import * as relations from "../database/relations";

drizzleInstance = drizzle({
  client: pool, schema, relations
});

6. 触发错误的查询函数

async checkMemberRole(
  userId: string,
  workspaceId: string,
  allowedRoles: ("OWNER" | "ADMIN" | "DEVELOPER" | "BILLING")[]
): Promise<boolean> {
  const db = getDb();

  const member = await db._query.workspaceMembers.findFirst({
    where: (row, { eq, and, inArray, isNull }) =>
      and(
        eq(row.userId, userId),
        eq(row.workspaceId, workspaceId),
        inArray(row.role, allowedRoles),
        isNull(row.deletedAt)
      ),
    with: {
      workspace: true,
    },
  }) as MemberWithWorkspace | undefined;

  return (
    !!member && !!member.workspace && member.workspace.deletedAt === null
  );
},

错误栈信息 #

138 | function normalizeRelation(schema, tableNamesMap, relation) {
139 |   if (is(relation, One) && relation.config) return {
140 |           fields: relation.config.fields,
141 |           references: relation.config.references
142 |   };
143 |   const referencedTableTsName = tableNamesMap[getTableUniqueName(relation.referencedTable)];
                                                                      ^
TypeError: undefined is not an object (evaluating 'relation.referencedTable')
      at normalizeRelation (/Users/muzak/Desktop/labs/services/main/node_modules/drizzle-orm/_relations.js:143:65)
      at buildRelationalQueryWithoutPK (/Users/muzak/Desktop/labs/services/main/node_modules/drizzle-orm/cockroach-core/dialect.js:360:35)
      at _toSQL (/Users/muzak/Desktop/labs/services/main/node_modules/drizzle-orm/cockroach-core/query-builders/query.js:72:22)
      at <anonymous> (/Users/muzak/Desktop/labs/services/main/node_modules/drizzle-orm/cockroach-core/query-builders/query.js:45:39)
      at <anonymous> (/Users/muzak/Desktop/labs/services/main/node_modules/drizzle-orm/cockroach-core/query-builders/query.js:90:16)
      at then (/Users/muzak/Desktop/labs/services/main/node_modules/drizzle-orm/query-promise.js:20:15)

错误原因 #

1. 关联关系定义语法错误：在defineRelations返回对象中，错误地将workspaceMembers表的关联嵌套在同名键下，导致Drizzle无法正确识别关联对应的表，进而找不到referencedTable。
2. relations.ts语法错误：最后一行多余的s";可能导致模块导出异常，影响关联关系的加载。

解决方案 #

1. 修正关联关系定义 #

将workspaceMemberRelations改为直接为workspaceMembers表声明关联，去掉多余的嵌套键：

import { defineRelations } from "drizzle-orm";
import { users, workspaces, workspaceMembers } from "../tables";

export const workspaceMemberRelations = defineRelations(
  { users, workspaces, workspaceMembers },
  (r) => ({
    // 直接为当前表定义关联
    workspace: r.one(workspaces, {
      from: workspaceMembers.workspaceId,
      to: workspaces.id,
    }),
    user: r.one(users, {
      from: workspaceMembers.userId,
      to: users.id,
    }),
  })
);

2. 修复relations.ts的语法错误 #

删除最后一行多余的s";：

export { userRelations } from "./relations/users.relations";
export { workspaceRelations } from "./relations/workspaces.relations";
export { workspaceMemberRelations } from "./relations/workspaceMembers.relations";

3. 验证导入正确性 #

确保schema和relations的导入路径正确，不存在循环引用问题。

验证 #

重新运行查询函数，确认错误消失，关联查询能够正常返回包含workspace关联数据的结果。

内容的提问来源于stack exchange，提问作者user30851698