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

bash
1# Core ORM
2npm install drizzle-orm
3
4# Database driver (choose one)
5npm install pg            # PostgreSQL
6npm install mysql2        # MySQL
7npm install better-sqlite3 # SQLite
8
9# Drizzle Kit (migrations)
10npm install -D drizzle-kit

Basic Setup

typescript
1// db/schema.ts
2import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';
3
4export const users = pgTable('users', {
5  id: serial('id').primaryKey(),
6  email: text('email').notNull().unique(),
7  name: text('name').notNull(),
8  createdAt: timestamp('created_at').defaultNow(),
9});
10
11// db/client.ts
12import { drizzle } from 'drizzle-orm/node-postgres';
13import { Pool } from 'pg';
14import * as schema from './schema';
15
16const pool = new Pool({ connectionString: process.env.DATABASE_URL });
17export const db = drizzle(pool, { schema });

First Query

typescript
1import { db } from './db/client';
2import { users } from './db/schema';
3import { eq } from 'drizzle-orm';
4
5// Insert
6const newUser = await db
7  .insert(users)
8  .values({
9    email: 'user@example.com',
10    name: 'John Doe',
11  })
12  .returning();
13
14// Select
15const allUsers = await db.select().from(users);
16
17// Where
18const user = await db.select().from(users).where(eq(users.id, 1));
19
20// Update
21await db.update(users).set({ name: 'Jane Doe' }).where(eq(users.id, 1));
22
23// Delete
24await db.delete(users).where(eq(users.id, 1));

Schema Definition

Column Types Reference

Common Schema Patterns

typescript
1import { pgTable, serial, text, varchar, integer, boolean, timestamp, json, unique } from 'drizzle-orm/pg-core';
2
3export const users = pgTable(
4  'users',
5  {
6    id: serial('id').primaryKey(),
7    email: varchar('email', { length: 255 }).notNull().unique(),
8    passwordHash: varchar('password_hash', { length: 255 }).notNull(),
9    role: text('role', { enum: ['admin', 'user', 'guest'] }).default('user'),
10    metadata: json('metadata').$type<{ theme: string; locale: string }>(),
11    isActive: boolean('is_active').default(true),
12    createdAt: timestamp('created_at').defaultNow().notNull(),
13    updatedAt: timestamp('updated_at').defaultNow().notNull(),
14  },
15  table => ({
16    emailIdx: unique('email_unique_idx').on(table.email),
17  }),
18);
19
20// Infer TypeScript types
21type User = typeof users.$inferSelect;
22type NewUser = typeof users.$inferInsert;

Relations

One-to-Many

typescript
1import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
2import { relations } from 'drizzle-orm';
3
4export const authors = pgTable('authors', {
5  id: serial('id').primaryKey(),
6  name: text('name').notNull(),
7});
8
9export const posts = pgTable('posts', {
10  id: serial('id').primaryKey(),
11  title: text('title').notNull(),
12  authorId: integer('author_id')
13    .notNull()
14    .references(() => authors.id),
15});
16
17export const authorsRelations = relations(authors, ({ many }) => ({
18  posts: many(posts),
19}));
20
21export const postsRelations = relations(posts, ({ one }) => ({
22  author: one(authors, {
23    fields: [posts.authorId],
24    references: [authors.id],
25  }),
26}));
27
28// Query with relations
29const authorsWithPosts = await db.query.authors.findMany({
30  with: { posts: true },
31});

Many-to-Many

