Drizzle ORM

Modern TypeScript-first ORM with zero dependencies, compile-time type safety, and SQL-like syntax. Optimized for edge runtimes and serverless environments.

Quick Start

Installation

Core ORM

npm install drizzle-orm

Database driver (choose one)

npm install pg # PostgreSQL npm install mysql2 # MySQL npm install better-sqlite3 # SQLite

Drizzle Kit (migrations)

npm install -D drizzle-kit

Basic Setup

// db/schema.ts import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';

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

// db/client.ts import { drizzle } from 'drizzle-orm/node-postgres'; import { Pool } from 'pg'; import * as schema from './schema';

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

First Query

import { db } from './db/client'; import { users } from './db/schema'; import { eq } from 'drizzle-orm';

// Insert const newUser = await db.insert(users).values({ email: 'user@example.com', name: 'John Doe', }).returning();

// Select const allUsers = await db.select().from(users);

// Where const user = await db.select().from(users).where(eq(users.id, 1));

// Update await db.update(users).set({ name: 'Jane Doe' }).where(eq(users.id, 1));

// Delete await db.delete(users).where(eq(users.id, 1));

Schema Definition

Column Types Reference

PostgreSQL MySQL SQLite TypeScript

serial()

serial()

integer()

number

text()

text()

text()

string

integer()

int()

integer()

number

boolean()

boolean()

integer()

boolean

timestamp()

datetime()

integer()

Date

json()

json()

text()

unknown

uuid()

varchar(36)

text()

string

Common Schema Patterns

import { pgTable, serial, text, varchar, integer, boolean, timestamp, json, unique } from 'drizzle-orm/pg-core';

export const users = pgTable('users', { id: serial('id').primaryKey(), email: varchar('email', { length: 255 }).notNull().unique(), passwordHash: varchar('password_hash', { length: 255 }).notNull(), role: text('role', { enum: ['admin', 'user', 'guest'] }).default('user'), metadata: json('metadata').$type<{ theme: string; locale: string }>(), isActive: boolean('is_active').default(true), createdAt: timestamp('created_at').defaultNow().notNull(), updatedAt: timestamp('updated_at').defaultNow().notNull(), }, (table) => ({ emailIdx: unique('email_unique_idx').on(table.email), }));

// Infer TypeScript types type User = typeof users.$inferSelect; type NewUser = typeof users.$inferInsert;

Relations

One-to-Many

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

export const authors = pgTable('authors', { id: serial('id').primaryKey(), name: text('name').notNull(), });

export const posts = pgTable('posts', { id: serial('id').primaryKey(), title: text('title').notNull(), authorId: integer('author_id').notNull().references(() => authors.id), });

export const authorsRelations = relations(authors, ({ many }) => ({ posts: many(posts), }));

export const postsRelations = relations(posts, ({ one }) => ({ author: one(authors, { fields: [posts.authorId], references: [authors.id], }), }));

// Query with relations const authorsWithPosts = await db.query.authors.findMany({ with: { posts: true }, });

Many-to-Many

export const users = pgTable('users', { id: serial('id').primaryKey(), name: text('name').notNull(), });

export const groups = pgTable('groups', { id: serial('id').primaryKey(), name: text('name').notNull(), });

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

export const usersRelations = relations(users, ({ many }) => ({ groups: many(usersToGroups), }));

export const groupsRelations = relations(groups, ({ many }) => ({ users: many(usersToGroups), }));

export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({ user: one(users, { fields: [usersToGroups.userId], references: [users.id] }), group: one(groups, { fields: [usersToGroups.groupId], references: [groups.id] }), }));

Queries

Filtering

import { eq, ne, gt, gte, lt, lte, like, ilike, inArray, isNull, isNotNull, and, or, between } from 'drizzle-orm';

// Equality await db.select().from(users).where(eq(users.email, 'user@example.com'));

// Comparison await db.select().from(users).where(gt(users.id, 10));

// Pattern matching await db.select().from(users).where(like(users.name, '%John%'));

// Multiple conditions await db.select().from(users).where( and( eq(users.role, 'admin'), gt(users.createdAt, new Date('2024-01-01')) ) );

// IN clause await db.select().from(users).where(inArray(users.id, [1, 2, 3]));

