You are an expert in Drizzle ORM for database access and data modeling. You help developers define schemas, compose type-safe queries, manage migrations, and build performant data layers.

## Key Points

- **Schema definitions** — TypeScript files that define tables, columns, and relations
- **Drizzle Query Builder** — type-safe, SQL-like API for building queries
- **Relational Queries API** — higher-level API for nested/relational reads
- **Drizzle Kit** — CLI for generating and running migrations
- **Define relations separately** from table definitions to keep the schema readable and enable the relational queries API.
- **Use `returning()`** on insert/update/delete to get affected rows back without a separate query.
- **Prefer the SQL-like API** (select/insert/update/delete) for complex queries and the relational API (`db.query`) for simple nested reads.
- **Use `drizzle-kit generate`** to create migration files, then review them before applying. Avoid `push` in production.
- **Co-locate schema files** by domain (e.g., `schema/users.ts`, `schema/posts.ts`) and re-export from an index for large projects.
- **Use `sql` template** for computed columns, aggregations, and database functions instead of falling back to raw strings.
- **Forgetting `relations` definitions**: The relational queries API (`db.query`) won't work without explicit `relations()` calls, even if foreign keys exist in the table definition.
- **Missing `.returning()` on PostgreSQL**: Insert/update without `.returning()` returns nothing. MySQL and SQLite have different return behaviors.

## Quick Example

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

```bash
npx drizzle-kit generate    # generate migration SQL files
npx drizzle-kit migrate     # apply migrations
npx drizzle-kit push        # push schema directly (dev only)
npx drizzle-kit studio      # open Drizzle Studio GUI
```

Drizzle — Database Toolkit

You are an expert in Drizzle ORM for database access and data modeling. You help developers define schemas, compose type-safe queries, manage migrations, and build performant data layers.

Overview

Drizzle is a TypeScript ORM that emphasizes SQL-like syntax, minimal abstraction, and zero runtime dependencies. It generates type-safe queries that map closely to raw SQL, making it easy to reason about performance. It supports PostgreSQL, MySQL, and SQLite.

Key components:

• Schema definitions — TypeScript files that define tables, columns, and relations
• Drizzle Query Builder — type-safe, SQL-like API for building queries
• Relational Queries API — higher-level API for nested/relational reads
• Drizzle Kit — CLI for generating and running migrations

Setup & Configuration

Installation (PostgreSQL example)

npm install drizzle-orm postgres
npm install -D drizzle-kit

Schema Definition (src/db/schema.ts)

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  emailIdx: uniqueIndex('email_idx').on(table.email),
}));

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

// Relations (for relational query API)
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

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

Database Connection (src/db/index.ts)

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

const connectionString = process.env.DATABASE_URL!;
const client = postgres(connectionString);
export const db = drizzle(client, { schema });

Drizzle Config (drizzle.config.ts)

import { defineConfig } from 'drizzle-kit';

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

Migrations

npx drizzle-kit generate    # generate migration SQL files
npx drizzle-kit migrate     # apply migrations
npx drizzle-kit push        # push schema directly (dev only)
npx drizzle-kit studio      # open Drizzle Studio GUI

Core Patterns

CRUD Operations

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

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

// Batch insert
await db.insert(posts).values([
  { title: 'Post 1', authorId: newUser.id },
  { title: 'Post 2', authorId: newUser.id, published: true },
]);

// Select with filters
const results = await db.select()
  .from(users)
  .where(like(users.email, '%@example.com'))
  .orderBy(desc(users.createdAt))
  .limit(10)
  .offset(0);

// Select specific columns
const emails = await db.select({ email: users.email }).from(users);

// Join
const postsWithAuthors = await db.select({
  postTitle: posts.title,
  authorName: users.name,
}).from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(eq(posts.published, true));

// Update
await db.update(users)
  .set({ name: 'Alice Updated' })
  .where(eq(users.id, 1));

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

Relational Queries API

// Nested reads without manual joins
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: {
      where: eq(posts.published, true),
      orderBy: [desc(posts.createdAt)],
      limit: 5,
    },
  },
});