typescript
1export const users = pgTable('users', {
2  id: serial('id').primaryKey(),
3  name: text('name').notNull(),
4});
5
6export const groups = pgTable('groups', {
7  id: serial('id').primaryKey(),
8  name: text('name').notNull(),
9});
10
11export const usersToGroups = pgTable(
12  'users_to_groups',
13  {
14    userId: integer('user_id')
15      .notNull()
16      .references(() => users.id),
17    groupId: integer('group_id')
18      .notNull()
19      .references(() => groups.id),
20  },
21  table => ({
22    pk: primaryKey({ columns: [table.userId, table.groupId] }),
23  }),
24);
25
26export const usersRelations = relations(users, ({ many }) => ({
27  groups: many(usersToGroups),
28}));
29
30export const groupsRelations = relations(groups, ({ many }) => ({
31  users: many(usersToGroups),
32}));
33
34export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({
35  user: one(users, { fields: [usersToGroups.userId], references: [users.id] }),
36  group: one(groups, { fields: [usersToGroups.groupId], references: [groups.id] }),
37}));

Queries

Filtering

typescript
1import { eq, ne, gt, gte, lt, lte, like, ilike, inArray, isNull, isNotNull, and, or, between } from 'drizzle-orm';
2
3// Equality
4await db.select().from(users).where(eq(users.email, 'user@example.com'));
5
6// Comparison
7await db.select().from(users).where(gt(users.id, 10));
8
9// Pattern matching
10await db.select().from(users).where(like(users.name, '%John%'));
11
12// Multiple conditions
13await db
14  .select()
15  .from(users)
16  .where(and(eq(users.role, 'admin'), gt(users.createdAt, new Date('2024-01-01'))));
17
18// IN clause
19await db
20  .select()
21  .from(users)
22  .where(inArray(users.id, [1, 2, 3]));
23
24// NULL checks
25await db.select().from(users).where(isNull(users.deletedAt));

Joins

typescript
1import { eq } from 'drizzle-orm';
2
3// Inner join
4const result = await db
5  .select({
6    user: users,
7    post: posts,
8  })
9  .from(users)
10  .innerJoin(posts, eq(users.id, posts.authorId));
11
12// Left join
13const result = await db
14  .select({
15    user: users,
16    post: posts,
17  })
18  .from(users)
19  .leftJoin(posts, eq(users.id, posts.authorId));
20
21// Multiple joins with aggregation
22import { count, sql } from 'drizzle-orm';
23
24const result = await db
25  .select({
26    authorName: authors.name,
27    postCount: count(posts.id),
28  })
29  .from(authors)
30  .leftJoin(posts, eq(authors.id, posts.authorId))
31  .groupBy(authors.id);

Pagination & Sorting

typescript
1import { desc, asc } from 'drizzle-orm';
2
3// Order by
4await db.select().from(users).orderBy(desc(users.createdAt));
5
6// Limit & offset
7await db.select().from(users).limit(10).offset(20);
8
9// Pagination helper
10function paginate(page: number, pageSize: number = 10) {
11  return db
12    .select()
13    .from(users)
14    .limit(pageSize)
15    .offset(page * pageSize);
16}

Transactions

typescript
1// Auto-rollback on error
2await db.transaction(async (tx) => {
3  await tx.insert(users).values({ email: 'user@example.com', name: 'John' });
4  await tx.insert(posts).values({ title: 'First Post', authorId: 1 });
5  // If any query fails, entire transaction rolls back
6});
7
8// Manual control
9const tx = db.transaction(async (tx) => {
10  const user = await tx.insert(users).values({ ... }).returning();
11
12  if (!user) {
13    tx.rollback();
14    return;
15  }
16
17  await tx.insert(posts).values({ authorId: user.id });
18});

Migrations

Drizzle Kit Configuration

typescript
1// drizzle.config.ts
2import type { Config } from 'drizzle-kit';
3
4export default {
5  schema: './db/schema.ts',
6  out: './drizzle',
7  dialect: 'postgresql',
8  dbCredentials: {
9    url: process.env.DATABASE_URL!,
10  },
11} satisfies Config;

Migration Workflow

bash
1# Generate migration
2npx drizzle-kit generate
3
4# View SQL
5cat drizzle/0000_migration.sql
6
7# Apply migration
8npx drizzle-kit migrate
9
10# Introspect existing database
11npx drizzle-kit introspect
12
13# Drizzle Studio (database GUI)
14npx drizzle-kit studio

Example Migration

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

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

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]