Claude Code for Drizzle ORM Advanced: Relations, Migrations, and Multi-Tenant

Drizzle ORM provides type-safe SQL query building with a schema-as-code approach — table definitions generate TypeScript types and migration SQL simultaneously. relations() defines one-to-many, many-to-many, and one-to-one relationships that compose in the db.query API with nested with clauses. drizzle-kit generate produces migration files from schema changes; drizzle-kit migrate applies them. Custom column types handle JSONB, PostgreSQL enums, and array columns. db.transaction() wraps operations atomically. Row-level security (RLS) policies set in migrations isolate tenant data without application-layer filtering. Neon’s HTTP driver drops into Drizzle for serverless functions where persistent connections aren’t available. Claude Code generates Drizzle schema files with full relations, migration scripts, RLS policy migrations, multi-tenant patterns, and the query compositions for complex reporting queries.

CLAUDE.md for Drizzle Advanced

## Drizzle ORM Stack
- Version: drizzle-orm >= 0.38, drizzle-kit >= 0.30
- Schema: pgTable/mysqlTable/sqliteTable with columns — relations() for associations
- Query: db.query.posts.findMany({ with: { author: true, tags: true } }) — relations API
- Builder: db.select().from(posts).leftJoin(authors, eq(posts.authorId, authors.id))
- Migrations: drizzle-kit generate → migrations/; drizzle-kit migrate → applies to DB
- RLS: sql`CREATE POLICY` in custom migrations + enableRlsPlugin for Postgres
- Transactions: db.transaction(async tx => { await tx.insert(...); })
- Multi-tenant: appSchema per tenant schema OR row-level app_id column with RLS

Schema with Relations

// db/schema/index.ts — complete schema with relationships
import {
  pgTable,
  uuid,
  varchar,
  integer,
  timestamp,
  text,
  boolean,
  jsonb,
  pgEnum,
  index,
  uniqueIndex,
} from "drizzle-orm/pg-core"
import { relations, sql } from "drizzle-orm"

// Enums
export const orderStatusEnum = pgEnum("order_status", [
  "pending", "processing", "shipped", "delivered", "cancelled"
])

export const userRoleEnum = pgEnum("user_role", ["customer", "admin", "editor"])

// Users table
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: varchar("email", { length: 254 }).notNull().unique(),
  name: varchar("name", { length: 100 }).notNull(),
  role: userRoleEnum("role").notNull().default("customer"),
  stripeCustomerId: varchar("stripe_customer_id", { length: 50 }),
  metadata: jsonb("metadata").$type<Record<string, unknown>>().default(sql`'{}'::jsonb`),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
}, table => ({
  emailIdx: uniqueIndex("users_email_idx").on(table.email),
}))

// Products table
export const products = pgTable("products", {
  id: uuid("id").primaryKey().defaultRandom(),
  slug: varchar("slug", { length: 100 }).notNull().unique(),
  name: varchar("name", { length: 200 }).notNull(),
  description: text("description"),
  priceCents: integer("price_cents").notNull(),
  stock: integer("stock").notNull().default(0),
  active: boolean("active").notNull().default(true),
  tags: text("tags").array().notNull().default(sql`ARRAY[]::text[]`),
  createdAt: timestamp("created_at").notNull().defaultNow(),
}, table => ({
  slugIdx: uniqueIndex("products_slug_idx").on(table.slug),
  activeIdx: index("products_active_idx").on(table.active),
}))

// Orders table
export const orders = pgTable("orders", {
  id: uuid("id").primaryKey().defaultRandom(),
  customerId: uuid("customer_id").notNull().references(() => users.id),
  status: orderStatusEnum("status").notNull().default("pending"),
  totalCents: integer("total_cents").notNull(),
  shippingAddressId: uuid("shipping_address_id").references(() => addresses.id),
  stripePaymentIntentId: varchar("stripe_payment_intent_id", { length: 100 }),
  trackingNumber: varchar("tracking_number", { length: 100 }),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
}, table => ({
  customerIdx: index("orders_customer_id_idx").on(table.customerId),
  statusIdx: index("orders_status_idx").on(table.status),
  createdIdx: index("orders_created_at_idx").on(table.createdAt),
}))

// Order items (junction)
export const orderItems = pgTable("order_items", {
  id: uuid("id").primaryKey().defaultRandom(),
  orderId: uuid("order_id").notNull().references(() => orders.id, { onDelete: "cascade" }),
  productId: uuid("product_id").notNull().references(() => products.id),
  quantity: integer("quantity").notNull(),
  priceCents: integer("price_cents").notNull(),  // snapshot at time of order
}, table => ({
  orderIdx: index("order_items_order_id_idx").on(table.orderId),
}))

// Addresses
export const addresses = pgTable("addresses", {
  id: uuid("id").primaryKey().defaultRandom(),
  userId: uuid("user_id").notNull().references(() => users.id),
  line1: varchar("line1", { length: 100 }).notNull(),
  city: varchar("city", { length: 50 }).notNull(),
  postalCode: varchar("postal_code", { length: 20 }),
  country: varchar("country", { length: 2 }).notNull(),
  isDefault: boolean("is_default").notNull().default(false),
})

// --- Relations ---

export const usersRelations = relations(users, ({ many }) => ({
  orders: many(orders),
  addresses: many(addresses),
}))

export const ordersRelations = relations(orders, ({ one, many }) => ({
  customer: one(users, { fields: [orders.customerId], references: [users.id] }),
  items: many(orderItems),
  shippingAddress: one(addresses, {
    fields: [orders.shippingAddressId],
    references: [addresses.id],
  }),
}))