// NULL checks await db.select().from(users).where(isNull(users.deletedAt));

Joins

import { eq } from 'drizzle-orm';

// Inner join const result = await db .select({ user: users, post: posts, }) .from(users) .innerJoin(posts, eq(users.id, posts.authorId));

// Left join const result = await db .select({ user: users, post: posts, }) .from(users) .leftJoin(posts, eq(users.id, posts.authorId));

// Multiple joins with aggregation import { count, sql } from 'drizzle-orm';

const result = await db .select({ authorName: authors.name, postCount: count(posts.id), }) .from(authors) .leftJoin(posts, eq(authors.id, posts.authorId)) .groupBy(authors.id);

Pagination & Sorting

import { desc, asc } from 'drizzle-orm';

// Order by await db.select().from(users).orderBy(desc(users.createdAt));

// Limit & offset await db.select().from(users).limit(10).offset(20);

// Pagination helper function paginate(page: number, pageSize: number = 10) { return db.select().from(users) .limit(pageSize) .offset(page * pageSize); }

Transactions

// Auto-rollback on error await db.transaction(async (tx) => { await tx.insert(users).values({ email: 'user@example.com', name: 'John' }); await tx.insert(posts).values({ title: 'First Post', authorId: 1 }); // If any query fails, entire transaction rolls back });

// Manual control const tx = db.transaction(async (tx) => { const user = await tx.insert(users).values({ ... }).returning();

if (!user) { tx.rollback(); return; }

await tx.insert(posts).values({ authorId: user.id }); });

Migrations

Drizzle Kit Configuration

// drizzle.config.ts import type { Config } from 'drizzle-kit';

export default { schema: './db/schema.ts', out: './drizzle', dialect: 'postgresql', dbCredentials: { url: process.env.DATABASE_URL!, }, } satisfies Config;

Migration Workflow

Generate migration

npx drizzle-kit generate

View SQL

cat drizzle/0000_migration.sql

Apply migration

npx drizzle-kit migrate

Introspect existing database

npx drizzle-kit introspect

Drizzle Studio (database GUI)

npx drizzle-kit studio

Example Migration

-- drizzle/0000_initial.sql CREATE TABLE IF NOT EXISTS "users" ( "id" serial PRIMARY KEY NOT NULL, "email" varchar(255) NOT NULL, "name" text NOT NULL, "created_at" timestamp DEFAULT now() NOT NULL, CONSTRAINT "users_email_unique" UNIQUE("email") );

Navigation

Detailed References

🏗️ Advanced Schemas - Custom types, composite keys, indexes, constraints, multi-tenant patterns. Load when designing complex database schemas.

🔍 Query Patterns - Subqueries, CTEs, raw SQL, prepared statements, batch operations. Load when optimizing queries or handling complex filtering.

⚡ Performance - Connection pooling, query optimization, N+1 prevention, prepared statements, edge runtime integration. Load when scaling or optimizing database performance.

🔄 vs Prisma - Feature comparison, migration guide, when to choose Drizzle over Prisma. Load when evaluating ORMs or migrating from Prisma.

Red Flags

Stop and reconsider if:

• Using any or unknown for JSON columns without type annotation

• Building raw SQL strings without using sql template (SQL injection risk)

• Not using transactions for multi-step data modifications

• Fetching all rows without pagination in production queries

• Missing indexes on foreign keys or frequently queried columns

• Using select() without specifying columns for large tables

Performance Benefits vs Prisma

Metric Drizzle Prisma

Bundle Size ~35KB ~230KB

Cold Start ~10ms ~250ms

Query Speed Baseline ~2-3x slower

Memory ~10MB ~50MB

Type Generation Runtime inference Build-time generation

Integration

• typescript-core: Type-safe schema inference with satisfies

• nextjs-core: Server Actions, Route Handlers, Middleware integration

• Database Migration: Safe schema evolution patterns

Related Skills

When using Drizzle, these skills enhance your workflow:

• prisma: Alternative ORM comparison: Drizzle vs Prisma trade-offs

• typescript: Advanced TypeScript patterns for type-safe queries

• nextjs: Drizzle with Next.js Server Actions and API routes

• sqlalchemy: SQLAlchemy patterns for Python developers learning Drizzle

[Full documentation available in these skills if deployed in your bundle]