Drizzle Team•15mo ago

Inferred Types not picking up Relations

I'm having issues with my inferred types from the "Core Type API" not picking up relations. I am trying to model a 1-to-Many relationship between users and assignments . A user can "create many" assignments. My query looks like this:

await client(context.env.DB).query.assignmentsTable.findMany({
        with: {
          createdBy: true
        },
      });

await client(context.env.DB).query.assignmentsTable.findMany({
        with: {
          createdBy: true
        },
      });

And the involved tables and relations from my schema file are defined as:

export const assignmentsTable = sqliteTable("assignments", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  position: text("position").notNull(),
  // ...other columns
  createdBy: integer("created_by")
    .references(() => usersTable.id, { onDelete: "cascade" })
    .notNull(),

export const assignmentsRelations = relations(
  assignmentsTable,
  ({ one, many }) => ({
    // 1 assignment is created by 1 user
    createdBy: one(usersTable, {
      fields: [assignmentsTable.createdBy],
      references: [usersTable.id],
      relationName: "assignments.created",
    }),
  }),
);

export const usersTable = sqliteTable("users", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  userId: text("user_id").notNull(),
  last: text("last").notNull(),
  first: text("first").notNull(),
  // ...other columns
});

export const usersRelations = relations(usersTable, ({ many }) => ({
  assignmentsCreated: many(assignmentsTable, {
    relationName: "assignments.created",
  }),
  // ...other relations
}));

export const assignmentsTable = sqliteTable("assignments", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  position: text("position").notNull(),
  // ...other columns
  createdBy: integer("created_by")
    .references(() => usersTable.id, { onDelete: "cascade" })
    .notNull(),

export const assignmentsRelations = relations(
  assignmentsTable,
  ({ one, many }) => ({
    // 1 assignment is created by 1 user
    createdBy: one(usersTable, {
      fields: [assignmentsTable.createdBy],
      references: [usersTable.id],
      relationName: "assignments.created",
    }),
  }),
);

export const usersTable = sqliteTable("users", {
  id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
  userId: text("user_id").notNull(),
  last: text("last").notNull(),
  first: text("first").notNull(),
  // ...other columns
});

export const usersRelations = relations(usersTable, ({ many }) => ({
  assignmentsCreated: many(assignmentsTable, {
    relationName: "assignments.created",
  }),
  // ...other relations
}));

My type is defined as:

export type Assignment = typeof assignmentsTable.$inferSelect;

export type Assignment = typeof assignmentsTable.$inferSelect;

The query properly retrieves the user under the createdBy column but the typing still thinks the createdBy column should be a number and not the embedded user from the relation. What am I missing here? For reference, this is in a Remix project using Cloudflare D1.

5 Replies

Angelelz•15mo ago

Your relation and your foreign key are named the same, I think that's your confusion. The type that you defined has nothing to do with the query you are running. The type is just the shape of the object you'll have when you select from that table, without any relation.

volcanicislanderOP•15mo ago

I will change their name and try that, thank you. The docs here https://orm.drizzle.team/docs/rqb#include-relations state "For any nested with queries Drizzle will infer types using Core Type API". I read that as the inferred type will properly include the relations?

Drizzle Queries - DrizzleORM

Drizzle ORM | %s

Angelelz•15mo ago

Yeah, the type returned from the query will be correct. But the type you showed is not that type.

volcanicislanderOP•15mo ago

I see that the type from the query is indeed correct, however when I pass it to a component I have to provide a type in the component definition and assumed I could pass the inferred type which doesn't know about the query/relations. Is there a way to generate a proper "relationed" type to pass to components? I'm just wondering if there is an auto-magic way to have the relations in the type definition. If not, I can manually define the type by omitting the foreign key and replacing it with the relation as key and inferred select-type as value. In case anyone stumbles upon this in the future, I defined my own types by embedding the inferred select types and use them when appropriate i.e. when typing the props to a component. Hope this helps!

titongo•14mo ago

Thanks!

Gaming

Programming

Inferred Types not picking up Relations