Drizzle

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

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](./references/advanced-schemas.md) - Custom types, composite keys, indexes, constraints, multi-tenant patterns. Load when designing complex database schemas.

• [🔍 Query Patterns](./references/query-patterns.md) - Subqueries, CTEs, raw SQL, prepared statements, batch operations. Load when optimizing queries or handling complex filtering.

• [⚡ Performance](./references/performance.md) - Connection pooling, query optimization, N+1 prevention, prepared statements, edge runtime integration. Load when scaling or optimizing database performance.

• [🔄 vs Prisma](./references/vs-prisma.md) - 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]

Advanced Schemas

Deep dive into complex schema patterns, custom types, and database-specific features in Drizzle ORM.

Custom Column Types

Enums

import { pgEnum, pgTable, serial } from 'drizzle-orm/pg-core';

// PostgreSQL native enum
export const roleEnum = pgEnum('role', ['admin', 'user', 'guest']);

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  role: roleEnum('role').default('user'),
});

// MySQL/SQLite: Use text with constraints
import { mysqlTable, text } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  role: text('role', { enum: ['admin', 'user', 'guest'] }).default('user'),
});

Custom JSON Types

import { pgTable, serial, json } from 'drizzle-orm/pg-core';
import { z } from 'zod';

// Type-safe JSON with Zod
const MetadataSchema = z.object({
  theme: z.enum(['light', 'dark']),
  locale: z.string(),
  notifications: z.boolean(),
});

type Metadata = z.infer<typeof MetadataSchema>;

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  metadata: json('metadata').$type<Metadata>(),
});

// Runtime validation
async function updateMetadata(userId: number, metadata: unknown) {
  const validated = MetadataSchema.parse(metadata);
  await db.update(users).set({ metadata: validated }).where(eq(users.id, userId));
}

Arrays

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

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  tags: text('tags').array(),
});

// Query array columns
import { arrayContains, arrayContained } from 'drizzle-orm';

await db.select().from(posts).where(arrayContains(posts.tags, ['typescript', 'drizzle']));

Indexes

Basic Indexes

import { pgTable, serial, text, varchar, index, uniqueIndex } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  name: text('name'),
  city: text('city'),
}, (table) => ({
  emailIdx: uniqueIndex('email_idx').on(table.email),
  nameIdx: index('name_idx').on(table.name),
  cityNameIdx: index('city_name_idx').on(table.city, table.name),
}));

Partial Indexes

import { sql } from 'drizzle-orm';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }),
  deletedAt: timestamp('deleted_at'),
}, (table) => ({
  activeEmailIdx: uniqueIndex('active_email_idx')
    .on(table.email)
    .where(sql`${table.deletedAt} IS NULL`),
}));

Full-Text Search

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

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
}, (table) => ({
  searchIdx: index('search_idx').using(
    'gin',
    sql`to_tsvector('english', ${table.title} || ' ' || ${table.content})`
  ),
}));

// Full-text search query
const results = await db.select().from(posts).where(
  sql`to_tsvector('english', ${posts.title} || ' ' || ${posts.content}) @@ plainto_tsquery('english', 'typescript orm')`
);

Composite Keys

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

export const userPreferences = pgTable('user_preferences', {
  userId: integer('user_id').notNull(),
  key: text('key').notNull(),
  value: text('value').notNull(),
}, (table) => ({
  pk: primaryKey({ columns: [table.userId, table.key] }),
}));

Check Constraints

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

export const products = pgTable('products', {
  id: serial('id').primaryKey(),
  price: integer('price').notNull(),
  discountPrice: integer('discount_price'),
}, (table) => ({
  priceCheck: check('price_check', sql`${table.price} > 0`),
  discountCheck: check('discount_check', sql`${table.discountPrice} < ${table.price}`),
}));

Generated Columns

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  firstName: text('first_name').notNull(),
  lastName: text('last_name').notNull(),
  fullName: text('full_name').generatedAlwaysAs(
    (): SQL => sql`${users.firstName} || ' ' || ${users.lastName}`,
    { mode: 'stored' }
  ),
});

Multi-Tenant Patterns

Row-Level Security (PostgreSQL)

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

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

export const documents = pgTable('documents', {
  id: serial('id').primaryKey(),
  tenantId: uuid('tenant_id').notNull().references(() => tenants.id),
  title: text('title').notNull(),
  content: text('content'),
});

// Apply RLS policy (via migration SQL)
/*
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);
*/

// Set tenant context
await db.execute(sql`SET app.current_tenant_id = ${tenantId}`);

Schema-Per-Tenant

import { drizzle } from 'drizzle-orm/node-postgres';

// Create schema-aware connection
function getTenantDb(tenantId: string) {
  const schemaName = `tenant_${tenantId}`;

  return drizzle(pool, {
    schema: {
      ...schema,
    },
    schemaPrefix: schemaName,
  });
}

// Use tenant-specific DB
const tenantDb = getTenantDb('tenant123');
await tenantDb.select().from(users);

Database-Specific Features

PostgreSQL: JSONB Operations

import { pgTable, serial, jsonb } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';

