Implement infering table model with relations

[dankochetov]

Prisma API:

import { Prisma } from '@prisma/client'

// 1: Define a type that includes the relation to `Post`
const userWithPosts = Prisma.validator<Prisma.UserDefaultArgs>()({
  include: { posts: true },
})

// 2: Define a type that only contains a subset of the scalar fields
const userPersonalData = Prisma.validator<Prisma.UserDefaultArgs>()({
  select: { email: true, name: true },
})

// 3: This type will include a user and all their posts
type UserWithPosts = Prisma.UserGetPayload<typeof userWithPosts>

[tanjunior]

+1

[rollemira]

Please? 🙏

[VladBrok]

I found a workaround for this. For example, I have two tables: employee with fields "id" and "role_id" and role with field "id". I want to get a type of such query:

(
    await db
      .select()
      .from(employee)
      .where(eq(employee.id, id))
      .leftJoin(role, eq(employee.roleId, role.id))
)[0]

(i.e. an employee with their role). The type would be:

export type EmployeeWithRole = {
   [employee._.name]: typeof employee.$inferSelect;
   [role._.name]: typeof role.$inferSelect | null;
};

[Hebilicious]

For anyone looking for a workaround : https://github.com/drizzle-team/drizzle-orm/discussions/1483

Thanks @Angelelz 🙏🏽

[BearToCode]

For queries using with I made these simple helper types:

import type { BuildQueryResult, DBQueryConfig, ExtractTablesWithRelations } from 'drizzle-orm';
import * as schema from './path/to/schema';

type Schema = typeof schema;
type TSchema = ExtractTablesWithRelations<Schema>;

export type IncludeRelation<TableName extends keyof TSchema> = DBQueryConfig<
  'one' | 'many',
  boolean,
  TSchema,
  TSchema[TableName]
>['with'];

export type InferResultType<
  TableName extends keyof TSchema,
  With extends IncludeRelation<TableName> | undefined = undefined
> = BuildQueryResult<
  TSchema,
  TSchema[TableName],
  {
    with: With;
  }
>;

You can now do:

type UserWithPosts = InferResultType<'user', { posts: true }>

[amjed-ali-k]

For queries using with I made these simple helper types:

import type { BuildQueryResult, DBQueryConfig, ExtractTablesWithRelations } from 'drizzle-orm';
import * as schema from './path/to/schema';

type Schema = typeof schema;
type TSchema = ExtractTablesWithRelations<Schema>;

export type IncludeRelation<TableName extends keyof TSchema> = DBQueryConfig<
  'one' | 'many',
  boolean,
  TSchema,
  TSchema[TableName]
>['with'];

export type InferResultType<
  TableName extends keyof TSchema,
  With extends IncludeRelation<TableName> | undefined = undefined
> = BuildQueryResult<
  TSchema,
  TSchema[TableName],
  {
    with: With;
  }
>;

You can now do:

type UserWithPosts = InferResultType<'user', { posts: true }>

Thanks a lot ❤️

[AyandaAde]

import type { BuildQueryResult, DBQueryConfig, ExtractTablesWithRelations } from 'drizzle-orm';
import * as schema from './path/to/schema';

type Schema = typeof schema;
type TSchema = ExtractTablesWithRelations<Schema>;

export type IncludeRelation<TableName extends keyof TSchema> = DBQueryConfig<
  'one' | 'many',
  boolean,
  TSchema,
  TSchema[TableName]
>['with'];

export type InferResultType<
  TableName extends keyof TSchema,
  With extends IncludeRelation<TableName> | undefined = undefined
> = BuildQueryResult<
  TSchema,
  TSchema[TableName],
  {
    with: With;
  }
>;

can you do this but with a nested with?

[Lukasz17git]

You can also use the type BearToCode provided in https://github.com/drizzle-team/drizzle-orm/issues/695#issuecomment-1891811082 to achieve that:

type UserWithPostsWithUser = InferResultType<'user', { posts: { with: user: true } }>

I ll also post an alternative using unions if nested relations or filtering fields/columns aren't needed.

Somewhere in your app:

import type { db } from '@/path-to-db';

type DB = typeof db
type DbSchemas = NonNullable<DB['_']['schema']>

export type SchemaRelations<
   TTableName extends keyof DbSchemas,
   TExcludeRelations extends keyof NonNullable<DbSchemas[TTableName]['relations']> = never,
> = Exclude<keyof NonNullable<DbSchemas[TTableName]['relations']>, TExcludeRelations>

export type SchemaWithRelations<
   TTableName extends keyof DbSchemas,
   TInclude extends SchemaRelations<TTableName> = never
