Aljoscha Posts - Answer Overflow

Aljoscha

•Created by Aljoscha on 4/1/2025 in #help

Problems querying with findMany and relations

Hi. I started trying out drizzle (0.41.0) today and sql-like queries work pretty well. Unfortunately I have a problem with the findMany query when including relations. I created a schema "studentsTable" and set a simple one-to-many relation at "dormId" to the schema "dormsTable". When querying with findMany() and trying to get the relation I always get ⨯ TypeError: Cannot read properties of undefined (reading 'referencedTable')

export const studentsTable = pgTable("students", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  firstName: varchar({ length: 255 }).notNull(),
  lastName: varchar({ length: 255 }).notNull(),
  birthDate: date(),
  isActive: boolean().notNull().default(true),
  email: varchar({ length: 255 }).notNull().unique(),
  dormId: integer().references(() => dormsTable.id),
  ...timestamps,
});

export const dormsTable = pgTable(
  "dorms",
  {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    name: varchar({ length: 255 }).notNull(),
    shorty: varchar({ length: 3 }).notNull(),
    ...timestamps,
  },
  (table) => [
    check(
      "check_shorty_length_and_format",
      sql`CHAR_LENGTH(${table.shorty}) = 3 AND ${table.shorty} ~ '^[a-zA-Z]+$'`,
    ),
  ],
);

export const studentsTable = pgTable("students", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  firstName: varchar({ length: 255 }).notNull(),
  lastName: varchar({ length: 255 }).notNull(),
  birthDate: date(),
  isActive: boolean().notNull().default(true),
  email: varchar({ length: 255 }).notNull().unique(),
  dormId: integer().references(() => dormsTable.id),
  ...timestamps,
});

export const dormsTable = pgTable(
  "dorms",
  {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    name: varchar({ length: 255 }).notNull(),
    shorty: varchar({ length: 3 }).notNull(),
    ...timestamps,
  },
  (table) => [
    check(
      "check_shorty_length_and_format",
      sql`CHAR_LENGTH(${table.shorty}) = 3 AND ${table.shorty} ~ '^[a-zA-Z]+$'`,
    ),
  ],
);

My query:

export async function getStudents() {
  const queryBuilder = db.query.studentsTable.findMany({
    columns: {
      id: true,
      firstName: true,
    },
    with: {
      dormId: true,
    },
  });

  console.log(queryBuilder.toSQL());

  return queryBuilder;
}

export async function getStudents() {
  const queryBuilder = db.query.studentsTable.findMany({
    columns: {
      id: true,
      firstName: true,
    },
    with: {
      dormId: true,
    },
  });

  console.log(queryBuilder.toSQL());

  return queryBuilder;
}

I added the schema to drizzle() initialization:

import "dotenv/config";
import { drizzle } from "drizzle-orm/node-postgres";
import * as schema from "@/db/schema";

export const db = drizzle({
  connection: {
    connectionString: process.env.DATABASE_URL!,
  },
  casing: "snake_case",
  schema,
});

import "dotenv/config";
import { drizzle } from "drizzle-orm/node-postgres";
import * as schema from "@/db/schema";

export const db = drizzle({
  connection: {
    connectionString: process.env.DATABASE_URL!,
  },
  casing: "snake_case",
  schema,
});

What am I doing wrong? Thank you very much

6 replies

Gaming

Programming