/postgres-drizzle

Drizzle Schema Definition

Comprehensive reference for defining PostgreSQL schemas with Drizzle ORM (stable 0.x syntax — see RELATIONS.md for the v1.0 changes, which affect relations/queries but not the column/constraint syntax below).

Contents

• Column Types
• Primary Keys
• String Types
• Numeric Types
• Date/Time Types
• JSON/JSONB
• Enums
• Arrays
• Constraints
• Foreign Keys
• Indexes
• Composite Primary Key
• Timestamps Pattern
• Soft Delete Pattern
• Multi-Tenant Pattern
• Generated Columns
• Schema Organization

Column Types

Imports

import {
  pgTable,
  uuid,
  text,
  varchar,
  char,
  integer,
  smallint,
  bigint,
  serial,
  smallserial,
  bigserial,
  boolean,
  timestamp,
  date,
  time,
  interval,
  numeric,
  decimal,
  real,
  doublePrecision,
  json,
  jsonb,
  pgEnum,
  index,
  uniqueIndex,
  primaryKey,
  foreignKey,
  check,
} from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';

Primary Keys

UUID (Recommended)

// UUIDv4 - random
id: uuid('id').primaryKey().defaultRandom(),

// UUIDv7 - timestamp-ordered (PostgreSQL 18+, better index performance)
id: uuid('id').primaryKey().default(sql`uuidv7()`),

Identity (Preferred over Serial for Integer PKs)

PostgreSQL recommends identity columns over serial: they are SQL-standard, own their sequence (dropped with the column), and GENERATED ALWAYS prevents accidental manual inserts into the ID column.

// GENERATED ALWAYS AS IDENTITY
id: integer('id').primaryKey().generatedAlwaysAsIdentity(),

// GENERATED BY DEFAULT AS IDENTITY (allows manual override)
id: integer('id').primaryKey().generatedByDefaultAsIdentity(),

// With sequence options
id: integer('id').primaryKey().generatedAlwaysAsIdentity({
  startWith: 1000,
  increment: 1,
  minValue: 1,
  maxValue: 2147483647,
  cache: 100,
}),

Serial (Legacy — avoid in new schemas)

id: serial('id').primaryKey(),        // 4 bytes, 1 to 2,147,483,647
id: bigserial('id').primaryKey(),     // 8 bytes, 1 to 9,223,372,036,854,775,807
id: smallserial('id').primaryKey(),   // 2 bytes, 1 to 32,767

String Types

In PostgreSQL, text and varchar have identical performance — use text unless you want the database to enforce a maximum length.

// Unlimited length (most common)
name: text('name').notNull(),

// Variable length with limit
email: varchar('email', { length: 255 }).notNull(),

// Fixed length (padded with spaces)
countryCode: char('country_code', { length: 2 }),

// With default
status: text('status').notNull().default('pending'),

Numeric Types

// Integers
age: integer('age'),                    // 4 bytes, -2B to 2B
count: smallint('count'),               // 2 bytes, -32K to 32K
bigNumber: bigint('big_number', { mode: 'number' }),  // JS number
bigNumberStr: bigint('big_number', { mode: 'bigint' }), // JS BigInt

// Floating point (approximate)
score: real('score'),                   // 4 bytes, 6 decimal precision
amount: doublePrecision('amount'),      // 8 bytes, 15 decimal precision

// Exact numeric (use for money!)
price: numeric('price', { precision: 10, scale: 2 }),  // 12345678.90
total: decimal('total', { precision: 19, scale: 4 }), // alias for numeric

Date/Time Types

// Timestamp with timezone (RECOMMENDED)
createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),

// Timestamp without timezone
localTime: timestamp('local_time', { withTimezone: false }),

// Timestamp modes
tsDate: timestamp('ts', { mode: 'date' }),        // JavaScript Date (default)
tsString: timestamp('ts', { mode: 'string' }),    // ISO string
tsNumber: timestamp('ts', { mode: 'number' }),    // Unix timestamp

// Precision (0-6 microseconds)
precise: timestamp('precise', { precision: 6, withTimezone: true }),

// Date only
birthDate: date('birth_date'),
birthDateString: date('birth_date', { mode: 'string' }),  // 'YYYY-MM-DD'

