Drizzle ORM Domain Skill

Purpose

Provide expert-level guidance on Drizzle ORM, including schema definitions, type-safe queries, relational queries, migrations with Drizzle Kit, transactions, prepared statements, and performance optimization. Drizzle is SQL-first: if you know SQL, you know Drizzle.

Why Drizzle

• Zero overhead: Generates SQL at build time, no runtime query parsing
• SQL-like API: select().from().where() maps directly to SQL
• Full type safety: Schema types flow through all queries
• Serverless-friendly: Lightweight, no heavy runtime, works with edge functions
• Relational queries: Prisma-like with syntax for nested data

Key Patterns

1. Schema Definition

// db/schema/users.ts
import {
  pgTable, text, timestamp, boolean, integer, uuid,
  uniqueIndex, index, pgEnum, check, primaryKey,
} from 'drizzle-orm/pg-core';
import { relations, sql } from 'drizzle-orm';

// Enums
export const roleEnum = pgEnum('role', ['admin', 'member', 'viewer']);

// Tables
export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull(),
  name: text('name').notNull(),
  role: roleEnum('role').notNull().default('member'),
  isActive: boolean('is_active').notNull().default(true),
  credits: integer('credits').notNull().default(0),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow()
    .$onUpdate(() => new Date()),
  deletedAt: timestamp('deleted_at', { withTimezone: true }),
}, (table) => [
  uniqueIndex('users_email_idx').on(table.email),
  index('users_role_created_idx').on(table.role, table.createdAt),
  index('users_active_idx').on(table.email).where(
    sql`${table.isActive} = true AND ${table.deletedAt} IS NULL`
  ),
  check('credits_non_negative', sql`${table.credits} >= 0`),
]);

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  title: text('title').notNull(),
  slug: text('slug').notNull().unique(),
  content: text('content').notNull(),
  published: boolean('published').notNull().default(false),
  publishedAt: timestamp('published_at', { withTimezone: true }),
  authorId: uuid('author_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  viewCount: integer('view_count').notNull().default(0),
  tags: text('tags').array(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow()
    .$onUpdate(() => new Date()),
}, (table) => [
  index('posts_author_published_idx').on(table.authorId, table.published),
  index('posts_published_date_idx').on(table.published, table.publishedAt),
]);

export const categories = pgTable('categories', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull().unique(),
  slug: text('slug').notNull().unique(),
});

export const postsToCategories = pgTable('posts_to_categories', {
  postId: uuid('post_id').notNull().references(() => posts.id, { onDelete: 'cascade' }),
  categoryId: uuid('category_id').notNull().references(() => categories.id, { onDelete: 'cascade' }),
  assignedAt: timestamp('assigned_at', { withTimezone: true }).notNull().defaultNow(),
}, (table) => [
  // Composite primary key
  primaryKey({ columns: [table.postId, table.categoryId] }),
]);

// Relations (for relational query API)
export const usersRelations = relations(users, ({ many, one }) => ({
  posts: many(posts),
  profile: one(profiles, {
    fields: [users.id],
    references: [profiles.userId],
  }),
}));

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

export const postsToCategoriesRelations = relations(postsToCategories, ({ one }) => ({
  post: one(posts, {
    fields: [postsToCategories.postId],
    references: [posts.id],
  }),
  category: one(categories, {
    fields: [postsToCategories.categoryId],
    references: [categories.id],
  }),
}));

2. Database Client Setup

// db/index.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
});

export const db = drizzle(pool, {
  schema,
  logger: process.env.NODE_ENV === 'development',
});

// For serverless (Neon, Vercel Postgres)
import { neon } from '@neondatabase/serverless';
import { drizzle as drizzleNeon } from 'drizzle-orm/neon-http';

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzleNeon(sql, { schema });

// Type exports
export type Database = typeof db;
export type User = typeof schema.users.$inferSelect;
export type NewUser = typeof schema.users.$inferInsert;

3. Query Patterns

import { eq, and, or, gt, lt, desc, asc, sql, ilike, inArray, isNull, count, avg } from 'drizzle-orm';

// Basic CRUD
// Select with filtering
const activeAdmins = await db
  .select()
  .from(users)
  .where(
    and(
      eq(users.role, 'admin'),
      eq(users.isActive, true),
      isNull(users.deletedAt),
    )
  )
  .orderBy(desc(users.createdAt))
  .limit(20);

// Select specific columns
const userEmails = await db
  .select({ id: users.id, email: users.email, name: users.name })
  .from(users)
  .where(eq(users.isActive, true));

// Insert with returning
const [newUser] = await db
  .insert(users)
  .values({
    email: 'jane@example.com',
    name: 'Jane Doe',
    role: 'member',
  })
  .returning();

// Bulk insert
await db.insert(users).values([
  { email: 'a@example.com', name: 'Alice' },
  { email: 'b@example.com', name: 'Bob' },
]);

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

