Drizzle ORM Advanced Guide | Transactions, Relations, and Performance

Beyond Basic Drizzle

This guide assumes you know Drizzle basics (schema definition, basic queries). We cover production patterns.

---

1. Schema Design Patterns

// db/schema.ts
import {
  pgTable, serial, text, timestamp, boolean, integer,
  uuid, jsonb, pgEnum, index, uniqueIndex
} from 'drizzle-orm/pg-core'
import { relations } from 'drizzle-orm'

// Enum type
export const userRoleEnum = pgEnum('user_role', ['admin', 'moderator', 'user'])
export const postStatusEnum = pgEnum('post_status', ['draft', 'published', 'archived'])

// Base columns (reusable)
const timestamps = {
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}

// Users table
export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  role: userRoleEnum('role').default('user').notNull(),
  passwordHash: text('password_hash').notNull(),
  emailVerified: boolean('email_verified').default(false).notNull(),
  deletedAt: timestamp('deleted_at'),  // soft delete
  ...timestamps,
}, (t) => ({
  emailIdx: index('users_email_idx').on(t.email),
  roleIdx: index('users_role_idx').on(t.role),
}))

// Posts table
export const posts = pgTable('posts', {
  id: uuid('id').defaultRandom().primaryKey(),
  authorId: uuid('author_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  title: text('title').notNull(),
  slug: text('slug').notNull(),
  content: text('content').notNull(),
  status: postStatusEnum('status').default('draft').notNull(),
  metadata: jsonb('metadata').$type<{ views: number; tags: string[] }>(),
  publishedAt: timestamp('published_at'),
  ...timestamps,
}, (t) => ({
  slugIdx: uniqueIndex('posts_slug_idx').on(t.slug),
  authorIdx: index('posts_author_idx').on(t.authorId),
  statusIdx: index('posts_status_idx').on(t.status),
}))

// Tags (many-to-many)
export const tags = pgTable('tags', {
  id: serial('id').primaryKey(),
  name: text('name').notNull().unique(),
})

export const postTags = pgTable('post_tags', {
  postId: uuid('post_id').notNull().references(() => posts.id, { onDelete: 'cascade' }),
  tagId: integer('tag_id').notNull().references(() => tags.id, { onDelete: 'cascade' }),
}, (t) => ({
  pk: { columns: [t.postId, t.tagId] },
}))

---

2. Relations — Typed Associations

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

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

export const postTagsRelations = relations(postTags, ({ one }) => ({
  post: one(posts, { fields: [postTags.postId], references: [posts.id] }),
  tag: one(tags, { fields: [postTags.tagId], references: [tags.id] }),
}))

export const tagsRelations = relations(tags, ({ many }) => ({
  postTags: many(postTags),
}))

---

3. Relational Queries

import { db } from './db'
import { users, posts } from './schema'

// Fetch user with all their posts
const userWithPosts = await db.query.users.findFirst({
  where: (users, { eq }) => eq(users.id, userId),
  with: {
    posts: {
      where: (posts, { eq }) => eq(posts.status, 'published'),
      orderBy: (posts, { desc }) => [desc(posts.publishedAt)],
      limit: 10,
      columns: {
        content: false,  // exclude large field
      },
      with: {
        postTags: {
          with: { tag: true },
        },
      },
    },
  },
})

// Result is fully typed:
// userWithPosts.posts[0].postTags[0].tag.name

// Fetch all published posts with author info
const publishedPosts = await db.query.posts.findMany({
  where: (posts, { eq, isNull }) =>
    and(eq(posts.status, 'published'), isNull(posts.deletedAt)),
  orderBy: (posts, { desc }) => [desc(posts.publishedAt)],
  with: {
    author: {
      columns: { id: true, name: true, email: true },
    },
    postTags: {
      with: { tag: true },
    },
  },
})

---

4. Transactions

import { db } from './db'
import { users, posts } from './schema'

// Basic transaction
async function createPostWithTags(
  authorId: string,
  postData: { title: string; content: string; slug: string },
  tagIds: number[]
) {
  return await db.transaction(async (tx) => {
    // Create post
    const [post] = await tx.insert(posts)
      .values({
        authorId,
        title: postData.title,
        content: postData.content,
        slug: postData.slug,
      })
      .returning()

    // Insert tag associations
    if (tagIds.length > 0) {
      await tx.insert(postTags)
        .values(tagIds.map(tagId => ({ postId: post.id, tagId })))
    }

    // Update user's post count in metadata
    await tx.execute(sql`
      UPDATE users
      SET metadata = jsonb_set(
        COALESCE(metadata, '{}'),
        '{postCount}',
        (COALESCE((metadata->>'postCount')::int, 0) + 1)::text::jsonb
      )
      WHERE id = ${authorId}
    `)

    return post
  })
  // If any step throws, all changes are rolled back
}

// Nested transactions (savepoints)
async function complexOperation() {
  return await db.transaction(async (tx) => {
    await tx.insert(users).values({ email: 'alice@test.com', name: 'Alice', passwordHash: '...' })

    // Nested transaction uses savepoint
    await tx.transaction(async (innerTx) => {
      try {
        await innerTx.insert(posts).values({ /* ... */ })
      } catch {
        // Only rolls back the inner transaction (savepoint)
        // Outer transaction continues
      }
    })

    return 'done'
  })
}

---

5. Prepared Statements

import { db } from './db'
import { users, posts } from './schema'
import { eq, and, isNull, placeholder } from 'drizzle-orm'

// Prepare once, execute many times
const getPublishedPostById = db
  .select()
  .from(posts)
  .where(and(
    eq(posts.id, placeholder('id')),
    eq(posts.status, 'published')
  ))
  .prepare('get_published_post')

// Execute (no parse/plan overhead)
const post = await getPublishedPostById.execute({ id: 'some-uuid' })

// Prepared insert
const insertUser = db
  .insert(users)
  .values({
    email: placeholder('email'),
    name: placeholder('name'),
    passwordHash: placeholder('passwordHash'),
  })
  .returning({ id: users.id, email: users.email })
  .prepare('insert_user')

const newUser = await insertUser.execute({
  email: 'bob@example.com',
  name: 'Bob',
  passwordHash: await hashPassword('secret'),
})

---

6. Soft Deletes

import { isNull, and } from 'drizzle-orm'

// Soft delete
async function deleteUser(userId: string) {
  await db.update(users)
    .set({ deletedAt: new Date() })
    .where(eq(users.id, userId))
}

// Always filter soft-deleted records
const activeUsers = db.$with('active_users').as(
  db.select().from(users).where(isNull(users.deletedAt))
)

// Use in queries
const result = await db.with(activeUsers)
  .select()
  .from(activeUsers)
  .where(eq(activeUsers.role, 'admin'))

// Helper to always exclude soft-deleted
function withoutDeleted<T extends typeof users>(table: T) {
  return db.select().from(table).where(isNull(table.deletedAt))
}

const aliveUsers = await withoutDeleted(users)

// Restore
async function restoreUser(userId: string) {
  await db.update(users)
    .set({ deletedAt: null })
    .where(eq(users.id, userId))
}

---

7. Custom Column Types

import { customType } from 'drizzle-orm/pg-core'

// Custom type for encrypted values
const encryptedText = customType<{
  data: string
  driverData: string
}>({
  dataType() { return 'text' },
  toDriver(value: string): string {
    return encrypt(value)
  },
  fromDriver(value: string): string {
    return decrypt(value)
  },
})

// Custom type for money (avoid float precision issues)
const money = customType<{ data: number; driverData: string }>({
  dataType() { return 'numeric(12,2)' },
  toDriver(value: number): string { return value.toString() },
  fromDriver(value: string): number { return parseFloat(value) },
})

// Use in schema
export const payments = pgTable('payments', {
  id: uuid('id').defaultRandom().primaryKey(),
  amount: money('amount').notNull(),
  cardNumber: encryptedText('card_number').notNull(),  // stored encrypted
})

---

8. Migrations

# Generate migration from schema changes
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Push schema directly (dev only — no migration file)
npx drizzle-kit push

# View current schema state
npx drizzle-kit studio

// 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!,
  },
  verbose: true,
  strict: true,  // require confirmation for destructive migrations
})