// Time only
openTime: time('open_time'),
openTimeWithTz: time('open_time', { withTimezone: true }),

// Interval
duration: interval('duration'),

Boolean

isActive: boolean('is_active').notNull().default(true),
verified: boolean('verified').default(false),

JSON/JSONB

JSONB is preferred (binary format, indexable, faster queries).

// Basic JSONB
data: jsonb('data'),

// Typed JSONB
settings: jsonb('settings').$type<{
  theme: 'light' | 'dark';
  notifications: boolean;
  language: string;
}>(),

// With default
config: jsonb('config').$type<Record<string, unknown>>().default({}),

// JSON (text format, preserves whitespace/order)
rawData: json('raw_data'),

Querying JSONB

import { sql } from 'drizzle-orm';

// Access nested field
.where(sql`${events.data}->>'type' = 'purchase'`)

// Containment (@>)
.where(sql`${events.data} @> '{"status": "active"}'`)

// Key existence
.where(sql`${events.data} ? 'error_code'`)

Enums

PostgreSQL Enum

// Define enum type
export const statusEnum = pgEnum('status', ['pending', 'active', 'archived']);
export const roleEnum = pgEnum('user_role', ['admin', 'user', 'guest']);

// Use in table
export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  status: statusEnum('status').notNull().default('pending'),
  role: roleEnum('role').notNull().default('user'),
});

TypeScript Enum (Alternative)

// Check constraint instead of pg enum (easier to modify)
export const users = pgTable('users', {
  status: text('status', { enum: ['pending', 'active', 'archived'] }).notNull(),
});

Arrays

// Text array
tags: text('tags').array(),

// Integer array
scores: integer('scores').array(),

// Array with default
categories: text('categories').array().default([]),

// Querying arrays
import { arrayContains, arrayContained, arrayOverlaps } from 'drizzle-orm';

.where(arrayContains(posts.tags, ['typescript', 'drizzle']))
.where(arrayOverlaps(posts.tags, ['react', 'vue']))

Constraints

Not Null & Default

email: text('email').notNull(),
status: text('status').notNull().default('active'),
createdAt: timestamp('created_at').notNull().defaultNow(),

Unique

// Column-level unique
email: text('email').notNull().unique(),

// Table-level unique (composite)
}, (table) => [
  uniqueIndex('users_email_tenant_idx').on(table.email, table.tenantId),
]);

Check Constraints

export const products = pgTable('products', {
  price: numeric('price', { precision: 10, scale: 2 }).notNull(),
  quantity: integer('quantity').notNull(),
}, (table) => [
  check('price_positive', sql`${table.price} > 0`),
  check('quantity_non_negative', sql`${table.quantity} >= 0`),
]);

Foreign Keys

Inline Reference

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  authorId: uuid('author_id')
    .notNull()
    .references(() => users.id),
});

With Actions

authorId: uuid('author_id')
  .notNull()
  .references(() => users.id, {
    onDelete: 'cascade',    // CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION
    onUpdate: 'cascade',
  }),

Self-Referential

import { AnyPgColumn } from 'drizzle-orm/pg-core';

export const categories = pgTable('categories', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  parentId: uuid('parent_id').references((): AnyPgColumn => categories.id),
});

Composite Foreign Key

export const orderItems = pgTable('order_items', {
  orderId: uuid('order_id').notNull(),
  productId: uuid('product_id').notNull(),
  quantity: integer('quantity').notNull(),
}, (table) => [
  foreignKey({
    columns: [table.orderId, table.productId],
    foreignColumns: [orders.id, products.id],
  }),
]);

Indexes

Single Column

}, (table) => [
  index('users_email_idx').on(table.email),
]);

Composite Index

}, (table) => [
  index('orders_user_date_idx').on(table.userId, table.createdAt),
]);

Unique Index

}, (table) => [
  uniqueIndex('users_email_unique').on(table.email),
]);

Partial Index

}, (table) => [
  index('active_users_idx')
    .on(table.email)
    .where(sql`deleted_at IS NULL`),
]);

Expression Index

}, (table) => [
  index('users_email_lower_idx').on(sql`lower(${table.email})`),
]);

