How to declare and query with relations using Drizzle?

I'm having this Next.js project where I would like to query a user with their permissions using Drizzle ORM and Supabase. I keep getting the following error when I try to add with into the query as documented here.

Error message:

Error: Cannot read properties of undefined (reading 'referencedTable')

This is my schema, where I tried to create references with userId and a userRelations function to define the relations.

export const users = pgTable("users", {
  id: uuid("id").primaryKey().notNull(),
  fullName: text("full_name"),
  avatarUrl: text("avatar_url"),
  email: text("email"),
  workspaceId: uuid("workspace_id"),
})

export const permissions = pgTable("permissions", {
  id: uuid("id").defaultRandom().primaryKey().notNull(),
  userId: uuid("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  userCanViewKbase: boolean("user_can_view_kbase"),
  userCanManageContent: boolean("user_can_manage_content"),
  userCanViewWorkspaceSettings: boolean("user_can_view_workspace_settings"),
})

export const userRelations = relations(users, ({ one }) => ({
  permission: one(permissions, {
    fields: [users.id],
    references: [permissions.userId],
  }),
}))

I then created a readUser function with the following:

"use server"

// Next
import { cookies } from "next/headers"

// Auth
import { createClient } from "@/lib/supabase/server"
import db from "@/lib/supabase/db"

export default async function readUser() {
  const cookieStore = cookies()
  const supabase = createClient(cookieStore)

  const {
    data: { user: authUser },
  } = await supabase.auth.getUser()

  if (!authUser) {
    return null
  }

  const user = await db.query.users.findFirst({
    where: (users, { eq }) => eq(users.id, authUser?.id || ""),
    with: {
      permissions: true,
    },
  })

  console.log(user)

  return user
}

If I remove the following, the query works but without the relation to permissions:

with: {
      permissions: true,
    },

Can you spot what I do wrong here?

Your schema defines a named relation called permission and your query is using a named relation called permissions. Since a user only has one permission, I would suggest updating your query to use permission.

The following snippet is from your schema definition. Here, permission is singular:

export const userRelations = relations(users, ({ one }) => ({
  permission: one(permissions, {
    fields: [users.id],
    references: [permissions.userId],
  }),
}))

Fix: In your query, you need to use the same named relation. Use permission: true.

  const user = await db.query.users.findFirst({
    where: (users, { eq }) => eq(users.id, authUser?.id || ""),
    with: {
      permission: true,
    },
  })