TypeScript Academy · Lesson

Schema-to-Type Mapping

How table definitions become TypeScript types.

Schema-to-Type Mapping is a free TypeScript Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the TypeScript Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

From Table Definition to Row Type

A type-safe ORM lets you describe a database table once and derive its TypeScript row type automatically. You never hand-write the row shape; it is computed from the column definitions.

In this lesson we use a Drizzle-style pgTable as the running example.

Defining a Table

A table is an object mapping column names to column builders. Each builder encodes a SQL type plus modifiers like notNull or primaryKey.

import { pgTable, serial, text, integer, boolean } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  age: integer("age"),
  isAdmin: boolean("is_admin").notNull(),
});

Column Type Inference

Each column builder carries a phantom type describing the value it produces. serial and integer map to number, text to string, boolean to boolean.

The builder also tracks whether the column is nullable.

import { InferSelectModel } from "drizzle-orm";

// Row type inferred from the table above
type User = InferSelectModel<typeof users>;
// {
//   id: number;
//   name: string;
//   age: number | null;   // not notNull -> nullable
//   isAdmin: boolean;
// }

notNull Controls Nullability

The single most important driver of the row type is notNull(). A column WITHOUT it becomes T | null in the inferred type, because SQL allows NULL by default.

const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),  // string
  subtitle: text("subtitle"),      // string | null
});

type Post = InferSelectModel<typeof posts>;
// { id: number; title: string; subtitle: string | null }

Select vs Insert Models

The row type for reading (select) differs from the type for writing (insert). On insert, columns with defaults or auto-increment become optional.

import { InferInsertModel } from "drizzle-orm";

type NewUser = InferInsertModel<typeof users>;
// {
//   id?: number;        // serial has a default -> optional
//   name: string;
//   age?: number | null;
//   isAdmin: boolean;
// }

How the Inference Works

Under the hood each column builder is a generic class like PgColumn<{ data: number; notNull: true }>. A mapped type walks every key of the table and reads those flags.

// Simplified mental model of the inference
type InferRow<T> = {
  [K in keyof T]: T[K] extends { _: { data: infer D; notNull: infer N } }
    ? N extends true ? D : D | null
    : never;
};

Enum and Custom Types

String-union columns map to literal unions, not just string. This means an invalid status value is a compile error.

import { pgEnum } from "drizzle-orm/pg-core";

export const roleEnum = pgEnum("role", ["user", "admin", "owner"]);

export const members = pgTable("members", {
  id: serial("id").primaryKey(),
  role: roleEnum("role").notNull(), // "user" | "admin" | "owner"
});

Default Values

.default() makes a column optional on insert but keeps it present on select. The type system encodes "has default" so it can flip optionality only where appropriate.

const events = pgTable("events", {
  id: serial("id").primaryKey(),
  createdAt: text("created_at").notNull().default("now()"),
});
// Select: createdAt is string
// Insert: createdAt is optional (default fills it)

Why This Matters

Because the row type is derived, a schema change automatically updates every query result type. Rename a column and the compiler flags every stale reference. There is no second source of truth to drift.

A Tiny End-to-End Example

Putting it together: define once, infer the type, and use it as the contract for a repository function.

type User = InferSelectModel<typeof users>;

async function findUser(id: number): Promise<User | undefined> {
  // db.query returns rows already typed as User
  const rows = await db.select().from(users).where(eq(users.id, id));
  return rows[0];
}

Pitfall: Forgetting notNull

If you forget notNull() on a column that is actually required, the inferred type becomes T | null and forces unnecessary null checks throughout your code. Keep the schema honest.

Quick Check

Test your understanding of schema-to-type mapping.

Recap

You learned that a table definition is the single source of truth: column builders carry phantom types, mapped types walk the table to build the row type, notNull() controls nullability, and select vs insert models differ on optionality. Change the schema and every result type follows automatically.

Frequently asked questions

Is the “Schema-to-Type Mapping” lesson free?

Yes — the full text of “Schema-to-Type Mapping” is free to read here on the web, and the TypeScript Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the TypeScript Academy course, upgrade to CoddyKit PRO.

What will I learn in “Schema-to-Type Mapping”?

How table definitions become TypeScript types. You practise TypeScript Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start TypeScript Academy?

No prior experience is required. TypeScript Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Schema-to-Type Mapping” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this TypeScript Academy lesson?

Yes. Every TypeScript Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to TypeScript Academy