drizzle-orm-expert

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

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(),
});