Drizzle Orm

Drizzle ORM

Overview

Drizzle ORM is a lightweight, type-safe TypeScript ORM that maps directly to SQL for PostgreSQL, MySQL, and SQLite. It provides both a SQL-like query builder and a relational queries API, with zero dependencies and full serverless compatibility. Use Drizzle when you need compile-time type safety with SQL-level control; avoid it when you need a full active-record ORM with automatic migrations (use Prisma) or when working with MongoDB/NoSQL databases.

Quick Reference

Common Mistakes

Delegation

If the tanstack-query skill is available, delegate client-side caching and server state management to it.

Otherwise, recommend: npx skills add oakoss/agent-skills --skill tanstack-query

>

If the tanstack-start skill is available, delegate server function patterns for database access to it.

Otherwise, recommend: npx skills add oakoss/agent-skills --skill tanstack-start

>

If the electricsql skill is available, delegate Electric setup, shape configuration, and write patterns to it.

Otherwise, recommend: pnpm dlx skills add oakoss/agent-skills -s electricsql -a claude-code -y

>

If the pglite skill is available, delegate PGlite-specific configuration, extensions, and browser storage patterns to it.

Otherwise, recommend: pnpm dlx skills add oakoss/agent-skills -s pglite -a claude-code -y

• Schema validation: Use drizzle-zod to generate Zod schemas from Drizzle tables for input validation
• Query pattern discovery: Use Explore agent
• Code review: Delegate to code-reviewer agent

References

• Schema definition, column types, constraints, indexes, and type inference
• Relational queries, SQL-like API, joins, subqueries, and aggregations
• Insert, update, delete, upsert, and transactions
• Relations: one, many, nested with clauses, self-referencing
• Migrations: drizzle-kit generate, migrate, push, pull, studio
• Filter operators: eq, ne, gt, lt, like, inArray, sql template
• Views, materialized views, generated columns, check constraints, $count, batch API
• ElectricSQL + PGlite integration: driver setup, schema-to-shape mapping, type inference, local sync

Electric Integration

Overview

Drizzle + ElectricSQL + PGlite enables a local-first architecture where Drizzle defines the schema, Electric syncs data from Postgres, and Drizzle queries the local PGlite instance. There is no official integration plugin from either team — this is a community-driven pattern (see ElectroDrizzle).

PGlite Driver Setup

Drizzle has first-class PGlite support via drizzle-orm/pglite:

import { PGlite } from '@electric-sql/pglite';
import { drizzle } from 'drizzle-orm/pglite';
import * as schema from './schema';

const client = new PGlite();
const db = drizzle(client, { schema });

With Persistent Storage

const client = new PGlite('idb://my-app-db');
const db = drizzle(client, { schema });

With Extensions

import { PGlite } from '@electric-sql/pglite';
import { vector } from '@electric-sql/pglite/contrib/pgvector';
import { drizzle } from 'drizzle-orm/pglite';

const client = new PGlite({
  dataDir: 'idb://my-app-db',
  extensions: { vector },
});
const db = drizzle(client, { schema });

Schema-to-Shape Mapping

No automatic mapping exists between Drizzle schemas and Electric shapes. Map manually by matching table names and column selections:

import {
  pgTable,
  text,
  boolean,
  timestamp,
  integer,
} from 'drizzle-orm/pg-core';
import { ShapeStream } from '@electric-sql/client';

export const todos = pgTable('todos', {
  id: text().primaryKey(),
  title: text().notNull(),
  completed: boolean().notNull().default(false),
  userId: text('user_id').notNull(),
  createdAt: timestamp('created_at', { mode: 'string' }).notNull().defaultNow(),
});

type Todo = typeof todos.$inferSelect;

const stream = new ShapeStream<Todo>({
  url: '/api/shapes',
  params: {
    table: 'todos',
    columns: 'id,title,completed,user_id,created_at',
  },
});

The columns param must use the database column names (snake_case), not the TypeScript property names.

Type Inference from Drizzle Schema

Use $inferSelect to derive types for ShapeStream generics:

import { type ShapeStream, type Shape } from '@electric-sql/client';

type Todo = typeof todos.$inferSelect;
type TodoInsert = typeof todos.$inferInsert;

const stream = new ShapeStream<Todo>({
  url: '/api/shapes',
  params: { table: 'todos' },
});

const shape = new Shape<Todo>(stream);

shape.subscribe((data: Map<string, Todo>) => {
  const rows = [...data.values()];
  const incomplete = rows.filter((t) => !t.completed);
});

This keeps the Electric client types in sync with the Drizzle schema as the single source of truth.

PGlite + Drizzle + Electric Combo

The full pattern: define schema with Drizzle, sync from Postgres via Electric, query locally with Drizzle against PGlite.

import { PGlite } from '@electric-sql/pglite';
import { drizzle } from 'drizzle-orm/pglite';
import { ShapeStream, Shape } from '@electric-sql/client';
import { eq } from 'drizzle-orm';
import * as schema from './schema';

