drizzle-orm

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 ORMnpm install drizzle-orm# Database driver (choose one)npm install pg            # PostgreSQLnpm install mysql2        # MySQLnpm install better-sqlite3 # SQLite# Drizzle Kit (migrations)npm install -D drizzle-kit

Basic Setup

// db/schema.tsimport { 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.tsimport { 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';// Insertconst newUser = await db.insert(users).values({  email: '[email protected]',  name: 'John Doe',}).returning();// Selectconst allUsers = await db.select().from(users);// Whereconst user = await db.select().from(users).where(eq(users.id, 1));// Updateawait db.update(users).set({ name: 'Jane Doe' }).where(eq(users.id, 1));// Deleteawait db.delete(users).where(eq(users.id, 1));

Schema Definition

Column Types Reference

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 typestype 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 relationsconst 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';// Equalityawait db.select().from(users).where(eq(users.email, '[email protected]'));// Comparisonawait db.select().from(users).where(gt(users.id, 10));// Pattern matchingawait db.select().from(users).where(like(users.name, '%John%'));// Multiple conditionsawait db.select().from(users).where(  and(    eq(users.role, 'admin'),    gt(users.createdAt, new Date('2024-01-01'))  ));// IN clauseawait db.select().from(users).where(inArray(users.id, [1, 2, 3]));// NULL checksawait db.select().from(users).where(isNull(users.deletedAt));

Joins

import { eq } from 'drizzle-orm';// Inner joinconst result = await db  .select({    user: users,    post: posts,  })  .from(users)  .innerJoin(posts, eq(users.id, posts.authorId));// Left joinconst result = await db  .select({    user: users,    post: posts,  })  .from(users)  .leftJoin(posts, eq(users.id, posts.authorId));// Multiple joins with aggregationimport { 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 byawait db.select().from(users).orderBy(desc(users.createdAt));// Limit & offsetawait db.select().from(users).limit(10).offset(20);// Pagination helperfunction paginate(page: number, pageSize: number = 10) {  return db.select().from(users)    .limit(pageSize)    .offset(page * pageSize);}

Transactions

// Auto-rollback on errorawait db.transaction(async (tx) => {  await tx.insert(users).values({ email: '[email protected]', name: 'John' });  await tx.insert(posts).values({ title: 'First Post', authorId: 1 });  // If any query fails, entire transaction rolls back});// Manual controlconst 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.tsimport 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 migrationnpx drizzle-kit generate# View SQLcat drizzle/0000_migration.sql# Apply migrationnpx drizzle-kit migrate# Introspect existing databasenpx drizzle-kit introspect# Drizzle Studio (database GUI)npx drizzle-kit studio

Example Migration

-- drizzle/0000_initial.sqlCREATE 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

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]