export const settings = pgTable('settings', {
  id: serial('id').primaryKey(),
  config: jsonb('config').$type<Record<string, unknown>>(),
});

// JSONB operators
await db.select().from(settings).where(
  sql`${settings.config}->>'theme' = 'dark'`
);

// JSONB path query
await db.select().from(settings).where(
  sql`${settings.config} @> '{"notifications": {"email": true}}'::jsonb`
);

MySQL: Spatial Types

import { mysqlTable, serial, geometry } from 'drizzle-orm/mysql-core';
import { sql } from 'drizzle-orm';

export const locations = mysqlTable('locations', {
  id: serial('id').primaryKey(),
  point: geometry('point', { type: 'point', srid: 4326 }),
});

// Spatial query
await db.select().from(locations).where(
  sql`ST_Distance_Sphere(${locations.point}, POINT(${lng}, ${lat})) < 1000`
);

SQLite: FTS5

import { sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const documents = sqliteTable('documents', {
  title: text('title'),
  content: text('content'),
});

// Create FTS5 virtual table (via migration)
/*
CREATE VIRTUAL TABLE documents_fts USING fts5(title, content, content='documents');
*/

Schema Versioning

Migration Strategy

// db/schema.ts
export const schemaVersion = pgTable('schema_version', {
  version: serial('version').primaryKey(),
  appliedAt: timestamp('applied_at').defaultNow(),
});

// Track migrations
await db.insert(schemaVersion).values({ version: 1 });

// Check version
const [currentVersion] = await db.select().from(schemaVersion).orderBy(desc(schemaVersion.version)).limit(1);

Type Inference Helpers

import { InferSelectModel, InferInsertModel } from 'drizzle-orm';

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

// Generate types
export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;

// Partial updates
export type UserUpdate = Partial<NewUser>;

// Nested relation types
export type UserWithPosts = User & {
  posts: Post[];
};

Best Practices

Schema Organization

// db/schema/users.ts
export const users = pgTable('users', { ... });
export const userRelations = relations(users, { ... });

// db/schema/posts.ts
export const posts = pgTable('posts', { ... });
export const postRelations = relations(posts, { ... });

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

// db/client.ts
import * as schema from './schema';
export const db = drizzle(pool, { schema });

Naming Conventions

// ✅ Good: Consistent naming
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  firstName: text('first_name'),
  createdAt: timestamp('created_at'),
});

// ❌ Bad: Inconsistent naming
export const Users = pgTable('user', {
  ID: serial('userId').primaryKey(),
  first_name: text('firstname'),
});

Default Values

import { sql } from 'drizzle-orm';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  slug: text('slug').notNull(),
  views: integer('views').default(0),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').default(sql`CURRENT_TIMESTAMP`),
  uuid: uuid('uuid').defaultRandom(),
});

Performance Optimization

Connection pooling, query optimization, edge runtime integration, and performance best practices.

Connection Pooling

PostgreSQL (node-postgres)

import { Pool } from 'pg';
import { drizzle } from 'drizzle-orm/node-postgres';

const pool = new Pool({
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT || '5432'),
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20,                    // Maximum pool size
  idleTimeoutMillis: 30000,   // Close idle clients after 30s
  connectionTimeoutMillis: 2000, // Timeout connection attempts
});

export const db = drizzle(pool);

// Graceful shutdown
process.on('SIGTERM', async () => {
  await pool.end();
});

MySQL (mysql2)

import mysql from 'mysql2/promise';
import { drizzle } from 'drizzle-orm/mysql2';

const poolConnection = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  maxIdle: 10,
  idleTimeout: 60000,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
});

export const db = drizzle(poolConnection);

SQLite (better-sqlite3)

import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';

const sqlite = new Database('sqlite.db', {
  readonly: false,
  fileMustExist: false,
  timeout: 5000,
  verbose: console.log, // Remove in production
});

// Performance pragmas
sqlite.pragma('journal_mode = WAL');
sqlite.pragma('synchronous = normal');
sqlite.pragma('cache_size = -64000'); // 64MB cache
sqlite.pragma('temp_store = memory');

export const db = drizzle(sqlite);

process.on('exit', () => sqlite.close());

Query Optimization

Select Only Needed Columns

// ❌ Bad: Fetch all columns
const users = await db.select().from(users);

// ✅ Good: Fetch only needed columns
const users = await db.select({
  id: users.id,
  email: users.email,
  name: users.name,
}).from(users);

Use Indexes Effectively

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  city: text('city'),
  status: text('status'),
}, (table) => ({
  // Index frequently queried columns
  emailIdx: index('email_idx').on(table.email),

  // Composite index for common query patterns
  cityStatusIdx: index('city_status_idx').on(table.city, table.status),
}));

// Query uses index
const activeUsersInNYC = await db.select()
  .from(users)
  .where(and(
    eq(users.city, 'NYC'),
    eq(users.status, 'active')
  ));

Analyze Query Plans

import { sql } from 'drizzle-orm';

// PostgreSQL EXPLAIN
const plan = await db.execute(
  sql`EXPLAIN ANALYZE SELECT * FROM ${users} WHERE ${users.email} = 'user@example.com'`
);

console.log(plan.rows);