const client = new PGlite('idb://my-app');
const db = drizzle(client, { schema });

async function initLocalDb() {
  await client.exec(`
    CREATE TABLE IF NOT EXISTS todos (
      id TEXT PRIMARY KEY,
      title TEXT NOT NULL,
      completed BOOLEAN NOT NULL DEFAULT false,
      user_id TEXT NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT now()
    )
  `);
}

type Todo = typeof schema.todos.$inferSelect;

function startSync(userId: string) {
  const stream = new ShapeStream<Todo>({
    url: '/api/shapes',
    params: {
      table: 'todos',
      where: `user_id = '${userId}'`,
    },
  });

  stream.subscribe(async (messages) => {
    for (const msg of messages) {
      if ('operation' in msg.headers) {
        switch (msg.headers.operation) {
          case 'insert':
            await db
              .insert(schema.todos)
              .values(msg.value as Todo)
              .onConflictDoUpdate({
                target: schema.todos.id,
                set: msg.value as Partial<Todo>,
              });
            break;
          case 'update':
            await db
              .update(schema.todos)
              .set(msg.value as Partial<Todo>)
              .where(eq(schema.todos.id, msg.key));
            break;
          case 'delete':
            await db.delete(schema.todos).where(eq(schema.todos.id, msg.key));
            break;
        }
      }
    }
  });

  return stream;
}

Querying Local PGlite with Drizzle

Once data is synced locally, use the full Drizzle query API:

const activeTodos = await db
  .select()
  .from(schema.todos)
  .where(eq(schema.todos.completed, false))
  .orderBy(schema.todos.createdAt);

const todoWithRelations = await db.query.todos.findFirst({
  where: eq(schema.todos.id, todoId),
});

Migrations on Local PGlite

Run Drizzle migrations against PGlite to keep the local schema in sync:

import { PGlite } from '@electric-sql/pglite';
import { drizzle } from 'drizzle-orm/pglite';
import { migrate } from 'drizzle-orm/pglite/migrator';

const client = new PGlite('idb://my-app');
const db = drizzle(client);

await migrate(db, { migrationsFolder: './drizzle' });

For browser environments, bundle migrations as JSON:

import migrations from './drizzle/migrations.json';
import { migrate } from 'drizzle-orm/pglite/migrator';

await migrate(db, { migrations });

Generate the JSON bundle with drizzle-kit:

drizzle-kit generate --dialect=postgresql --schema=./src/schema.ts --out=./drizzle

Reusable Column Patterns

Spread common column definitions across tables:

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

const timestamps = {
  createdAt: timestamp('created_at', { mode: 'string' }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'string' })
    .notNull()
    .defaultNow()
    .$onUpdateFn(() => new Date().toISOString()),
};

const withUserId = {
  userId: text('user_id').notNull(),
};

export const todos = pgTable('todos', {
  id: text().primaryKey(),
  title: text().notNull(),
  completed: boolean().notNull().default(false),
  ...withUserId,
  ...timestamps,
});

export const notes = pgTable('notes', {
  id: text().primaryKey(),
  content: text().notNull(),
  ...withUserId,
  ...timestamps,
});

Automatic Casing

Configure Drizzle to auto-convert between snake_case in the database and camelCase in TypeScript:

const db = drizzle(client, {
  casing: 'snake_case',
  schema,
});

With this option, define columns without explicit name arguments:

export const todos = pgTable('todos', {
  id: text().primaryKey(),
  title: text().notNull(),
  completed: boolean().notNull().default(false),
  userId: text().notNull(),
  createdAt: timestamp({ mode: 'string' }).notNull().defaultNow(),
  updatedAt: timestamp({ mode: 'string' }).notNull().defaultNow(),
});

Drizzle maps userId to user_id and createdAt to created_at in SQL. Electric shape columns params must still use the database names (user_id, created_at).

Drizzle v1.0 Migration Notes

Key changes relevant to Electric integration (none are hard breaking — legacy APIs still work):

If the electricsql skill is available, delegate Electric setup, shape configuration, and write patterns to it.

Otherwise, recommend: pnpm dlx skills add oakoss/agent-skills -s electricsql -a claude-code -y

>

If the pglite skill is available, delegate PGlite-specific configuration, extensions, and browser storage patterns to it.

Otherwise, recommend: pnpm dlx skills add oakoss/agent-skills -s pglite -a claude-code -y

Filters and Operators

Operator Reference

All operators are imported from drizzle-orm:

import {
  eq,
  ne,
  gt,
  gte,
  lt,
  lte,
  like,
  ilike,
  inArray,
  notInArray,
  isNull,
  isNotNull,
  between,
  notBetween,
  and,
  or,
  not,
  exists,
  sql,
} from 'drizzle-orm';

Comparison Operators

const adults = await db.select().from(users).where(gte(users.age, 18));

const premiumProducts = await db
  .select()
  .from(products)
  .where(gt(products.price, 100));

const recentPosts = await db
  .select()
  .from(posts)
  .where(gte(posts.createdAt, new Date('2024-01-01')));

