Relations input

[mGasiorek998]

I have a schema that looks like somewhat like this:

export const employeesSchema = pgTable('employees', {
  firstName: varchar('first_name', { length: 256 }),
  lastName: varchar('last_name', { length: 256 }),
  email: varchar('email', { length: 256 }).unique(),
  avatarId: uuid('avatar_id').unique(),
});

export const employeeRelations = relations(employeesSchema, ({ one, many }) => ({
  avatar: one(photosSchema, {
    fields: [employeesSchema.avatarId],
    references: [photosSchema.id],
  }),
  photos: many(photosSchema),
}));

Now i want to create a function for getting data from database with specific relations e.g

async function getEmployeeById(id: string, withInput: any) {
  return db.query.employeesSchema.findFirst({
    where: eq(employeesSchema.id, id),
    with: withInput
  });
}

Is there a way to get type of relations for given table (somewhat similar to how Prisma.EmployeeWhereInput would work)?

[Angelelz]

For those of us who don't know the feature you referred to from Prisma, can you elaborate what is it that you're looking for?

[mGasiorek998]

Sure,
In example I've mention above, employee has two relations: avatar and references

So if I want to have employee with just avatar I would write it like so:

db.query.employeesSchema.findFirst({
    where: eq(employeesSchema.id, id),
    with: {
      avatar: true
  });

Similar to photos I would write something like this:

db.query.employeesSchema.findFirst({
    where: eq(employeesSchema.id, id),
    with: {
      photos: true
  });

Now, I want to have a function where I can query the employee table and have an argument for that with object,

async function getEmployeeById(id: string, withInput: any) { // here I want to have a type that satisfies 'with' below instead of any
  return db.query.employeesSchema.findFirst({
    where: eq(employeesSchema.id, id),
    with: withInput
  });
}

I could not find a way to the get the type I need from neither employeeSchema nor employeeRelations.
Is it possible to retrieve this type from drizzle or I need to created it myself?

[Angelelz]

Unfortunately, drizzle doesn't have an easy to use type helper for this. You might want to submit an issue.
But there are workarounds. It you don't want to depend on Drizzle's internal types, you could use some typescript magic and come up with this:

type WithInput = NonNullable<Parameters<typeof db['query']['employeesSchema']['findFirst']>[0]>['with']

If you want to use drizzle's internal types, here is how you can get to the same result:

type WithInput = DBQueryConfig<'many', true, ExtractTablesWithRelations<typeof schema>, ExtractTablesWithRelations<typeof schema>['employeesSchema']>['with']

Take a look at this playground to see this in action.

[Makisuo]

Is it possible to also still have the drizzle type inference work properly in a wrapper function like this?
Can't seem to understand the drizzle generics there.

async function getEmployeeById(id: string, withInput: any) { // here I want to have a type that satisfies 'with' below instead of any
  return db.query.employeesSchema.findFirst({
    where: eq(employeesSchema.id, id),
    with: withInput
  });
}

[Angelelz]

Here is a working implementation.
You just need a little bit of generic type magic.

[jakeriksen]

Thanks for the solution @Angelelz, went ahead and made it a bit more generic with the help of #695, hope this helps:

import type * as schemas from '@backend/lib/schemas';
import type { db } from '@backend/lib/db';

type TablesWithRelations = ExtractTablesWithRelations<typeof schemas>

export type QueryRelationsConfig<TableName extends keyof TablesWithRelations> = NonNullable<Parameters<typeof db['query'][TableName]['findFirst' | "findMany"]>["0"]>;


// Testing it out
type OrgQueryConfig = QueryRelationsConfig<"organisation">;

const testOrgConfig: OrgQueryConfig = {
  with: {owner: true}
}

[wolfgang-hartl]

Unfortunately, drizzle doesn't have an easy to use type helper for this. You might want to submit an issue. But there are workarounds. It you don't want to depend on Drizzle's internal types, you could use some typescript magic and come up with this:

type WithInput = NonNullable<Parameters<typeof db['query']['employeesSchema']['findFirst']>[0]>['with']

If you want to use drizzle's internal types, here is how you can get to the same result:

type WithInput = DBQueryConfig<'many', true, ExtractTablesWithRelations<typeof schema>, ExtractTablesWithRelations<typeof schema>['employeesSchema']>['with']

Take a look at this playground to see this in action.

Hey, just found that snippet but i'm a bit struggling with it. I have the following implementation:

export type QueryConfig<TableName extends keyof TablesWithRelations> = DBQueryConfig<
  "one",
  true,
  ExtractTablesWithRelations<typeof schema>,
  ExtractTablesWithRelations<typeof schema>[TableName]
>;

And trying to use it that way, which gives me all the type hints inside of VSCode when using it, but the return type doesn't work anymore.

  async findFirst(query: QueryConfig<"users">) {
   // ........
    return await this.db.query.users.findFirst({
      ...query,
    });
  }

Does anyone have an idea what I could change to also have a generic return type?!

Thanks in Advance.

EDIT:

Including it as a generic type also doesn't do the job:

  async findFirst<TQuery extends QueryConfig<"users">>(query: TQuery) {
   // ........
    return await this.db.query.users.findFirst(query);
  }

[daveycodez]

This is SOOO messy!