02 - Schema & Tables

Defining Tables

In Drizzle, you define tables with pgTable. Each table is a TypeScript object that maps directly to a PostgreSQL table.

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

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  active: boolean("active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
});

The first argument to pgTable is the actual table name in PostgreSQL. The object keys (id, name, email) are what you use in TypeScript. The string inside each column function ("id", "name", "email") is the column name in the database.

// ❌ Table name doesn't match convention
export const Users = pgTable("Users", { ... });

// ✅ Use snake_case for PostgreSQL table names
export const users = pgTable("users", { ... });

Column Types

Here are the types you'll use most often:

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

export const profiles = pgTable("profiles", {
  // Text
  name: text("name"),              // unlimited text
  bio: text("bio"),

  // Numbers
  age: integer("age"),             // whole numbers
  loginCount: integer("login_count").default(0),

  // Boolean
  verified: boolean("verified").default(false),

  // Timestamps
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),

  // UUID
  token: uuid("token").defaultRandom(),
});

Primary Keys

Two common patterns for primary keys:

// Option 1: Auto-incrementing integer (serial)
export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
});
// id: 1, 2, 3, 4, ...

// Option 2: UUID (good for distributed systems)
export const sessions = pgTable("sessions", {
  id: uuid("id").defaultRandom().primaryKey(),
  userId: integer("user_id").notNull(),
});
// id: "a1b2c3d4-e5f6-..."

Use serial for simplicity. Use uuid when you need IDs that are globally unique or don't want sequential, guessable IDs.

PostgreSQL also supports integer("id").primaryKey().generatedAlwaysAsIdentity() as a modern alternative to serial. Both auto-increment. This course uses serial throughout.

Column Modifiers

Modifiers chain onto column definitions. The most common are .notNull(), .unique(), .default(), and .primaryKey() (covered in detail in the next lesson):

export const products = pgTable("products", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),              // cannot be NULL
  sku: text("sku").notNull().unique(),        // must be unique across all rows
  price: integer("price").notNull(),
  description: text("description"),           // nullable by default
  inStock: boolean("in_stock").default(true), // default value
});

// ❌ Forgetting notNull on required fields
export const orders = pgTable("orders", {
  userId: integer("user_id"),  // can accidentally be NULL
});

// ✅ Mark required fields explicitly
export const orders = pgTable("orders", {
  userId: integer("user_id").notNull(),
});

A Real Example: Users and Posts

Here's a complete schema file with two related tables:

// src/db/schema.ts
import {
  pgTable,
  serial,
  text,
  integer,
  boolean,
  timestamp,
  uuid,
} from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  passwordHash: text("password_hash").notNull(),
  role: text("role").default("user"),
  active: boolean("active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
});

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  content: text("content"),
  published: boolean("published").default(false),
  authorId: integer("author_id")
    .notNull()
    .references(() => users.id),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

The .references(() => users.id) on authorId creates a foreign key. We'll cover relations in depth in a later lesson. For now, just know it links posts.author_id to users.id in the database.

Pushing Your Schema

Once your schema is defined, push it to the database:

npx drizzle-kit push

This reads your drizzle.config.ts, compares your schema to the database, and applies the changes. No migration files needed during development.

# Output:
# [✓] Changes applied to database
# Tables: users, posts

The Schema File Pattern

Keep all your tables in one schema file to start:

src/
  db/
    index.ts      # connection
    schema.ts     # all tables here

As your app grows, split into multiple files:

src/
  db/
    index.ts
    schema/
      users.ts
      posts.ts
      index.ts    # re-exports everything

// src/db/schema/index.ts
export * from "./users";
export * from "./posts";

Update drizzle.config.ts to point to the folder:

export default defineConfig({
  schema: "./src/db/schema",
  // ...
});

Key Takeaways

Tables are defined with pgTable("table_name", { columns }) in TypeScript
Use snake_case for database table and column names
serial gives auto-incrementing integer IDs, uuid gives random unique IDs
Chain .notNull(), .unique(), .default() to configure columns
.references(() => table.column) creates a foreign key
npx drizzle-kit push syncs your schema to the database
Start with one schema file, split later when it gets large