Pattern Matching

const johns = await db.select().from(users).where(like(users.name, 'John%'));

const gmailUsers = await db
  .select()
  .from(users)
  .where(ilike(users.email, '%@gmail.com'));

ilike is PostgreSQL-specific. For MySQL/SQLite, use like (MySQL is case-insensitive by default; SQLite requires COLLATE NOCASE).

Array Operators

const adminsAndMods = await db
  .select()
  .from(users)
  .where(inArray(users.role, ['admin', 'moderator']));

const filtered = await db
  .select()
  .from(users)
  .where(notInArray(users.id, blockedUserIds));

Null Checks

const activeUsers = await db
  .select()
  .from(users)
  .where(isNull(users.deletedAt));

const verifiedUsers = await db
  .select()
  .from(users)
  .where(isNotNull(users.emailVerifiedAt));

Range Operators

const midRange = await db
  .select()
  .from(products)
  .where(between(products.price, 10, 50));

const thisWeek = await db
  .select()
  .from(posts)
  .where(between(posts.createdAt, weekStart, weekEnd));

Combining Conditions

AND

const activeAdmins = await db
  .select()
  .from(users)
  .where(
    and(
      eq(users.role, 'admin'),
      eq(users.isActive, true),
      isNull(users.deletedAt),
    ),
  );

OR

const moderatorsOrAdmins = await db
  .select()
  .from(users)
  .where(or(eq(users.role, 'admin'), eq(users.role, 'moderator')));

Complex Combinations

const results = await db
  .select()
  .from(posts)
  .where(
    and(
      eq(posts.published, true),
      or(
        eq(posts.authorId, currentUserId),
        gte(posts.publishedAt, thirtyDaysAgo),
      ),
    ),
  );

NOT

const nonAdmins = await db
  .select()
  .from(users)
  .where(not(eq(users.role, 'admin')));

EXISTS Subquery

const usersWithPosts = await db
  .select()
  .from(users)
  .where(exists(db.select().from(posts).where(eq(posts.authorId, users.id))));

The sql Template

Basic Usage

import { sql } from 'drizzle-orm';

const results = await db
  .select()
  .from(users)
  .where(sql`${users.age} > 18`);

Parameters passed via ${} are automatically parameterized (safe from SQL injection). Table and column references are properly escaped.

Custom Expressions in Select

const usersWithFullName = await db
  .select({
    id: users.id,
    fullName: sql<string>`${users.firstName} || ' ' || ${users.lastName}`,
  })
  .from(users);

Typed sql Expressions

const postCounts = await db
  .select({
    authorId: posts.authorId,
    count: sql<number>`count(*)`.as('count'),
  })
  .from(posts)
  .groupBy(posts.authorId);

sql.raw() for Unescaped Values

const orderDirection = 'DESC';
const results = await db
  .select()
  .from(users)
  .orderBy(sql`${users.createdAt} ${sql.raw(orderDirection)}`);

Use sql.raw() only for trusted values. Never pass user input to sql.raw().

sql.placeholder() for Prepared Statements

const prepared = db
  .select()
  .from(users)
  .where(
    and(
      eq(users.role, sql.placeholder('role')),
      gte(users.age, sql.placeholder('minAge')),
    ),
  )
  .prepare('get_users_by_role_and_age');

const admins = await prepared.execute({ role: 'admin', minAge: 18 });

Using sql in ORDER BY

const results = await db
  .select()
  .from(posts)
  .orderBy(sql`${posts.viewCount} DESC NULLS LAST`);

Using sql in HAVING

const popularAuthors = await db
  .select({
    authorId: posts.authorId,
    postCount: sql<number>`count(*)`.as('post_count'),
  })
  .from(posts)
  .groupBy(posts.authorId)
  .having(sql`count(*) >= 10`);

Dynamic Filters

function buildUserQuery(filters: {
  role?: string;
  isActive?: boolean;
  search?: string;
}) {
  const conditions = [];

  if (filters.role) {
    conditions.push(eq(users.role, filters.role));
  }
  if (filters.isActive !== undefined) {
    conditions.push(eq(users.isActive, filters.isActive));
  }
  if (filters.search) {
    conditions.push(ilike(users.name, `%${filters.search}%`));
  }

  return db
    .select()
    .from(users)
    .where(conditions.length > 0 ? and(...conditions) : undefined);
}

Full-Text Search (PostgreSQL)

const results = await db
  .select()
  .from(posts)
  .where(
    sql`to_tsvector('english', ${posts.title} || ' ' || ${posts.content})
        @@ plainto_tsquery('english', ${searchTerm})`,
  );

Migrations

drizzle.config.ts

import { defineConfig } from 'drizzle-kit';

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

Multi-File Schema

import { defineConfig } from 'drizzle-kit';

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

MySQL Configuration

import { defineConfig } from 'drizzle-kit';

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

SQLite Configuration

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  dialect: 'sqlite',
  schema: './src/schema.ts',
  out: './drizzle',
  dbCredentials: {
    url: './sqlite.db',
  },
});

