Inferred row and insert types

Over the last several lessons you built the schema: the organizations and invoices tables, the line items, the tags, the junctions, the relations. Every column, type, and constraint now lives in one file. The chapter’s promise was that db/schema.ts becomes the root of a derivation tree: every typed shape your app needs downstream is generated from it, not retyped by hand. A hand-written type Invoice copied across five files goes stale the instant someone renames a column.

Say a Server Component needs an Invoice[] prop. That Invoice type comes from the schema in one line, not a hand-typed interface, and it cannot drift because it reads the table you already built. Two helpers do this: $inferSelect and $inferInsert.

$inferSelect: the row you read back

Every table you’ve built carries a property called $inferSelect. It resolves to the TypeScript type of one row, exactly as you’d get it back from a db.select() . Drizzle derives it from the column definitions you already wrote, so you never compute or maintain it.

$inferSelect lives at the type level, not the value level, so you reach it through typeof:

type Invoice = typeof invoices.$inferSelect;

There’s no runtime value to read here; it’s purely a type-side projection of the table. Read typeof invoices.$inferSelect as “the type of the rows in the invoices table.”

Anywhere you’d have hand-typed this:

type Invoice = {
  id: string;
  organizationId: string;
  amountDue: string;
  status: 'draft' | 'sent' | 'paid' | 'void';
  assignedToId: string | null;
  createdAt: Date;
  tags: string[];
};

You write this instead:

type Invoice = typeof invoices.$inferSelect;

Both produce the same type today. The difference: the second re-derives itself the moment you touch the schema, while the first goes stale the moment someone else does.

Every column maps to a TypeScript member by a fixed set of rules, driven by the per-column builders you chose for your data types. Here is the invoices table, one column at a time.

export const invoices = pgTable('invoices', {
  id: uuid().primaryKey().default(sql`uuidv7()`),
  organizationId: uuid().notNull(),
  amountDue: numeric({ precision: 12, scale: 2 }).notNull(),
  status: invoiceStatus().notNull().default('draft'),
  assignedToId: uuid(),
  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
  tags: text().array().notNull(),
});

type Invoice = typeof invoices.$inferSelect;
// {
//   id: string;
//   organizationId: string;
//   amountDue: string;
//   status: 'draft' | 'sent' | 'paid' | 'void';
//   assignedToId: string | null;
//   createdAt: Date;
//   tags: string[];
// }

The plain cases: uuid() becomes string, and a .notNull() column lands as the bare type, with no | null. Both id and organizationId are string on the read side.

export const invoices = pgTable('invoices', {
  id: uuid().primaryKey().default(sql`uuidv7()`),
  organizationId: uuid().notNull(),
  amountDue: numeric({ precision: 12, scale: 2 }).notNull(),
  status: invoiceStatus().notNull().default('draft'),
  assignedToId: uuid(),
  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
  tags: text().array().notNull(),
});

type Invoice = typeof invoices.$inferSelect;
// {
//   id: string;
//   organizationId: string;
//   amountDue: string;
//   status: 'draft' | 'sent' | 'paid' | 'void';
//   assignedToId: string | null;
//   createdAt: Date;
//   tags: string[];
// }

numeric({ precision: 12, scale: 2 }) infers as string, not number. Money carries more precision than a JS number can hold without rounding, so it arrives as a string and a decimal library handles the math. Read amountDue: string as the precision guarantee.

export const invoices = pgTable('invoices', {
  id: uuid().primaryKey().default(sql`uuidv7()`),
  organizationId: uuid().notNull(),
  amountDue: numeric({ precision: 12, scale: 2 }).notNull(),
  status: invoiceStatus().notNull().default('draft'),
  assignedToId: uuid(),
  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
  tags: text().array().notNull(),
});

type Invoice = typeof invoices.$inferSelect;
// {
//   id: string;
//   organizationId: string;
//   amountDue: string;
//   status: 'draft' | 'sent' | 'paid' | 'void';
//   assignedToId: string | null;
//   createdAt: Date;
//   tags: string[];
// }

A pgEnum column lands as the string-literal union of its members, so status can only ever be one of the four.

export const invoices = pgTable('invoices', {
  id: uuid().primaryKey().default(sql`uuidv7()`),
  organizationId: uuid().notNull(),
  amountDue: numeric({ precision: 12, scale: 2 }).notNull(),
  status: invoiceStatus().notNull().default('draft'),
  assignedToId: uuid(),
  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
  tags: text().array().notNull(),
});

