Drizzle ORM: Schema Definition & Table Design

Drizzle is a TypeScript-first ORM that keeps you close to SQL while providing type safety. Your schema is the single source of truth for both the database and TypeScript types.

Setup

# Install Drizzle with your database driver
npm install drizzle-orm @neondatabase/serverless   # Neon / serverless PostgreSQL
npm install drizzle-orm postgres                    # Standard PostgreSQL
npm install drizzle-orm better-sqlite3              # SQLite
npm install drizzle-orm mysql2                      # MySQL

# Install drizzle-kit for migrations
npm install -D drizzle-kit

Database Client (Neon)

// src/db/client.ts
import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';
import * as schema from './schema';

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });

// For connection pooling (serverful environments)
import { Pool } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-serverless';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
export const db = drizzle(pool, { schema });

Defining Tables (PostgreSQL)

// src/db/schema.ts
import {
  pgTable, text, varchar, integer, bigint, boolean,
  timestamp, jsonb, uuid, serial, unique, index
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  clerkId: text('clerk_id').unique().notNull(),
  email: text('email').unique().notNull(),
  name: text('name').notNull(),
  nickname: varchar('nickname', { length: 50 }).unique(),
  plan: text('plan', { enum: ['free', 'pro', 'enterprise'] }).default('free').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => [
  index('users_email_idx').on(table.email),
  index('users_clerk_id_idx').on(table.clerkId),
]);

export const folders = pgTable('folders', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  parentId: uuid('parent_id').references((): AnyPgColumn => folders.id, { onDelete: 'cascade' }),
  name: text('name').notNull(),
  slug: text('slug').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => [
  unique('folders_user_slug_unique').on(table.userId, table.slug),
]);

export const pages = pgTable('pages', {
  id: uuid('id').primaryKey().defaultRandom(),
  folderId: uuid('folder_id').notNull().references(() => folders.id, { onDelete: 'cascade' }),
  title: text('title').notNull(),
  slug: text('slug').notNull(),
  content: text('content'),
  status: text('status', { enum: ['to_learn', 'learning', 'proficient', 'expert'] }).default('to_learn'),
  tags: uuid('tags').array().default([]).notNull(),
  metadata: jsonb('metadata').$type<{ wordCount?: number; lastEdited?: string }>(),
  isImportant: boolean('is_important').default(false).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

Type Inference

// Infer TypeScript types from schema — no duplication needed
export type User = typeof users.$inferSelect;       // SELECT result type
export type NewUser = typeof users.$inferInsert;    // INSERT input type

export type Folder = typeof folders.$inferSelect;
export type NewFolder = typeof folders.$inferInsert;

export type Page = typeof pages.$inferSelect;
export type NewPage = typeof pages.$inferInsert;

// Partial types for updates
export type UpdatePage = Partial<Omit<NewPage, 'id' | 'folderId' | 'createdAt'>>;

Column Reference Types (self-referencing)

// Self-referencing table needs explicit type annotation
import type { AnyPgColumn } from 'drizzle-orm/pg-core';

export const categories = pgTable('categories', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  // Must annotate with (): AnyPgColumn for circular reference
  parentId: integer('parent_id').references((): AnyPgColumn => categories.id),
});

Column Types Reference

// Common PostgreSQL column types in Drizzle
text('col')                           // TEXT — variable length string
varchar('col', { length: 255 })      // VARCHAR(n)
char('col', { length: 10 })          // CHAR(n) — fixed length
integer('col')                        // INT4 — 32-bit integer
bigint('col', { mode: 'number' })    // INT8 — 64-bit (use mode:'bigint' for >2^53)
smallint('col')                       // INT2 — 16-bit integer
serial('col')                         // SERIAL — auto-increment int
boolean('col')                        // BOOLEAN
timestamp('col')                      // TIMESTAMP WITHOUT TIME ZONE
timestamp('col', { withTimezone: true }) // TIMESTAMPTZ
date('col')                          // DATE
numeric('col', { precision: 10, scale: 2 }) // NUMERIC — for money
real('col')                           // FLOAT4
doublePrecision('col')               // FLOAT8
uuid('col')                          // UUID
jsonb('col')                          // JSONB — binary JSON, indexable
json('col')                           // JSON — text JSON
vector('col', { dimensions: 1536 }) // pgvector extension