Drizzle Orm Expert

---
name: drizzle-orm-expert
description: Expert in Drizzle ORM for TypeScript — schema design, relational queries, migrations, serverless DB integration. Use when building type-safe database layers with Drizzle, migrating from Prisma, or optimizing edge runtime DB access
user-invocable: false
---

# Drizzle ORM Expert

TypeScript-first ORM that compiles to raw SQL with zero runtime overhead. Ideal for edge runtimes and serverless. Two complementary APIs: SQL-like query builder and Prisma-style relational queries.

## Use this skill when

- Setting up Drizzle ORM in a new or existing project
- Designing database schemas with Drizzle's TypeScript-first approach
- Writing complex relational queries (joins, subqueries, aggregations)
- Setting up or troubleshooting Drizzle Kit migrations
- Integrating Drizzle with Next.js App Router, tRPC, or Hono
- Optimizing performance (prepared statements, batching, pooling)
- Migrating from Prisma, TypeORM, or Knex

## Do not use this skill when

- The DB layer uses Prisma, TypeORM, or raw SQL exclusively
- You need a database-agnostic ORM guide
- The task is about DB server configuration (use `postgresql-optimization`)

## Workflow

1. Identify the DB driver (PostgreSQL, SQLite, MySQL) and select the adapter
2. Define schema in `db/schema.ts` (or domain-split `db/schema/users.ts`)
3. Define relations separately so the relational query API (`db.query.*`) works
4. Use `InferSelectModel` and `InferInsertModel` for type inference — never manual interfaces
5. Run `drizzle-kit generate` then `migrate` for schema changes in production
6. **Never** use `drizzle-kit push` in production

## Core Architecture

Drizzle ORM compiles TypeScript schema definitions into prepared SQL statements at build time. There is no runtime query engine binary (unlike Prisma), which makes it edge-compatible (Cloudflare Workers, Vercel Edge Functions, Deno Deploy).

Two APIs coexist:
- **SQL-like builder**: `db.select().from().where().join().orderBy()` — close to SQL semantics
- **Relational query API**: `db.query.users.findMany({ with: { posts: true } })` — Prisma-style nested fetching

## Schema Design

```typescript
import { pgTable, uuid, text, timestamp, pgEnum, index, uniqueIndex } from 'drizzle-orm/pg-core';

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

export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  email: text('email').notNull().unique(),
  role: roleEnum('role').default('user').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  emailIdx: uniqueIndex('users_email_idx').on(table.email),
  createdIdx: index('users_created_idx').on(table.createdAt),
}));

export const posts = pgTable('posts', {
  id: uuid('id').defaultRandom().primaryKey(),
  userId: uuid('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
  title: text('title').notNull(),
});
```

## Relations API

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

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

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

// Initialize client WITH schema for db.query.* to work:
import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from './schema';
export const db = drizzle(pool, { schema });
```

Many-to-many requires an explicit junction table — Drizzle has no implicit join tables.

## Queries

### SQL-like builder
```typescript
import { eq, and, desc, count } from 'drizzle-orm';

const recent = await db
  .select({ id: posts.id, title: posts.title, author: users.email })
  .from(posts)
  .innerJoin(users, eq(users.id, posts.userId))
  .where(and(eq(users.role, 'user'), eq(posts.published, true)))
  .orderBy(desc(posts.createdAt))
  .limit(20);
```

### Relational

In `db.query.<table>.findMany`, the `where` / `orderBy` clauses can be either a value built from the imported schema's table (`eq(users.role, 'user')`) or, with the latest relational query builder, a callback that receives the table:

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

const activeUsers = await db.query.users.findMany({
  where: (user, { eq }) => eq(user.role, 'user'),
  orderBy: (user, { desc }) => desc(user.createdAt),
  with: {
    posts: {
      where: (post, { eq }) => eq(post.published, true),
      limit: 5,
    },
  },
  limit: 20,
});
```

Both forms work. The callback form avoids shadowing the imported `users` table when the local result variable would otherwise collide.

