[BUG]: defineRelations not working on Cockroach.

[hasan-muzak]

Report hasn't been filed before.

• I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

1.0.0-beta.11-fc31a5f

What version of drizzle-kit are you using?

1.0.0-beta.11-fc31a5f

Other packages

No response

Describe the Bug

I'm working on a project that I created on Elysia. I have several tables and relations.

Example: relations.ts:

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

Example tables.ts

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

My workspace Member schema:

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>;

And my workspace member relations:

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,
      }),
    },
  })
);

And this is how I initialize the drizzle instance:

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

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

However, when a function is called like below:

  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;

    // Check if member exists AND the related workspace is not deleted
    return (
      !!member && !!member.workspace && member.workspace.deletedAt === null
    );
  },

The runtime throws an error:

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)

Somehow it does not define my relations.

I even tried to define relations right before defining drizzleInstance, such as:

const relations = defineRelations(
    schema,
    (r) => ({
      apiKeys: {
        workspace: r.one.workspaces({
          from: r.apiKeys.workspaceId,
          to: r.workspaces.id,
        }),
        createdBy: r.one.users({
          from: r.apiKeys.createdById,
          to: r.users.id,
        }),
      },
    })
  )

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


  console.log(drizzleInstance._.schema)

  logger.info("Database connection pool initialized");

However, It seems like relations are empty for apiKeys table. (console.log output)

{
  apiKeys: {
    tsName: "apiKeys",
    dbName: "api_keys",
    schema: undefined,
    columns: {
      id: [Object ...],
      name: [Object ...],
      keyHash: [Object ...],
      public: [Object ...],
      workspaceId: [Object ...],
      createdById: [Object ...],
      lastUsedAt: [Object ...],
      createdAt: [Object ...],
      updatedAt: [Object ...],
      deletedAt: [Object ...],
    },
    relations: {},
    primaryKey: [
      [Object ...]
    ],
  },

What I tried:

• https://stackoverflow.com/questions/78177831/drizzle-db-query-throwing-undefined-is-not-an-object-evaluating-relation-refe
• Cannot read properties of undefined (reading 'referencedTable') #2718
• https://www.answeroverflow.com/m/1219061432860278784

Unfortunately I could not solve.

I tried to get help from LLMs but most of the answer are out-of-date and did not work for me.

[OleksiiKH0240]

Hi @muzakon.
The defineRelations function is used to define relations for RQB v2, which is currently not available for the cockroach driver.

Once RQB v2 is shipped for the cockroach driver, you will be able to use it with the query property, for example:

const member = await db.query.workspaceMembers.findFirst(...);

Until then, you need to use RQB v1 syntax for relations.
schema.ts

import {
  cockroachTable,
  uuid,
  int4,
  index,
  text,
  timestamp,
} from "drizzle-orm/cockroach-core";
import { InferSelectModel } from "drizzle-orm";
import { relations } from "drizzle-orm/_relations";

const baseColumns = {
  createdAt: timestamp().defaultNow(),
  deletedAt: timestamp(),
};

export const workspaces = cockroachTable("workspaces", {
  id: uuid().primaryKey(),
  ...baseColumns,
});

export const users = cockroachTable("users", {
  id: uuid().primaryKey(),
  ...baseColumns,
});

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 const workspaceMembersRelations = relations(
  workspaceMembers,
  ({ one }) => ({
    workspace: one(workspaces, {
      fields: [workspaceMembers.workspaceId],
      references: [workspaces.id],
    }),
    user: one(users, {
      fields: [workspaceMembers.userId],
      references: [users.id],
    }),
  }),
);

Specify the relations together with the tables using the schema option when creating the Drizzle instance:

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

const db = drizzle(
    "postgresql://root@127.0.0.1:26257/defaultdb?sslmode=disable",
    { schema },
  );

const main = async () => {
    const member = await db._query.workspaceMembers.findFirst(...);
}

main();

[hasan-muzak]

Oh thanks, that makes sense!

[OleksiiKH0240]

You also won’t need to specify schema in the Drizzle instance for RQB v2 (once it’s shipped for the cockroach driver), only relations.
I’ll close the issue then.