// Check for:
// - "Seq Scan" (bad) vs "Index Scan" (good)
// - Actual time vs estimated time
// - Rows removed by filter

Pagination Performance

// ❌ Bad: OFFSET on large datasets (gets slower as offset increases)
const page = await db.select()
  .from(users)
  .limit(20)
  .offset(10000); // Scans 10,020 rows!

// ✅ Good: Cursor-based pagination (constant time)
const page = await db.select()
  .from(users)
  .where(gt(users.id, lastSeenId))
  .orderBy(asc(users.id))
  .limit(20);

// ✅ Good: Seek method for timestamp-based pagination
const page = await db.select()
  .from(posts)
  .where(lt(posts.createdAt, lastSeenTimestamp))
  .orderBy(desc(posts.createdAt))
  .limit(20);

Edge Runtime Integration

Cloudflare Workers (D1)

import { drizzle } from 'drizzle-orm/d1';

export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const db = drizzle(env.DB);

    const users = await db.select().from(users).limit(10);

    return Response.json(users);
  },
};

Vercel Edge (Neon)

import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';

export const runtime = 'edge';

export async function GET() {
  const sql = neon(process.env.DATABASE_URL!);
  const db = drizzle(sql);

  const users = await db.select().from(users);

  return Response.json(users);
}

Supabase Edge Functions

import { createClient } from '@supabase/supabase-js';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

Deno.serve(async (req) => {
  const client = postgres(Deno.env.get('DATABASE_URL')!);
  const db = drizzle(client);

  const data = await db.select().from(users);

  return new Response(JSON.stringify(data), {
    headers: { 'Content-Type': 'application/json' },
  });
});

Caching Strategies

In-Memory Cache

import { LRUCache } from 'lru-cache';

const cache = new LRUCache<string, any>({
  max: 500,
  ttl: 1000 * 60 * 5, // 5 minutes
});

async function getCachedUser(id: number) {
  const key = `user:${id}`;
  const cached = cache.get(key);

  if (cached) return cached;

  const user = await db.select().from(users).where(eq(users.id, id));
  cache.set(key, user);

  return user;
}

Redis Cache Layer

import { Redis } from 'ioredis';

const redis = new Redis(process.env.REDIS_URL);

async function getCachedData<T>(
  key: string,
  fetcher: () => Promise<T>,
  ttl: number = 300
): Promise<T> {
  // Try cache first
  const cached = await redis.get(key);
  if (cached) return JSON.parse(cached);

  // Fetch from database
  const data = await fetcher();

  // Store in cache
  await redis.setex(key, ttl, JSON.stringify(data));

  return data;
}

// Usage
const users = await getCachedData(
  'users:all',
  () => db.select().from(users),
  600
);

Materialized Views (PostgreSQL)

// Create materialized view (via migration)
/*
CREATE MATERIALIZED VIEW user_stats AS
SELECT
  u.id,
  u.name,
  COUNT(p.id) AS post_count,
  COUNT(c.id) AS comment_count
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
LEFT JOIN comments c ON c.user_id = u.id
GROUP BY u.id;

CREATE UNIQUE INDEX ON user_stats (id);
*/

// Define schema
export const userStats = pgMaterializedView('user_stats').as((qb) =>
  qb.select({
    id: users.id,
    name: users.name,
    postCount: sql<number>`COUNT(${posts.id})`,
    commentCount: sql<number>`COUNT(${comments.id})`,
  })
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id))
  .leftJoin(comments, eq(comments.userId, users.id))
  .groupBy(users.id)
);

// Refresh materialized view
await db.execute(sql`REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats`);

// Query materialized view (fast!)
const stats = await db.select().from(userStats);

Batch Operations Optimization

Batch Insert with COPY (PostgreSQL)

import { copyFrom } from 'pg-copy-streams';
import { pipeline } from 'stream/promises';
import { Readable } from 'stream';

async function bulkInsert(data: any[]) {
  const client = await pool.connect();

  try {
    const stream = client.query(
      copyFrom(`COPY users (email, name) FROM STDIN WITH (FORMAT csv)`)
    );

    const input = Readable.from(
      data.map(row => `${row.email},${row.name}\n`)
    );

    await pipeline(input, stream);
  } finally {
    client.release();
  }
}

// 10x faster than batch INSERT for large datasets

Chunk Processing

async function* chunked<T>(array: T[], size: number) {
  for (let i = 0; i < array.length; i += size) {
    yield array.slice(i, i + size);
  }
}

async function bulkUpdate(updates: { id: number; name: string }[]) {
  for await (const chunk of chunked(updates, 100)) {
    await db.transaction(async (tx) => {
      for (const update of chunk) {
        await tx.update(users)
          .set({ name: update.name })
          .where(eq(users.id, update.id));
      }
    });
  }
}

Connection Management

Serverless Optimization

// ❌ Bad: New connection per request
export async function handler() {
  const pool = new Pool({ connectionString: process.env.DATABASE_URL });
  const db = drizzle(pool);

  const users = await db.select().from(users);

  await pool.end();
  return users;
}

// ✅ Good: Reuse connection across warm starts
let cachedDb: ReturnType<typeof drizzle> | null = null;