> = BuildQueryResult<DbSchemas, DbSchemas[TTableName], { with: { [Key in TInclude]: true } }>

In your schema.ts file:

(-) // export type User = typeof USERS_TABLE.$inferSelect
(+) export type User<T extends SchemaRelations<'USERS_TABLE'> = never> = SchemaWithRelations<'USERS_TABLE', T>
// OR if you want to exclude some relations, for example 'accounts' and 'sessions' from the User
(+) export type User<T extends SchemaRelations<'USERS_TABLE', 'accounts' | 'sessions'> = never> = SchemaWithRelations<'USERS_TABLE', T>

Usage:

type NormalUser = User
type UserWithReviews = User<'reviews'>
type UserWithReviewsRoomsEmployees = User<'reviews' | 'rooms' | 'employees'>

[ax-at]

For queries using with I made these simple helper types:

import type { BuildQueryResult, DBQueryConfig, ExtractTablesWithRelations } from 'drizzle-orm';
import * as schema from './path/to/schema';

type Schema = typeof schema;
type TSchema = ExtractTablesWithRelations<Schema>;

export type IncludeRelation<TableName extends keyof TSchema> = DBQueryConfig<
  'one' | 'many',
  boolean,
  TSchema,
  TSchema[TableName]
>['with'];

export type InferResultType<
  TableName extends keyof TSchema,
  With extends IncludeRelation<TableName> | undefined = undefined
> = BuildQueryResult<
  TSchema,
  TSchema[TableName],
  {
    with: With;
  }
>;

You can now do:

type UserWithPosts = InferResultType<'user', { posts: true }>

I upgraded it to include both columns and relations. As shown below:

import type {
  BuildQueryResult,
  DBQueryConfig,
  ExtractTablesWithRelations,
} from "drizzle-orm";

import type { schema } from "../schema";

type Schema = typeof schema;
type TablesWithRelations = ExtractTablesWithRelations<Schema>;

export type IncludeRelation<TableName extends keyof TablesWithRelations> =
  DBQueryConfig<
    "one" | "many",
    boolean,
    TablesWithRelations,
    TablesWithRelations[TableName]
  >["with"];

export type IncludeColumns<TableName extends keyof TablesWithRelations> =
  DBQueryConfig<
    "one" | "many",
    boolean,
    TablesWithRelations,
    TablesWithRelations[TableName]
  >["columns"];

export type InferQueryModel<
  TableName extends keyof TablesWithRelations,
  Columns extends IncludeColumns<TableName> | undefined = undefined,
  With extends IncludeRelation<TableName> | undefined = undefined,
> = BuildQueryResult<
  TablesWithRelations,
  TablesWithRelations[TableName],
  {
    columns: Columns;
    with: With;
  }
>;

You can now do:

type UserNameAndEmailWithPosts = InferQueryModel<'user', { name: true, email: true }, { posts: true }>

[efstathiosntonas]

@akashdevcc can you please post your InferResultType? Thanks

[ax-at]

@akashdevcc can you please post your InferResultType? Thanks

I now noticed, I made a mistake, instead of InferQueryModel, I used InferResultType. I have corrected it now.

@efstathiosntonas So now, do you want me to post the result of this statement?

type UserNameAndEmailWithPosts = InferQueryModel<'user', { name: true, email: true }, { posts: true }>

[hlspablo]

For queries using with I made these simple helper types:

import type { BuildQueryResult, DBQueryConfig, ExtractTablesWithRelations } from 'drizzle-orm';
import * as schema from './path/to/schema';

type Schema = typeof schema;
type TSchema = ExtractTablesWithRelations<Schema>;

export type IncludeRelation<TableName extends keyof TSchema> = DBQueryConfig<
  'one' | 'many',
  boolean,
  TSchema,
  TSchema[TableName]
>['with'];

export type InferResultType<
  TableName extends keyof TSchema,
  With extends IncludeRelation<TableName> | undefined = undefined
> = BuildQueryResult<
  TSchema,
  TSchema[TableName],
  {
    with: With;
  }
>;

You can now do:

type UserWithPosts = InferResultType<'user', { posts: true }>

I upgraded it to include both columns and relations. As shown below:

import type {
  BuildQueryResult,
  DBQueryConfig,
  ExtractTablesWithRelations,
} from "drizzle-orm";

import type { schema } from "../schema";

type Schema = typeof schema;
type TablesWithRelations = ExtractTablesWithRelations<Schema>;

export type IncludeRelation<TableName extends keyof TablesWithRelations> =
  DBQueryConfig<
    "one" | "many",
    boolean,
    TablesWithRelations,
    TablesWithRelations[TableName]
  >["with"];

