Unable to infer nested return type from select() when using a DTO object in Drizzle ORM

[nisharg-codal]

I’m creating a generic CRUD wrapper in Drizzle ORM, and I’m facing an issue with type inference when selecting custom DTOs that include nested objects.

Here’s a simplified version of my code:

export const staticPageDetailsDto = {
  id: staticPagesTable.id,
  title: staticPagesTable.title,
  description: staticPagesTable.description,
  slug: staticPagesTable.slug,
  isPublic: staticPagesTable.isPublic,
  createdAt: staticPagesTable.createdAt,
  updatedAt: staticPagesTable.updatedAt,
  sql: sql<number>`1`,
  nested: {
    updatedAt: staticPagesTable.updatedAt,
  },
} as const;

const findById: StaticPageService['findById'] = async (id) => {
  const [data] = await db
    .select(staticPageDetailsDto)
    .from(staticPagesTable)
    .where(eq(staticPagesTable.id, id))
    .limit(1);

  if (!data) return new AppError(messages.ID_NOT_FOUND);

  return data;
};

What I Get from Drizzle

The return type from Drizzle is correctly inferred as:

readonly id: number;
readonly title: string;
readonly description: string;
readonly slug: string;
readonly isPublic: boolean;
readonly createdAt: Date;
readonly updatedAt: Date;
readonly sql: number;
readonly nested: {
  readonly updatedAt: Date;
};

What I Want

I want to extract this inferred type (including nested objects) inside a generic type utility, so that I can use it as an explicit return type in my service layer.

I explored Drizzle’s internal types and even tried to replicate the structure manually. I managed to infer everything except the nested object structure, Drizzle seems to flatten or lose nested type inference when accessed generically.

Question

Is there a built-in type utility (or any workaround) in Drizzle that allows me to infer the exact nested type returned from a select() statement based on my custom DTO object?

For example, something like:

type InferSelectType<T> = ??? // to get full nested return type
type MyDtoType = InferSelectType<typeof staticPageDetailsDto>;

I’d like to use this so that my function signature can be explicitly typed as:

const findById = async (id: number): Promise<MyDtoType> => { ... };

[nisharg-codal]

I forget to add my solution, here nested is not working

import type { SQL } from 'drizzle-orm';
import type { PgColumn } from 'drizzle-orm/pg-core';

type SelectedDTOType = Record<string, SQL | PgColumn>;

type SelectedFields<DTO extends SelectedDTOType> = {
  [Key in keyof DTO]: DTO[Key] extends { _: { brand: 'Column'; data: infer ColumnType } }
    ? ColumnType
    : DTO[Key] extends { _: { brand: 'SQL'; type: infer SQLType } }
      ? SQLType
      : DTO[Key] extends SelectedDTOType
        ? SelectedFields<DTO[Key]>
        : never;
};

[nisharg-codal]

Resolved

import type { SQL } from 'drizzle-orm';
import type { PgColumn } from 'drizzle-orm/pg-core';

type Simplify<T> = {[KeyType in keyof T]: T[KeyType]} & {};

type SelectedDTOType = Record<string, SQL | PgColumn>;

export type DtoFields<
  DTO extends SelectedDTOType,
  IsNestedNullable extends boolean = true,
> = Simplify<{
  [Key in keyof DTO]: DTO[Key] extends {
    _: { brand: 'Column'; data: infer ColumnType; notNull: infer RequiredColumn };
  }
    ? RequiredColumn extends true
      ? ColumnType
      : ColumnType | null
    : DTO[Key] extends { _: { brand: 'SQL'; type: infer SQLType } }
      ? SQLType
      : DTO[Key] extends SelectedDTOType
        ? IsNestedNullable extends true
          ? DtoFields<DTO[Key]> | null
          : DtoFields<DTO[Key]>
        : never;
}>;