Introduction

Drizzle ORM has emerged as one of the fastest-growing database libraries in the TypeScript ecosystem. Unlike heavyweight ORMs that generate SQL through opaque abstraction layers, Drizzle takes a fundamentally different approach: it lets you write SQL that TypeScript can understand. Your schema is defined in TypeScript, your queries look like SQL, and the TypeScript compiler catches errors before they reach the database.

This guide walks through everything you need to get productive with Drizzle: defining schemas, understanding type inference, writing queries, handling relations, and making the right call between Drizzle and Prisma for your project. All code examples use PostgreSQL, but the patterns apply equally to MySQL and SQLite.

Tip: If you have existing SQL table definitions, use the SQL to Drizzle Converter to generate Drizzle schema code automatically from your CREATE TABLE statements.

What Makes Drizzle Different

Most ORMs force you to think in objects and then translate those objects into SQL. Drizzle flips this: you think in SQL and Drizzle gives you TypeScript types for free. This distinction matters more than it sounds.

SQL-like query builder

Drizzle queries read like SQL. db.select().from(users).where(eq(users.role, 'admin')) is immediately readable to anyone who knows SQL — there is no ORM-specific vocabulary to learn. The method names (select, from, where, join,orderBy, limit) map one-to-one to SQL keywords.

Zero overhead at runtime

Drizzle does not use Proxy objects, code generation, or complex reflection at runtime. It is a thin, purely additive layer over your database driver. The resulting bundle is much smaller than Prisma's (Drizzle adds roughly 35 KB gzipped; Prisma adds 3–5 MB for the query engine alone). This is why Drizzle is the natural choice for edge runtimes — Cloudflare Workers, Vercel Edge Functions, and Deno Deploy — where bundle size and cold start latency matter.

Schema as single source of truth

Your Drizzle schema file is a TypeScript module. It exports table definitions that serve three purposes simultaneously: they describe the database structure, they are the source for migration generation (drizzle-kit), and they carry full type information for queries. You write the schema once; everything else is derived.

Defining Your Schema

Install Drizzle and the PostgreSQL driver:

npm install drizzle-orm postgres
npm install --save-dev drizzle-kit

Define tables in a schema file. Each column builder function corresponds to a PostgreSQL type:

// schema.ts
import { pgTable, serial, varchar, text, boolean, integer,
         timestamp, decimal, uuid, pgEnum } from 'drizzle-orm/pg-core';

export const roleEnum = pgEnum('user_role', ['admin', 'user', 'moderator']);

