Infer select and insert types

The schema already knows what a note looks like. If you also write interface Note { id: number; title: string; ... } by hand, you now have two descriptions of the same thing, and one of them will go stale.

Drizzle can hand you the types instead:

import { notes } from './db/schema'

export type Note = typeof notes.$inferSelect
export type NewNote = typeof notes.$inferInsert

$inferSelect is the shape of a row coming out of the database. Every column, with archivedAt as string | null because the column is nullable.

$inferInsert is the shape you’re allowed to pass to .values(). id is optional, because it’s generated. body and createdAt are optional, because they have defaults. authorId and title are required. Change the schema and both types follow, with no extra work.

Use them at the database boundary: as return types of your query functions, as the parameter type of an insert helper.

Three shapes, not one

Here is where I see projects go wrong. They use Note everywhere: for the database row, for the HTTP request, for the JSON response. It feels efficient. It’s a leak waiting to happen.

The request that creates a note should not include id, authorId, or createdAt. The server owns those. So define the input separately:

type CreateNoteInput = {
  title: string
  body?: string
}

Validate the incoming JSON against this at runtime, with whatever validation library you like. Then map it into a NewNote, adding the author from the session:

const newNote: NewNote = {
  ...input,
  authorId: session.userId,
}

The response should not necessarily include authorId either, and might add fields that don’t exist in the database, like a URL:

type PublicNote = {
  id: number
  title: string
  body: string
  url: string
}

Map a Note into a PublicNote explicitly, field by field. It’s a few lines, and it means adding a private column to the table tomorrow doesn’t push it into the API today.

Types don’t run

One more time, because it matters: NewNote is checked at compile time. JSON.parse(body) as NewNote makes TypeScript happy and checks nothing. The browser can send any shape. The runtime validation step is what stands between the network and your .values() call.

Try this on the project. Add a column like internalFlags: text('internal_flags') to notes. Note picks it up automatically. PublicNote doesn’t, because you wrote it by hand, and your mapping function keeps compiling without exposing it. That’s the split working as intended.