Programmatic migrations (for testing)

import { migrate } from 'drizzle-orm/node-postgres/migrator'
import { db } from './db'

// Run migrations programmatically
await migrate(db, { migrationsFolder: './drizzle' })

---

9. Performance Patterns

// ✅ Select only needed columns
const names = await db.select({ id: users.id, name: users.name }).from(users)

// ✅ Batch inserts (single round-trip)
await db.insert(posts).values([post1, post2, post3, post4])

// ✅ Upsert
await db.insert(users)
  .values({ email: 'alice@example.com', name: 'Alice', passwordHash: '...' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'Alice Updated', updatedAt: new Date() },
  })

// ✅ Count without fetching all rows
const [{ count }] = await db
  .select({ count: sql<number>`count(*)::int` })
  .from(posts)
  .where(eq(posts.status, 'published'))

// ✅ Use indexes — ensure your WHERE columns are indexed
// Check with: EXPLAIN ANALYZE SELECT ...

// ✅ Pagination (cursor-based is faster than offset for large tables)
const cursor = lastPost?.createdAt ?? new Date()
const nextPage = await db.select().from(posts)
  .where(lt(posts.createdAt, cursor))
  .orderBy(desc(posts.createdAt))
  .limit(20)

---

Key Takeaways

• Schema: use enums, indexes, and timestamps consistently — define reusable fragments
• Relations: relations() enables the relational query API with automatic nested JOINs
• Transactions: db.transaction(tx => { ... }) — use tx for all operations inside
• Prepared statements: compile once, execute many — ideal for hot query paths
• Soft deletes: deletedAt timestamp + filter helpers — never DELETE user data
• Custom types: encrypt sensitive columns at the ORM layer, handle money with numeric
• Migrations: drizzle-kit generate → commit migration files → drizzle-kit migrate in CI
• Performance: select specific columns, batch inserts, use cursor pagination for large tables