Drizzle Team•14mo ago

TS Types when using schema and "with"

So I've recently started a project and after using Prisma for an OSS i've been contributing to I decided to try out Drizzle. Overall really liking it, but I'm running into a weird thing where sometimes relationships will show up when added in with the with property, but other times they refuse to. For example, in this case there is a many to many relationship between users and households; a single user can belong to many households, and households can have many users. The DB schema is user <-> users_to_households(user.id, household.id) <-> household

const tmpUser = await db.query.users.findFirst({
    with: {
      households: {
        with: true,
      },
    },
    where({ id }, { eq }) {
      return eq(id, user.id);
    }
  });

const tmpUser = await db.query.users.findFirst({
    with: {
      households: {
        with: true,
      },
    },
    where({ id }, { eq }) {
      return eq(id, user.id);
    }
  });

Where users has the following schema

export const users = authSchema.table('users', {
  id: uuid('id').notNull().primaryKey(),
  email: varchar('email').notNull(),
});

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

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

export const households = pgTable(
  'households',
  {
    id: text('id').primaryKey().$defaultFn(() => ulid()),
    name: text('name').notNull(),
    createdAt: date('created_at').notNull().defaultNow()
  },
  // Creating an index on the name as we will search on it.
  ({ name }) => ({
    name: index('name_index').on(name)
  })
);

export const users = authSchema.table('users', {
  id: uuid('id').notNull().primaryKey(),
  email: varchar('email').notNull(),
});

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

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

export const households = pgTable(
  'households',
  {
    id: text('id').primaryKey().$defaultFn(() => ulid()),
    name: text('name').notNull(),
    createdAt: date('created_at').notNull().defaultNow()
  },
  // Creating an index on the name as we will search on it.
  ({ name }) => ({
    name: index('name_index').on(name)
  })
);

Gives me the attached image, but other relationships work without error. I feel like I should also note that up until a few days ago, the types here were working just fine.

8 Replies

jhechtfOP•14mo ago

In testing a bit more, it seems like the issue is more "queries that use with inside another with" -- the types for anything below the initial "with" don't look like they're picked up. e.g.

const householdsValue = await db.query.usersToHouseholds.findMany({
    where: ({ userId }, { eq }) => eq(userId, user.id),
    with: {
      household: {
        with: {
          users: true,
        }
      },
    }
  });

console.info(householdsValue[0].users); // <- red squiggles of doom

const householdsValue = await db.query.usersToHouseholds.findMany({
    where: ({ userId }, { eq }) => eq(userId, user.id),
    with: {
      household: {
        with: {
          users: true,
        }
      },
    }
  });

console.info(householdsValue[0].users); // <- red squiggles of doom

yeah I'm not entirely sure what I should be doing differently here -- it seems like these queries should have the types show up, they just don't.

Angelelz•14mo ago

I believe nested withs are not supported. You should fall back to regular CRUD api

jhechtfOP•13mo ago

it is in the docs, so i figured it was ok, issue i have now is that it's not even working for even first level.

jhechtfOP•13mo ago

For reference: https://orm.drizzle.team/docs/rqb

You can chain nested with statements as much as necessary. For any nested with queries Drizzle will infer types using Core Type API. Get all users with posts. Each post should contain a list of comments

Code snippet under that

const users = await db.query.users.findMany({
    with: {
        posts: {
            with: {
                comments: true,
            },
        },
    },
});

const users = await db.query.users.findMany({
    with: {
        posts: {
            with: {
                comments: true,
            },
        },
    },
});

Drizzle Queries - DrizzleORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind

Angelelz•13mo ago

You're right. Those are supported. I believe I see your problem You can't go from user to household directly, your relation user to household is many to many You have it set up properly, but your query should look like this:

const householdsValue = await db.query.usersToHouseholds.findMany({
    where: ({ userId }, { eq }) => eq(userId, user.id),
    with: {
      household: {
        with: {
          users: true,  // <-- this wont work because household is not directly related to users. they have usersToHouseHolds in the middle
        }
      },
    }
  });

const householdsValue = await db.query.usersToHouseholds.findMany({
    where: ({ userId }, { eq }) => eq(userId, user.id),
    with: {
      household: {
        with: {
          users: true,  // <-- this wont work because household is not directly related to users. they have usersToHouseHolds in the middle
        }
      },
    }
  });

You could do:

const householdsValue = await db.query.usersToHouseholds.findMany({
    where: ({ userId }, { eq }) => eq(userId, user.id),
    with: {
      household: true,
      user: true
    }
  });

const householdsValue = await db.query.usersToHouseholds.findMany({
    where: ({ userId }, { eq }) => eq(userId, user.id),
    with: {
      household: true,
      user: true
    }
  });

Depending on what data you want in return

jhechtfOP•13mo ago

what's weird for me is that i'm not getting any type inference on the result at all, even just one layer deep, which is weird because it used to and then just stopped didn't anymore.

Steven•3mo ago

@jhechtf Same, I believe it used to work for me and now it doesn't! Have you managed to get it back?

Darren•3mo ago

@jhechtf doest the userid in the junction table need to have a references users.id ? going ot edit this, its not required, but it was my first thought ah ok i understand now, you started from the junction table so can go directly to users or households, but then you would need to go back to junction table before you can go to users or households again.. Angelelz did say it, i totally missed it!!

Gaming

Programming

TS Types when using schema and "with"