Drizzle Team•11mo ago

.query.with relationship types don't work unless I manually type them out

I thought I had posted this here some time ago, but I can't find the post so I hope I'm not double posting but I'm looking for a bit of guidance. For reference, I'm creating a bill payment tracker that can be used with multiple people through groups I call "households." For a while, using the db.query.households.findMany({with: { users: true }) gave me proper type inference in my editor. For reference, I have my files split up per the picture, with each table having a separate file, with all exports from those files being re-exported in the index.ts like so:

// $lib/server/db/schema.ts
export * from './households.table';
export * from './payments.table';
export * from './users.table';
export * from './usersToHouseholds.table';
export * from './bills.table';
export * from './identities.table';
export * from './invites.table';

// client.ts
import * as exportedSchema from '$lib/server/db/schema';
/// creating the PG Client here
export const db = drizzle(client, { schema: exportedSchema, logger: true });

// $lib/server/db/schema.ts
export * from './households.table';
export * from './payments.table';
export * from './users.table';
export * from './usersToHouseholds.table';
export * from './bills.table';
export * from './identities.table';
export * from './invites.table';

// client.ts
import * as exportedSchema from '$lib/server/db/schema';
/// creating the PG Client here
export const db = drizzle(client, { schema: exportedSchema, logger: true });

If I try to call db.query. I get auto complete for the tables, shown in the second screenshot. However, using any with in the clauses later is not populated. Here is usersToHouseholds.table.ts for reference

import { pgTable, text, uuid, index } from 'drizzle-orm/pg-core';
import { users } from './users.table';
import { relations } from 'drizzle-orm';
import { households } from './households.table';

export const usersToHouseholds = pgTable(
  'users_to_households',
  {
    id: uuid('id').notNull().primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
    householdId: text('household_id')
      .notNull()
      .references(() => households.id, { onDelete: 'cascade' }),
  },
  ({ userId }) => ({
    userIdIndex: index('household_user_id_index').on(userId),
  }),
);

export const usersToHouseholdsRelations = relations(
  usersToHouseholds,
  ({ one }) => ({
    user: one(users, {
      fields: [usersToHouseholds.userId],
      references: [users.id],
    }),
    household: one(households, {
      fields: [usersToHouseholds.householdId],
      references: [households.id],
    }),
  }),
);

export const usersHouseholds = relations(users, ({ many }) => ({
  households: many(usersToHouseholds),
}));

export const householdUsers = relations(households, ({ many }) => ({
  users: many(usersToHouseholds),
}));

import { pgTable, text, uuid, index } from 'drizzle-orm/pg-core';
import { users } from './users.table';
import { relations } from 'drizzle-orm';
import { households } from './households.table';

export const usersToHouseholds = pgTable(
  'users_to_households',
  {
    id: uuid('id').notNull().primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
    householdId: text('household_id')
      .notNull()
      .references(() => households.id, { onDelete: 'cascade' }),
  },
  ({ userId }) => ({
    userIdIndex: index('household_user_id_index').on(userId),
  }),
);

export const usersToHouseholdsRelations = relations(
  usersToHouseholds,
  ({ one }) => ({
    user: one(users, {
      fields: [usersToHouseholds.userId],
      references: [users.id],
    }),
    household: one(households, {
      fields: [usersToHouseholds.householdId],
      references: [households.id],
    }),
  }),
);

export const usersHouseholds = relations(users, ({ many }) => ({
  households: many(usersToHouseholds),
}));

export const householdUsers = relations(households, ({ many }) => ({
  users: many(usersToHouseholds),
}));

But if i try await db.query.users.findFirst({ with: { h }}) I do not get the correct values showing up (third picture) I thought this was all really weird. Then one day I decided to explicitly define the object, instead of relying on the type inference from the import (abbreviated a bit)

export const exportedSchema = {
  // Schema
  authSchema: schema.authSchema,
  // Tables
  bills: schema.bills,
  households: schema.households,
  users: schema.users,
  payments: schema.payments,
  usersToHouseholds: schema.usersToHouseholds,
  invites: schema.invites,
  // Relationships
  paymentsToBill: schema.paymentToBill,
  billsToPayments: schema.billsToPayments,
  householdToUsers: schema.householdUsers,
  userHouseholds: schema.usersToHouseholds,
  usersToHouseholdsRelations: schema.usersToHouseholdsRelations,
  householdUsers: schema.householdUsers,
  usersHouseholds: schema.usersHouseholds
};

export const exportedSchema = {
  // Schema
  authSchema: schema.authSchema,
  // Tables
  bills: schema.bills,
  households: schema.households,
  users: schema.users,
  payments: schema.payments,
  usersToHouseholds: schema.usersToHouseholds,
  invites: schema.invites,
  // Relationships
  paymentsToBill: schema.paymentToBill,
  billsToPayments: schema.billsToPayments,
  householdToUsers: schema.householdUsers,
  userHouseholds: schema.usersToHouseholds,
  usersToHouseholdsRelations: schema.usersToHouseholdsRelations,
  householdUsers: schema.householdUsers,
  usersHouseholds: schema.usersHouseholds
};

Switching over the drizzle(...) call to use this explicit schema gives what I saw before, and what I believe is the expected result (4th image) I spoke with a friend on another discord server who is also using drizzle in the same fashion (re-exporting everything from a singular schema file) and his queries have proper relationship suggestions. Any suggestions on moving forward?

13 Replies

jhechtfOP•11mo ago

For some reason the 3rd and 4th images weren't showing up so here they are.

Angelelz•11mo ago

Are you using database schemas?

jhechtfOP•11mo ago

unclear what you mean by that. are you talking about things like "schemaName"."tableName" or are you talking about the schema option when making a drizzle adapter?

Angelelz•11mo ago

First option I've seen similar bugs due to schemas

jhechtfOP•11mo ago

I'm using a singular db schema -- using supabase auth so i setup a barebones drizzle schema + table to be able to use foreign keys.

Angelelz•11mo ago

Can you try commenting out this line: authSchema: schema.authSchema, Because I believe the the authSchema is called auth.users in supabase. the name of the table might be causing issues.

jhechtfOP•11mo ago

Doesn't appear to be fixing it right now. im at work rn so i can't spend too much time trying to get it working. will update later once i can spend more time on it Ok so I think I've found the source, though in fairness I don't understand. I had split up the relationships based on the places where they sort of occurred. e.g. households <-> bills <-> payments, so in the payments.table.ts file I would setup the relationships pertaining to the payments table in there, e.g.

// payments.table.ts
import { bills } from './bills.table';
export const payments = pgTable(...);
export const paymentToBill = relations(payments, ({ one }) => ({
  bill: one(bills, {
    fields: [payments.billId],
    references: [bills.id],
  }),
}));

export const billsToPayments = relations(bills, ({ many }) => ({
  payments: many(payments),
}));

// payments.table.ts
import { bills } from './bills.table';
export const payments = pgTable(...);
export const paymentToBill = relations(payments, ({ one }) => ({
  bill: one(bills, {
    fields: [payments.billId],
    references: [bills.id],
  }),
}));

export const billsToPayments = relations(bills, ({ many }) => ({
  payments: many(payments),
}));

Which also meant that in say, the bills.table.ts file I was doing something similar

// bills.table.ts
import { households } from './households.table';
export const bills = pgTable(...);
export const billToHousehold = relations(bills, ({ one }) => ({
  household: one(households, {
    fields: [bills.householdId],
    references: [households.id],
  }),
}));

// bills.table.ts
import { households } from './households.table';
export const bills = pgTable(...);
export const billToHousehold = relations(bills, ({ one }) => ({
  household: one(households, {
    fields: [bills.householdId],
    references: [households.id],
  }),
}));

What I've noticed is that the moment the schema includes more than 1 relations() call on the same table, the type hinting goes out the window. To note though, using with still works, but now the return type is incorrect, and I have to read through the files to remember the corresponding name, which is suboptimal. will do some work to try and move the relationships around so that i can have 1 relationship call per table to see if that still works, but would definitely like to be able to split things up if possible.

Angelelz•11mo ago

Can you create a reproduction? This doesn't seem right

jhechtfOP•11mo ago

Sure, give me a bit to get a repo up and going.

jhechtfOP•11mo ago

Hopefully this is enough but LMK if you need something else to help debug https://github.com/jhechtf/drizzle-orm-issue

GitHub

GitHub - jhechtf/drizzle-orm-issue

Contribute to jhechtf/drizzle-orm-issue development by creating an account on GitHub.

Angelelz•11mo ago

Ok, I'm dumb I didn't see your problem before I just saw your explanation in the readme and it was clear You are supposed to defined all the relations of a table in the same relation() call This is correct behavior. Whenever you define a relation, all drizzle expects all the relations of that table to be defined in the same function call

jhechtfOP•11mo ago

Ah, i must have missed that in the docs -- the dangers of reading fast. I had assumed i could split up the relations calls and as long as they were in the schema everything would work fine. Glad I finally got an answer!

Angelelz•11mo ago

You can split them per table, but all the table relations have to be in the same relations object

Gaming

Programming

.query.with relationship types don't work unless I manually type them out