type Invoice = typeof invoices.$inferSelect;
// {
//   id: string;
//   organizationId: string;
//   amountDue: string;
//   status: 'draft' | 'sent' | 'paid' | 'void';
//   assignedToId: string | null;
//   createdAt: Date;
//   tags: string[];
// }

assignedToId is uuid() with no .notNull(), so it’s nullable in the database, and that nullability shows up here as string | null. Every reader now has to handle the null.

export const invoices = pgTable('invoices', {
  id: uuid().primaryKey().default(sql`uuidv7()`),
  organizationId: uuid().notNull(),
  amountDue: numeric({ precision: 12, scale: 2 }).notNull(),
  status: invoiceStatus().notNull().default('draft'),
  assignedToId: uuid(),
  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
  tags: text().array().notNull(),
});

type Invoice = typeof invoices.$inferSelect;
// {
//   id: string;
//   organizationId: string;
//   amountDue: string;
//   status: 'draft' | 'sent' | 'paid' | 'void';
//   assignedToId: string | null;
//   createdAt: Date;
//   tags: string[];
// }

.array() wraps the element type, so text().array() becomes string[] for the tags column.

← Previous 1 / 1 Next →

The mapping is mechanical: text → string, integer → number, boolean → boolean, uuid → string, timestamp({ withTimezone: true }) → Date, a pgEnum to its literal union, .array() to T[]. A .notNull() column gives the bare type; a nullable column gives T | null. Two columns are worth pausing on: numeric → string, a precision choice rather than a defect, and jsonb, which the lesson returns to at the end.

type Amount = Invoice['amountDue']; // string

Now read one for yourself. The table is fixed; your job is to read the type the compiler infers. The ^? query surfaces the resolved type, and the @ts-expect-error line proves amountDue is a string.

The invoices table is given, so you don't need to touch it. Read the inferred Invoice type where the ^? points: it should resolve to the full row, with amountDue as a string. The @ts-expect-error on the last line proves that string is not a number; leave it in place, and the checker stays quiet only while the assignment below it genuinely fails.

• Type query at line 13 must resolve to a type containing amountDue: string

Booting type-checker… Reset

$inferInsert: only what you must supply

$inferSelect was the easy half: one row, exactly what’s stored. Its sibling has a sharper edge.

$inferInsert is the type that db.insert(invoices).values(...) accepts, the shape of a row you’re creating. You reach it through typeof, the same way:

type NewInvoice = typeof invoices.$inferInsert;

NewInvoice is not the same as Invoice. It’s narrower. A row you read back has id, createdAt, and status populated, but you don’t supply those on insert, because the database fills them in. The insert type won’t make you provide a value the database is going to generate.

The asymmetry between read and write is just the schema’s own facts projected into TypeScript. A column with a default, a column the database computes, and a column the app must provide are three different obligations, and $inferInsert encodes all three. Three rules govern how a column on $inferSelect becomes, or doesn’t become, a member on $inferInsert, and they mirror the column modifiers you already know.