export const users = pgTable('users', {
  id:        serial('id').primaryKey(),
  uuid:      uuid('uuid').defaultRandom().notNull(),
  email:     varchar('email', { length: 255 }).unique().notNull(),
  name:      varchar('name', { length: 100 }).notNull(),
  role:      roleEnum('role').default('user').notNull(),
  bio:       text('bio'),
  isActive:  boolean('is_active').default(true).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

export const posts = pgTable('posts', {
  id:        serial('id').primaryKey(),
  title:     varchar('title', { length: 200 }).notNull(),
  content:   text('content').notNull(),
  authorId:  integer('author_id').notNull().references(() => users.id),
  published: boolean('published').default(false).notNull(),
  views:     integer('views').default(0).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

Drizzle's column types mirror PostgreSQL types directly. Common mappings:

SQL Type	Drizzle Builder	TypeScript Type
SERIAL / INT	serial() / integer()	number
BIGINT	bigint({ mode: "number" })	number
VARCHAR(n)	varchar({ length: n })	string
TEXT	text()	string
BOOLEAN	boolean()	boolean
TIMESTAMP	timestamp()	Date
NUMERIC / DECIMAL	numeric({ precision, scale })	string
UUID	uuid()	string
JSONB	jsonb()	unknown
TEXT[] (array)	text().array()	string[]

Notice that NUMERIC maps to string in TypeScript, not number. This is intentional: JavaScript's number type is a 64-bit float and cannot represent arbitrary-precision decimals without loss. Drizzle surfaces the raw string from the database driver; use a library like big.js or decimal.js for arithmetic on monetary values.

If you have existing SQL table definitions and want the Drizzle schema generated automatically, paste them into the SQL to Drizzle tool — it handles all column type mappings and generates the full schema file including primary keys, foreign key references, and not-null constraints.

$inferSelect and $inferInsert: Drizzle's Type System

Drizzle exposes two utility types on every table definition that you will use constantly:

import { users, posts } from './schema';

// The shape of a row returned by SELECT
type User = typeof users.$inferSelect;
// {
//   id: number;
//   uuid: string;
//   email: string;
//   name: string;
//   role: 'admin' | 'user' | 'moderator';
//   bio: string | null;        <-- nullable because no .notNull()
//   isActive: boolean;
//   createdAt: Date;
//   updatedAt: Date;
// }

// The shape required for INSERT (optional fields have defaults)
type NewUser = typeof users.$inferInsert;
// {
//   email: string;             <-- required (notNull, no default)
//   name: string;              <-- required
//   id?: number;               <-- optional (serial has auto-generated value)
//   uuid?: string;             <-- optional (defaultRandom)
//   role?: 'admin' | 'user' | 'moderator'; <-- optional (has default)
//   bio?: string | null;       <-- optional (nullable)
//   isActive?: boolean;        <-- optional (has default)
//   createdAt?: Date;          <-- optional (defaultNow)
//   updatedAt?: Date;          <-- optional (defaultNow)
// }

$inferSelect reflects every column exactly — nullable columns becomeT | null, non-nullable columns are T. $inferInsertmakes fields with defaults or auto-generation optional, and keeps the rest required. This means TypeScript catches missing required fields at insert time, before the query ever runs.

These types power your application layer. Pass $inferSelect as the return type of API handlers, use $inferInsert as the input type of creation functions. No separate type declarations, no drift between schema and types.

Writing Queries

Connecting and selecting

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import { users, posts } from './schema';
import { eq, and, gt, like, desc, count } from 'drizzle-orm';

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

// Select all active users
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.isActive, true));

// Select specific columns
const emails = await db
  .select({ id: users.id, email: users.email })
  .from(users)
  .where(eq(users.role, 'admin'));

// Combine conditions with and()
const recentAdmins = await db
  .select()
  .from(users)
  .where(
    and(
      eq(users.role, 'admin'),
      gt(users.createdAt, new Date('2025-01-01'))
    )
  )
  .orderBy(desc(users.createdAt))
  .limit(10);

// Search with LIKE
const matchingUsers = await db
  .select()
  .from(users)
  .where(like(users.email, '%@example.com'));

Insert, update, and delete

import type { NewUser } from './schema'; // typeof users.$inferInsert

// Insert a single row (TypeScript enforces required fields)
const newUser: NewUser = {
  email: '[email protected]',
  name: 'Alice',
  role: 'user',
};

const [inserted] = await db
  .insert(users)
  .values(newUser)
  .returning(); // returns the full inserted row with generated id

// Insert multiple rows
await db.insert(users).values([
  { email: '[email protected]', name: 'Bob' },
  { email: '[email protected]', name: 'Carol' },
]);

// Update with type-safe conditions
await db
  .update(users)
  .set({ isActive: false, updatedAt: new Date() })
  .where(eq(users.email, '[email protected]'));

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

Joins

// Join users with their posts — typed result includes both tables
const usersWithPosts = await db
  .select({
    userId:    users.id,
    userName:  users.name,
    postId:    posts.id,
    postTitle: posts.title,
    views:     posts.views,
  })
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id))
  .where(eq(posts.published, true))
  .orderBy(desc(posts.views));

// Aggregate: count posts per user
const postCounts = await db
  .select({
    authorId: posts.authorId,
    postCount: count(),
  })
  .from(posts)
  .where(eq(posts.published, true))
  .groupBy(posts.authorId);

