Belajar Drizzle ORM - Sub-Query & Common Table Expressions (CTE)

#Pendahuluan

Setelah di episode 8 kita mempelajari SQL-like JOIN Queries — inner join, left join, multi-table join — pada episode ini kita masuk ke sub-query dan CTE: teknik untuk memecah query kompleks menjadi bagian-bagian yang lebih mudah dikelola dan di-read.

Sub-query dan CTE adalah tools yang powerful untuk queries analitik dan reporting. Memahami kapan menggunakan masing-masing akan meningkatkan kualitas query kalian.

#Sub-Query

#Konsep Dasar

Sub-query adalah query di dalam query — digunakan sebagai tabel atau expression di FROM, WHERE, atau JOIN.

Sub-query: hitung posts per user lalu join

const postCounts = db
  .select({
    userId: posts.authorId,
    totalPosts: count(),
  })
  .from(posts)
  .groupBy(posts.authorId)
  .as('post_counts')
 
const result = await db
  .select({
    userName: users.name,
    totalPosts: postCounts.totalPosts,
  })
  .from(users)
  .innerJoin(postCounts, eq(users.id, postCounts.userId))

.as('post_counts') menamai sub-query sehingga bisa dirujuk di JOIN.

#Sub-query di WHERE

Sub-query di WHERE clause

const activeUserIds = db
  .select({ id: users.id })
  .from(users)
  .where(eq(users.isActive, true))
  .as('active_users')
 
await db.select().from(posts).where(
  inArray(posts.authorId, db.select({ id: activeUserIds.id }).from(activeUserIds))
)

#Common Table Expressions (CTE)

#Konsep Dasar

CTE adalah named temporary result set yang bisa digunakan beberapa kali dalam satu query. Lebih readable dibanding sub-query untuk queries kompleks.

CTE dengan $with()

const activeUsers = db.$with('active_users').as(
  db.select().from(users).where(eq(users.isActive, true))
)
 
const result = await db
  .with(activeUsers)
  .select()
  .from(activeUsers)

#CTE Complex: Multi-Step Query

CTE multi-step untuk analytics

const postStats = db.$with('post_stats').as(
  db.select({
    authorId: posts.authorId,
    totalPosts: count(),
    avgViews: avg(posts.viewCount),
  })
  .from(posts)
  .groupBy(posts.authorId)
)
 
const result = await db
  .with(postStats)
  .select({
    userName: users.name,
    totalPosts: postStats.totalPosts,
    avgViews: postStats.avgViews,
  })
  .from(users)
  .innerJoin(postStats, eq(users.id, postStats.authorId))
  .where(gte(postStats.totalPosts, 5))

#Kapan Sub-Query vs CTE

Aspek	Sub-Query	CTE
Readability	Sulit untuk queries panjang	Sangat readable
Reusable	Tidak (inline)	Ya (bisa digunakan berkali-kali)
Complexity	Cocok untuk sekali pakai	Cocok untuk multi-step
Performance	Sama (optimizedName oleh DB)	Sama

Tip

Gunakan sub-query untuk sekali pakai (one-off). Gunakan CTE untuk queries kompleks yang butuh readability atau reusable. Keduanya di-optimasi oleh database engine — tidak ada perbedaan performance yang signifikan.

#Performance Considerations

#Index Strategy

Pastikan kolom yang digunakan di JOIN dan WHERE memiliki index:

Index untuk kolom JOIN dan WHERE

import { index } from 'drizzle-orm/pg-core'
 
export const posts = pgTable('posts', {
  // ... columns
}, (table) => ({
  authorIdIdx: index('posts_author_id_idx').on(table.authorId),
  statusIdx: index('posts_status_idx').on(table.status),
}))

Tanpa index, query yang menggunakan JOIN dan WHERE akan melakukan full table scan — sangat lambat untuk dataset besar.

#N+1 Problem

Relational queries Drizzle sudah mengoptimasi untuk menghindari N+1 problem. Tidak perlu khawatir tentang lazy loading seperti di TypeORM.

#Penutup

Pada episode 9 ini, kalian telah memahami Sub-Query & CTE:

Sub-query dengan .as() untuk nested queries di JOIN dan WHERE.
CTE dengan $with() untuk reusable, readable queries.
Sub-query untuk one-off, CTE untuk multi-step queries.
Index strategy: index kolom JOIN dan WHERE untuk performance.
N+1: Drizzle sudah mengoptimasi — tidak perlu khawatir.

Di episode 10 selanjutnya kita akan mempelajari Drizzle Kit: Generate & Run Migrations — workflow migrasi dari schema changes hingga migration files, push vs migrate, dan best practice. Sampai jumpa di episode 10!