Drizzle ORM: TypeScript SQL ORM Guide

When building modern applications with TypeScript, interacting with a database is almost always required. Traditional ORMs like Prisma or TypeORM offer convenience but come with heavy abstractions, large bundle sizes, and sometimes unpredictable SQL generation. Drizzle ORM enters the scene as a lightweight, type-safe alternative that feels like writing SQL but with the full power of TypeScript's type system.

Drizzle ORM is designed for developers who know SQL and want to stay close to the database without sacrificing type safety. It provides a relational query builder that maps directly to SQL constructs, making it easy to reason about the generated queries. It supports multiple databases including PostgreSQL, MySQL, SQLite, and more, with automatic type inference from your schema.

In this guide, you'll learn how to set up Drizzle ORM, define schemas, perform CRUD operations, handle relationships, and optimize queries. We'll also cover a real-world production incident where Drizzle's type safety prevented a costly bug, and provide debugging tips for common issues. By the end, you'll be equipped to use Drizzle ORM in your next TypeScript project.

Setting Up Drizzle ORM

To get started with Drizzle ORM, you need to install the core package and the database-specific driver. For PostgreSQL, you can use drizzle-orm and pg. For MySQL, use mysql2. For SQLite, use better-sqlite3.

First, create a new TypeScript project and install dependencies:

``bash npm init -y npm install drizzle-orm pg npm install -D typescript @types/node ``

Next, define your database schema using Drizzle's schema definition syntax. Create a file src/schema.ts:

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

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

Then, create a database connection and use Drizzle's query builder. In src/index.ts:

```typescript import { drizzle } from 'drizzle-orm/node-postgres'; import { Pool } from 'pg'; import { users } from './schema';

const pool = new Pool({ connectionString: process.env.DATABASE_URL }); const db = drizzle(pool);

async function main() { const result = await db.select().from(users); console.log(result); }

main(); ```

Drizzle also provides a CLI tool drizzle-kit for migrations. Install it globally or as a dev dependency:

``bash npm install -D drizzle-kit ``

Configure drizzle.config.ts:

```typescript import type { Config } from 'drizzle-kit';

export default { schema: './src/schema.ts', out: './drizzle', driver: 'pg', dbCredentials: { connectionString: process.env.DATABASE_URL, }, } satisfies Config; ```

Run npx drizzle-kit generate to create SQL migration files, then npx drizzle-kit push to apply them to the database.

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

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

CRUD Operations with Drizzle ORM

Drizzle ORM provides a fluent API for Create, Read, Update, and Delete operations that closely mirrors SQL syntax.

Create (Insert)

To insert a new record, use the insert method:

``typescript const newUser = await db.insert(users).values({ name: 'Alice', age: 30, }).returning(); ``

The returning() method returns the inserted row(s). Without it, you get a summary object.

Read (Select)

Select queries are built with select():

``typescript const allUsers = await db.select().from(users); const user = await db.select().from(users).where(eq(users.id, 1)); ``

You can select specific columns:

``typescript const names = await db.select({ name: users.name }).from(users); ``

Update

Update uses update() and requires a where clause:

``typescript await db.update(users) .set({ age: 31 }) .where(eq(users.id, 1)); ``

Delete

Delete similarly requires a where clause:

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

Drizzle also supports batch operations and transactions. For example, to insert multiple users:

``typescript await db.insert(users).values([ { name: 'Bob', age: 25 }, { name: 'Charlie', age: 35 }, ]); ``

Transactions are handled via the transaction method:

``typescript await db.transaction(async (tx) => { await tx.insert(users).values({ name: 'Dave' }); await tx.update(users).set({ age: 40 }).where(eq(users.name, 'Dave')); }); ``

import { db } from './db';
import { users } from './schema';
import { eq } from 'drizzle-orm';

// Create
const newUser = await db.insert(users).values({ name: 'Alice', age: 30 }).returning();

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

// Update
await db.update(users).set({ age: 31 }).where(eq(users.id, 1));

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