Custom Migration Table

import { defineConfig } from 'drizzle-kit';

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

Drizzle Kit Commands

generate

Reads your schema files and generates SQL migration files based on changes.

npx drizzle-kit generate

Produces a timestamped migration folder:

drizzle/
├── 0000_initial/
│   └── migration.sql
├── 0001_add_posts/
│   └── migration.sql
└── meta/
    └── _journal.json

migrate

Applies pending migration files to the database.

npx drizzle-kit migrate

push

Pushes schema changes directly to the database without creating migration files. Useful for prototyping and development.

npx drizzle-kit push

pull (Introspection)

Reads an existing database schema and generates Drizzle schema TypeScript files.

npx drizzle-kit pull

studio

Launches Drizzle Studio, a browser-based database GUI.

npx drizzle-kit studio

Programmatic Migrations

PostgreSQL with node-postgres

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

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

await migrate(db, { migrationsFolder: './drizzle' });

PostgreSQL with Neon Serverless

import { drizzle } from 'drizzle-orm/neon-http';
import { migrate } from 'drizzle-orm/neon-http/migrator';

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

await migrate(db, { migrationsFolder: './drizzle' });

SQLite with better-sqlite3

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

const db = drizzle('./sqlite.db');

migrate(db, { migrationsFolder: './drizzle' });

Migration Strategies

Development Workflow

Use push for fast iteration during development:

npx drizzle-kit push

Switch to generate + migrate when you need reproducible, versioned migrations for staging/production.

Production Workflow

npx drizzle-kit generate

git add drizzle/
git commit -m "feat: add posts table migration"

npx drizzle-kit migrate

CI/CD Migration Script

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

async function runMigrations() {
  const db = drizzle(process.env.DATABASE_URL!);

  console.log('Running migrations...');
  await migrate(db, { migrationsFolder: './drizzle' });
  console.log('Migrations complete.');

  process.exit(0);
}

runMigrations().catch((err) => {
  console.error('Migration failed:', err);
  process.exit(1);
});

Migrating from Another ORM

Pull existing schema, then manage with Drizzle going forward:

npx drizzle-kit pull

npx drizzle-kit generate

Migration SQL Format

Generated SQL files use --> statement breakpoints:

CREATE TABLE "users" (
  "id" INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  "name" TEXT NOT NULL,
  "email" TEXT NOT NULL UNIQUE
);
--> statement-breakpoint
CREATE TABLE "posts" (
  "id" INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  "title" TEXT NOT NULL,
  "author_id" INTEGER NOT NULL REFERENCES "users"("id")
);

Configuration Reference

Common Drizzle Kit Errors

Mutations

Insert

Single Row

const [newUser] = await db
  .insert(users)
  .values({
    name: 'John',
    email: 'john@example.com',
  })
  .returning();

Batch Insert

const newUsers = await db
  .insert(users)
  .values([
    { name: 'John', email: 'john@example.com' },
    { name: 'Jane', email: 'jane@example.com' },
    { name: 'Bob', email: 'bob@example.com' },
  ])
  .returning();

Insert with Partial Returning

const [{ id, email }] = await db
  .insert(users)
  .values({ name: 'John', email: 'john@example.com' })
  .returning({ id: users.id, email: users.email });

Insert from Select

await db
  .insert(archivedUsers)
  .select(db.select().from(users).where(eq(users.isActive, false)));

Update

Basic Update

const [updated] = await db
  .update(users)
  .set({ name: 'Updated Name' })
  .where(eq(users.id, userId))
  .returning();

Update Multiple Columns

const [updated] = await db
  .update(users)
  .set({
    name: 'New Name',
    email: 'new@example.com',
    updatedAt: new Date(),
  })
  .where(eq(users.id, userId))
  .returning();

Update with SQL Expressions

import { sql } from 'drizzle-orm';

await db
  .update(posts)
  .set({
    viewCount: sql`${posts.viewCount} + 1`,
  })
  .where(eq(posts.id, postId));

Delete

Basic Delete

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

Delete with Returning

const [deleted] = await db
  .delete(users)
  .where(eq(users.id, userId))
  .returning();

Soft Delete Pattern

await db
  .update(posts)
  .set({ deletedAt: new Date() })
  .where(eq(posts.id, postId));

Upsert (ON CONFLICT)

Upsert on Single Column

await db
  .insert(users)
  .values({
    id: 1,
    name: 'John',
    email: 'john@example.com',
  })
  .onConflictDoUpdate({
    target: users.id,
    set: { name: 'John Updated', email: 'john-updated@example.com' },
  });

Upsert on Composite Key

await db
  .insert(userSettings)
  .values({
    userId: 1,
    key: 'theme',
    value: 'dark',
  })
  .onConflictDoUpdate({
    target: [userSettings.userId, userSettings.key],
    set: { value: 'dark' },
  });

Upsert with Where Clause

await db
  .insert(users)
  .values({ id: 1, name: 'John', email: 'john@example.com' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'John Updated' },
    where: eq(users.isActive, true),
  });