export async function handler() {
  if (!cachedDb) {
    const pool = new Pool({
      connectionString: process.env.DATABASE_URL,
      max: 1, // Serverless: single connection per instance
    });
    cachedDb = drizzle(pool);
  }

  const users = await cachedDb.select().from(users);
  return users;
}

HTTP-based Databases (Neon, Turso)

// No connection pooling needed - uses HTTP
import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';

const sql = neon(process.env.DATABASE_URL!);
const db = drizzle(sql);

// Each query is a single HTTP request
const users = await db.select().from(users);

Read Replicas

import { Pool } from 'pg';
import { drizzle } from 'drizzle-orm/node-postgres';

// Primary (writes)
const primaryPool = new Pool({ connectionString: process.env.PRIMARY_DB_URL });
const primaryDb = drizzle(primaryPool);

// Replica (reads)
const replicaPool = new Pool({ connectionString: process.env.REPLICA_DB_URL });
const replicaDb = drizzle(replicaPool);

// Route queries appropriately
async function getUsers() {
  return replicaDb.select().from(users); // Read from replica
}

async function createUser(data: NewUser) {
  return primaryDb.insert(users).values(data).returning(); // Write to primary
}

Monitoring & Profiling

Query Logging

import { drizzle } from 'drizzle-orm/node-postgres';

const db = drizzle(pool, {
  logger: {
    logQuery(query: string, params: unknown[]) {
      console.log('Query:', query);
      console.log('Params:', params);
      console.time('query');
    },
  },
});

// Custom logger with metrics
class MetricsLogger {
  private queries: Map<string, { count: number; totalTime: number }> = new Map();

  logQuery(query: string) {
    const start = Date.now();

    return () => {
      const duration = Date.now() - start;
      const stats = this.queries.get(query) || { count: 0, totalTime: 0 };

      this.queries.set(query, {
        count: stats.count + 1,
        totalTime: stats.totalTime + duration,
      });

      if (duration > 1000) {
        console.warn(`Slow query (${duration}ms):`, query);
      }
    };
  }

  getStats() {
    return Array.from(this.queries.entries()).map(([query, stats]) => ({
      query,
      count: stats.count,
      avgTime: stats.totalTime / stats.count,
    }));
  }
}

Performance Monitoring

import { performance } from 'perf_hooks';

async function measureQuery<T>(
  name: string,
  query: Promise<T>
): Promise<T> {
  const start = performance.now();

  try {
    const result = await query;
    const duration = performance.now() - start;

    console.log(`[${name}] completed in ${duration.toFixed(2)}ms`);

    return result;
  } catch (error) {
    const duration = performance.now() - start;
    console.error(`[${name}] failed after ${duration.toFixed(2)}ms`, error);
    throw error;
  }
}

// Usage
const users = await measureQuery(
  'fetchUsers',
  db.select().from(users).limit(100)
);

Database-Specific Optimizations

PostgreSQL

// Connection optimization
const pool = new Pool({
  max: 20,
  application_name: 'myapp',
  statement_timeout: 30000, // 30s query timeout
  query_timeout: 30000,
  connectionTimeoutMillis: 5000,
  idle_in_transaction_session_timeout: 10000,
});

// Session optimization
await db.execute(sql`SET work_mem = '256MB'`);
await db.execute(sql`SET maintenance_work_mem = '512MB'`);
await db.execute(sql`SET effective_cache_size = '4GB'`);

MySQL

const pool = mysql.createPool({
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
  dateStrings: false,
  supportBigNumbers: true,
  bigNumberStrings: false,
  multipleStatements: false, // Security
  timezone: 'Z', // UTC
});

SQLite

// WAL mode for concurrent reads
sqlite.pragma('journal_mode = WAL');

// Optimize for performance
sqlite.pragma('synchronous = NORMAL');
sqlite.pragma('cache_size = -64000'); // 64MB
sqlite.pragma('temp_store = MEMORY');
sqlite.pragma('mmap_size = 30000000000'); // 30GB mmap

// Disable for bulk inserts
const stmt = sqlite.prepare('INSERT INTO users (email, name) VALUES (?, ?)');

const insertMany = sqlite.transaction((users) => {
  for (const user of users) {
    stmt.run(user.email, user.name);
  }
});

insertMany(users); // 100x faster than individual inserts

Best Practices Summary

1. Always use connection pooling in long-running processes 2. Select only needed columns to reduce network transfer 3. Add indexes on frequently queried columns and foreign keys 4. Use cursor-based pagination instead of OFFSET for large datasets 5. Batch operations when inserting/updating multiple records 6. Cache expensive queries with appropriate TTL 7. Monitor slow queries and optimize with EXPLAIN ANALYZE 8. Use prepared statements for frequently executed queries 9. Implement read replicas for high-traffic read operations 10. Use HTTP-based databases (Neon, Turso) for edge/serverless

Query Patterns

Advanced querying techniques, subqueries, CTEs, and raw SQL in Drizzle ORM.

Subqueries

SELECT Subqueries

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

// Scalar subquery
const avgPrice = db.select({ value: avg(products.price) }).from(products);

const expensiveProducts = await db
  .select()
  .from(products)
  .where(gt(products.price, avgPrice));