Defining and Querying Relations

Drizzle has a separate relations API that enables nested query syntax. Define relations alongside your table definitions:

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

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

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

Once relations are defined, use the relational query API to fetch nested data without manual joins:

// Create db with the schema to enable relational queries
import * as schema from './schema';
const db = drizzle(client, { schema });

// Fetch users with their published posts nested
const usersWithPosts = await db.query.users.findMany({
  where: eq(users.isActive, true),
  with: {
    posts: {
      where: eq(posts.published, true),
      columns: { id: true, title: true, views: true },
      orderBy: [desc(posts.views)],
      limit: 5,
    },
  },
  limit: 20,
});

// Result is fully typed:
// Array<{
//   id: number; email: string; name: string; ...
//   posts: Array<{ id: number; title: string; views: number }>;
// }>

The relational API generates optimized SQL (a single query or a small number of queries depending on the relation type), not N+1 queries. The result shape is inferred from the query options, so TypeScript knows exactly which columns are present.

Drizzle vs Prisma: Honest Comparison

Both are excellent choices. The decision usually comes down to three factors:

Choose Drizzle when:

You are deploying to edge runtimes (Cloudflare Workers, Vercel Edge, Deno Deploy) — Drizzle has no binary dependency and tiny bundle size
You want SQL-level control: raw SQL escape hatches, complex window functions, or specific index hints
Your team already thinks in SQL and wants the ORM to stay out of the way
You need direct access to the underlying driver without abstraction overhead
You want $inferSelect / $inferInsert types derived directly from your schema without code generation

Choose Prisma when:

You want a fully managed migration workflow with a GUI (prisma studio)
Your team prefers schema-first design using Prisma's SDL rather than TypeScript column builders
You need built-in connection pooling via Prisma Accelerate
You work with MongoDB (Drizzle is SQL-only)
You want a larger ecosystem of third-party extensions and community resources

There is no universally better choice — Prisma has a longer track record and a more polished migration toolchain; Drizzle has a lighter footprint and tighter SQL alignment. Teams switching from Prisma to Drizzle often cite edge runtime requirements or the desire for more predictable query behavior as the trigger.

Generating and Running Migrations

Drizzle Kit compares your schema to the current database state and generates SQL migration files:

# drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/schema.ts',
  out:    './migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

# Generate a new migration file
npx drizzle-kit generate

# Apply migrations to the database
npx drizzle-kit migrate

# Or push schema directly (useful in development)
npx drizzle-kit push

The generate command creates SQL files in the migrations folder. Commit these files to version control — they are the audit trail of every schema change. In production, run migrate as part of your deploy pipeline; Drizzle tracks which migrations have been applied using a __drizzle_migrations table.

Practical Patterns

Reusable prepared statements

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

// Prepare a query once, execute many times with different params
const findUserByEmail = db
  .select()
  .from(users)
  .where(eq(users.email, placeholder('email')))
  .prepare('find_user_by_email');

// Each call reuses the prepared plan
const alice = await findUserByEmail.execute({ email: '[email protected]' });

Transactions

const result = await db.transaction(async (tx) => {
  const [newUser] = await tx
    .insert(users)
    .values({ email: '[email protected]', name: 'New User' })
    .returning({ id: users.id });

  await tx
    .insert(posts)
    .values({
      title: 'First Post',
      content: 'Hello!',
      authorId: newUser.id,
    });

  return newUser;
});
// If any operation throws, the entire transaction rolls back

Upsert (insert or update on conflict)

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

await db
  .insert(users)
  .values({ email: '[email protected]', name: 'Alice' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'Alice (Updated)', updatedAt: new Date() },
  });

Independent Developer · Last updated June 2026

Back to Tutorials Next: Zod Schema Guide

Drizzle ORM: A Practical Guide to Type-Safe SQL in TypeScript