Relationships and Joins

Drizzle ORM supports defining relationships between tables and performing joins with type safety. You can define foreign keys and relations in your schema.

Defining Relationships

Consider two tables: users and posts, where each user has many posts.

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

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

export const posts = pgTable('posts', { id: serial('id').primaryKey(), title: text('title'), userId: integer('user_id').references(() => users.id), }); ```

Performing Joins

Drizzle provides a leftJoin, innerJoin, rightJoin, and fullJoin methods:

```typescript import { db } from './db'; import { users, posts } from './schema'; import { eq } from 'drizzle-orm';

const result = await db.select() .from(users) .leftJoin(posts, eq(users.id, posts.userId)); ```

The result is an array of objects with nested properties: { users: {...}, posts: {...} }. You can also select specific fields:

``typescript const result = await db.select({ userName: users.name, postTitle: posts.title, }).from(users) .leftJoin(posts, eq(users.id, posts.userId)); ``

Relations API

Drizzle also offers a relations API for more complex queries. Define relations in the schema:

```typescript import { relations } from 'drizzle-orm';

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

export const postsRelations = relations(posts, ({ one }) => ({ user: one(users, { fields: [posts.userId], references: [users.id], }), })); ```

Then you can use findMany with relations:

``typescript const userWithPosts = await db.query.users.findMany({ with: { posts: true, }, }); ``

This returns nested objects with the related data.

import { db } from './db';
import { users, posts } from './schema';
import { eq } from 'drizzle-orm';

// Simple join
const result = await db.select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId));

// With specific fields
const result2 = await db.select({
  userName: users.name,
  postTitle: posts.title,
}).from(users)
  .leftJoin(posts, eq(users.id, posts.userId));

// Using relations
const userWithPosts = await db.query.users.findMany({
  with: {
    posts: true,
  },
});

Migrations with Drizzle Kit

Drizzle Kit is the official CLI tool for managing database migrations. It generates SQL migration files based on your schema changes and can apply them to your database.

Generating Migrations

After modifying your schema, run:

``bash npx drizzle-kit generate ``

This creates a new migration file in the drizzle directory (or the folder specified in out). The file contains SQL statements to alter the database schema.

Applying Migrations

To apply pending migrations, run:

``bash npx drizzle-kit push ``

This executes the migration files against your database. You can also use migrate for more control:

``bash npx drizzle-kit migrate ``

Rolling Back

Drizzle Kit does not support automatic rollbacks. To revert a migration, you need to manually create a new migration that reverses the changes.

Snapshot and Diff

Drizzle Kit can also generate a snapshot of your current schema and compare it with the database to detect drift:

``bash npx drizzle-kit snapshot npx drizzle-kit diff ``

Integration with CI/CD

In a CI/CD pipeline, you can run migrations as part of your deployment process. For example:

``yaml - name: Run migrations run: npx drizzle-kit push ``

Make sure to set the DATABASE_URL environment variable in your CI environment.

import type { Config } from 'drizzle-kit';

export default {
  schema: './src/schema.ts',
  out: './drizzle',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL,
  },
} satisfies Config;

Advanced Queries and Raw SQL

While Drizzle's query builder covers most use cases, sometimes you need to write raw SQL for complex queries or database-specific features. Drizzle provides a sql template tag for embedding raw SQL safely.

Using the sql Tag

You can use the sql tag to write raw SQL within Drizzle queries:

```typescript import { sql } from 'drizzle-orm';