On Conflict Do Nothing

await db
  .insert(users)
  .values({ name: 'John', email: 'john@example.com' })
  .onConflictDoNothing();

await db
  .insert(users)
  .values({ name: 'John', email: 'john@example.com' })
  .onConflictDoNothing({ target: users.email });

Transactions

Basic Transaction

await db.transaction(async (tx) => {
  const [user] = await tx
    .insert(users)
    .values({ name: 'John', email: 'john@example.com' })
    .returning();

  await tx.insert(profiles).values({
    userId: user.id,
    bio: 'Hello world',
  });
});

Transaction with Rollback

await db.transaction(async (tx) => {
  await tx.insert(users).values({ name: 'John', email: 'john@example.com' });

  const balance = await tx.query.accounts.findFirst({
    where: eq(accounts.userId, userId),
  });

  if (!balance || balance.amount < requiredAmount) {
    tx.rollback();
  }

  await tx
    .update(accounts)
    .set({ amount: sql`${accounts.amount} - ${requiredAmount}` })
    .where(eq(accounts.userId, userId));
});

Nested Transactions (Savepoints)

await db.transaction(async (tx) => {
  await tx.insert(users).values({ name: 'John', email: 'john@example.com' });

  try {
    await tx.transaction(async (nestedTx) => {
      await nestedTx.insert(posts).values({ title: 'Post', authorId: 1 });
      throw new Error('Rollback nested only');
    });
  } catch {
    // Nested transaction rolled back, outer continues
  }

  await tx.insert(logs).values({ message: 'User created without post' });
});

Transaction Isolation Levels

await db.transaction(
  async (tx) => {
    const [user] = await tx.query.users.findFirst({
      where: eq(users.id, userId),
    });
    await tx
      .update(accounts)
      .set({ balance: user.balance - amount })
      .where(eq(accounts.userId, userId));
  },
  {
    isolationLevel: 'serializable',
  },
);

Supported levels: read uncommitted, read committed, repeatable read, serializable.

Patterns

Create or Fail

async function createUser(data: NewUser): Promise<User> {
  const [user] = await db.insert(users).values(data).returning();
  return user;
}

Update or Throw

async function updateUser(id: number, data: Partial<NewUser>): Promise<User> {
  const [user] = await db
    .update(users)
    .set(data)
    .where(eq(users.id, id))
    .returning();

  if (!user) {
    throw new Error(`User ${id} not found`);
  }
  return user;
}

Batch Operations in Transaction

async function transferFunds(fromId: number, toId: number, amount: number) {
  await db.transaction(async (tx) => {
    await tx
      .update(accounts)
      .set({ balance: sql`${accounts.balance} - ${amount}` })
      .where(eq(accounts.id, fromId));

    await tx
      .update(accounts)
      .set({ balance: sql`${accounts.balance} + ${amount}` })
      .where(eq(accounts.id, toId));
  });
}

Queries

Relational Queries API

Relational queries require passing schema to the drizzle() client.

findMany

const allUsers = await db.query.users.findMany();

const activeUsers = await db.query.users.findMany({
  where: eq(users.isActive, true),
  orderBy: desc(users.createdAt),
  limit: 10,
  offset: 0,
});

findFirst

const user = await db.query.users.findFirst({
  where: eq(users.id, userId),
});

Partial Select (columns)

const userNames = await db.query.users.findMany({
  columns: {
    id: true,
    name: true,
  },
});

const usersWithoutEmail = await db.query.users.findMany({
  columns: {
    email: false,
  },
});

Nested Relations (with)

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

const usersWithNestedData = await db.query.users.findMany({
  with: {
    posts: {
      with: {
        comments: true,
      },
      limit: 5,
      orderBy: desc(posts.createdAt),
    },
  },
});

const partialWithRelations = await db.query.users.findMany({
  columns: { id: true, name: true },
  with: {
    posts: {
      columns: { title: true },
    },
  },
});

Relational Query Filters

const user = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    sessions: {
      orderBy: (sessions, { desc }) => desc(sessions.createdAt),
      limit: 5,
    },
  },
});

SQL-like Query API

Basic Select

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

const userNames = await db
  .select({ name: users.name, email: users.email })
  .from(users);

Select with Conditions

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

const admins = await db.select().from(users).where(eq(users.role, 'admin'));

const activeAdmins = await db
  .select()
  .from(users)
  .where(and(eq(users.role, 'admin'), eq(users.isActive, true)));

const moderatorsOrAdmins = await db
  .select()
  .from(users)
  .where(or(eq(users.role, 'admin'), eq(users.role, 'moderator')));

const johns = await db.select().from(users).where(like(users.name, 'John%'));

const unverified = await db
  .select()
  .from(users)
  .where(isNull(users.emailVerifiedAt));

Ordering, Limit, Offset

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

const recentUsers = await db
  .select()
  .from(users)
  .orderBy(desc(users.createdAt))
  .limit(10)
  .offset(20);

Joins

