DT
Drizzle Team•3d ago
Aljoscha

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
3 Replies
claude
claude•3d ago
with:{
  dorms:true
}
with:{
  dorms:true
}
you need to make a relations object and pass it to the db schema the docs are not very good
claude
claude•3d ago
https://orm.drizzle.team/docs/relations
Drizzle ORM - Relations
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
Aljoscha
AljoschaOP•3d ago
This totally works 🥳 Thank you very much!

Did you find this page helpful?