23 replies

.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 })

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

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.

db.query.

I get auto complete for the tables, shown in the second screenshot. However, using any

with

with

in the clauses later is not populated.

Here is

usersToHouseholds.table.ts

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

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(...)

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?

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

Similar Threads