Drizzle Advanced Patterns (80-20)

Advanced queries, optimizations, and real-world patterns.

Complex Queries

Subqueries

import { sql, gt } from 'drizzle-orm'

// Find users with more than 10 posts
const activeUsers = await db
  .select()
  .from(users)
  .where(
    gt(
      sql`(SELECT COUNT(*) FROM ${posts} WHERE ${posts.userId} = ${users.id})`,
      10
    )
  )

Window Functions

// Rank users by post count
const rankedUsers = await db.execute(sql`
  SELECT 
    ${users.id},
    ${users.name},
    COUNT(${posts.id}) as post_count,
    RANK() OVER (ORDER BY COUNT(${posts.id}) DESC) as rank
  FROM ${users}
  LEFT JOIN ${posts} ON ${users.id} = ${posts.userId}
  GROUP BY ${users.id}, ${users.name}
`)

Aggregate Queries

import { count, avg, sum, max, min } from 'drizzle-orm'

// Group stats
const stats = await db
  .select({
    userId: posts.userId,
    totalPosts: count(posts.id),
    avgLength: avg(posts.contentLength),
    totalViews: sum(posts.views),
  })
  .from(posts)
  .groupBy(posts.userId)

Advanced Joins

Multiple Joins

const usersWithPostsAndComments = await db
  .select({
    user: users,
    post: posts,
    comment: comments,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId))
  .leftJoin(comments, eq(posts.id, comments.postId))

Self Join

const employees = pgTable('employees', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  managerId: integer('manager_id'),
})

// Get employee with manager
const employeesWithManager = await db
  .select({
    employee: employees,
    manager: sql`manager`,
  })
  .from(employees)
  .leftJoin(
    sql`${employees} as manager`,
    sql`${employees.managerId} = manager.id`
  )

Batch Operations

Bulk Insert

// Insert many records efficiently
const users = Array.from({ length: 1000 }, (_, i) => ({
  name: `User ${i}`,
  email: `user${i}@example.com`,
}))

// Insert in batches of 100
for (let i = 0; i < users.length; i += 100) {
  await db.insert(users).values(users.slice(i, i + 100))
}

Upsert (Insert or Update)

// PostgreSQL
await db
  .insert(users)
  .values({ id: 1, name: 'John', email: 'john@example.com' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'John Updated' },
  })

// MySQL
await db
  .insert(users)
  .values({ id: 1, name: 'John', email: 'john@example.com' })
  .onDuplicateKeyUpdate({
    set: { name: 'John Updated' },
  })

Prepared Statements

// Prepare once, execute many times (faster)
const getUserById = db
  .select()
  .from(users)
  .where(eq(users.id, sql.placeholder('id')))
  .prepare('get_user_by_id')

// Execute
const user1 = await getUserById.execute({ id: 1 })
const user2 = await getUserById.execute({ id: 2 })

Query Builders

Dynamic Filters

function getUsers(filters: {
  search?: string
  minAge?: number
  isActive?: boolean
}) {
  const conditions = []

  if (filters.search) {
    conditions.push(like(users.name, `%${filters.search}%`))
  }

  if (filters.minAge !== undefined) {
    conditions.push(gte(users.age, filters.minAge))
  }

  if (filters.isActive !== undefined) {
    conditions.push(eq(users.isActive, filters.isActive))
  }

  return db
    .select()
    .from(users)
    .where(conditions.length > 0 ? and(...conditions) : undefined)
}

// Usage
const users1 = await getUsers({ search: 'John', minAge: 18 })
const users2 = await getUsers({ isActive: true })

Dynamic Sorting

import { asc, desc } from 'drizzle-orm'

function getUsers(sortBy?: string, order: 'asc' | 'desc' = 'asc') {
  const orderFn = order === 'asc' ? asc : desc

  let query = db.select().from(users)

  if (sortBy === 'name') {
    query = query.orderBy(orderFn(users.name))
  } else if (sortBy === 'createdAt') {
    query = query.orderBy(orderFn(users.createdAt))
  }

  return query
}

Pagination

Offset-based

async function getPaginatedUsers(page: number, pageSize: number = 10) {
  const offset = (page - 1) * pageSize

  const [users, totalCount] = await Promise.all([
    db.select().from(users).limit(pageSize).offset(offset),
    db.select({ count: count() }).from(users),
  ])

  return {
    data: users,
    pagination: {
      page,
      pageSize,
      totalPages: Math.ceil(totalCount[0].count / pageSize),
      totalCount: totalCount[0].count,
    },
  }
}

Cursor-based (better performance)