## Mutations

```typescript
// Insert + returning (Postgres only)
const [inserted] = await db.insert(users).values({ email: 'a@b.c' }).returning();

// Batch
await db.insert(users).values([{ email: 'x@x' }, { email: 'y@y' }]);

// Update
await db.update(users).set({ role: 'admin' }).where(eq(users.id, id));

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

// Upsert
await db.insert(users)
  .values({ id, email })
  .onConflictDoUpdate({ target: users.id, set: { email } });
```

## Transactions

```typescript
await db.transaction(async (tx) => {
  await tx.insert(orders).values(order);
  await tx.update(inventory).set({ stock: sql`stock - 1` });
  // any throw → rollback
});
```

## Drizzle Kit

```bash
drizzle-kit generate    # write SQL migration files (review them)
drizzle-kit migrate     # apply pending migrations — production
drizzle-kit push        # sync schema directly — DEV ONLY, NEVER prod
drizzle-kit studio      # GUI database browser
```

`drizzle.config.ts`:
```typescript
import type { Config } from 'drizzle-kit';
export default {
  schema: './db/schema.ts',
  out: './db/migrations',
  dialect: 'postgresql',
  dbCredentials: { url: process.env.DATABASE_URL! },
} satisfies Config;
```

## Adapters

| DB | Adapter |
|---|---|
| Postgres (Neon) | `drizzle-orm/neon-http` |
| Postgres (node-pg) | `drizzle-orm/node-postgres` |
| Postgres (postgres.js) | `drizzle-orm/postgres-js` |
| SQLite (Turso) | `drizzle-orm/libsql` |
| SQLite (local) | `drizzle-orm/better-sqlite3` |
| MySQL (PlanetScale) | `drizzle-orm/planetscale-serverless` |
| MySQL (mysql2) | `drizzle-orm/mysql2` |

Always pass `{ schema }` to enable `db.query.*`.

## Type Inference

```typescript
import { InferSelectModel, InferInsertModel } from 'drizzle-orm';
type User = InferSelectModel<typeof users>;
type NewUser = InferInsertModel<typeof users>;
```

Never write manual TS interfaces for DB rows — schema is the source of truth.

## Performance

- **Prepared statements** for hot paths: `.prepare('getUser')` once, execute many times
- **Partial select**: choose only needed columns
- **Batch**: `db.batch([...])` for many independent queries in one round-trip (libsql / d1 only)
- **Indexes**: declare `index()` / `uniqueIndex()` inside the table builder

## Next.js Integration

```typescript
// app/dashboard/page.tsx — Server Component
import { db } from '@/db';

export default async function Dashboard() {
  const allUsers = await db.query.users.findMany({ limit: 20 });
  return <UsersList users={allUsers} />;
}
```

```typescript
// Server Action
'use server';
import { db } from '@/db';
export async function createUser(formData: FormData) {
  await db.insert(users).values({ email: formData.get('email') as string });
}
```

## Constraints

- **NEVER** use `drizzle-kit push` in production — can cause data loss
- **NEVER** write raw SQL when the query builder supports the op
- **ALWAYS** define `relations()` before using `db.query.*` with `with`
- **ALWAYS** use connection pooling (`pgBouncer`, `Neon serverless`, `node-pg Pool`)
- **MySQL** doesn't support `.returning()` — read `insertId` from the result

## Common Issues

| Symptom | Cause | Fix |
|---------|-------|-----|
| `db.query.users` is undefined | `{ schema }` not passed to `drizzle()` | Pass schema |
| Migration conflict | Out-of-sync DB state | `generate` new migration, review SQL, `migrate` |
| Type error on `relations` | Forgot to `export const usersRelations = relations(...)` | Add export |
| Slow query in Edge runtime | New connection per request | Use a serverless adapter (Neon, PlanetScale) or HTTP-based driver |

Adapted from VKirill/codex-starter-kit (Apache 2.0 community skill).