Rule 1, a column with a default becomes optional. Any column with .default(...), .defaultNow(), or .$defaultFn(...) is optional on insert: pass a value or skip it, and the database or Drizzle fills it. The clearest case is the primary key. id: uuid().primaryKey().default(sql\uuidv7()`)has a default, so it's optional on$inferInsert, and you let the database mint a fresh UUIDv7 rather than passing one. The same holds for createdAt (defaultNow()) and status (.default(‘draft’)). A default comes two ways, a SQL DEFAULTclause the database applies or a$defaultFn` Drizzle runs in JS before the insert; the mechanism differs but the column is optional either way.

Rule 2, a generated column is omitted entirely. A column declared .generatedAlwaysAs(...) or generatedAlwaysAsIdentity() is absent from $inferInsert, not optional but gone. The database computes it from other columns, so passing your own is a type error. The emailLowercased column on users is .generatedAlwaysAs(...) computed from email, so it never appears on the insert type. Optional is not omitted: a defaulted column is there and you may skip it; a generated column isn’t there at all.

Rule 3, a .notNull() column with no default is required. That’s everything left over: NOT NULL with no fallback value means the app must provide it. On invoices, that’s organizationId and amountDue, and every insert has to carry both. Everything else falls out of Rules 1 and 2.

One nuance is worth slowing down for. A defaulted column is optional, so undefined is fine and you may omit it. It does not become nullable: passing null is legal only if the column is itself nullable. So createdAt?: Date and assignedToId: string | null mean opposite things. ?: T says “you don’t have to provide this,” while : T | null says “you may provide nothing on purpose.”

Now put the two shapes side by side. The example switches to the users table, which exercises all three rules at once: defaulted columns (id, createdAt), a required one (email), and the generated emailLowercased from earlier in the chapter.

$inferSelect

type User = typeof users.$inferSelect;
// {
//   id: string;
//   email: string;
//   emailLowercased: string;
//   createdAt: Date;
// }

Everything stored, fully known. id and createdAt are required: read a row back and they’re populated. emailLowercased is here too, because the database computed it and you read it.

$inferInsert

type NewUser = typeof users.$inferInsert;
// {
//   id?: string;
//   email: string;
//   createdAt?: Date;
// }

Only what the app must supply. id and createdAt carry defaults, so they’re optional (?:), by Rule 1. emailLowercased is generated, so it’s gone, with no line to highlight, by Rule 2. email is NOT NULL with no default, so it’s required, by Rule 3.

Three columns changed between read and write: id and createdAt went from required to optional because they have defaults, and emailLowercased vanished because it’s generated. The one that didn’t change, email, is the column the app must supply.

The starter gives you the table and a NewInvoice type; complete the draft object so tsc passes. Two things are wrong: a required field is missing, and a field you’re not allowed to supply is present. Fix both by reading the insert type.

Complete the draft object so it satisfies NewInvoice. The object is missing a required field and is wrongly supplying one the database owns. Read the error, add the required field, and remove the id line: id carries a default, so you let the database mint it rather than passing one by hand.

• Fix all errors

Booting type-checker… Reset

Why read and write are two shapes

The rule in one line: read returns everything stored; write accepts only the subset the app owns. The database fills the rest, the values it defaults and the columns it computes. $inferSelect is the full row; $inferInsert is narrower because inserting is only your half of the work.

A hand-written interface can’t express this. To say “required when you read it, optional when you write it,” you’d need two types, both maintained by hand, both drifting apart. The schema says it once. One pgTable declaration knows both shapes, because the facts that separate them, which columns have defaults, which are generated, which are NOT NULL, are facts the schema already records.

READ typeof users.$inferSelect

id

email

emailLowercased

createdAt

WRITE typeof users.$inferInsert

id optional

email

emailLowercased omitted

createdAt optional

required NOT NULL, no default optional has a default omitted generated

The write shape is a projection of the read shape: defaulted columns become optional, generated columns drop out, and only the NOT NULL-without-a-default columns stay required.

The same split returns one layer down: drizzle-zod, the library that generates runtime validators from the schema, ships createSelectSchema and createInsertSchema for exactly this reason.

Place the types next to the table, name them well

Put the type exports directly under the table they derive from, in db/schema.ts:

export const invoices = pgTable('invoices', {
  id: uuid().primaryKey().default(sql`uuidv7()`),
  organizationId: uuid().notNull(),
  amountDue: numeric({ precision: 12, scale: 2 }).notNull(),
  status: invoiceStatus().notNull().default('draft'),
  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
});

export type Invoice = typeof invoices.$inferSelect;
export type NewInvoice = typeof invoices.$inferInsert;

The type and its source sit three lines apart, so nobody hunts for “where’s the Invoice type.” These two lines re-export a derived shape rather than hand-copy one: they restate zero field names. This is the one place where writing a type for a row is correct.

The naming convention, which the project chapters reuse verbatim:

• The select type is the entity noun: Invoice, Organization, Membership.
• The insert type is the noun with a New prefix: NewInvoice, NewOrganization, NewMembership.

Downstream files pull these in as type-only imports, import type { Invoice } from '@/db/schema'.

Note

You’ll see an older spelling in legacy code: InferSelectModel<typeof invoices> and InferInsertModel<typeof invoices>, imported from drizzle-orm. These generic helpers produce the exact same types as the $inferSelect / $inferInsert property form this course uses throughout. Read InferSelectModel as the same thing spelled the long way.

Compose new shapes without restating fields

So far you’ve replaced hand-typed rows one-for-one. But most of the time you don’t want the whole row: a list view wants three columns plus the org’s name, an update form wants a partial. Rather than hand-type those narrower shapes, build them from the inferred types so they inherit the same can’t-drift guarantee.

The rule is that every derived shape roots in $inferSelect or $inferInsert. You never restate a field name; you compose with the utility types you already know. Here’s the canonical example, a summary type for a dashboard list:

type InvoiceSummary = Pick<Invoice, 'id' | 'status' | 'amountDue'> & {
  organizationName: Organization['name'];
};

// The 80% update shape — a partial of the insert type:
type InvoiceUpdate = Partial<NewInvoice>;

// The precise update shape — id required (which row?), the rest optional:
type PreciseUpdate = { id: Invoice['id'] } & Partial<Omit<NewInvoice, 'id'>>;

Pick pulls those three members off the inferred Invoice, both their names and types, straight from the source. Rename amountDue in the schema and this line breaks at compile time. No field types restated.

type InvoiceSummary = Pick<Invoice, 'id' | 'status' | 'amountDue'> & {
  organizationName: Organization['name'];
};

// The 80% update shape — a partial of the insert type:
type InvoiceUpdate = Partial<NewInvoice>;

// The precise update shape — id required (which row?), the rest optional:
type PreciseUpdate = { id: Invoice['id'] } & Partial<Omit<NewInvoice, 'id'>>;

Indexed access, Type['field'], reads one field’s type out of the source. organizationName gets whatever Organization.name is, without you writing string; the join column borrows the type of the column it joins to.

type InvoiceSummary = Pick<Invoice, 'id' | 'status' | 'amountDue'> & {
  organizationName: Organization['name'];
};

// The 80% update shape — a partial of the insert type:
type InvoiceUpdate = Partial<NewInvoice>;

// The precise update shape — id required (which row?), the rest optional:
type PreciseUpdate = { id: Invoice['id'] } & Partial<Omit<NewInvoice, 'id'>>;

Partial<NewInvoice> makes every insert field optional, the 80% answer for an update payload where the caller sends only the columns that changed.

type InvoiceSummary = Pick<Invoice, 'id' | 'status' | 'amountDue'> & {
  organizationName: Organization['name'];
};

// The 80% update shape — a partial of the insert type:
type InvoiceUpdate = Partial<NewInvoice>;

// The precise update shape — id required (which row?), the rest optional:
type PreciseUpdate = { id: Invoice['id'] } & Partial<Omit<NewInvoice, 'id'>>;

The precise shape, for when the type must force you to say which row. Indexed access pins id as required, Omit drops id from the insert type, and Partial makes the remaining columns optional. Use it when the looseness of Partial<NewInvoice> actually causes a problem.

← Previous 1 / 1 Next →

Nothing in that block is a hand-written type, no string, no 'draft' | 'sent' | …. Pick, Omit, Partial, the & intersection, and Type['field'] indexed access are the five composition tools you met in the TypeScript lessons, and here every shape they build is anchored to the schema. Rename a column and InvoiceSummary, InvoiceUpdate, and PreciseUpdate all break at the type checker, which is a bug you never shipped.

Partial<NewInvoice> is the update shape you’ll reach for most. The id-pinned variant is more correct, since it won’t let you forget which row, but it costs more machinery; name the trade-off and pick the simple one until the precision pays off.

Write InvoiceSummary from scratch so it resolves to the expected members, then prove the anchor holds: the @ts-expect-error line reaches for a field your summary leaves out (organizationId), and that access should fail.

Write InvoiceSummary so it has id, status, amountDue (picked from Invoice) plus an organizationName (the type of Organization.name). Use Pick and indexed access, and restate no field types. The @ts-expect-error line confirms your summary really is anchored: reaching for a field the summary doesn't include (organizationId) should fail.

• Type query at line 25 must resolve to a type containing organizationName: string

Booting type-checker… Reset

Thread one type from schema to prop

Watch a single name thread every layer. A Server Component reads invoices from the database and hands them to a Client Component to render. (The query itself is the next chapter’s job; here, just picture its result.) Follow the Invoice type through it:

db/schema.ts

export type Invoice = typeof invoices.$inferSelect;

// db/queries/invoices.ts — the next chapter owns the query body
export const listInvoices = async (): Promise<Invoice[]> => {
  // ...
};

// app/invoices/invoice-list.tsx
type Props = { invoices: Invoice[] };

export const InvoiceList = ({ invoices }: Props) => {
  // render the rows
};

The same Invoice appears four times: the schema export, the query’s return annotation, the component’s Props, and the destructured parameter. It’s one declaration the whole way down; the schema defines it and everything else borrows it. Run the counterfactual from the first lesson of this chapter. If InvoiceList hand-typed its own prop interface, a column rename in the schema would update the query but leave that prop quietly stale, no longer matching the data flowing into it, and the type checker would say nothing, because the two were never connected. Thread one type through instead, and the rename breaks every layer at once, which is how you find out before production does.

The chain continues past the type: once you reach forms and Server Actions, the same invoices table also feeds the runtime validator that parses the action’s input, so every layer aligns because every layer reads the same root.

When inference is too wide, and the relations gap

Inference is excellent, but it has four known edges. None are defects; each is a behavior to expect.

jsonb without $type infers as unknown. A bare jsonb() column tells the compiler nothing about its contents, so $inferSelect gives you unknown: correct, but useless to work with. The fix isn’t a cast at the read site; it’s upstream, on the column. Declare the shape with $type<...>().

Without $type

export const webhookDeliveries = pgTable('webhook_deliveries', {
  payload: jsonb().notNull(),
});

type Delivery = typeof webhookDeliveries.$inferSelect;
// payload: unknown   ← nothing to work with

Inference is only as good as what you told the schema. A bare jsonb() carries no shape, so payload is unknown, and every read has to narrow it by hand.

With $type<WebhookEvent>()

export const webhookDeliveries = pgTable('webhook_deliveries', {
  payload: jsonb().$type<WebhookEvent>().notNull(),
});

type Delivery = typeof webhookDeliveries.$inferSelect;
// payload: WebhookEvent   ← the real shape

$type<T>() is a compile-time promise about the stored shape, so $inferSelect now resolves payload to WebhookEvent. It informs the type only; Postgres still stores bytes and Zod still validates the data on the way in.

So $type tells the compiler what to expect; confirming the stored bytes actually match WebhookEvent is Zod’s job at the boundary. The same reflex covers the next two edges: when inference is too wide, fix the schema, not the consumer.

numeric stays string. You met this already: amountDue: string, not number. The string preserves arbitrary precision, so nothing here is broken. Read numeric → string as the money guarantee, every time.

An enum-like text column gives you string, not a union. Declare a fixed set of values as plain text() and $inferSelect infers string, losing the closed set: the compiler will let 'pending' sit in a column that only ever holds 'draft' | 'sent' | 'paid' | 'void'. Fix it upstream by making it a pgEnum, and the union comes back for free, every consumer inheriting the narrow type.

$inferSelect is flat: it does not include relations. An inferred Invoice has organizationId: string, the raw foreign-key column, and nothing more: no nested organization object, no lineItems array, no tags. The relations you declared in the previous lesson get their own inferred types from the relational query API, produced only when you ask for them (db.query.invoices.findMany({ with: { tags: true } }), next chapter’s territory). The flat stored row and the nested query shape are separate by design.

Caution

$inferSelect never carries relations. typeof invoices.$inferSelect gives you the flat row: foreign keys as bare string columns, no nested objects. If you need an invoice with its line items and tags as one typed object, that’s a different shape entirely, inferred from the relational query (db.query.…({ with })) in the next chapter. Don’t expect with: { tags: true } to show up in $inferSelect. It can’t, because the flat row knows nothing about the join.

A related case: a query that selects only some columns infers a result narrower than $inferSelect, so let it infer rather than hand-type that shape. Custom-select result types are the next chapter’s topic.

Here’s a quick check on the relations gap.

You write type Invoice = typeof invoices.$inferSelect, and invoices has a relation to organizations declared with defineRelations. What does Invoice give you for the linked organization?

Nothing nested — only organizationId: string, the raw foreign-key column. The organization itself is a separate type you reach for at the query layer.

An organization: Organization, always populated, since declaring the relation wires it into the row.

An organization: Organization | null — present when the row points at one, null otherwise.

A lazy proxy that hits the database for organization the first time you read the property.

External resources

Drizzle ORM — Type inference (Goodies)

orm.drizzle.team

The canonical reference for $inferSelect / $inferInsert and the InferSelectModel / InferInsertModel aliases.

Drizzle ORM — drizzle-zod

orm.drizzle.team

The schema-to-Zod pipeline. createSelectSchema / createInsertSchema mirror this lesson's read/write split at the runtime validation layer.

TypeScript Handbook — Utility Types

typescriptlang.org

The official reference for Pick, Omit, and Partial, the composition tools that turn an inferred row into narrower derived shapes.