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

> $inferSelect describes the whole row, not the three columns you selected. Use the query builder's inferred types, Awaited<ReturnType<...>>, and helper types instead of hand-maintained interfaces.

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

```ts
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:

```ts
// 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.

```ts
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:

```ts
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:

```ts
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

```ts
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:

```ts
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

```ts
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:

```ts
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:

```ts
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:

```ts
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

```ts
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.

---

Agentic Boilerplate: A Next.js repo your agent already knows. $99 once. Lifetime access and updates.

- Site map for agents: https://agenticboilerplate.com/llms.txt
- Public API: https://agenticboilerplate.com/openapi.json
- Contact: agenticstudio@gmail.com