Introduction
The TypeScript ORM landscape has undergone a significant transformation. For years, developers chose between heavyweight ORMs like TypeORM and Sequelize that carried Java-inspired patterns into JavaScript, or raw SQL that sacrificed type safety for control. Prisma emerged as a compelling middle ground with its declarative schema and auto-generated client, but introduced its own trade-offs: a Rust-based query engine that adds deployment complexity, a custom query language that differs from SQL, and cold start penalties in serverless environments.
Drizzle ORM represents the next evolution in this space. It takes a fundamentally different approach by embracing SQL as the query language while wrapping it in TypeScript's type system. There is no query engine binary, no code generation step, and no custom DSL to learn. You write TypeScript that maps directly to the SQL you would write by hand, and the type system ensures correctness at compile time.
This guide provides a comprehensive exploration of Drizzle ORM, covering schema design patterns, query construction, migration management, and integration with modern TypeScript frameworks. Whether you are evaluating Drizzle for a new project or migrating from another ORM, you will gain the knowledge to use it effectively in production.
Understanding Drizzle ORM: Core Concepts
Why Drizzle Exists
The fundamental tension in TypeScript database access is between type safety and developer experience. Raw SQL offers maximum control but no type safety—your queries are strings that the compiler cannot validate. Traditional ORMs provide type safety through class hierarchies and decorators, but these abstractions leak in complex scenarios and add runtime overhead. Prisma improved on this with a declarative schema and generated types, but the query engine binary and custom DSL create friction for teams comfortable with SQL.
Drizzle resolves this tension by making TypeScript's type system work directly with SQL. The schema definition is a TypeScript object that Drizzle uses to infer types for queries. There is no intermediate representation, no generated code to inspect, and no runtime reflection. The result is an ORM that feels like writing SQL but catches errors at compile time.
The Schema Definition Model
Drizzle schemas are TypeScript objects that map directly to database tables. Each column definition specifies the database column name, type, and constraints. TypeScript infers the row type from the schema, providing type safety without explicit type annotations.
import { pgTable, serial, text, timestamp, integer, boolean, jsonb } from 'drizzle-orm/pg-core';
export const articles = pgTable('articles', {
id: serial('id').primaryKey(),
title: text('title').notNull(),
slug: text('slug').notNull().unique(),
content: text('content').notNull(),
excerpt: text('excerpt'),
authorId: integer('author_id').references(() => users.id).notNull(),
isPublished: boolean('is_published').default(false).notNull(),
metadata: jsonb('metadata').$type<{ views: number; likes: number }>(),
createdAt: timestamp('created_at').defaultNow().notNull(),
updatedAt: timestamp('updated_at').defaultNow().notNull(),
});
// Types inferred automatically
type Article = typeof articles.$inferSelect; // { id: number; title: string; ... }
type NewArticle = typeof articles.$inferInsert; // { title: string; content: string; id?: number; ... }Database Support
Drizzle supports three database families with dialect-specific APIs:
- PostgreSQL:
pgTable,pgEnum,pgSchemafor full PostgreSQL feature support including arrays, JSONB, and custom types - MySQL:
mysqlTable,mysqlEnum,mysqlViewfor MySQL-specific features - SQLite:
sqliteTable,sqliteViewfor lightweight and embedded use cases
Each dialect's API surfaces the specific features of that database rather than abstracting them into a lowest-common-denominator interface.
Drizzle Kit: The Migration Companion
Drizzle Kit handles schema diffing and migration generation. It compares your TypeScript schema against the actual database state and generates the SQL needed to synchronize them.
# Generate a migration from schema changes
npx drizzle-kit generate --name add_user_avatar
# Apply pending migrations
npx drizzle-kit migrate
# Push schema directly to database (development only, skips migration files)
npx drizzle-kit push
# Open Drizzle Studio for visual database browsing
npx drizzle-kit studio
# Pull existing database schema into TypeScript (introspection)
npx drizzle-kit introspectArchitecture and Design Patterns
Connection Management
Drizzle wraps your chosen database driver, adding type safety and query building without replacing the driver's connection management.
// src/db/index.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,
max: 20,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
});
export const db = drizzle(pool, { schema });
// For serverless environments (Neon)
import { drizzle as drizzleNeon } from 'drizzle-orm/neon-http';
export const serverlessDb = drizzleNeon(process.env.DATABASE_URL!, { schema });Modular Schema Design
Large applications benefit from splitting schemas by domain. Drizzle supports this through module imports and a centralized export.
// src/schema/users.ts
import { pgTable, serial, text, timestamp, pgEnum } from 'drizzle-orm/pg-core';
export const userRole = pgEnum('user_role', ['viewer', 'editor', 'admin']);
export const users = pgTable('users', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
email: text('email').notNull().unique(),
role: userRole('role').default('viewer').notNull(),
createdAt: timestamp('created_at').defaultNow().notNull(),
});
export const sessions = pgTable('sessions', {
id: text('id').primaryKey(),
userId: serial('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
expiresAt: timestamp('expires_at').notNull(),
});// src/schema/content.ts
import { pgTable, serial, text, timestamp, integer, boolean, index } from 'drizzle-orm/pg-core';
import { users } from './users';
export const categories = pgTable('categories', {
id: serial('id').primaryKey(),
name: text('name').notNull().unique(),
slug: text('slug').notNull().unique(),
});
export const articles = pgTable('articles', {
id: serial('id').primaryKey(),
title: text('title').notNull(),
slug: text('slug').notNull().unique(),
body: text('body').notNull(),
categoryId: integer('category_id').references(() => categories.id),
authorId: integer('author_id').references(() => users.id).notNull(),
isPublished: boolean('is_published').default(false).notNull(),
createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
authorIdx: index('articles_author_idx').on(table.authorId),
categoryIdx: index('articles_category_idx').on(table.categoryId),
}));// src/schema/relations.ts
import { relations } from 'drizzle-orm';
import { users, sessions } from './users';
import { articles, categories } from './content';
export const usersRelations = relations(users, ({ many }) => ({
sessions: many(sessions),
articles: many(articles),
}));
export const articlesRelations = relations(articles, ({ one }) => ({
author: one(users, { fields: [articles.authorId], references: [users.id] }),
category: one(categories, { fields: [articles.categoryId], references: [categories.id] }),
}));
export const categoriesRelations = relations(categories, ({ many }) => ({
articles: many(articles),
}));// src/schema/index.ts - Central export
export * from './users';
export * from './content';
export * from './relations';Repository Pattern with Drizzle
For applications that prefer a repository abstraction over direct database access, Drizzle's type safety extends naturally to repository classes.
// src/repositories/article.repository.ts
import { db } from '../db';
import { articles, type Article, type NewArticle } from '../schema';
import { eq, and, desc, sql, count } from 'drizzle-orm';
export class ArticleRepository {
async findById(id: number): Promise<Article | undefined> {
const [article] = await db.select().from(articles).where(eq(articles.id, id));
return article;
}
async findBySlug(slug: string): Promise<Article | undefined> {
const [article] = await db.select().from(articles).where(eq(articles.slug, slug));
return article;
}
async findPublished(options: { page?: number; limit?: number } = {}) {
const { page = 1, limit = 20 } = options;
const offset = (page - 1) * limit;
return db
.select()
.from(articles)
.where(eq(articles.isPublished, true))
.orderBy(desc(articles.createdAt))
.limit(limit)
.offset(offset);
}
async create(data: NewArticle): Promise<Article> {
const [article] = await db.insert(articles).values(data).returning();
return article;
}
async update(id: number, data: Partial<NewArticle>): Promise<Article | undefined> {
const [updated] = await db
.update(articles)
.set({ ...data, updatedAt: new Date() })
.where(eq(articles.id, id))
.returning();
return updated;
}
async delete(id: number): Promise<boolean> {
const result = await db.delete(articles).where(eq(articles.id, id));
return result.rowCount > 0;
}
async count(): Promise<number> {
const [result] = await db.select({ count: count() }).from(articles);
return result.count;
}
}Step-by-Step Implementation
Project Setup
# Create new project
mkdir drizzle-app && cd drizzle-app
npm init -y
# Install dependencies
npm install drizzle-orm postgres
npm install -D drizzle-kit typescript @types/node tsx
# Create directory structure
mkdir -p src/db src/schema src/repositories src/services// package.json scripts
{
"scripts": {
"db:generate": "drizzle-kit generate",
"db:migrate": "drizzle-kit migrate",
"db:push": "drizzle-kit push",
"db:studio": "drizzle-kit studio",
"db:seed": "tsx src/seed.ts",
"dev": "tsx watch src/index.ts"
}
}// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';
export default defineConfig({
schema: './src/schema/index.ts',
out: './drizzle/migrations',
dialect: 'postgresql',
dbCredentials: {
url: process.env.DATABASE_URL!,
},
});Complete CRUD Application
// src/services/article.service.ts
import { db } from '../db';
import { articles, users } from '../schema';
import { eq, and, desc, sql, ilike, count } from 'drizzle-orm';
interface CreateArticleInput {
title: string;
slug: string;
body: string;
categoryId?: number;
authorId: number;
isPublished?: boolean;
}
interface ArticleFilters {
authorId?: number;
categoryId?: number;
isPublished?: boolean;
search?: string;
}
export class ArticleService {
async create(input: CreateArticleInput) {
const [article] = await db
.insert(articles)
.values({
title: input.title,
slug: input.slug,
body: input.body,
categoryId: input.categoryId,
authorId: input.authorId,
isPublished: input.isPublished ?? false,
})
.returning();
return article;
}
async getById(id: number) {
return db.query.articles.findFirst({
where: eq(articles.id, id),
with: {
author: { columns: { id: true, name: true, email: true } },
category: true,
},
});
}
async list(filters: ArticleFilters, page: number = 1, pageSize: number = 20) {
const conditions = [];
if (filters.authorId !== undefined) {
conditions.push(eq(articles.authorId, filters.authorId));
}
if (filters.categoryId !== undefined) {
conditions.push(eq(articles.categoryId, filters.categoryId));
}
if (filters.isPublished !== undefined) {
conditions.push(eq(articles.isPublished, filters.isPublished));
}
if (filters.search) {
conditions.push(ilike(articles.title, `%${filters.search}%`));
}
const where = conditions.length > 0 ? and(...conditions) : undefined;
const offset = (page - 1) * pageSize;
const [data, totalResult] = await Promise.all([
db
.select({
article: articles,
authorName: users.name,
})
.from(articles)
.leftJoin(users, eq(articles.authorId, users.id))
.where(where)
.orderBy(desc(articles.createdAt))
.limit(pageSize)
.offset(offset),
db.select({ count: count() }).from(articles).where(where),
]);
return {
data,
pagination: {
page,
pageSize,
total: totalResult[0].count,
totalPages: Math.ceil(totalResult[0].count / pageSize),
},
};
}
async publish(id: number) {
const [updated] = await db
.update(articles)
.set({ isPublished: true, updatedAt: new Date() })
.where(eq(articles.id, id))
.returning();
return updated;
}
async delete(id: number) {
await db.delete(articles).where(eq(articles.id, id));
}
}Database Seeding
// src/seed.ts
import { db } from './db';
import { users, categories, articles } from './schema';
async function seed() {
console.log('Seeding database...');
// Create users
const insertedUsers = await db
.insert(users)
.values([
{ name: 'Alice Johnson', email: 'alice@example.com', role: 'admin' },
{ name: 'Bob Smith', email: 'bob@example.com', role: 'editor' },
{ name: 'Carol Davis', email: 'carol@example.com', role: 'viewer' },
])
.returning();
// Create categories
const insertedCategories = await db
.insert(categories)
.values([
{ name: 'Technology', slug: 'technology' },
{ name: 'Science', slug: 'science' },
{ name: 'Business', slug: 'business' },
])
.returning();
// Create articles
await db.insert(articles).values([
{
title: 'Getting Started with Drizzle',
slug: 'getting-started-with-drizzle',
body: 'Drizzle ORM is a lightweight TypeScript ORM...',
authorId: insertedUsers[0].id,
categoryId: insertedCategories[0].id,
isPublished: true,
},
{
title: 'Understanding Type Safety',
slug: 'understanding-type-safety',
body: 'Type safety in TypeScript ensures...',
authorId: insertedUsers[1].id,
categoryId: insertedCategories[0].id,
isPublished: true,
},
{
title: 'Draft: Future of Databases',
slug: 'future-of-databases',
body: 'Database technology is evolving...',
authorId: insertedUsers[0].id,
categoryId: insertedCategories[1].id,
isPublished: false,
},
]);
console.log('Seeding complete!');
process.exit(0);
}
seed().catch((err) => {
console.error('Seeding failed:', err);
process.exit(1);
});Real-World Use Cases and Case Studies
Use Case 1: SaaS B2B Platform
A B2B SaaS platform serving 200 enterprise clients adopted Drizzle to replace a custom query builder. The team valued Drizzle's SQL transparency because their existing DBAs could review generated queries without learning a new DSL. The modular schema design supported their multi-tenant architecture where each tenant's data lived in a separate PostgreSQL schema. Migration generation from TypeScript schemas eliminated the manual SQL migration scripts that had been a source of bugs.
Use Case 2: E-Commerce API
An e-commerce startup built their product catalog and order management API with Drizzle and Express. The relational queries API simplified complex product listing queries with filters, sorting, and pagination. JSONB columns stored flexible product attributes without requiring schema changes for each new product category. The team reported 60% fewer database-related bugs compared to their previous project that used raw SQL with manual type definitions.
Use Case 3: Content Management System
A digital media company migrated their CMS from TypeORM to Drizzle. The migration reduced their API bundle size from 2.3MB to 400KB, improving cold start times on AWS Lambda from 3 seconds to 800 milliseconds. The TypeScript-first approach meant their frontend developers, who were already proficient in TypeScript, could contribute to backend database code without learning TypeORM's decorator patterns.
Use Case 4: Analytics Dashboard Backend
An analytics platform processing millions of events daily used Drizzle for their dashboard API. Complex aggregation queries with window functions and CTEs were written using Drizzle's sql template literals, preserving type safety while maintaining full SQL expressiveness. The team chose Drizzle specifically because their analytical queries were too complex for ORM abstractions but needed type-safe result mapping.
Best Practices for Production
-
Always define relations: Use the
relations()API even if you primarily use SQL-style queries. It documents your data model and enables the relational queries API for simpler use cases. -
Use database constraints: Define NOT NULL, UNIQUE, FOREIGN KEY, and CHECK constraints in your schema. Drizzle generates these in migrations, ensuring data integrity at the database level.
-
Create indexes proactively: Add indexes for columns used in WHERE, JOIN, and ORDER BY clauses. Define them in the schema so they are tracked in migrations.
-
Use transactions for mutations: Wrap related writes in transactions to prevent partial updates. Drizzle's transaction API supports savepoints for nested transactions.
-
Parameterize queries: Drizzle parameterizes queries by default. When using the
sqltemplate literal, always use${value}interpolation, never string concatenation. -
Configure connection pools: Set appropriate pool sizes based on your database's
max_connectionsand your application's concurrency. UseidleTimeoutMillisandconnectionTimeoutMillisfor resilience. -
Run migrations in CI/CD: Generate migrations in your CI pipeline and verify they match the committed migration files. Apply migrations as a separate deployment step before deploying application code.
-
Monitor slow queries: Enable query logging in development and staging. Use database-level query analysis tools (pg_stat_statements, MySQL slow query log) in production.
Common Pitfalls and Solutions
| Pitfall | Impact | Solution |
|---|---|---|
Forgetting returning() after mutations | Cannot access generated/default values | Always use .returning() when you need the affected row |
| Missing indexes on foreign keys | Slow joins and lookups | Define explicit indexes in schema table definitions |
Using push in production | No migration history, no rollback capability | Use generate + migrate for all non-development environments |
Not handling undefined from findFirst | Runtime type errors | Always check for undefined before accessing query results |
| Overusing relational API for complex queries | Suboptimal SQL generation | Use SQL-style queries for complex joins, aggregations, and CTEs |
| Schema drift between team members | Migration conflicts | Generate migrations in a single branch; review SQL in PRs |
Not using sql template for raw expressions | SQL injection risk | Always use sql\...`` template literals, never string concatenation |
| Connection pool exhaustion | Application hangs under load | Configure pool size, use timeouts, close connections on shutdown |
Performance Optimization
// Use prepared statements for frequently executed queries
import { sql, placeholder } from 'drizzle-orm';
const findActiveUsers = db
.select()
.from(users)
.where(eq(users.isActive, true))
.prepare('find_active_users');
// Execute the prepared statement
const activeUsers = await findActiveUsers.execute();
// Batch inserts for bulk data loading
async function bulkInsert(records: NewArticle[]) {
// Drizzle handles batching internally
const inserted = await db.insert(articles).values(records).returning();
return inserted;
}
// Efficient counting with approximate counts for large tables
async function getApproximateCount(): Promise<number> {
const [result] = await db.execute(
sql`SELECT reltuples::bigint AS estimate FROM pg_class WHERE relname = 'articles'`
);
return Number(result.estimate);
}
// Use select with specific columns to reduce data transfer
async function getArticleSummaries() {
return db
.select({
id: articles.id,
title: articles.title,
slug: articles.slug,
excerpt: articles.excerpt,
createdAt: articles.createdAt,
})
.from(articles)
.where(eq(articles.isPublished, true))
.orderBy(desc(articles.createdAt));
}Comparison with Alternatives
| Aspect | Drizzle | Prisma | TypeORM | Kysely |
|---|---|---|---|---|
| Type safety mechanism | TypeScript inference | Code generation | Decorators | TypeScript inference |
| Query language | SQL-like TypeScript | Prisma Query Engine API | QueryBuilder (SQL-like) | SQL-like TypeScript |
| Bundle size | ~50KB | ~2MB+ (engine) | ~500KB | ~30KB |
| Cold start impact | Minimal | Significant (engine load) | Moderate | Minimal |
| Migration tool | Drizzle Kit | Prisma Migrate | Built-in | External (knex, etc.) |
| Database support | PostgreSQL, MySQL, SQLite | 7+ databases | 8+ databases | PostgreSQL, MySQL, SQLite |
| Learning curve | Low (SQL knowledge) | Moderate (custom API) | High (decorators, patterns) | Low (SQL knowledge) |
| Relational queries | Yes (with API) | Yes (native) | Yes (QueryBuilder) | No (manual joins) |
| Raw SQL escape hatch | sql template literal | $queryRaw | query() | sql template literal |
| Community maturity | Growing rapidly | Very mature | Mature | Growing |
| Serverless optimized | Yes | Improving | No | Yes |
| Schema visualization | Drizzle Studio | Prisma ERD | Entity diagrams | No |
Advanced Patterns
Multi-Schema Support (PostgreSQL)
import { pgSchema, pgTable, serial, text } from 'drizzle-orm/pg-core';
export const tenantSchema = pgSchema('tenant_acme');
export const tenantArticles = tenantSchema.table('articles', {
id: serial('id').primaryKey(),
title: text('title').notNull(),
});Database-Level Enums
import { pgEnum } from 'drizzle-orm/pg-core';
export const priorityLevel = pgEnum('priority_level', ['low', 'medium', 'high', 'critical']);
export const tasks = pgTable('tasks', {
id: serial('id').primaryKey(),
title: text('title').notNull(),
priority: priorityLevel('priority').default('medium').notNull(),
});Composite Primary Keys and Unique Constraints
import { pgTable, integer, primaryKey, uniqueIndex } from 'drizzle-orm/pg-core';
export const userRoles = pgTable('user_roles', {
userId: integer('user_id').notNull(),
roleId: integer('role_id').notNull(),
assignedAt: timestamp('assigned_at').defaultNow(),
}, (t) => ({
pk: primaryKey({ columns: [t.userId, t.roleId] }),
userIdx: uniqueIndex('user_role_idx').on(t.userId, t.roleId),
}));Testing Strategies
// test/helpers.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import { Pool } from 'pg';
import * as schema from '../src/schema';
let testPool: Pool;
let testDb: ReturnType<typeof drizzle>;
export async function createTestDb() {
testPool = new Pool({ connectionString: process.env.TEST_DATABASE_URL });
testDb = drizzle(testPool, { schema });
await migrate(testDb, { migrationsFolder: './drizzle/migrations' });
return testDb;
}
export async function truncateAll() {
await testDb.execute(
`TRUNCATE TABLE articles, sessions, users, categories RESTART IDENTITY CASCADE`
);
}
export async function closeTestDb() {
await testPool.end();
}// test/article.service.test.ts
import { createTestDb, truncateAll, closeTestDb } from './helpers';
import { ArticleService } from '../src/services/article.service';
import { db } from '../src/db';
let service: ArticleService;
beforeAll(async () => {
await createTestDb();
service = new ArticleService();
});
afterEach(truncateAll);
afterAll(closeTestDb);
describe('ArticleService', () => {
it('should create and retrieve an article', async () => {
const created = await service.create({
title: 'Test Article',
slug: 'test-article',
body: 'Test body content',
authorId: 1,
});
expect(created.id).toBeDefined();
expect(created.title).toBe('Test Article');
const found = await service.getById(created.id);
expect(found).toBeDefined();
expect(found!.article.title).toBe('Test Article');
});
it('should paginate results correctly', async () => {
for (let i = 0; i < 25; i++) {
await service.create({
title: `Article ${i}`,
slug: `article-${i}`,
body: `Body ${i}`,
authorId: 1,
isPublished: true,
});
}
const page1 = await service.list({ isPublished: true }, 1, 10);
expect(page1.data).toHaveLength(10);
expect(page1.pagination.total).toBe(25);
expect(page1.pagination.totalPages).toBe(3);
const page3 = await service.list({ isPublished: true }, 3, 10);
expect(page3.data).toHaveLength(5);
});
});Future Outlook
Drizzle's growth trajectory reflects the TypeScript ecosystem's preference for tools that leverage the type system rather than working around it. The project's roadmap includes improved query builder ergonomics, expanded database support, and deeper integration with edge computing platforms.
The convergence of Drizzle with serverless databases like Neon, PlanetScale, and Turso positions it well for the edge-first deployment model that is becoming standard. Its minimal bundle size and absence of a query engine binary make it uniquely suited for environments where cold start performance is critical.
As the ecosystem matures, expect to see Drizzle integration with ORMs, tRPC routers, and GraphQL schema generators become more standardized. The pattern of using TypeScript schema definitions as the single source of truth is likely to influence how other tools approach database integration.
Conclusion
Drizzle ORM delivers on the promise of TypeScript-native database access. By making SQL type-safe without introducing abstractions that hide it, Drizzle gives developers the control of raw SQL with the safety of a traditional ORM.
Key takeaways:
- TypeScript inference eliminates code generation: Schema definitions produce both database tables and TypeScript types automatically, keeping your codebase DRY.
- SQL transparency builds trust: Every Drizzle query maps to SQL you can read, understand, and optimize. This makes debugging and performance tuning straightforward.
- Minimal runtime overhead: At approximately 50KB with no query engine binary, Drizzle is ideal for serverless and edge deployments.
- Drizzle Kit handles migrations: Generate, review, and apply SQL migrations from your TypeScript schema with a simple CLI tool.
- Choose the right query approach: Use the SQL-like API for complex queries and the relational API for convenient data loading with eager loading.
- Database constraints are your safety net: Define all integrity constraints in your Drizzle schema so the database enforces them regardless of how data enters the system.