What is the difference between Drizzle's relational query API and SQL joins?

Drizzle's db.query API (relational queries) uses the schema you defined to automatically build efficient JOINs and map results to nested objects. The db.select().from().leftJoin() SQL builder gives you full control over the query shape. Use relational queries for typical CRUD; use the builder for complex custom queries.

How do Drizzle transactions work?

Drizzle transactions use db.transaction(async (tx) => { ... }). All operations using the tx object are part of the same database transaction ??either all succeed or all roll back. Nested transactions use savepoints.

Does Drizzle ORM support soft deletes?

Not natively, but it's easy to implement: add a deletedAt column and use Drizzle's custom views or a helper function to filter out soft-deleted records. You can also wrap queries in a function that always adds WHERE deleted_at IS NULL.

What are Drizzle prepared statements and when should I use them?

Prepared statements compile the query once and reuse the plan, avoiding parse/plan overhead on repeated executions. Use them for hot paths ??queries executed hundreds of times per second. For most queries, regular Drizzle queries are fine.

Drizzle ORM Advanced Guide | Transactions· Relations

2025년 7월 15일 · 20분 읽기 · 수정 2026년 4월 7일 Advanced tutorial

이 글의 핵심

This guide covers Drizzle ORM's advanced features: transactions, relational queries with joins, custom types, prepared statements, soft deletes, multi-tenant patterns, and migration strategies for production databases.

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

Frequently Asked Questions (FAQ)

Q. When would I use this in practice?

A. This guide covers Drizzle ORM’s advanced features: transactions, relational queries with joins, custom types, prepared statements, soft deletes, multi-tenant patterns, and migration strategies for production databases.

Q. What should I read before this?

A. Follow the previous article or related articles links at the bottom of each post to learn in sequence.

Q. Where can I study this more deeply?

A. Check cppreference and the relevant library’s official documentation. The reference links at the end of the article are also worth using.

이 글이 도움이 되셨나요?

여러분의 피드백은 더 나은 콘텐츠를 만드는 데 도움이 됩니다

문제가 있거나 개선 제안이 있으시면 연락처로 알려주세요.