// Correlated subquery
const authorsWithPostCount = await db
  .select({
    author: authors,
    postCount: sql<number>`(
      SELECT COUNT(*)
      FROM ${posts}
      WHERE ${posts.authorId} = ${authors.id}
    )`,
  })
  .from(authors);

EXISTS Subqueries

// Find authors with posts
const authorsWithPosts = await db
  .select()
  .from(authors)
  .where(
    sql`EXISTS (
      SELECT 1
      FROM ${posts}
      WHERE ${posts.authorId} = ${authors.id}
    )`
  );

// Find authors without posts
const authorsWithoutPosts = await db
  .select()
  .from(authors)
  .where(
    sql`NOT EXISTS (
      SELECT 1
      FROM ${posts}
      WHERE ${posts.authorId} = ${authors.id}
    )`
  );

IN Subqueries

// Find users who commented
const usersWhoCommented = await db
  .select()
  .from(users)
  .where(
    sql`${users.id} IN (
      SELECT DISTINCT ${comments.userId}
      FROM ${comments}
    )`
  );

Common Table Expressions (CTEs)

Basic CTE

import { sql } from 'drizzle-orm';

const topAuthors = db.$with('top_authors').as(
  db.select({
    id: authors.id,
    name: authors.name,
    postCount: sql<number>`COUNT(${posts.id})`.as('post_count'),
  })
    .from(authors)
    .leftJoin(posts, eq(authors.id, posts.authorId))
    .groupBy(authors.id)
    .having(sql`COUNT(${posts.id}) > 10`)
);

const result = await db
  .with(topAuthors)
  .select()
  .from(topAuthors);

Recursive CTE

// Organizational hierarchy
export const employees = pgTable('employees', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  managerId: integer('manager_id').references((): AnyPgColumn => employees.id),
});

const employeeHierarchy = db.$with('employee_hierarchy').as(
  db.select({
    id: employees.id,
    name: employees.name,
    managerId: employees.managerId,
    level: sql<number>`1`.as('level'),
  })
    .from(employees)
    .where(isNull(employees.managerId))
    .unionAll(
      db.select({
        id: employees.id,
        name: employees.name,
        managerId: employees.managerId,
        level: sql<number>`employee_hierarchy.level + 1`,
      })
        .from(employees)
        .innerJoin(
          sql`employee_hierarchy`,
          sql`${employees.managerId} = employee_hierarchy.id`
        )
    )
);

const hierarchy = await db
  .with(employeeHierarchy)
  .select()
  .from(employeeHierarchy);

Multiple CTEs

const activeUsers = db.$with('active_users').as(
  db.select().from(users).where(eq(users.isActive, true))
);

const recentPosts = db.$with('recent_posts').as(
  db.select().from(posts).where(gt(posts.createdAt, sql`NOW() - INTERVAL '30 days'`))
);

const result = await db
  .with(activeUsers, recentPosts)
  .select({
    user: activeUsers,
    post: recentPosts,
  })
  .from(activeUsers)
  .leftJoin(recentPosts, eq(activeUsers.id, recentPosts.authorId));

Raw SQL

Safe Raw Queries

import { sql } from 'drizzle-orm';

// Parameterized query (safe from SQL injection)
const userId = 123;
const user = await db.execute(
  sql`SELECT * FROM ${users} WHERE ${users.id} = ${userId}`
);

// Raw SQL with type safety
const result = await db.execute<{ count: number }>(
  sql`SELECT COUNT(*) as count FROM ${users}`
);

SQL Template Composition

// Reusable SQL fragments
function whereActive() {
  return sql`${users.isActive} = true`;
}

function whereRole(role: string) {
  return sql`${users.role} = ${role}`;
}

// Compose fragments
const admins = await db
  .select()
  .from(users)
  .where(sql`${whereActive()} AND ${whereRole('admin')}`);

Dynamic WHERE Clauses

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

interface Filters {
  name?: string;
  role?: string;
  isActive?: boolean;
}

function buildFilters(filters: Filters): SQL | undefined {
  const conditions: SQL[] = [];

  if (filters.name) {
    conditions.push(like(users.name, `%${filters.name}%`));
  }

  if (filters.role) {
    conditions.push(eq(users.role, filters.role));
  }

  if (filters.isActive !== undefined) {
    conditions.push(eq(users.isActive, filters.isActive));
  }

  return conditions.length > 0 ? and(...conditions) : undefined;
}

// Usage
const filters: Filters = { name: 'John', isActive: true };
const users = await db
  .select()
  .from(users)
  .where(buildFilters(filters));

Aggregations

Basic Aggregates

import { count, sum, avg, min, max, sql } from 'drizzle-orm';

// Count
const userCount = await db.select({ count: count() }).from(users);

// Sum
const totalRevenue = await db.select({ total: sum(orders.amount) }).from(orders);

// Average
const avgPrice = await db.select({ avg: avg(products.price) }).from(products);

// Multiple aggregates
const stats = await db
  .select({
    count: count(),
    total: sum(orders.amount),
    avg: avg(orders.amount),
    min: min(orders.amount),
    max: max(orders.amount),
  })
  .from(orders);

