Drizzle Team•11mo ago

Postgres 'with' returns null with basic setup?

Hi all. Trying to get my head around Drizzle's query API but I've hit a stumbling block. I've got a basic team (has many) users schema, and have created a route that gets the user's current team. This is done with the following code:

  const data = await db.query.users.findFirst({
      where: eq(users.id, userId),
      with: {
          team: true
      }
  })

  const data = await db.query.users.findFirst({
      where: eq(users.id, userId),
      with: {
          team: true
      }
  })

However, on logging out the result, the team field in the response object is null. Here is my schema.ts:

export const users = pgTable('users', {
    id: varchar('id', {length: 256}).primaryKey(),
    authId: varchar('auth_id', {length: 256}).unique(),
    firstName: varchar('first_name', { length: 256 }),
    lastName: varchar('last_name', { length: 256 }),
    email: varchar('email', { length: 256 }).notNull(),
    teamId: varchar('team_id', {length:256}).references(() => teams.id),
    role: roleEnum('role').notNull(),
    isStaff: boolean('is_staff').default(false),
    createdAt: timestamp('created_at').defaultNow(),
    updatedAt: timestamp('updated_at').defaultNow(),
});

export const usersRelations = relations(users, ({ one }) => ({
    team: one(teams, {
        fields: [users.id],
        references: [teams.id],
    }),
}));

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

export const teams = pgTable('teams', {
    id: varchar('id', {length: 256}).primaryKey(),
    name: varchar('name', { length: 256 }),
    createdAt: timestamp('created_at').defaultNow(),
    updatedAt: timestamp('updated_at').defaultNow(),
});

export const teamsRelations = relations(teams, ({ many }) => ({
    users: many(users),
}));

export const users = pgTable('users', {
    id: varchar('id', {length: 256}).primaryKey(),
    authId: varchar('auth_id', {length: 256}).unique(),
    firstName: varchar('first_name', { length: 256 }),
    lastName: varchar('last_name', { length: 256 }),
    email: varchar('email', { length: 256 }).notNull(),
    teamId: varchar('team_id', {length:256}).references(() => teams.id),
    role: roleEnum('role').notNull(),
    isStaff: boolean('is_staff').default(false),
    createdAt: timestamp('created_at').defaultNow(),
    updatedAt: timestamp('updated_at').defaultNow(),
});

export const usersRelations = relations(users, ({ one }) => ({
    team: one(teams, {
        fields: [users.id],
        references: [teams.id],
    }),
}));

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

export const teams = pgTable('teams', {
    id: varchar('id', {length: 256}).primaryKey(),
    name: varchar('name', { length: 256 }),
    createdAt: timestamp('created_at').defaultNow(),
    updatedAt: timestamp('updated_at').defaultNow(),
});

export const teamsRelations = relations(teams, ({ many }) => ({
    users: many(users),
}));

What might I be doing wrong here? Been stuck on this for a while.

2 Replies

Harrie•11mo ago

Classic user error. Issue was: export const usersRelations = relations(users, ({ one }) => ({ team: one(teams, { fields: [users.id], references: [teams.id], }), })); should be export const usersRelations = relations(users, ({ one }) => ({ team: one(teams, { fields: [users.teamId], references: [teams.id], }), })); I need to sleep 😄

Angelelz•11mo ago

It didn't throw a type error? This might be a good improvement

Gaming

Programming

Postgres 'with' returns null with basic setup?