Typing partial selects and joins in Drizzle without writing the types by hand

Drizzle's types are inferred from the query, which is the whole point of it. The friction shows up the moment you try to name one of those types (for a function signature, a component prop, a shared helper) and reach for the wrong tool.

The wrong tool

export type Post = typeof posts.$inferSelect;

async function listPostSummaries(): Promise<Post[]> {
  return db.select({ id: posts.id, title: posts.title }).from(posts);
  //     ^ Type '{ id: string; title: string; }[]' is not assignable to 'Post[]'
}

$inferSelect is the full row: every column, including the jsonb blob and the timestamps you did not select. It is right for select() with no argument and for insert/update payload shapes, and wrong for anything narrower.

Hand-writing the narrow interface is worse:

// don't
interface PostSummary {
  id: string;
  title: string;
}

It compiles today and lies tomorrow. Rename title to headline in the schema and the query updates, the interface does not, and nothing tells you.

Tool 1: let the return type be inferred

Ninety percent of the time the fix is to delete the annotation.

export async function listPostSummaries() {
  return db
    .select({ id: posts.id, title: posts.title, authorName: users.name })
    .from(posts)
    .innerJoin(users, eq(users.id, posts.authorId))
    .orderBy(desc(posts.publishedAt))
    .limit(50);
}

Callers get { id: string; title: string; authorName: string }[] with no maintenance. When you do need the name (for a prop type) derive it:

export type PostSummary = Awaited<ReturnType<typeof listPostSummaries>>[number];

That type is defined by the query. Change the select list and every consumer updates or fails to compile, which is exactly the behaviour you want.

Tool 2: name the selection, not the result

When several queries share a projection, hoist the select object:

const postSummaryColumns = {
  id: posts.id,
  title: posts.title,
  publishedAt: posts.publishedAt,
} as const;

export async function recentPosts() {
  return db.select(postSummaryColumns).from(posts).limit(10);
}

export async function postsByAuthor(authorId: string) {
  return db.select(postSummaryColumns).from(posts).where(eq(posts.authorId, authorId));
}

One projection, one place to change it, and both functions keep precise types.

Tool 3: joins produce nested objects unless you say otherwise

const rows = await db.select().from(posts).innerJoin(users, eq(users.id, posts.authorId));
// rows: { posts: Post; users: User }[]

A bare select() over a join gives you one key per table. That is often not what you want, and it fetches every column of both tables. Pass an explicit projection to flatten it:

const rows = await db
  .select({
    id: posts.id,
    title: posts.title,
    author: { id: users.id, name: users.name },   // nesting is allowed, one level
  })
  .from(posts)
  .innerJoin(users, eq(users.id, posts.authorId));

Watch the nullability: a leftJoin means the joined side can be null, and Drizzle types it that way. If your code does row.author.name after a leftJoin, TypeScript is right and you are wrong.

Tool 4: computed columns keep their types

const rows = await db
  .select({
    authorId: posts.authorId,
    total: count(posts.id),
    // sql<T> is you asserting the runtime type: be honest about it.
    lastTitle: sql<string>`max(${posts.title})`,
  })
  .from(posts)
  .groupBy(posts.authorId);

sql<string> is an assertion, not an inference. Postgres returns bigint from count() as a string in some drivers, and a numeric column arrives as a string too, so sql<number> on an aggregate is a common source of "why is my number a string at runtime". Use .mapWith(Number) when you want the conversion to actually happen:

total: count(posts.id).mapWith(Number),

Tool 5: the relational API has its own inference

db.query returns nested objects, and the type follows columns and with exactly:

const order = await db.query.orders.findFirst({
  columns: { id: true, total: true },
  with: { items: { columns: { id: true, quantity: true } } },
});
// { id: string; total: number; items: { id: string; quantity: number }[] } | undefined

Note the | undefined on findFirst. Handle it at the boundary rather than asserting it away with !: a missing row is a 404, not a crash.

To name that type, Drizzle exposes helpers so you do not have to reconstruct it:

import type { InferSelectModel } from "drizzle-orm";

type OrderRow = InferSelectModel<typeof orders>;                        // full row
type OrderWithItems = Awaited<ReturnType<typeof loadOrder>>;            // the real shape

Insert types are their own thing

type NewPost = typeof posts.$inferInsert;

$inferInsert respects defaults and generated columns: a column with .defaultRandom() or .defaultNow() is optional, a .notNull() column without a default is required. That is why insert payload types must come from $inferInsert and never from $inferSelect: the latter makes id and createdAt mandatory on a value you have not created yet.

The habits

1. Do not annotate query function return types; derive names with Awaited<ReturnType<...>>[number] when you need one.
2. Never hand-write an interface that mirrors a query. It will drift.
3. $inferSelect for whole rows, $inferInsert for payloads, inference for everything in between.
4. Give every join an explicit projection, and respect the nullability a leftJoin introduces.
5. Treat sql<T> as a promise you are making to the compiler, and use .mapWith() when the runtime needs to keep it.