Drizzle ORM

Drizzle ORM is a lightweight, headless ORM for TypeScript and JavaScript. It provides type-safe database queries with zero dependencies at ~7.4kb minified+gzipped. Drizzle supports PostgreSQL, MySQL, SQLite (including serverless options like Cloudflare D1, Turso, Neon, and PlanetScale) and works across Node.js, Bun, Deno, Cloudflare Workers, and edge runtimes. It offers both SQL-like query builders and relational query syntax while generating migrations automatically via Drizzle Kit.

Quick References

File	Purpose
drizzle-orm/src/index.ts	Main library entry point with core exports
drizzle-orm/src/pg-core/index.ts	PostgreSQL core dialect with query builders
drizzle-orm/src/sqlite-core/index.ts	SQLite core dialect with query builders
drizzle-orm/src/mysql-core/index.ts	MySQL core dialect with query builders
drizzle-orm/src/sql/expressions/conditions.ts	Query operators (eq, and, or, etc.)
drizzle-orm/src/sql/functions/aggregate.ts	SQL aggregate functions (count, sum, avg)
README.md	Project overview and documentation links

Packages

Package	npm name	Description
drizzle-orm	drizzle-orm	Core ORM library - install database driver separately
drizzle-kit	drizzle-kit	CLI companion for automatic SQL migration generation
drizzle-zod	drizzle-zod	Generate Zod validation schemas from Drizzle schemas
drizzle-valibot	drizzle-valibot	Generate Valibot validation schemas from Drizzle schemas
drizzle-typebox	drizzle-typebox	Generate Typebox validation schemas from Drizzle schemas
drizzle-arktype	drizzle-arktype	Generate Arktype validation schemas from Drizzle schemas
drizzle-seed	drizzle-seed	Deterministic fake data seeding for testing

When to Use

• Building TypeScript projects requiring type-safe database access
• Using serverless databases (Neon, Supabase, PlanetScale, Turso, Cloudflare D1)
• Needing automatic SQL migration generation without manual SQL files
• Prefer SQL-like APIs over complex ORM abstractions
• Want tree-shakable, dependency-free database layer
• Building for edge runtimes (Cloudflare Workers, Vercel Edge Functions)
• Need lightweight but powerful query building capabilities
• Working with PostgreSQL, MySQL, or SQLite in Node.js, Bun, or Deno

Installation

# Core package with PostgreSQL driver
npm install drizzle-orm postgres

# Core package with MySQL driver
npm install drizzle-orm mysql2

# Core package with SQLite driver (better-sqlite3)
npm install drizzle-orm better-sqlite3

# CLI for migrations
npm install -D drizzle-kit

Install database-specific drivers:

# PostgreSQL (node-postgres)
npm install pg

# PostgreSQL (postgres.js)
npm install postgres

# MySQL
npm install mysql2

# SQLite (better-sqlite3)
npm install better-sqlite3

# Cloudflare D1 (no driver needed)
# Vercel Postgres
npm install @vercel/postgres

# Neon
npm install @neondatabase/serverless

# Turso
npm install @libsql/client

Best Practices

1. Export table schemas for drizzle-kit migrations: always use export const table = ... in schema files
2. Use $type() method for branded/custom column types when needed
3. Pass all database config to drizzle() constructor: avoid partial config options
4. Use db.transaction() for multi-step operations requiring atomicity
5. Leverage InferModel and InferModel<typeof table, 'insert'> for type inference
6. Use prepared queries with .prepare() for repeated queries with better performance
7. Configure logger via logger: true or custom logger for query debugging
8. Use $cache plugin for query caching in read-heavy workloads
9. Enable RLS (Row Level Security) for PostgreSQL with .enableRLS() on table creation
10. Use aggregate functions (count(), sum(), avg()) through imported function exports

Common Patterns

Schema Declaration:

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
}, (t) => ({
  emailIdx: index('email_idx').on(t.email),
}));

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

Database Connection (PostgreSQL with postgres.js):

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

const client = postgres(process.env.DATABASE_URL!);
const db = drizzle(client);

Database Connection (SQLite with better-sqlite3):

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

const sqlite = new Database('sqlite.db');
const db = drizzle(sqlite);

Select Queries:

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

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

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

// Multiple conditions
const filteredUsers = await db.select().from(users)
  .where(and(eq(users.name, 'John'), eq(users.email, 'john@test.com')));

// Order and limit
const orderedUsers = await db.select().from(users)
  .orderBy(desc(users.created_at))
  .limit(10);

Insert:

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

// Insert multiple records
await db.insert(users)
  .values([
    { name: 'John Doe', email: 'john@test.com' },
    { name: 'Jane Doe', email: 'jane@test.com' }
  ]);

Update:

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

Delete:

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

Transactions:

await db.transaction(async (tx) => {
  const newPost = await tx.insert(posts)
    .values({ title: 'Hello', authorId: 1 })
    .returning();
  // other operations
});

Aggregates:

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

const userCount = await db.select({ count: count() }).from(users);
const total = await db.select({ total: sum(posts.views) }).from(posts);
const average = await db.select({ avg: avg(posts.rating) }).from(posts);

Join Queries:

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

Prepared Queries:

const userQuery = db.select().from(users)
  .where(eq(users.id, placeholder('id')))
  .prepare();

const user1 = await userQuery.execute({ id: 1 });
const user2 = await userQuery.execute({ id: 2 });

Schema Type Inference:

import { InferModel } from 'drizzle-orm';

type User = InferModel<typeof users>;
type NewUser = InferModel<typeof users, 'insert'>;
type UpdateUser = Partial<NewUser>;

Migrations with drizzle-kit:

# Generate migrations
drizzle-kit generate --out migrations-folder --schema src/db/schema.ts

# Push schema directly (development)
drizzle-kit push --schema src/db/schema.ts

API Quick Reference

Export	Type	Description
drizzle()	Function	Creates database instance with driver connection
pgTable()	Function	Declare PostgreSQL table schema
sqliteTable()	Function	Declare SQLite table schema
mysqlTable()	Function	Declare MySQL table schema
eq()	Function	Equality comparison operator
ne()	Function	Not equal comparison operator
and()	Function	Logical AND condition combiner
or()	Function	Logical OR condition combiner
gt() / gte()	Function	Greater than / greater than or equal
lt() / lte()	Function	Less than / less than or equal
like() / ilike()	Function	Pattern matching operators
inArray()	Function	IN clause for array values
isNull() / isNotNull()	Function	NULL check operators
desc() / asc()	Function	Order direction modifiers
count()	Function	COUNT aggregate function
sum()	Function	SUM aggregate function
avg()	Function	AVG aggregate function
sql	Class	Raw SQL template literal
placeholder()	Function	SQL placeholder for prepared queries
InferModel	Type helper	Infer TypeScript types from table schema
primaryKey()	Function	Mark column as primary key
foreignKey()	Function	Define foreign key constraint
index() / uniqueIndex()	Function	Create indexes
references()	Method	Reference another table column