export const orderItemsRelations = relations(orderItems, ({ one }) => ({
  order: one(orders, { fields: [orderItems.orderId], references: [orders.id] }),
  product: one(products, { fields: [orderItems.productId], references: [products.id] }),
}))

export const addressesRelations = relations(addresses, ({ one }) => ({
  user: one(users, { fields: [addresses.userId], references: [users.id] }),
}))

Query API with Relations

// db/queries.ts — relations query API
import { db } from "./client"
import { orders, users, orderItems, products, orderStatusEnum } from "./schema"
import { eq, and, desc, count, sum, avg, between, inArray, ilike } from "drizzle-orm"

// Deep join via relations API — typed result
export async function getOrderWithDetails(orderId: string) {
  return db.query.orders.findFirst({
    where: eq(orders.id, orderId),
    with: {
      customer: {
        columns: { id: true, email: true, name: true },
      },
      items: {
        with: {
          product: {
            columns: { id: true, name: true, slug: true },
          },
        },
      },
      shippingAddress: true,
    },
  })
  // Returns: Order & { customer: User, items: (OrderItem & { product: Product })[], shippingAddress: Address | null }
}

// Paginated with filter
export async function listOrders({
  customerId,
  status,
  search,
  limit = 20,
  offset = 0,
}: {
  customerId?: string
  status?: typeof orderStatusEnum.enumValues[number]
  search?: string
  limit?: number
  offset?: number
}) {
  const conditions = [
    customerId ? eq(orders.customerId, customerId) : undefined,
    status ? eq(orders.status, status) : undefined,
  ].filter(Boolean) as ReturnType<typeof eq>[]

  const [rows, [{ total }]] = await Promise.all([
    db.query.orders.findMany({
      where: conditions.length > 0 ? and(...conditions) : undefined,
      with: {
        customer: { columns: { name: true, email: true } },
        items: { columns: { quantity: true, priceCents: true } },
      },
      orderBy: desc(orders.createdAt),
      limit,
      offset,
    }),
    db.select({ total: count() }).from(orders)
      .where(conditions.length > 0 ? and(...conditions) : undefined),
  ])

  return { rows, total }
}

// Aggregate with raw SQL aggregates
export async function getRevenueStats(startDate: Date, endDate: Date) {
  const [stats] = await db
    .select({
      orderCount: count(orders.id),
      totalRevenueCents: sum(orders.totalCents),
      avgOrderCents: avg(orders.totalCents),
    })
    .from(orders)
    .where(
      and(
        between(orders.createdAt, startDate, endDate),
        inArray(orders.status, ["delivered", "shipped"])
      )
    )

  return {
    orderCount: stats.orderCount,
    totalRevenueCents: Number(stats.totalRevenueCents ?? 0),
    avgOrderCents: Math.round(Number(stats.avgOrderCents ?? 0)),
  }
}

Transactions

// db/checkout.ts — transactional checkout
import { db } from "./client"
import { orders, orderItems, products } from "./schema"
import { eq, sql } from "drizzle-orm"

export async function processCheckout(
  customerId: string,
  cartItems: { productId: string; quantity: number }[]
) {
  return db.transaction(async tx => {
    let totalCents = 0
    const resolvedItems: { productId: string; quantity: number; priceCents: number; name: string }[] = []

    // Lock and validate stock for each item
    for (const item of cartItems) {
      const [product] = await tx
        .select()
        .from(products)
        .where(eq(products.id, item.productId))
        .for("update")  // Row-level lock

      if (!product) throw new Error(`Product ${item.productId} not found`)
      if (!product.active) throw new Error(`Product ${product.name} is not available`)
      if (product.stock < item.quantity) {
        throw new Error(`Insufficient stock for ${product.name}`)
      }

      // Decrement stock
      await tx
        .update(products)
        .set({ stock: sql`${products.stock} - ${item.quantity}` })
        .where(eq(products.id, item.productId))

      totalCents += product.priceCents * item.quantity
      resolvedItems.push({
        productId: product.id,
        quantity: item.quantity,
        priceCents: product.priceCents,
        name: product.name,
      })
    }

    // Create order
    const [order] = await tx
      .insert(orders)
      .values({ customerId, totalCents, status: "pending" })
      .returning()

    // Insert order items
    await tx.insert(orderItems).values(
      resolvedItems.map(item => ({ orderId: order.id, ...item }))
    )

    return order
  })
}

drizzle-kit Configuration

// drizzle.config.ts — migration configuration
import type { Config } from "drizzle-kit"

export default {
  schema: "./db/schema/index.ts",
  out: "./db/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  migrations: {
    table: "drizzle_migrations",
    schema: "public",
  },
  // Strict: error on destructive changes without explicit confirm
  strict: true,
  verbose: true,
} satisfies Config

# Workflow
npx drizzle-kit generate    # Detect schema changes, create SQL migration
npx drizzle-kit migrate     # Apply pending migrations
npx drizzle-kit studio      # Open Drizzle Studio at localhost:4983
npx drizzle-kit check       # Verify migrations in sync with schema

For the Kysely type-safe query builder alternative that stays closer to raw SQL syntax without the ORM abstraction layer — better when you want full SQL control with TypeScript safety and no implicit queries, see the Kysely guide for query builder patterns. For the Prisma ORM alternative when the Prisma ecosystem — Studio GUI, Accelerate connection pooling, Pulse real-time events, and automatic migration management — justifies the stronger abstraction over raw SQL, the Prisma advanced guide covers schema and client patterns. The Claude Skills 360 bundle includes Drizzle ORM skill sets covering schema design, relations, and migrations. Start with the free tier to try Drizzle schema generation.