Inner Join

const usersWithPosts = await db
  .select({
    userName: users.name,
    postTitle: posts.title,
  })
  .from(users)
  .innerJoin(posts, eq(users.id, posts.authorId));

Left Join

const usersWithOptionalPosts = await db
  .select({
    userName: users.name,
    postTitle: posts.title,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

Multiple Joins

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

Subqueries

import { sql } from 'drizzle-orm';

const subquery = db
  .select({
    authorId: posts.authorId,
    postCount: sql<number>`count(*)`.as('post_count'),
  })
  .from(posts)
  .groupBy(posts.authorId)
  .as('post_counts');

const usersWithPostCount = await db
  .select({
    name: users.name,
    postCount: subquery.postCount,
  })
  .from(users)
  .leftJoin(subquery, eq(users.id, subquery.authorId));

Aggregations

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

const totalUsers = await db.select({ count: count() }).from(users);

const postsByAuthor = await db
  .select({
    authorId: posts.authorId,
    totalPosts: count(),
    avgRating: avg(posts.rating),
  })
  .from(posts)
  .groupBy(posts.authorId);

const topAuthors = await db
  .select({
    authorId: posts.authorId,
    totalPosts: count(),
  })
  .from(posts)
  .groupBy(posts.authorId)
  .having(sql`count(*) > 5`);

Distinct

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

Prepared Statements

import { sql } from 'drizzle-orm';

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

const user = await prepared.execute({ id: 1 });
const anotherUser = await prepared.execute({ id: 2 });

Raw SQL Execution

import { sql } from 'drizzle-orm';

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

const customQuery = await db.execute(
  sql`SELECT u.name, COUNT(p.id) as post_count
      FROM ${users} u
      LEFT JOIN ${posts} p ON u.id = p.author_id
      GROUP BY u.name`,
);

Pagination Pattern

const page = 1;
const pageSize = 20;

const paginatedUsers = await db.query.users.findMany({
  orderBy: asc(users.createdAt),
  limit: pageSize,
  offset: (page - 1) * pageSize,
});

const paginatedWithSql = await db
  .select()
  .from(users)
  .orderBy(asc(users.createdAt))
  .limit(pageSize)
  .offset((page - 1) * pageSize);

Relations

Relations in Drizzle are declared separately from table definitions. They enable the relational queries API (db.query) but do not create SQL foreign keys. Use .references() on columns for SQL-level foreign keys, and relations() for the query builder.

One-to-Many

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

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

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

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

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

One-to-One

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

export const profiles = pgTable('profiles', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  bio: text(),
  avatarUrl: text('avatar_url'),
  userId: integer('user_id')
    .notNull()
    .unique()
    .references(() => users.id),
});

export const usersRelations = relations(users, ({ one }) => ({
  profile: one(profiles),
}));

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

Many-to-Many (Junction Table)

export const posts = pgTable('posts', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  title: text().notNull(),
});

export const tags = pgTable('tags', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: text().notNull().unique(),
});

export const postsToTags = pgTable('posts_to_tags', {
  postId: integer('post_id')
    .notNull()
    .references(() => posts.id),
  tagId: integer('tag_id')
    .notNull()
    .references(() => tags.id),
});

export const postsRelations = relations(posts, ({ many }) => ({
  postsToTags: many(postsToTags),
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  postsToTags: many(postsToTags),
}));

export const postsToTagsRelations = relations(postsToTags, ({ one }) => ({
  post: one(posts, {
    fields: [postsToTags.postId],
    references: [posts.id],
  }),
  tag: one(tags, {
    fields: [postsToTags.tagId],
    references: [tags.id],
  }),
}));

Querying Many-to-Many

const postsWithTags = await db.query.posts.findMany({
  with: {
    postsToTags: {
      with: {
        tag: true,
      },
    },
  },
});

const flattenedTags = postsWithTags.map((post) => ({
  ...post,
  tags: post.postsToTags.map((pt) => pt.tag),
}));

Self-Referencing Relations

export const categories = pgTable('categories', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: text().notNull(),
  parentId: integer('parent_id'),
});

export const categoriesRelations = relations(categories, ({ one, many }) => ({
  parent: one(categories, {
    fields: [categories.parentId],
    references: [categories.id],
    relationName: 'parentChild',
  }),
  children: many(categories, {
    relationName: 'parentChild',
  }),
}));

Querying Self-Referencing Relations

const categoriesWithChildren = await db.query.categories.findMany({
  where: isNull(categories.parentId),
  with: {
    children: {
      with: {
        children: true,
      },
    },
  },
});

Multiple Relations to Same Table

export const messages = pgTable('messages', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  content: text().notNull(),
  senderId: integer('sender_id')
    .notNull()
    .references(() => users.id),
  receiverId: integer('receiver_id')
    .notNull()
    .references(() => users.id),
});

export const usersRelations = relations(users, ({ many }) => ({
  sentMessages: many(messages, { relationName: 'sender' }),
  receivedMessages: many(messages, { relationName: 'receiver' }),
}));