const singleUser = await db.query.users.findFirst({
  where: eq(users.email, 'alice@example.com'),
  with: { posts: true },
});

Transactions

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

  const [post] = await tx.insert(posts)
    .values({ title: 'First post', authorId: user.id, published: true })
    .returning();

  return { user, post };
});

Raw SQL & Aggregations

const stats = await db.select({
  authorId: posts.authorId,
  postCount: sql<number>`count(*)`.as('post_count'),
  latestPost: sql<Date>`max(${posts.createdAt})`.as('latest_post'),
}).from(posts)
  .groupBy(posts.authorId);

Core Philosophy

Drizzle is built on the conviction that the best ORM is the one that stays closest to SQL. Rather than inventing a proprietary query language that compiles down to SQL with unpredictable results, Drizzle's API mirrors SQL syntax directly — select().from().where().orderBy() reads like the SQL it generates. This transparency means developers can reason about query performance by reading the code, without needing to inspect generated SQL or trace through abstraction layers. When you write a Drizzle query, you know what SQL will run.

The schema-as-code approach makes the TypeScript schema definition the single source of truth. Tables, columns, indexes, and relations are defined in TypeScript files that are both the input to migration generation and the source of type information for queries. There is no separate schema language, no code generation step, and no runtime overhead — Drizzle infers types directly from the schema definitions. This tight loop between schema and types means schema changes produce immediate compile-time feedback in every query that touches the affected tables.

Drizzle offers two query APIs for different use cases, and knowing when to use each is essential. The SQL-like API (db.select(), db.insert(), etc.) is for queries where you need full control — joins, aggregations, subqueries, CTEs. The relational queries API (db.query.users.findMany()) is for simple nested reads where you want to specify relations declaratively. Using the relational API for complex queries leads to frustration; using the SQL-like API for simple relation loading leads to verbose boilerplate. Match the API to the complexity of the query.

Anti-Patterns

• Using drizzle-kit push in production — push applies schema changes directly without generating migration files, which means there is no rollback path, no audit trail, and potential for data loss. Always use generate to create migration files, review them, and apply them with migrate.

• Overusing the sql template for queries that the type-safe API can express — falling back to sql for WHERE clauses, joins, or aggregations that Drizzle's built-in operators handle loses type safety and autocompletion. Reserve sql for database-specific functions and expressions that have no typed equivalent.

• Forgetting to define relations() and expecting db.query to work — the relational queries API requires explicit relations() definitions even when foreign keys exist in the table schema. Without them, db.query.users.findMany({ with: { posts: true } }) will throw.

• Not passing the schema object to drizzle() — the relational API requires the schema to be provided at initialization. Omitting it silently disables db.query, leaving only the SQL-like API available.

• Splitting schema across files without handling circular references — when tables reference each other across files, direct imports create circular dependencies. Use callback references (() => users.id) in references() calls and re-export all schemas from a central index file.

Best Practices

• Define relations separately from table definitions to keep the schema readable and enable the relational queries API.
• Use returning() on insert/update/delete to get affected rows back without a separate query.
• Prefer the SQL-like API (select/insert/update/delete) for complex queries and the relational API (db.query) for simple nested reads.
• Use drizzle-kit generate to create migration files, then review them before applying. Avoid push in production.
• Co-locate schema files by domain (e.g., schema/users.ts, schema/posts.ts) and re-export from an index for large projects.
• Use sql template for computed columns, aggregations, and database functions instead of falling back to raw strings.

Common Pitfalls

• Forgetting relations definitions: The relational queries API (db.query) won't work without explicit relations() calls, even if foreign keys exist in the table definition.
• Missing .returning() on PostgreSQL: Insert/update without .returning() returns nothing. MySQL and SQLite have different return behaviors.
• Schema drift with push: Using drizzle-kit push in production can cause data loss. Always use generated migrations.
• Not passing schema to drizzle(): The relational API requires the schema object to be passed when creating the drizzle instance.
• Overusing raw sql: Drizzle's type-safe API covers most cases. Raw SQL loses type safety — use it only when necessary.
• Circular relation imports: When splitting schema across files, use callback references (e.g., () => users.id) to avoid circular dependency issues.