GROUP BY with HAVING

// Authors with more than 5 posts
const prolificAuthors = await db
  .select({
    author: authors.name,
    postCount: count(posts.id),
  })
  .from(authors)
  .leftJoin(posts, eq(authors.id, posts.authorId))
  .groupBy(authors.id)
  .having(sql`COUNT(${posts.id}) > 5`);

Window Functions

// Rank products by price within category
const rankedProducts = await db
  .select({
    product: products,
    priceRank: sql<number>`RANK() OVER (PARTITION BY ${products.categoryId} ORDER BY ${products.price} DESC)`,
  })
  .from(products);

// Running total
const ordersWithRunningTotal = await db
  .select({
    order: orders,
    runningTotal: sql<number>`SUM(${orders.amount}) OVER (ORDER BY ${orders.createdAt})`,
  })
  .from(orders);

// Row number
const numberedUsers = await db
  .select({
    user: users,
    rowNum: sql<number>`ROW_NUMBER() OVER (ORDER BY ${users.createdAt})`,
  })
  .from(users);

Prepared Statements

Reusable Queries

// Prepare once, execute many times
const getUserById = db
  .select()
  .from(users)
  .where(eq(users.id, sql.placeholder('id')))
  .prepare('get_user_by_id');

// Execute with different parameters
const user1 = await getUserById.execute({ id: 1 });
const user2 = await getUserById.execute({ id: 2 });

// Complex prepared statement
const searchUsers = db
  .select()
  .from(users)
  .where(
    and(
      like(users.name, sql.placeholder('name')),
      eq(users.role, sql.placeholder('role'))
    )
  )
  .prepare('search_users');

const admins = await searchUsers.execute({ name: '%John%', role: 'admin' });

Batch Operations

Batch Insert

// Insert multiple rows
const newUsers = await db.insert(users).values([
  { email: 'user1@example.com', name: 'User 1' },
  { email: 'user2@example.com', name: 'User 2' },
  { email: 'user3@example.com', name: 'User 3' },
]).returning();

// Batch with onConflictDoNothing
await db.insert(users).values(bulkUsers).onConflictDoNothing();

// Batch with onConflictDoUpdate (upsert)
await db.insert(users)
  .values(bulkUsers)
  .onConflictDoUpdate({
    target: users.email,
    set: { name: sql`EXCLUDED.name` },
  });

Batch Update

// Update multiple specific rows
await db.transaction(async (tx) => {
  for (const update of updates) {
    await tx.update(users)
      .set({ name: update.name })
      .where(eq(users.id, update.id));
  }
});

// Bulk update with CASE
await db.execute(sql`
  UPDATE ${users}
  SET ${users.role} = CASE ${users.id}
    ${sql.join(
      updates.map((u) => sql`WHEN ${u.id} THEN ${u.role}`),
      sql.raw(' ')
    )}
  END
  WHERE ${users.id} IN (${sql.join(updates.map((u) => u.id), sql.raw(', '))})
`);

Batch Delete

// Delete multiple IDs
await db.delete(users).where(inArray(users.id, [1, 2, 3, 4, 5]));

// Conditional batch delete
await db.delete(posts).where(
  and(
    lt(posts.createdAt, new Date('2023-01-01')),
    eq(posts.isDraft, true)
  )
);

LATERAL Joins

// Get top 3 posts for each author
const authorsWithTopPosts = await db
  .select({
    author: authors,
    post: posts,
  })
  .from(authors)
  .leftJoin(
    sql`LATERAL (
      SELECT * FROM ${posts}
      WHERE ${posts.authorId} = ${authors.id}
      ORDER BY ${posts.views} DESC
      LIMIT 3
    ) AS ${posts}`,
    sql`true`
  );

UNION Queries

// Combine results from multiple queries
const allContent = await db
  .select({ id: posts.id, title: posts.title, type: sql<string>`'post'` })
  .from(posts)
  .union(
    db.select({ id: articles.id, title: articles.title, type: sql<string>`'article'` })
      .from(articles)
  );

// UNION ALL (includes duplicates)
const allItems = await db
  .select({ id: products.id, name: products.name })
  .from(products)
  .unionAll(
    db.select({ id: services.id, name: services.name }).from(services)
  );

Distinct Queries

// DISTINCT
const uniqueRoles = await db.selectDistinct({ role: users.role }).from(users);

// DISTINCT ON (PostgreSQL)
const latestPostPerAuthor = await db
  .selectDistinctOn([posts.authorId], {
    post: posts,
  })
  .from(posts)
  .orderBy(posts.authorId, desc(posts.createdAt));

Locking Strategies

// FOR UPDATE (pessimistic locking)
await db.transaction(async (tx) => {
  const user = await tx
    .select()
    .from(users)
    .where(eq(users.id, userId))
    .for('update');

  // Critical section - user row is locked
  await tx.update(users)
    .set({ balance: user.balance - amount })
    .where(eq(users.id, userId));
});

// FOR SHARE (shared lock)
const user = await db
  .select()
  .from(users)
  .where(eq(users.id, userId))
  .for('share');