async function getCursorPaginatedUsers(
  cursor?: string,
  limit: number = 10
) {
  const query = db
    .select()
    .from(users)
    .limit(limit + 1)
    .orderBy(desc(users.id))

  if (cursor) {
    query.where(lt(users.id, parseInt(cursor)))
  }

  const results = await query

  const hasMore = results.length > limit
  const data = hasMore ? results.slice(0, -1) : results
  const nextCursor = hasMore ? data[data.length - 1].id.toString() : null

  return { data, nextCursor, hasMore }
}

Optimistic Locking

const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title'),
  version: integer('version').default(0).notNull(),
})

async function updatePost(id: number, title: string, version: number) {
  const result = await db
    .update(posts)
    .set({ 
      title, 
      version: sql`${posts.version} + 1` 
    })
    .where(
      and(
        eq(posts.id, id),
        eq(posts.version, version)
      )
    )
    .returning()

  if (result.length === 0) {
    throw new Error('Post was modified by another user')
  }

  return result[0]
}

Soft Deletes

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name'),
  deletedAt: timestamp('deleted_at'),
})

// Soft delete
async function softDeleteUser(id: number) {
  return await db
    .update(users)
    .set({ deletedAt: new Date() })
    .where(eq(users.id, id))
}

// Get active users only
async function getActiveUsers() {
  return await db
    .select()
    .from(users)
    .where(isNull(users.deletedAt))
}

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

Indexes

import { index, uniqueIndex } from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull(),
  name: text('name'),
  createdAt: timestamp('created_at').defaultNow(),
}, (table) => ({
  emailIdx: uniqueIndex('email_idx').on(table.email),
  nameIdx: index('name_idx').on(table.name),
  createdAtIdx: index('created_at_idx').on(table.createdAt),
}))

Full-Text Search (PostgreSQL)

import { sql } from 'drizzle-orm'

// Add tsvector column
const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title'),
  content: text('content'),
  searchVector: sql`tsvector`,
})

// Create index
await db.execute(sql`
  CREATE INDEX posts_search_idx ON ${posts}
  USING GIN (search_vector)
`)

// Update trigger for auto-updating search_vector
await db.execute(sql`
  CREATE TRIGGER posts_search_update
  BEFORE INSERT OR UPDATE ON ${posts}
  FOR EACH ROW
  EXECUTE FUNCTION
    tsvector_update_trigger(search_vector, 'pg_catalog.english', title, content);
`)

// Search
const searchResults = await db
  .select()
  .from(posts)
  .where(
    sql`${posts.searchVector} @@ to_tsquery('english', ${query})`
  )
  .orderBy(
    sql`ts_rank(${posts.searchVector}, to_tsquery('english', ${query})) DESC`
  )

Computed Columns

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  firstName: text('first_name'),
  lastName: text('last_name'),
  fullName: text('full_name').generatedAlwaysAs(
    sql`${users.firstName} || ' ' || ${users.lastName}`
  ),
})

JSON Columns

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  metadata: jsonb('metadata').$type<{
    preferences: {
      theme: 'light' | 'dark'
      language: string
    }
    settings: Record<string, any>
  }>(),
})

// Query
const darkThemeUsers = await db
  .select()
  .from(users)
  .where(
    sql`${users.metadata}->>'preferences'->>'theme' = 'dark'`
  )

// Update
await db
  .update(users)
  .set({
    metadata: {
      preferences: { theme: 'dark', language: 'en' },
      settings: {},
    },
  })
  .where(eq(users.id, 1))

Connection Pooling

import { Pool } from 'pg'
import { drizzle } from 'drizzle-orm/node-postgres'

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20, // Maximum connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
})

export const db = drizzle(pool)

Repository Pattern

class UserRepository {
  constructor(private db: typeof db) {}

  async findById(id: number) {
    const result = await this.db
      .select()
      .from(users)
      .where(eq(users.id, id))
    return result[0]
  }

  async findByEmail(email: string) {
    const result = await this.db
      .select()
      .from(users)
      .where(eq(users.email, email))
    return result[0]
  }

  async create(data: NewUser) {
    const result = await this.db
      .insert(users)
      .values(data)
      .returning()
    return result[0]
  }

  async update(id: number, data: Partial<NewUser>) {
    const result = await this.db
      .update(users)
      .set(data)
      .where(eq(users.id, id))
      .returning()
    return result[0]
  }

  async delete(id: number) {
    await this.db
      .delete(users)
      .where(eq(users.id, id))
  }
}

export const userRepository = new UserRepository(db)

Performance Tips

• Use prepared statements for repeated queries
• Add indexes on frequently queried columns
• Use cursor pagination for large datasets
• Batch insert operations
• Use connection pooling in production
• Monitor slow queries with EXPLAIN