export const messagesRelations = relations(messages, ({ one }) => ({
  sender: one(users, {
    fields: [messages.senderId],
    references: [users.id],
    relationName: 'sender',
  }),
  receiver: one(users, {
    fields: [messages.receiverId],
    references: [users.id],
    relationName: 'receiver',
  }),
}));

Querying Multiple Relations

const userWithMessages = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    sentMessages: {
      with: { receiver: true },
      orderBy: desc(messages.createdAt),
      limit: 10,
    },
    receivedMessages: {
      with: { sender: true },
      orderBy: desc(messages.createdAt),
      limit: 10,
    },
  },
});

Nested With Clauses

const result = await db.query.users.findMany({
  with: {
    posts: {
      with: {
        comments: {
          with: {
            author: true,
          },
          orderBy: desc(comments.createdAt),
        },
      },
      where: eq(posts.published, true),
      orderBy: desc(posts.createdAt),
      limit: 5,
    },
    profile: true,
  },
});

Relation Name Disambiguation

When a table has multiple relations to the same target, use relationName to disambiguate:

export const usersRelations = relations(users, ({ many }) => ({
  authoredPosts: many(posts, { relationName: 'author' }),
  editedPosts: many(posts, { relationName: 'editor' }),
}));

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

Schema Organization

export * from './users';
export * from './posts';
export * from './comments';
export * from './tags';

Export all tables and relations from a central schema/index.ts file. Pass the combined schema object to the drizzle() client to enable all relational queries.

import * as schema from './schema';

const db = drizzle(client, { schema });

Schema Definition

Basic Table Definition

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

export const users = pgTable('users', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: text().notNull(),
  email: text().notNull().unique(),
  age: integer(),
  isActive: boolean().default(true),
  createdAt: timestamp({ mode: 'date' }).notNull().defaultNow(),
  updatedAt: timestamp({ mode: 'date' })
    .notNull()
    .defaultNow()
    .$onUpdateFn(() => new Date()),
});

Column Types (PostgreSQL)

Column Modifiers

text().notNull();
text().default('value');
text().unique();
text().$type<'admin' | 'user'>();
timestamp().defaultNow();
timestamp().$onUpdateFn(() => new Date());
integer().references(() => users.id);
integer().references(() => users.id, { onDelete: 'cascade' });

Identity Columns (Recommended over serial)

export const posts = pgTable('posts', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  title: text().notNull(),
});

export const legacyTable = pgTable('legacy', {
  id: serial().primaryKey(),
});

Enums

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

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

export const users = pgTable('users', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: text().notNull(),
  role: roleEnum().default('guest'),
});

JSON Column Typing

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

type UserPreferences = {
  theme: 'light' | 'dark';
  notifications: boolean;
};

export const users = pgTable('users', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  preferences: jsonb().$type<UserPreferences>(),
});

Indexes

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

export const posts = pgTable(
  'posts',
  {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    slug: text().notNull(),
    title: text().notNull(),
    authorId: integer('author_id').references(() => users.id),
  },
  (table) => [
    uniqueIndex('slug_idx').on(table.slug),
    index('title_idx').on(table.title),
    index('author_id_idx').on(table.authorId),
  ],
);

Composite Primary Key

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

export const orderDetails = pgTable(
  'order_details',
  {
    orderId: integer('order_id')
      .notNull()
      .references(() => orders.id),
    productId: integer('product_id')
      .notNull()
      .references(() => products.id),
    quantity: integer().notNull(),
  },
  (table) => [primaryKey({ columns: [table.orderId, table.productId] })],
);

Foreign Keys

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

export const employees = pgTable(
  'employees',
  {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    name: text().notNull(),
    managerId: integer('manager_id'),
  },
  (table) => [
    foreignKey({
      columns: [table.managerId],
      foreignColumns: [table.id],
    }),
  ],
);

export const cyclic = pgTable('cyclic', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  parentId: integer('parent_id').references((): AnyPgColumn => cyclic.id),
});

Type Inference

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

type User = typeof users.$inferSelect;
type NewUser = typeof users.$inferInsert;

type User2 = InferSelectModel<typeof users>;
type NewUser2 = InferInsertModel<typeof users>;

async function createUser(data: NewUser): Promise<User> {
  const [user] = await db.insert(users).values(data).returning();
  return user;
}

Casing Configuration

import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from './schema';

export const db = drizzle(process.env.DATABASE_URL!, {
  casing: 'snake_case',
  schema,
});

With casing: 'snake_case', define columns in camelCase without explicit column name arguments. Drizzle auto-converts createdAt to created_at in SQL.

Schema Validation with Zod

import {
  createInsertSchema,
  createSelectSchema,
  createUpdateSchema,
} from 'drizzle-orm/zod';

const userInsertSchema = createInsertSchema(users);
const userSelectSchema = createSelectSchema(users);
const userUpdateSchema = createUpdateSchema(users);

const userInsertWithRefinements = createInsertSchema(users, {
  name: (schema) => schema.min(2).max(50),
  email: (schema) => schema.email(),
});