Index Types

Non-btree methods use .using(method, ...columns) — the method comes first, columns/expressions after (there is no .on(col).using(method) chaining).

// B-tree (default)
index('idx').on(table.column),

// Hash (equality only)
index('idx').using('hash', table.column),

// GIN (arrays, JSONB, full-text)
index('idx').using('gin', table.data),

// GIN with operator class (smaller/faster for JSONB containment-only)
index('idx').using('gin', table.data.op('jsonb_path_ops')),

// GiST (geometric, range, exclusion)
index('idx').using('gist', table.location),

// GIN over an expression (full-text without a stored tsvector column)
index('idx').using('gin', sql`to_tsvector('english', ${table.title})`),

Composite Primary Key

import { primaryKey } from 'drizzle-orm/pg-core';

export const usersToGroups = pgTable('users_to_groups', {
  userId: uuid('user_id').notNull().references(() => users.id),
  groupId: uuid('group_id').notNull().references(() => groups.id),
  joinedAt: timestamp('joined_at').notNull().defaultNow(),
}, (table) => [
  primaryKey({ columns: [table.userId, table.groupId] }),
]);

Timestamps Pattern

Reusable Timestamps

const timestamps = {
  createdAt: timestamp('created_at', { withTimezone: true })
    .notNull()
    .defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .notNull()
    .defaultNow()
    .$onUpdate(() => new Date()),
};

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull(),
  ...timestamps,
});

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  title: text('title').notNull(),
  ...timestamps,
});

Soft Delete Pattern

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull(),
  deletedAt: timestamp('deleted_at', { withTimezone: true }),
  ...timestamps,
}, (table) => [
  // Partial index for active users only
  index('active_users_email_idx')
    .on(table.email)
    .where(sql`deleted_at IS NULL`),
]);

// Query active users
import { isNull } from 'drizzle-orm';

const activeUsers = await db
  .select()
  .from(users)
  .where(isNull(users.deletedAt));

Multi-Tenant Pattern

export const tenants = pgTable('tenants', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
});

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  tenantId: uuid('tenant_id').notNull().references(() => tenants.id),
  email: text('email').notNull(),
}, (table) => [
  // Unique email per tenant
  uniqueIndex('users_tenant_email_idx').on(table.tenantId, table.email),
  // Index for tenant queries
  index('users_tenant_idx').on(table.tenantId),
]);

Generated Columns

Stored (Computed at Write)

Drizzle's generatedAlwaysAs() emits GENERATED ALWAYS AS (...) STORED for PostgreSQL. Reference sibling columns via a (): SQL => thunk so the table can refer to itself:

import { SQL, sql } from 'drizzle-orm';

export const products = pgTable('products', {
  id: uuid('id').primaryKey().defaultRandom(),
  price: numeric('price', { precision: 10, scale: 2 }).notNull(),
  taxRate: numeric('tax_rate', { precision: 5, scale: 4 }).notNull(),
  totalPrice: numeric('total_price', { precision: 10, scale: 2 })
    .generatedAlwaysAs((): SQL => sql`${products.price} * (1 + ${products.taxRate})`),
});

A common use is a tsvector column for full-text search — see POSTGRES.md.

Virtual (PostgreSQL 18+, Computed at Read)

PostgreSQL 18 adds VIRTUAL generated columns (computed at read, not stored, cannot be indexed). Drizzle's pg-core only generates the STORED form — to use virtual columns, write the DDL in a custom migration (drizzle-kit generate --custom):

ALTER TABLE products
  ADD COLUMN display_price text GENERATED ALWAYS AS (price::text || ' USD') VIRTUAL;

Schema Organization

Single File (Small Projects)

src/db/
  schema.ts      # All tables, relations, types
  index.ts       # Database connection

Multi-File (Large Projects)

src/db/
  schema/
    index.ts     # Re-exports all
    users.ts     # User table + relations
    posts.ts     # Post table + relations
    comments.ts  # Comment table + relations
  index.ts       # Database connection

// schema/users.ts
export const users = pgTable('users', { ... });
export const usersRelations = relations(users, ({ many }) => ({ ... }));

// schema/index.ts
export * from './users';
export * from './posts';
export * from './comments';