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

// 2. Initialize database connection import { drizzle } from "drizzle-orm/node-postgres"; const db = drizzle(process.env.DATABASE_URL);

// 3. Query with full type safety const allUsers = await db.select().from(users); // ^? { id: number; name: string; }[]


---

## Core Philosophy

### Key Principles

1. **SQL-Like Syntax:** Drizzle mirrors SQL structure, not abstracted ORM patterns
2. **Type Safety First:** Full TypeScript inference from schema to queries
3. **Explicit Over Magic:** No hidden queries, lazy loading, or N+1 problems
4. **Composable Queries:** Build queries programmatically with type safety
5. **Database-First or Code-First:** Choose your migration strategy

### Two Query APIs

**SQL-Like Query Builder** (Core API):
```typescript
await db.select().from(users).where(eq(users.id, 1));

Relational Query Builder (Simpler for relations):

await db.query.users.findMany({
  with: { posts: true }
});

Both are fully type-safe and generate identical SQL under the hood.

Schema Definition Patterns

Basic Table Definition

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

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  age: integer("age"),
  verified: boolean("verified").default(false),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

// Type inference helpers
type User = typeof users.$inferSelect;      // { id: number; name: string; ... }
type NewUser = typeof users.$inferInsert;   // Omits id, uses defaults

Column Aliases (TypeScript != Database)

// Use different names in code vs database
export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  firstName: text("first_name"),  // TS: firstName, DB: first_name
  lastName: text("last_name"),
});

// Or use automatic snake_case mapping
const db = drizzle({
  connection: process.env.DATABASE_URL,
  casing: "snake_case"  // Auto-converts camelCase -> snake_case
});

Dialect-Specific Tables

// PostgreSQL
import { pgTable, serial, text } from "drizzle-orm/pg-core";
export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name"),
});

// MySQL
import { mysqlTable, int, varchar } from "drizzle-orm/mysql-core";
export const users = mysqlTable("users", {
  id: int("id").primaryKey().autoincrement(),
  name: varchar("name", { length: 255 }),
});

// SQLite
import { sqliteTable, integer, text } from "drizzle-orm/sqlite-core";
export const users = sqliteTable("users", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name"),
});

Constraints & Indexes

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

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: text("email").notNull(),
  name: text("name"),
}, (table) => [
  uniqueIndex("email_idx").on(table.email),
  index("name_idx").on(table.name),
  index("name_email_idx").on(table.name, table.email),
]);

Foreign Keys & Relations

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

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

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  content: text("content").notNull(),
  authorId: integer("author_id")
    .notNull()
    .references(() => users.id, {
      onDelete: "cascade",
      onUpdate: "cascade"
    }),
});

// Define relations for relational queries (query-only, not DB constraints)
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

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

Enums

// PostgreSQL native enum
import { pgEnum, pgTable, serial } from "drizzle-orm/pg-core";

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

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  role: roleEnum().default("guest"),
});

// SQLite (no native enum, use text with type constraint)
import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";

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

Reusable Column Patterns

// columns.helpers.ts
import { timestamp } from "drizzle-orm/pg-core";

export const timestamps = {
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
  deletedAt: timestamp("deleted_at"),
};

// users.ts
export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name"),
  ...timestamps,  // Spread pattern
});

Query Building & Execution

Basic SELECT Queries

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

// Partial select
const names = await db.select({
  id: users.id,
  name: users.name
}).from(users);

// Select with SQL expression
import { sql } from "drizzle-orm";

const result = await db.select({
  id: users.id,
  lowerName: sql<string>`lower(${users.name})`.as("lower_name"),
}).from(users);

Filtering with WHERE

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

// Single condition
await db.select().from(users).where(eq(users.id, 42));

// Multiple conditions (AND)
await db.select().from(users).where(
  and(
    eq(users.role, "admin"),
    gt(users.age, 18)
  )
);

// OR conditions
await db.select().from(users).where(
  or(
    eq(users.role, "admin"),
    eq(users.role, "moderator")
  )
);

// Common operators
await db.select().from(users).where(gt(users.age, 18));           // age > 18
await db.select().from(users).where(like(users.name, "%John%"));  // name LIKE '%John%'
await db.select().from(users).where(ilike(users.email, "%@gmail.com")); // case-insensitive
await db.select().from(users).where(inArray(users.id, [1, 2, 3]));
await db.select().from(users).where(between(users.age, 18, 65));
await db.select().from(users).where(isNull(users.deletedAt));

Dynamic/Conditional Filters

import { and, type SQL } from "drizzle-orm";

const searchUsers = async (filters: { name?: string; minAge?: number }) => {
  const conditions: SQL[] = [];

  if (filters.name) {
    conditions.push(like(users.name, `%${filters.name}%`));
  }
  if (filters.minAge) {
    conditions.push(gte(users.age, filters.minAge));
  }

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

ORDER BY, LIMIT, OFFSET

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

// Pagination
await db.select().from(users)
  .orderBy(desc(users.createdAt))
  .limit(10)
  .offset(20);

// Pagination helper
const getUsers = async (page = 1, pageSize = 10) => {
  return db.select().from(users)
    .orderBy(users.id)
    .limit(pageSize)
    .offset((page - 1) * pageSize);
};

Aggregations

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

// Count with groupBy
const roleStats = await db.select({
  role: users.role,
  count: count(),
}).from(users).groupBy(users.role);

// $count helper (simplified count)
const userCount = await db.$count(users);
const adminCount = await db.$count(users, eq(users.role, "admin"));

INSERT Operations

Basic Insert

// Insert single row
await db.insert(users).values({
  name: "John Doe",
  email: "john@example.com",
});

// Insert multiple rows
await db.insert(users).values([
  { name: "Alice", email: "alice@example.com" },
  { name: "Bob", email: "bob@example.com" },
]);

Insert with RETURNING (PostgreSQL/SQLite)

const [user] = await db.insert(users)
  .values({ name: "Jane", email: "jane@example.com" })
  .returning();

// Return specific columns
const [result] = await db.insert(users)
  .values({ name: "Jane", email: "jane@example.com" })
  .returning({ insertedId: users.id });

Upserts (ON CONFLICT)

// Do nothing on conflict
await db.insert(users)
  .values({ id: 1, name: "John", email: "john@example.com" })
  .onConflictDoNothing({ target: users.email });

// Update on conflict
await db.insert(users)
  .values({ email: "john@example.com", name: "John Updated" })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: "John Updated" },
  });

UPDATE Operations

Basic Update

await db.update(users)
  .set({ name: "Mr. Dan" })
  .where(eq(users.id, 42));

// Undefined values are ignored, use null explicitly
await db.update(users)
  .set({
    middleName: null,        // Sets to NULL
    nickname: undefined,     // Ignored (not updated)
  })
  .where(eq(users.id, 1));

Update with SQL Expressions

// Increment counter
await db.update(posts)
  .set({ views: sql`${posts.views} + 1` })
  .where(eq(posts.id, 1));

Update with RETURNING (PostgreSQL/SQLite)

const [updatedUser] = await db.update(users)
  .set({ name: "John Smith" })
  .where(eq(users.id, 1))
  .returning();

DELETE Operations

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

// Delete with RETURNING
const deletedUsers = await db.delete(users)
  .where(eq(users.role, "guest"))
  .returning();

Relations & Joins

Relational Query API

// Must pass schema to drizzle()
import * as schema from "./schema";
const db = drizzle({ client, schema });

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

// Nested relations with filters
const result = await db.query.users.findMany({
  columns: {
    id: true,
    name: true,
  },
  with: {
    posts: {
      where: (posts, { eq }) => eq(posts.published, true),
      limit: 5,
      orderBy: (posts, { desc }) => [desc(posts.createdAt)],
      columns: {
        id: true,
        title: true,
      },
    },
  },
});

SQL Joins (Core API)

import { eq } from "drizzle-orm";

// LEFT JOIN
const result = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));
// Result: { users: User; posts: Post | null }[]

// INNER JOIN
const result = await db
  .select()
  .from(users)
  .innerJoin(posts, eq(users.id, posts.authorId));
// Result: { users: User; posts: Post }[]

// Partial select with joins
const result = await db
  .select({
    userId: users.id,
    userName: users.name,
    postTitle: posts.title,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

Transactions

Basic Transaction

await db.transaction(async (tx) => {
  await tx.update(accounts)
    .set({ balance: sql`${accounts.balance} - 100` })
    .where(eq(accounts.userId, 1));

  await tx.update(accounts)
    .set({ balance: sql`${accounts.balance} + 100` })
    .where(eq(accounts.userId, 2));
});

Transaction Rollback

await db.transaction(async (tx) => {
  const [account] = await tx.select()
    .from(accounts)
    .where(eq(accounts.userId, 1));

  if (account.balance < 100) {
    tx.rollback();  // Throws error to rollback
  }

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

Return Values from Transactions

const newBalance = await db.transaction(async (tx) => {
  await tx.update(accounts)
    .set({ balance: sql`${accounts.balance} - 100` })
    .where(eq(accounts.userId, 1));

  const [account] = await tx.select({ balance: accounts.balance })
    .from(accounts)
    .where(eq(accounts.userId, 1));

  return account.balance;
});

Migrations

Configuration File

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",  // "mysql" | "sqlite" | "turso"
  schema: "./src/db/schema",
  out: "./drizzle",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Migration Commands

# Generate migration files
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Push without migrations (prototyping only)
npx drizzle-kit push

# Pull from database
npx drizzle-kit pull

# Check for pending migrations
npx drizzle-kit check

# Studio (database browser)
npx drizzle-kit studio

Runtime Migrations

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" });

Advanced Query Patterns

WITH Clause (CTEs)

const sq = db.$with("sq").as(
  db.select().from(users).where(eq(users.id, 42))
);

const result = await db.with(sq).select().from(sq);

Subqueries

// Subquery in WHERE
const result = await db.select().from(users).where(
  inArray(
    users.id,
    db.select({ id: posts.authorId }).from(posts)
  )
);

Dynamic Query Building

import { type PgSelect } from "drizzle-orm/pg-core";

function withPagination<T extends PgSelect>(
  qb: T,
  page = 1,
  pageSize = 10
) {
  return qb.limit(pageSize).offset((page - 1) * pageSize);
}

let dynamicQuery = db.select().from(users).$dynamic();
dynamicQuery = withPagination(dynamicQuery, 2, 20);
const results = await dynamicQuery;

Prepared Statements

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

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

The Magic `sql` Operator

Basic Usage

import { sql } from "drizzle-orm";

// Raw SQL in queries (parameterized - safe from injection)
await db.execute(sql`SELECT * FROM users WHERE id = ${42}`);

// In SELECT with type annotation
const result = await db.select({
  id: users.id,
  upperName: sql<string>`upper(${users.name})`,
}).from(users);

Type Annotations

const result = await db.select({
  count: sql<number>`count(*)`.mapWith(Number),
  avg: sql<number>`avg(${users.age})`,
}).from(users);

Raw SQL (No Escaping) - Use with Caution

// sql.raw() - no parameterization (dangerous with user input!)
const tableName = "users";
await db.execute(sql`SELECT * FROM ${sql.raw(tableName)}`);

Building SQL Dynamically

const conditions = [
  sql`${users.age} > 18`,
  sql`${users.verified} = true`,
];
const where = sql.join(conditions, sql` AND `);
// Result: age > 18 AND verified = true

Common Patterns & Best Practices

Repository Pattern

export class UsersRepository {
  async findById(id: number) {
    return db.query.users.findFirst({
      where: (users, { eq }) => eq(users.id, id),
    });
  }

  async create(data: typeof users.$inferInsert) {
    const [user] = await db.insert(users).values(data).returning();
    return user;
  }

  async update(id: number, data: Partial<typeof users.$inferInsert>) {
    const [user] = await db.update(users)
      .set(data)
      .where(eq(users.id, id))
      .returning();
    return user;
  }
}

Soft Deletes

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  deletedAt: timestamp("deleted_at"),
});

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

const softDelete = async (id: number) => {
  await db.update(users)
    .set({ deletedAt: new Date() })
    .where(eq(users.id, id));
};

Incremental Fields

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

// Toggle boolean
await db.update(users)
  .set({ verified: sql`NOT ${users.verified}` })
  .where(eq(users.id, userId));

Critical Rules & Gotchas

Schema & Types

DO	DON'T
Use dialect-specific table constructors (`pgTable`, `mysqlTable`, `sqliteTable`)	Mix table constructors across dialects
Export tables and relations for relational queries	Forget to export schema for Drizzle Kit
Use `$inferSelect` and `$inferInsert` for type inference	Use optional (`?`) in TS types instead of schema validators
Use `.notNull()` to enforce required fields	Assume foreign keys exist without defining them

Queries

DO	DON'T
Use prepared statements for repeated queries	Chain multiple `.where()` calls (use `and()` or `or()`)
Use `.$dynamic()` for conditional query building	Use `sql.raw()` with user input (SQL injection risk)
Explicitly type `sql<T>` for custom expressions	Forget `.as()` when using `sql` in SELECT
Use `.mapWith()` for runtime type transformations	Assume `sql<T>` performs runtime type casting

Relations

DO	DON'T
Define relations for relational query API	Confuse relations (query-only) with foreign keys (DB constraints)
Pass schema to `drizzle()` for relational queries	Expect relations to create foreign keys automatically
Use `relationName` to disambiguate multiple relations	Use relational queries without passing schema

Transactions

DO	DON'T
Use transactions for multi-step operations	Catch errors inside transactions without re-throwing
Use nested transactions (savepoints) when needed	Use original `db` inside transaction callback (use `tx`)
Call `tx.rollback()` to abort	Assume transactions are SERIALIZABLE by default

Performance

DO	DON'T
Use indexes on foreign keys and frequently queried columns	Select all columns when you need only a few
Use prepared statements for repeated queries	Perform N+1 queries (use joins or relational queries)
Batch operations in transactions	Forget to add indexes on join columns

Database-Specific Features

PostgreSQL

// Arrays
export const posts = pgTable("posts", {
  tags: text("tags").array(),
});

// JSON/JSONB
export const users = pgTable("users", {
  metadata: jsonb("metadata").$type<{ theme: string }>(),
});

// UUID
export const users = pgTable("users", {
  id: uuid("id").defaultRandom().primaryKey(),
});

// Schemas (namespaces)
const mySchema = pgSchema("my_schema");
export const users = mySchema.table("users", { ... });

SQLite

// INTEGER PRIMARY KEY = auto-increment
export const users = sqliteTable("users", {
  id: integer("id").primaryKey({ autoIncrement: true }),
});

// Type-safe enums (using text + TypeScript)
export const users = sqliteTable("users", {
  role: text("role").$type<"admin" | "user">().default("user"),
});

Reference Map

Schema Definition Patterns

Basic Table Definition

Column Aliases (TypeScript != Database)

Dialect-Specific Tables

Constraints & Indexes

Foreign Keys & Relations

Enums

Reusable Column Patterns

Query Building & Execution

Basic SELECT Queries

Filtering with WHERE

Dynamic/Conditional Filters

ORDER BY, LIMIT, OFFSET

Aggregations

INSERT Operations

Basic Insert

Insert with RETURNING (PostgreSQL/SQLite)

Upserts (ON CONFLICT)

UPDATE Operations

Basic Update

Update with SQL Expressions

Update with RETURNING (PostgreSQL/SQLite)

DELETE Operations

Relations & Joins

Relational Query API

SQL Joins (Core API)

Transactions

Basic Transaction

Transaction Rollback

Return Values from Transactions

Migrations

Configuration File

Migration Commands

Runtime Migrations

Advanced Query Patterns

WITH Clause (CTEs)

Subqueries

Dynamic Query Building

Prepared Statements

The Magic sql Operator

Basic Usage

Type Annotations

Raw SQL (No Escaping) - Use with Caution

Building SQL Dynamically

Common Patterns & Best Practices

Repository Pattern

Soft Deletes

Incremental Fields

Critical Rules & Gotchas

Schema & Types

Queries

Relations

Transactions

Performance

Database-Specific Features

PostgreSQL

SQLite

Reference Map

Core Documentation

Query Operations

Advanced Features

Migrations & Tools

The Magic `sql` Operator

Drizzle ORM

Drizzle ORM

What This Skill Does

Prerequisites

Quick Start