// SKIP LOCKED
const availableTask = await db
  .select()
  .from(tasks)
  .where(eq(tasks.status, 'pending'))
  .limit(1)
  .for('update', { skipLocked: true });

Query Builder Patterns

Type-Safe Query Builder

class UserQueryBuilder {
  private query = db.select().from(users);

  whereRole(role: string) {
    this.query = this.query.where(eq(users.role, role));
    return this;
  }

  whereActive() {
    this.query = this.query.where(eq(users.isActive, true));
    return this;
  }

  orderByCreated() {
    this.query = this.query.orderBy(desc(users.createdAt));
    return this;
  }

  async execute() {
    return await this.query;
  }
}

// Usage
const admins = await new UserQueryBuilder()
  .whereRole('admin')
  .whereActive()
  .orderByCreated()
  .execute();

Best Practices

Avoid N+1 Queries

// ❌ Bad: N+1 query
const authors = await db.select().from(authors);
for (const author of authors) {
  author.posts = await db.select().from(posts).where(eq(posts.authorId, author.id));
}

// ✅ Good: Single query with join
const authorsWithPosts = await db.query.authors.findMany({
  with: { posts: true },
});

// ✅ Good: Dataloader pattern
import DataLoader from 'dataloader';

const postLoader = new DataLoader(async (authorIds: number[]) => {
  const posts = await db.select().from(posts).where(inArray(posts.authorId, authorIds));

  const grouped = authorIds.map(id =>
    posts.filter(post => post.authorId === id)
  );

  return grouped;
});

Query Timeouts

// PostgreSQL statement timeout
await db.execute(sql`SET statement_timeout = '5s'`);

// Per-query timeout
const withTimeout = async <T>(promise: Promise<T>, ms: number): Promise<T> => {
  const timeout = new Promise<never>((_, reject) =>
    setTimeout(() => reject(new Error('Query timeout')), ms)
  );
  return Promise.race([promise, timeout]);
};

const users = await withTimeout(
  db.select().from(users),
  5000
);

Drizzle vs Prisma Comparison

Feature comparison, migration guide, and decision framework for choosing between Drizzle and Prisma.

Quick Comparison

When to Choose Drizzle

✅ Choose Drizzle if you need:

1. Performance-critical applications

• Microservices with tight latency requirements
• High-throughput APIs (>10K req/s)
• Serverless/edge functions with cold start concerns

2. Minimal bundle size

• Client-side database (SQLite in browser)
• Edge runtime deployments
• Mobile applications with bundle constraints

3. SQL control

• Complex queries with CTEs, window functions
• Raw SQL for specific database features
• Database-specific optimizations

4. Type inference over generation

• No build step for type generation
• Immediate TypeScript feedback
• Schema changes reflected instantly

Example: Edge Function with Drizzle

import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';

export const runtime = 'edge';

export async function GET() {
  const sql = neon(process.env.DATABASE_URL!);
  const db = drizzle(sql); // ~35KB bundle, <10ms cold start

  const users = await db.select().from(users);
  return Response.json(users);
}

When to Choose Prisma

✅ Choose Prisma if you need:

1. Rapid prototyping

• Quick schema iterations
• Automatic migrations
• Less SQL knowledge required

2. Team with varied SQL experience

• Abstracted query interface
• Declarative migrations
• Generated documentation

3. Mature ecosystem

• Extensive community resources
• Third-party integrations (Nexus, tRPC)
• Enterprise support options

4. Rich developer experience

• Prisma Studio (GUI)
• VS Code extension
• Comprehensive documentation

Example: Next.js App with Prisma

// schema.prisma
model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  posts Post[]
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  authorId Int
  author   User   @relation(fields: [authorId], references: [id])
}

// app/api/users/route.ts
import { prisma } from '@/lib/prisma';

export async function GET() {
  const users = await prisma.user.findMany({
    include: { posts: true },
  });
  return Response.json(users);
}

Feature Comparison

Schema Definition

Drizzle (TypeScript-first):

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

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

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

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

Prisma (Schema DSL):

model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  posts Post[]
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  authorId Int
  author   User   @relation(fields: [authorId], references: [id])
}

Querying

Drizzle (SQL-like):

import { eq, like, and, gt } from 'drizzle-orm';

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

// Complex filtering
const results = await db.select()
  .from(users)
  .where(
    and(
      like(users.email, '%@example.com'),
      gt(users.createdAt, new Date('2024-01-01'))
    )
  );

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

Prisma (Fluent API):

// Simple query
const user = await prisma.user.findUnique({ where: { id: 1 } });

// Complex filtering
const results = await prisma.user.findMany({
  where: {
    email: { endsWith: '@example.com' },
    createdAt: { gt: new Date('2024-01-01') },
  },
});

// Relations
const usersWithPosts = await prisma.user.findMany({
  include: { posts: true },
});

Migrations

Drizzle (SQL-based):

# Generate migration
npx drizzle-kit generate

# Output: drizzle/0000_migration.sql
# CREATE TABLE "users" (
#   "id" serial PRIMARY KEY,
#   "email" text NOT NULL UNIQUE
# );

# Apply migration
npx drizzle-kit migrate

Prisma (Declarative):

