[BUG]: Incorrect type inference on relational query

[williamlmao]

What version of drizzle-orm are you using?

0.27.2

What version of drizzle-kit are you using?

0.19.12

Describe the Bug

My prepared statement is correctly returning the data I would expect, but the type that is inferred does not match.

Prepared Statement

export const pGetTrainingSetWithExamples = db.query.trainingSets
  .findFirst({
    where: eq(trainingSets.publicId, placeholder("publicId")),
    with: {
      examples: true,
    },
  })
  .prepare();

Returned data:

{
id: 1,
publicId: 'abcd',
userId: '1234',
name: 'Indicators',
description: 'Indicators',
stopSequence: null,
promptSeparator: null,
createdAt: 2023-08-02T06:53:30.000Z,
lastUpdated: 2023-08-02T06:53:30.000Z,
examples: [
  {
    id: 1,
    prompt: "Hi",
    completion: 'Hello',
    flagged: null,
    reviewed: null,
    locked: null,
    validFrom: null,
    validTo: null,
    trainingSetId: 1,
    createdAt: 2023-08-02T08:05:49.000Z,
    lastUpdated: 2023-08-02T08:05:49.000Z
  }
]
}

I would expect examples to be on the type. You can see from the screenshot below that intellisense is not picking up on it.

Expected behavior

The type to match the data

Environment & setup

local

[jstlaurent]

Can confirm this is still an issue in 0.28.2.

[dankochetov]

Could you provide the relevant schema parts?

[jstlaurent]

@dankochetov : Sorry for the late reply!

Here's what my schema looks like, with some irrelevant fields removed for clarity:

export const dataset = pgTable(
  'dataset',
  {
    id: varchar('id', { length: 256 }).$defaultFn(generateId).primaryKey(),
    createdAt: timestamp('created_at', {withTimezone: true, mode: 'date'}).notNull().defaultNow(),
    deletedAt: timestamp('deleted_at', { withTimezone: true, mode: 'date' }),    
    state: datasetStateEnum('state').default('upload_pending').notNull(),

    // Fields
    name: text('name').notNull(),
  }
)

export const benchmark = pgTable(
  'benchmark',
  {
    id: varchar('id', { length: 256 }).$defaultFn(generateId).primaryKey(),
    createdAt: timestamp('created_at', {withTimezone: true, mode: 'date'}).notNull().defaultNow(),
    deletedAt: timestamp('deleted_at', { withTimezone: true, mode: 'date' }),
    state: benchmarkStateEnum('state').default('ready').notNull(),

    // Relationships
    datasetId: varchar('dataset_id', { length: 256 })
      .notNull()
      .references(() => dataset.id),

    // Fields
    name: text('name').notNull()
  }
)

export const datasetBenchmarkRelations = relations(dataset, ({ many }) => ({
  benchmarks: many(benchmark)
}))

export const benchmarkRelations = relations(benchmark, ({ one }) => ({
  dataset: one(dataset, {
    fields: [benchmark.datasetId],
    references: [dataset.id]
  })
}))

And here's what the code using it looks like:

export type Dataset = InferSelectModel<typeof dataset>

export async function remove(
  datasetId: DatasetIdentity,
  userId: UserIdentity
): Promise<Dataset> {
  return db.transaction(async _ => {
    const entity = await db.query.dataset.findFirst({
    where: and(
      eq(dataset.name, datasetId.name),
      isNull(dataset.deletedAt)
    ),
    with: {
      benchmarks: {
          where: isNull(benchmark.deletedAt),
          orderBy: [desc(benchmark.createdAt)],
          limit: 10,
          columns: {id: true}
        }
      }
  })

    // Skip type checking because of type inference issue on Drizzle Relational queries
    // See https://github.com/drizzle-team/drizzle-orm/issues/968
    // @ts-ignore
    if (entity.benchmarks.length > 0) {
      throw new ConflictError('Cannot delete dataset with benchmarks')
    }

    const updatedEntity = await db
      .update(dataset)
      .set({ deletedAt: new Date() })
      .where(eq(dataset.id, entity.id))
      .returning()

    return updatedEntity[0]
  })
}

The conditional entity.benchmarks.length > 0 has this TypeScript error:

TS2339: Property  benchmarks  does not exist on type
{ id: string; name: string; createdAt: Date; deletedAt: Date | null; state: "upload_pending" | "ready" }

It works fine at runtime, with the property present and filled correctly with related entities.

[xavier-kong]

Hi I seem to have a related issue, here is an extract of the related schema. At run time all the fields are present, but it causes the same typescript error where the property is said to not exist on the type, in my case it is the log field on the type item.

export const items = sqliteTable('items', {
    userId: text('user_id').notNull().references(() => users.userId),
    itemId: integer('item_id').primaryKey(),
    itemType: text('item_type', { enum: ['time', 'duration', 'amount', 'consistency']}).notNull(),
    itemName: text('item_name').notNull(),
    createdAt: integer('created_at', { mode: 'timestamp_ms' }).notNull().default(sql`CURRENT_TIMESTAMP`),
    direction: text('direction', { enum: ['increase', 'decrease']})
});

export const itemsToLogsRelations = relations(items, ({ many }) => ({
    logs: many(logs),
}));

  const dataQuery = await ctx.db.query.items.findMany({
      where: eq(items.userId, ctx.userId),
      columns: {
          userId: false,
          createdAt: false,
      },
      with: {
          logs: true,
      }
  });

[masonbrothers]

@xavier-kong

I had a similar issue. I had to ensure that the schema had the exported relations object. In your code, did your schema include the exported itemsToLogsRelations? Using your code as an example, convert:

const schema = {
    items,
}

export const db = drizzle(
    client,
    { schema },
);

to

const schema = {
    items,
    itemsToLogsRelations,
}

export const db = drizzle(
    client,
    { schema },
);

[xavier-kong]

@masonbrothers

I resolved the issue with a different method. While I had already exported the relations, I had them separate. I combined the relations to a single

export const itemsRelations = relations(items, ({ many, one }) => ({
    logs: many(logs),
    user: one(users, {
        fields: [items.userId],
        references: [users.userId]
    })
}));

and somehow this managed to solve my issue.

[3noix]

@xavier-kong

I had the same issue.
Do you know why did you fall into this trap?
For me I think it was because the documentation has no example with several relations on the same table.

[xavier-kong]

@3noix

For me it probably was the documentation but I think they recently updated the documentation here.

[shiny]

@masonbrothers

I resolved the issue with a different method. While I had already exported the relations, I had them separate. I combined the relations to a single

export const itemsRelations = relations(items, ({ many, one }) => ({
    logs: many(logs),
    user: one(users, {
        fields: [items.userId],
        references: [users.userId]
    })
}));

and somehow this managed to solve my issue.

Thank you, it works for my case!

[dum3ng]

@masonbrothers

I resolved the issue with a different method. While I had already exported the relations, I had them separate. I combined the relations to a single

export const itemsRelations = relations(items, ({ many, one }) => ({
    logs: many(logs),
    user: one(users, {
        fields: [items.userId],
        references: [users.userId]
    })
}));

and somehow this managed to solve my issue.

Confirmed that this is the problem. The relation can not be used mutiple times on the same table.