const parsed = userInsertSchema.parse({
  name: 'John',
  email: 'john@example.com',
});
await db.insert(users).values(parsed);

MySQL and SQLite Differences

import { mysqlTable, int, varchar, mysqlEnum } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  id: int().primaryKey().autoincrement(),
  name: varchar({ length: 256 }).notNull(),
  role: mysqlEnum(['guest', 'user', 'admin']).default('guest'),
});

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

export const users = sqliteTable('users', {
  id: integer().primaryKey({ autoIncrement: true }),
  name: text().notNull(),
  role: text().$type<'guest' | 'user' | 'admin'>().default('guest'),
});

Key differences:

• PostgreSQL: pgTable, pgEnum, serial() / generatedAlwaysAsIdentity()
• MySQL: mysqlTable, mysqlEnum, autoincrement()
• SQLite: sqliteTable, no native enum (use $type<>()), autoIncrement option

Views and Advanced Features

Views

Regular View

import { pgView } from 'drizzle-orm/pg-core';
import { eq, sql } from 'drizzle-orm';

export const activeUsers = pgView('active_users').as((qb) =>
  qb.select().from(users).where(eq(users.isActive, true)),
);

View with Explicit Columns

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

export const userSummary = pgView('user_summary', {
  name: text('name'),
  postCount: integer('post_count'),
}).as(
  sql`SELECT u.name, COUNT(p.id) as post_count
      FROM users u LEFT JOIN posts p ON u.id = p.author_id
      GROUP BY u.name`,
);

Materialized View

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

export const monthlyStats = pgMaterializedView('monthly_stats', {
  authorName: text('author_name'),
  totalPosts: integer('total_posts'),
}).as(
  sql`SELECT u.name as author_name, COUNT(p.id) as total_posts
      FROM users u LEFT JOIN posts p ON u.id = p.author_id
      GROUP BY u.name`,
);

Query a View

const activeUserList = await db.select().from(activeUsers);

const stats = await db.select().from(monthlyStats);

Refresh Materialized View

await db.refreshMaterializedView(monthlyStats);

MySQL View

import { mysqlView } from 'drizzle-orm/mysql-core';

export const activeUsers = mysqlView('active_users').as((qb) =>
  qb.select().from(users).where(eq(users.isActive, true)),
);

SQLite View

import { sqliteView } from 'drizzle-orm/sqlite-core';

export const activeUsers = sqliteView('active_users').as((qb) =>
  qb.select().from(users).where(eq(users.isActive, true)),
);

Generated Columns

PostgreSQL Generated Columns

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

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

SQLite Generated Columns (Virtual and Stored)

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
import { sql, type SQL } from 'drizzle-orm';

export const products = sqliteTable('products', {
  id: integer().primaryKey({ autoIncrement: true }),
  price: integer().notNull(),
  quantity: integer().notNull(),
  total: integer().generatedAlwaysAs(
    (): SQL => sql`${products.price} * ${products.quantity}`,
    { mode: 'virtual' },
  ),
  totalStored: integer().generatedAlwaysAs(
    (): SQL => sql`${products.price} * ${products.quantity}`,
    { mode: 'stored' },
  ),
});

SQLite supports both virtual (computed on read) and stored (persisted on write) modes. PostgreSQL only supports stored.

Check Constraints

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

export const users = pgTable(
  'users',
  {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    username: text().notNull(),
    age: integer(),
  },
  (table) => [check('age_check', sql`${table.age} > 0`)],
);

Multiple Check Constraints

export const products = pgTable(
  'products',
  {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    name: text().notNull(),
    price: integer().notNull(),
    discountPrice: integer('discount_price'),
  },
  (table) => [
    check('price_positive', sql`${table.price} > 0`),
    check(
      'discount_less_than_price',
      sql`${table.discountPrice} IS NULL OR ${table.discountPrice} < ${table.price}`,
    ),
  ],
);

$count Utility

A shorthand for counting rows, usable standalone or as a subquery.

Standalone Count

const totalUsers = await db.$count(users);

const activeCount = await db.$count(users, eq(users.isActive, true));

Count as Subquery in Select

const usersWithPostCount = await db
  .select({
    id: users.id,
    name: users.name,
    postsCount: db.$count(posts, eq(posts.authorId, users.id)),
  })
  .from(users);

Count in Relational Queries

const usersWithCount = await db.query.users.findMany({
  extras: {
    postsCount: db.$count(posts, eq(posts.authorId, users.id)),
  },
});

Batch API

Execute multiple SQL statements in a single round trip. Supported for LibSQL, Neon, and D1 databases.

const batchResponse = await db.batch([
  db.insert(users).values({ name: 'John' }).returning({ id: users.id }),
  db.update(users).set({ name: 'Dan' }).where(eq(users.id, 1)),
  db.query.users.findMany({}),
  db.select().from(users).where(eq(users.id, 1)),
]);

The batch method returns a tuple matching the input array order, with each element typed according to its query.