Drizzle ORM CTE·서브쿼리 심화

Drizzle ORM 고급 쿼리란?

Drizzle ORM은 TypeScript 타입 안전성을 최우선으로 설계된 경량 ORM이다. 단순 CRUD를 넘어 서브쿼리(Subquery), CTE(Common Table Expression), Window Function 같은 고급 SQL 패턴도 타입 안전하게 지원한다. 복잡한 분석 쿼리를 Raw SQL 없이 작성할 수 있다는 것이 핵심 강점이다.

import { drizzle } from 'drizzle-orm/node-postgres';
import { pgTable, serial, text, integer, timestamp, numeric } from 'drizzle-orm/pg-core';
import { eq, sql, gt, desc, and } from 'drizzle-orm';

// 스키마 정의
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  createdAt: timestamp('created_at').defaultNow(),
});

export const orders = pgTable('orders', {
  id: serial('id').primaryKey(),
  userId: integer('user_id').references(() => users.id),
  totalAmount: numeric('total_amount', { precision: 10, scale: 2 }).notNull(),
  status: text('status').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

서브쿼리: 인라인과 상관 서브쿼리

Drizzle의 서브쿼리는 db.select().from().as()로 생성하며, 메인 쿼리에서 타입 안전하게 참조할 수 있다.

// 인라인 서브쿼리: 각 유저의 주문 합계
const userTotals = db
  .select({
    userId: orders.userId,
    totalSpent: sql<string>`SUM(${orders.totalAmount})`.as('total_spent'),
    orderCount: sql<number>`COUNT(*)`.as('order_count'),
  })
  .from(orders)
  .where(eq(orders.status, 'completed'))
  .groupBy(orders.userId)
  .as('user_totals');  // 서브쿼리 별칭

// 메인 쿼리에서 서브쿼리 JOIN
const result = await db
  .select({
    userName: users.name,
    email: users.email,
    totalSpent: userTotals.totalSpent,
    orderCount: userTotals.orderCount,
  })
  .from(users)
  .innerJoin(userTotals, eq(users.id, userTotals.userId))
  .where(gt(userTotals.orderCount, 5))
  .orderBy(desc(userTotals.totalSpent));

// WHERE절 서브쿼리: 평균 이상 주문 유저
const avgOrderAmount = db
  .select({
    avg: sql<string>`AVG(${orders.totalAmount})`.as('avg_amount'),
  })
  .from(orders);

const highValueOrders = await db
  .select()
  .from(orders)
  .where(gt(orders.totalAmount, avgOrderAmount));

CTE: WITH 절로 쿼리 구조화

CTE(Common Table Expression)는 복잡한 쿼리를 단계별로 분리하여 가독성을 높인다. Drizzle의 $with과 with() 메서드로 타입 안전한 CTE를 작성할 수 있다.

// CTE: 월별 매출 집계 후 전월 대비 증감 분석
const monthlySales = db.$with('monthly_sales').as(
  db
    .select({
      month: sql<string>`DATE_TRUNC('month', ${orders.createdAt})`.as('month'),
      revenue: sql<string>`SUM(${orders.totalAmount})`.as('revenue'),
      orderCount: sql<number>`COUNT(*)`.as('order_count'),
    })
    .from(orders)
    .where(eq(orders.status, 'completed'))
    .groupBy(sql`DATE_TRUNC('month', ${orders.createdAt})`)
);

const result = await db
  .with(monthlySales)
  .select({
    month: monthlySales.month,
    revenue: monthlySales.revenue,
    orderCount: monthlySales.orderCount,
  })
  .from(monthlySales)
  .orderBy(desc(monthlySales.month))
  .limit(12);

// 다중 CTE: 단계별 분석
const activeUsers = db.$with('active_users').as(
  db
    .select({
      userId: orders.userId,
      lastOrder: sql<Date>`MAX(${orders.createdAt})`.as('last_order'),
    })
    .from(orders)
    .groupBy(orders.userId)
    .having(gt(sql`MAX(${orders.createdAt})`, sql`NOW() - INTERVAL '30 days'`))
);

const userStats = db.$with('user_stats').as(
  db
    .select({
      userId: activeUsers.userId,
      userName: users.name,
      totalSpent: sql<string>`SUM(${orders.totalAmount})`.as('total_spent'),
    })
    .from(activeUsers)
    .innerJoin(users, eq(activeUsers.userId, users.id))
    .innerJoin(orders, eq(activeUsers.userId, orders.userId))
    .groupBy(activeUsers.userId, users.name)
);

// 두 CTE를 체이닝
const topCustomers = await db
  .with(activeUsers, userStats)
  .select()
  .from(userStats)
  .orderBy(desc(userStats.totalSpent))
  .limit(10);

Window Function: 분석 쿼리

PostgreSQL Window Function은 집계 없이 행별 분석 값을 계산한다. Drizzle에서는 sql 템플릿으로 구현한다.

// ROW_NUMBER: 유저별 최신 주문 순위
const rankedOrders = await db
  .select({
    orderId: orders.id,
    userId: orders.userId,
    totalAmount: orders.totalAmount,
    createdAt: orders.createdAt,
    rowNum: sql<number>`ROW_NUMBER() OVER (
      PARTITION BY ${orders.userId}
      ORDER BY ${orders.createdAt} DESC
    )`.as('row_num'),
  })
  .from(orders);

// 서브쿼리로 감싸서 각 유저의 최근 3개 주문만 필터
const recentOrdersSq = db
  .select({
    orderId: orders.id,
    userId: orders.userId,
    totalAmount: orders.totalAmount,
    rowNum: sql<number>`ROW_NUMBER() OVER (
      PARTITION BY ${orders.userId}
      ORDER BY ${orders.createdAt} DESC
    )`.as('row_num'),
  })
  .from(orders)
  .as('ranked');

const top3PerUser = await db
  .select()
  .from(recentOrdersSq)
  .where(sql`${recentOrdersSq.rowNum} <= 3`);

// LAG/LEAD: 전월 대비 매출 증감률
const monthlyWithGrowth = db.$with('monthly').as(
  db
    .select({
      month: sql<string>`DATE_TRUNC('month', ${orders.createdAt})`.as('month'),
      revenue: sql<string>`SUM(${orders.totalAmount})`.as('revenue'),
      prevRevenue: sql<string>`LAG(SUM(${orders.totalAmount})) OVER (
        ORDER BY DATE_TRUNC('month', ${orders.createdAt})
      )`.as('prev_revenue'),
    })
    .from(orders)
    .where(eq(orders.status, 'completed'))
    .groupBy(sql`DATE_TRUNC('month', ${orders.createdAt})`)
);

const growthReport = await db
  .with(monthlyWithGrowth)
  .select({
    month: monthlyWithGrowth.month,
    revenue: monthlyWithGrowth.revenue,
    prevRevenue: monthlyWithGrowth.prevRevenue,
    growthRate: sql<string>`
      ROUND(
        (${monthlyWithGrowth.revenue}::numeric - ${monthlyWithGrowth.prevRevenue}::numeric)
        / NULLIF(${monthlyWithGrowth.prevRevenue}::numeric, 0) * 100, 2
      )
    `.as('growth_rate'),
  })
  .from(monthlyWithGrowth)
  .orderBy(desc(monthlyWithGrowth.month));

Prepared Statement로 성능 최적화

반복 실행되는 복잡한 쿼리는 db.query.*.prepare()로 사전 컴파일하여 파싱 오버헤드를 제거한다.

import { placeholder } from 'drizzle-orm';

// Prepared Statement 정의
const getUserOrderStats = db
  .select({
    userName: users.name,
    totalOrders: sql<number>`COUNT(${orders.id})`.as('total_orders'),
    totalSpent: sql<string>`SUM(${orders.totalAmount})`.as('total_spent'),
    avgOrder: sql<string>`AVG(${orders.totalAmount})`.as('avg_order'),
  })
  .from(users)
  .leftJoin(orders, eq(users.id, orders.userId))
  .where(eq(users.id, placeholder('userId')))
  .groupBy(users.name)
  .prepare('get_user_order_stats');

// 실행: 파라미터만 바인딩
const stats = await getUserOrderStats.execute({ userId: 42 });

NestJS 서비스에서 활용

NestJS Dynamic Module 패턴으로 Drizzle를 주입하고 고급 쿼리를 서비스에서 사용하는 예시다.

@Injectable()
export class AnalyticsService {
  constructor(@Inject('DRIZZLE') private db: NodePgDatabase) {}

  async getTopCustomers(limit: number = 10) {
    const customerStats = this.db.$with('stats').as(
      this.db
        .select({
          userId: orders.userId,
          name: users.name,
          totalSpent: sql<string>`SUM(${orders.totalAmount})`.as('total'),
          rank: sql<number>`DENSE_RANK() OVER (
            ORDER BY SUM(${orders.totalAmount}) DESC
          )`.as('rank'),
        })
        .from(orders)
        .innerJoin(users, eq(orders.userId, users.id))
        .where(eq(orders.status, 'completed'))
        .groupBy(orders.userId, users.name)
    );

    return this.db
      .with(customerStats)
      .select()
      .from(customerStats)
      .where(sql`${customerStats.rank} <= ${limit}`);
  }
}

정리

Drizzle ORM은 단순 CRUD를 넘어 서브쿼리, CTE($with), Window Function 같은 고급 SQL 패턴을 TypeScript 타입 안전하게 지원한다. .as()로 서브쿼리를 별칭하고, $with()으로 CTE를 체이닝하며, sql 템플릿으로 Window Function을 구현하면 Raw SQL 없이도 복잡한 분석 쿼리를 작성할 수 있다. Prepared Statement로 반복 쿼리 성능까지 최적화하면 프로덕션급 데이터 접근 계층을 완성할 수 있다.