// Upsert (INSERT ... ON CONFLICT)
await db
  .insert(users)
  .values({ email: 'jane@example.com', name: 'Jane', credits: 100 })
  .onConflictDoUpdate({
    target: users.email,
    set: { credits: sql`${users.credits} + 100` },
  });

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

// Joins
const postsWithAuthors = await db
  .select({
    postId: posts.id,
    title: posts.title,
    authorName: users.name,
    authorEmail: users.email,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(eq(posts.published, true))
  .orderBy(desc(posts.createdAt))
  .limit(20);

// Subqueries
const subquery = db
  .select({ authorId: posts.authorId, postCount: count().as('post_count') })
  .from(posts)
  .where(eq(posts.published, true))
  .groupBy(posts.authorId)
  .as('post_counts');

const prolificAuthors = await db
  .select({
    name: users.name,
    email: users.email,
    postCount: subquery.postCount,
  })
  .from(users)
  .innerJoin(subquery, eq(users.id, subquery.authorId))
  .where(gt(subquery.postCount, 10))
  .orderBy(desc(subquery.postCount));

// Aggregations
const stats = await db
  .select({
    role: users.role,
    count: count(),
    avgCredits: avg(users.credits),
  })
  .from(users)
  .where(eq(users.isActive, true))
  .groupBy(users.role);

4. Relational Queries (Prisma-like API)

// Nested queries with the relational API
const usersWithPosts = await db.query.users.findMany({
  where: eq(users.isActive, true),
  columns: {
    id: true,
    name: true,
    email: true,
  },
  with: {
    posts: {
      where: eq(posts.published, true),
      orderBy: [desc(posts.createdAt)],
      limit: 5,
      columns: {
        id: true,
        title: true,
        slug: true,
        createdAt: true,
      },
      with: {
        categories: {
          with: {
            category: true,
          },
        },
      },
    },
    profile: true,
  },
  orderBy: [desc(users.createdAt)],
  limit: 20,
});

// Find first
const user = await db.query.users.findFirst({
  where: eq(users.email, 'jane@example.com'),
  with: { profile: true },
});

5. Transactions and Prepared Statements

// Transaction
async function transferCredits(fromId: string, toId: string, amount: number) {
  return db.transaction(async (tx) => {
    const [sender] = await tx
      .select({ credits: users.credits })
      .from(users)
      .where(eq(users.id, fromId))
      .for('update'); // SELECT ... FOR UPDATE

    if (!sender || sender.credits < amount) {
      throw new Error('Insufficient credits');
      // Transaction auto-rolls back on throw
    }

    await tx
      .update(users)
      .set({ credits: sql`${users.credits} - ${amount}` })
      .where(eq(users.id, fromId));

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

    return { success: true };
  });
}

// Prepared statements (reusable, optimized)
const getUserByEmail = db
  .select()
  .from(users)
  .where(eq(users.email, sql.placeholder('email')))
  .prepare('get_user_by_email');

const user = await getUserByEmail.execute({ email: 'jane@example.com' });

// Dynamic where builder
function buildFilter(params: {
  search?: string;
  role?: string;
  active?: boolean;
}) {
  const conditions = [];

  if (params.search) {
    conditions.push(
      or(
        ilike(users.name, `%${params.search}%`),
        ilike(users.email, `%${params.search}%`),
      )
    );
  }

  if (params.role) {
    conditions.push(eq(users.role, params.role));
  }

  if (params.active !== undefined) {
    conditions.push(eq(users.isActive, params.active));
  }

  return conditions.length > 0 ? and(...conditions) : undefined;
}

6. Migrations with Drizzle Kit

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

export default defineConfig({
  schema: './db/schema/*',
  out: './db/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
});

# Generate migration from schema changes
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Open Drizzle Studio (database GUI)
npx drizzle-kit studio

# Push schema directly (development only)
npx drizzle-kit push

Best Practices

1. Use the SQL-like API for complex queries, relational API for nested reads
2. Define relations even if you only use the SQL API -- enables relational queries later
3. Use $inferSelect and $inferInsert for type exports, not manual type definitions
4. Use prepared statements for frequently executed queries
5. Use sql.placeholder() for dynamic but safe parameterized queries
6. Colocate schema with feature -- e.g., features/users/schema.ts
7. Use drizzle-kit generate for migrations, not push in production
8. Add indexes in the table definition, not as afterthoughts
9. Use .returning() after insert/update to avoid extra SELECT queries
10. Use for('update') in transactions to prevent race conditions

Common Pitfalls

Pitfall	Impact	Fix
Missing relations() definitions	Relational queries fail	Define relations for all foreign keys
Using push in production	Data loss risk	Use generate + migrate in production
Not using .returning()	Extra SELECT query needed	Chain .returning() on insert/update
Missing indexes on foreign keys	Slow joins	Add index() on all FK columns
Not handling undefined in dynamic filters	Invalid SQL generated	Use conditional array building with and()
Forgetting $onUpdate for updatedAt	Stale timestamps	Add .$onUpdate(() => new Date())
Using raw SQL without sql template	SQL injection risk	Always use sql\...`` tagged template