export type IncludeColumns<TableName extends keyof TablesWithRelations> =
  DBQueryConfig<
    "one" | "many",
    boolean,
    TablesWithRelations,
    TablesWithRelations[TableName]
  >["columns"];

export type InferQueryModel<
  TableName extends keyof TablesWithRelations,
  Columns extends IncludeColumns<TableName> | undefined = undefined,
  With extends IncludeRelation<TableName> | undefined = undefined,
> = BuildQueryResult<
  TablesWithRelations,
  TablesWithRelations[TableName],
  {
    columns: Columns;
    with: With;
  }
>;

You can now do:

type UserNameAndEmailWithPosts = InferQueryModel<'user', { name: true, email: true }, { posts: true }>

this works, thank you

[sillvva]

You can also add column support like this:

import * as schema from "$server/db/schema";
import type { BuildQueryResult, DBQueryConfig, ExtractTablesWithRelations } from "drizzle-orm";

type TSchema = ExtractTablesWithRelations<typeof schema>;
type QueryConfig<TableName extends keyof TSchema> = DBQueryConfig<"one" | "many", boolean, TSchema, TSchema[TableName]>;

export type InferQueryModel<
    TableName extends keyof TSchema,
    QBConfig extends QueryConfig<TableName> = {}
> = BuildQueryResult<TSchema, TSchema[TableName], QBConfig>;

type Result = InferQueryModel<
  "logs",
  {
    columns: { id: true },
    with: {
      character: {
        columns: { id: true }
      }
    }
  }
>;

// type Result = { id: string; character: { id: string; } }

[prichodko]

Thanks for the help. It's useful!

Has anyone found a way to disallow passing non-existent columns?

For example:

type T = InferQueryModel<
  'Test',
  {
    id: true
    createdAt: true
    nonExistentKey: true // <- not erroring
  },
  {}
>

I noticed that drizzle uses a KnownKeysOnly helper but didn't find a way to make it work.

[medv]

Thanks for the help. It's useful!

Has anyone found a way to disallow passing non-existent columns?

For example:

type T = InferQueryModel<
  'Test',
  {
    id: true
    createdAt: true
    nonExistentKey: true // <- not erroring
  },
  {}
>

I noticed that drizzle uses a KnownKeysOnly helper but didn't find a way to make it work.

https://github.com/sindresorhus/type-fest/blob/main/source/exact.d.ts

[prichodko]

Thanks for the help. It's useful! Has anyone found a way to disallow passing non-existent columns? For example:

type T = InferQueryModel<
  'Test',
  {
    id: true
    createdAt: true
    nonExistentKey: true // <- not erroring
  },
  {}
>

I noticed that drizzle uses a KnownKeysOnly helper but didn't find a way to make it work.

https://github.com/sindresorhus/type-fest/blob/main/source/exact.d.ts

Thanks 🙏

Here is the full snippet that ensures that keys are always valid:

import type * as schema from './schema'
import type {
  BuildQueryResult,
  DBQueryConfig,
  ExtractTablesWithRelations,
} from 'drizzle-orm'
import type { Exact } from 'type-fest'

type TSchema = ExtractTablesWithRelations<typeof schema>

type QueryConfig<TableName extends keyof TSchema> = DBQueryConfig<
  'one' | 'many',
  boolean,
  TSchema,
  TSchema[TableName]
>

export type InferQueryModel<
  TableName extends keyof TSchema,
  QBConfig extends Exact<QueryConfig<TableName>, QBConfig> = {} // <-- notice Exact here
> = BuildQueryResult<TSchema, TSchema[TableName], QBConfig>

---

I also recommend using the Pretty TypeScript Errors extension that helps decipher the errors. 😅

[alfredotoselli]

I had this need for column definition of a TanStack Table, I used drizzle-zod to infer the types.

import { createSelectSchema } from "drizzle-zod";
import { z } from "zod";

export const fooRelations = relations(fooTable, ({ one }) => ({
  bar: one(barTable, {
    fields: [fooTable.barId],
    references: [barTable.id],
  }),
}));

const selectBarSchema = createSelectSchema(barTable);
const selectFooSchema = createSelectSchema(fooTable);

const selectFooWithBarSchema = selectFooSchema.extend({
  user: selectFooSchema.nullable(),
});
export type selectFooWithBar = z.infer<typeof selectFooWithBarSchema >;

[ludersGabriel]

damn, was looking for something like that for an hour. Thanks @alfredotoselli !

[geryruslandi]

is there a way to make the relationship data to be optional ?

for example role and permissions

generated type would be { id: number; permissions?: {}[] }