03 - Columns & Types

Common Column Types

Drizzle maps TypeScript functions to PostgreSQL column types. Here's every type you'll use regularly:

import {
  pgTable,
  serial,
  text,
  varchar,
  integer,
  bigint,
  boolean,
  timestamp,
  date,
  json,
  jsonb,
  uuid,
  real,
  doublePrecision,
  numeric,
} from "drizzle-orm/pg-core";

export const examples = pgTable("examples", {
  id: serial("id").primaryKey(),

  // Strings
  name: text("name"),                    // unlimited length
  code: varchar("code", { length: 10 }), // max 10 characters

  // Numbers
  age: integer("age"),                   // -2,147,483,648 to 2,147,483,647 (2 Bytes)
  viewCount: bigint("view_count", { mode: "number" }), // larger integers up to ~9.2 quintillion (9.2 × 10¹⁸)
  price: real("price"),                  // 4-byte float
  latitude: doublePrecision("latitude"), // 8-byte float
  balance: numeric("balance", { precision: 10, scale: 2 }), // exact: 12345678.99

  // Boolean
  active: boolean("active"),             // true/false

  // Dates
  createdAt: timestamp("created_at"),    // date + time
  birthday: date("birthday"),            // date only (no time)

  // JSON
  metadata: json("metadata"),            // stored as text
  settings: jsonb("settings"),           // stored as binary (indexable, faster queries)

  // UUID
  token: uuid("token"),                  // 128-bit unique identifier
});

text vs varchar

// text - no length limit, most common choice
name: text("name"),

// varchar - enforced max length
countryCode: varchar("country_code", { length: 2 }),

Use text unless you need a strict length constraint. PostgreSQL treats them the same internally.

json vs jsonb

// json - stores raw JSON text, preserves formatting
rawData: json("raw_data"),

// jsonb - binary format, supports indexing and queries
settings: jsonb("settings"),

Use jsonb in almost all cases. It's faster to query and supports PostgreSQL JSON operators.

numeric for Money

// ❌ Floats lose precision with money
price: real("price"),
// 0.1 + 0.2 = 0.30000000000000004

// ✅ numeric is exact
price: numeric("price", { precision: 10, scale: 2 }),
// 0.1 + 0.2 = 0.30

precision is total digits. scale is digits after the decimal. numeric(10, 2) stores up to 99999999.99.

Column Modifiers

Here's the full list of modifiers you can chain onto any column:

Modifier	What it does
`.notNull()`	Column cannot be NULL
`.default(value)`	Value used when not provided on insert
`.defaultNow()`	Sets current timestamp as default (for timestamp columns)
`.defaultRandom()`	Generates a random UUID as default (for uuid columns)
`.unique()`	No duplicate values allowed
`.primaryKey()`	Marks as primary key
`.references(() => table.column)`	Foreign key to another table
`.array()`	Makes the column an array type

export const products = pgTable("products", {
  id: serial("id").primaryKey(),

  // notNull - column cannot be NULL
  name: text("name").notNull(),

  // default - value when not provided
  status: text("status").default("draft"),
  active: boolean("active").default(true),
  createdAt: timestamp("created_at").defaultNow(),

  // unique - no duplicate values allowed
  slug: text("slug").unique(),
  sku: text("sku").notNull().unique(),

  // primaryKey - marks as primary key
  // (usually on id, shown above)

  // references - foreign key to another table
  categoryId: integer("category_id").references(() => categories.id),
});

Modifiers chain together. Order doesn't matter, but notNull before unique reads better.

Array Columns

PostgreSQL supports array columns. Drizzle handles them with the column type's .array() method:

export const articles = pgTable("articles", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  tags: text("tags").array(),
  scores: integer("scores").array(),
});

// Inserting array data
await db.insert(articles).values({
  title: "Getting Started with Drizzle",
  tags: ["orm", "typescript", "postgresql"],
  scores: [95, 87, 92],
});

Enum Types

Use pgEnum for columns that should only accept specific values. PostgreSQL creates a custom type that's stored as an integer index internally but behaves like a string. Validation happens at the database level, so invalid values are rejected even if your app has a bug.

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

export const roleEnum = pgEnum("role", ["user", "admin", "moderator"]);
export const statusEnum = pgEnum("status", ["draft", "published", "archived"]);

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  role: roleEnum("role").default("user"),
});

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  status: statusEnum("status").default("draft"),
});

// ❌ Using plain text for fixed values
role: text("role").default("user"),
// Nothing stops you from inserting "superadmin" or "xyz"

// ✅ Using pgEnum for fixed values
role: roleEnum("role").default("user"),
// Database rejects anything not in ["user", "admin", "moderator"]

Enums are enforced at the database level. Invalid values throw an error before your app code even sees them.

Type Inference

Drizzle can infer TypeScript types directly from your schema. No separate type definitions needed.

import { pgTable, serial, text, boolean, timestamp } 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(),
});

// Infer types from the table
type User = typeof users.$inferSelect;
// { id: number; name: string; email: string; active: boolean | null; createdAt: Date | null }

type NewUser = typeof users.$inferInsert;
// { id?: number; name: string; email: string; active?: boolean | null; createdAt?: Date | null }

$inferSelect is the type you get back from queries. All columns are present.

$inferInsert is the type for inserting rows. Columns with defaults or auto-generated values are optional.

// Using inferred types in your app
function createUser(data: NewUser) {
  return db.insert(users).values(data);
}

function displayUser(user: User) {
  console.log(`${user.name} (${user.email})`);
}

This is one of Drizzle's biggest advantages. Your schema is the single source of truth for both the database and your TypeScript types.

Key Takeaways

text for strings, varchar for length-limited strings
integer for numbers, bigint for large numbers, numeric for exact decimals (money)
jsonb over json for most use cases (faster, indexable)
timestamp for date+time, date for date only
Column modifiers: .notNull(), .default(), .unique(), .primaryKey(), .references()
.array() creates PostgreSQL array columns
pgEnum enforces allowed values at the database level
$inferSelect and $inferInsert derive TypeScript types from your schema with no extra code