const result = await db.execute(sql SELECT * FROM users WHERE age > ${minAge} ); ```

The sql tag automatically escapes parameters to prevent SQL injection.

Combining with Query Builder

You can mix raw SQL with the query builder:

``typescript const result = await db.select() .from(users) .where(sql${users.age} > ${minAge}); ``

Window Functions

Drizzle supports window functions via the sql tag:

``typescript const result = await db.select({ name: users.name, rank: sqlRANK() OVER (ORDER BY ${users.age} DESC), }).from(users); ``

Full-Text Search

For PostgreSQL full-text search, you can use:

``typescript const result = await db.select() .from(users) .where(sqlto_tsvector('english', ${users.name}) @@ to_tsquery('english', ${searchTerm})); ``

Prepared Statements

Drizzle supports prepared statements for performance:

``typescript const stmt = db.select().from(users).where(eq(users.id, sql.placeholder('id'))).prepare(); const result = await stmt.execute({ id: 1 }); ``

This is useful for queries executed multiple times with different parameters.

import { sql } from 'drizzle-orm';
import { db } from './db';
import { users } from './schema';

// Raw SQL with parameter
const minAge = 25;
const result = await db.execute(sql`
  SELECT * FROM users WHERE age > ${minAge}
`);

// Window function
const result2 = await db.select({
  name: users.name,
  rank: sql`RANK() OVER (ORDER BY ${users.age} DESC)`,
}).from(users);

// Prepared statement
const stmt = db.select().from(users).where(eq(users.id, sql.placeholder('id'))).prepare();
const user = await stmt.execute({ id: 1 });

Performance Optimization and Best Practices

Optimizing Drizzle ORM queries is essential for production applications. Here are key strategies:

1. Use Indexes

Define indexes in your schema to speed up queries:

```typescript import { index } from 'drizzle-orm/pg-core';

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

2. Select Only Needed Columns

Avoid select * in production. Specify only the columns you need:

``typescript const names = await db.select({ name: users.name }).from(users); ``

3. Use limit and offset for Pagination

``typescript const page = await db.select().from(users).limit(10).offset(0); ``

For large datasets, consider keyset pagination (using where with a cursor) instead of offset.

4. Batch Operations

Use batch inserts/updates to reduce round trips:

``typescript await db.insert(users).values([...largeArray]); ``

5. Connection Pooling

Always use a connection pool. Drizzle works with pg.Pool, mysql2/pool, etc.

6. Logging and Monitoring

Enable Drizzle's logging in development to see generated SQL:

``typescript const db = drizzle(pool, { logger: true }); ``

In production, use database monitoring tools like pg_stat_statements or AWS RDS Performance Insights.

7. Avoid N+1 Queries

When using relations, Drizzle batches queries, but be cautious with nested relations. Use with wisely.

8. Use Raw SQL for Complex Queries

For very complex queries, raw SQL with sql tag can be more efficient than the query builder.

import { index } from 'drizzle-orm/pg-core';

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

// Select only needed columns
const names = await db.select({ name: users.name }).from(users);

// Pagination with limit/offset
const page = await db.select().from(users).limit(10).offset(0);

// Enable logging
const db = drizzle(pool, { logger: true });

Drizzle ORM vs Prisma: A Comparison

Drizzle ORM and Prisma are two popular TypeScript ORMs, but they have different philosophies. Here's a comparison to help you choose.

Bundle Size

Drizzle is significantly smaller. Prisma includes a query engine binary, while Drizzle is pure TypeScript.

Type Safety

Both offer excellent type safety, but Drizzle's type inference is more direct because it maps closely to SQL. Prisma uses a generated client.

Learning Curve

Drizzle is easier for developers who know SQL. Prisma has its own query syntax that abstracts SQL more.

Performance

Drizzle generally performs better because it generates SQL directly without an intermediate layer. Prisma's query engine adds overhead.

Migrations

Both have migration tools. Drizzle Kit is simpler; Prisma Migrate is more feature-rich but complex.

Database Support

Both support PostgreSQL, MySQL, SQLite, etc. Drizzle also supports CockroachDB and others.

When to Use Drizzle - You want a lightweight ORM with minimal overhead. - You prefer writing SQL-like queries. - You need to optimize for performance.

When to Use Prisma - You want a full-featured ORM with a rich ecosystem. - You prefer a more abstracted API. - You need features like relation filtering and nested writes out of the box.

Ultimately, the choice depends on your project's needs. Drizzle is great for performance-critical applications, while Prisma offers more convenience.