# Generate and apply migration
npx prisma migrate dev --name add_users

# Prisma compares schema.prisma to database
# Generates SQL automatically
# Applies migration

Type Generation

Drizzle (Inferred):

// Types are inferred at compile time
type User = typeof users.$inferSelect;
type NewUser = typeof users.$inferInsert;

// Immediate feedback in IDE
const user: User = await db.select().from(users);

Prisma (Generated):

// Types generated after schema change
// Run: npx prisma generate

import { User, Post } from '@prisma/client';

const user: User = await prisma.user.findUnique({ where: { id: 1 } });

Raw SQL

Drizzle (First-class):

import { sql } from 'drizzle-orm';

// Tagged template with type safety
const result = await db.execute(
  sql`SELECT * FROM ${users} WHERE ${users.email} = ${email}`
);

// Mix ORM and raw SQL
const customQuery = await db
  .select({
    user: users,
    postCount: sql<number>`COUNT(${posts.id})`,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .groupBy(users.id);

Prisma (Limited):

// Raw query (loses type safety)
const result = await prisma.$queryRaw`
  SELECT * FROM users WHERE email = ${email}
`;

// Typed raw query (manual type annotation)
const users = await prisma.$queryRaw<User[]>`
  SELECT * FROM users
`;

Performance Benchmarks

Query Execution Time (1000 queries)

Bundle Size Impact

# Next.js production build

# With Drizzle
├─ Client (First Load JS)
│  └─ pages/index.js: 85 KB (+35KB Drizzle)

# With Prisma
├─ Client (First Load JS)
│  └─ pages/index.js: 280 KB (+230KB Prisma)

Cold Start Times (AWS Lambda)

Migration from Prisma to Drizzle

Step 1: Install Drizzle

npm install drizzle-orm
npm install -D drizzle-kit

# Keep Prisma temporarily
# npm uninstall prisma @prisma/client

Step 2: Introspect Existing Database

// 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;

# Generate Drizzle schema from existing database
npx drizzle-kit introspect

Step 3: Convert Queries

Prisma:

// Before (Prisma)
const users = await prisma.user.findMany({
  where: { email: { contains: 'example.com' } },
  include: { posts: true },
  orderBy: { createdAt: 'desc' },
  take: 10,
});

Drizzle:

// After (Drizzle)
import { like, desc } from 'drizzle-orm';

const users = await db.query.users.findMany({
  where: like(users.email, '%example.com%'),
  with: { posts: true },
  orderBy: [desc(users.createdAt)],
  limit: 10,
});

// Or SQL-style
const users = await db
  .select()
  .from(users)
  .where(like(users.email, '%example.com%'))
  .orderBy(desc(users.createdAt))
  .limit(10);

Step 4: Conversion Patterns

// Prisma → Drizzle mapping

// findUnique
await prisma.user.findUnique({ where: { id: 1 } });
await db.select().from(users).where(eq(users.id, 1));

// findMany with filters
await prisma.user.findMany({ where: { role: 'admin' } });
await db.select().from(users).where(eq(users.role, 'admin'));

// create
await prisma.user.create({ data: { email: 'user@example.com' } });
await db.insert(users).values({ email: 'user@example.com' }).returning();

// update
await prisma.user.update({ where: { id: 1 }, data: { name: 'John' } });
await db.update(users).set({ name: 'John' }).where(eq(users.id, 1));

// delete
await prisma.user.delete({ where: { id: 1 } });
await db.delete(users).where(eq(users.id, 1));

// count
await prisma.user.count();
await db.select({ count: count() }).from(users);

// aggregate
await prisma.post.aggregate({ _avg: { views: true } });
await db.select({ avg: avg(posts.views) }).from(posts);

Step 5: Test & Remove Prisma

# Run tests with Drizzle
npm test

# Remove Prisma when confident
npm uninstall prisma @prisma/client
rm -rf prisma/

Decision Matrix

Hybrid Approach

You can use both in the same project:

// Use Drizzle for performance-critical paths
import { db as drizzleDb } from './lib/drizzle';

export async function GET() {
  const users = await drizzleDb.select().from(users);
  return Response.json(users);
}

// Use Prisma for admin dashboards (less performance-critical)
import { prisma } from './lib/prisma';

export async function getStaticProps() {
  const stats = await prisma.user.aggregate({
    _count: true,
    _avg: { posts: true },
  });
  return { props: { stats } };
}

Community & Resources

Drizzle

• Docs: orm.drizzle.team
• Discord: drizzle.team/discord
• GitHub: drizzle-team/drizzle-orm

Prisma

• Docs: prisma.io/docs
• Discord: pris.ly/discord
• GitHub: prisma/prisma

Final Recommendation

Choose Drizzle for:

• Greenfield projects prioritizing performance
• Edge/serverless applications
• Teams comfortable with SQL
• Minimal bundle size requirements

Choose Prisma for:

• Established teams with Prisma experience
• Rapid MVP development
• Teams new to databases
• Reliance on Prisma ecosystem (Nexus, etc.)

Consider migration when:

• Performance becomes a bottleneck
• Bundle size impacts user experience
• Edge runtime deployment needed
• Team SQL proficiency increases