Prisma Raw SQL 쿼리 심화

Prisma에서 Raw SQL이 필요한 순간

Prisma Client는 타입 안전한 쿼리 빌더를 제공하지만, 모든 SQL을 추상화하진 못한다. 복잡한 집계, 윈도우 함수, CTE(Common Table Expression), 풀텍스트 검색, 벌크 UPSERT 같은 고급 SQL은 Raw Query가 유일한 선택지다. Prisma는 $queryRaw, $executeRaw, $queryRawUnsafe 세 가지 Raw SQL 메서드를 제공하며, 각각 용도와 보안 수준이 다르다.

$queryRaw vs $executeRaw vs $queryRawUnsafe

메서드	반환값	SQL Injection 방어	용도
$queryRaw	결과 배열	Tagged template → 자동 파라미터 바인딩	SELECT 계열
$executeRaw	영향받은 행 수	Tagged template → 자동 파라미터 바인딩	INSERT/UPDATE/DELETE
$queryRawUnsafe	결과 배열	없음 (수동 이스케이프 필요)	동적 SQL 구성

Tagged Template: 안전한 파라미터 바인딩

$queryRaw는 JavaScript Tagged Template Literal을 사용해 자동으로 파라미터를 바인딩한다. 문자열 연결이 아니라 prepared statement로 변환되므로 SQL Injection이 원천 차단된다.

import { Prisma } from '@prisma/client';

// ✅ 안전: Tagged template → prepared statement
const minPrice = 10000;
const category = 'electronics';

const products = await prisma.$queryRaw<Product[]>`
  SELECT p.id, p.name, p.price, c.name as category_name
  FROM "Product" p
  JOIN "Category" c ON p.category_id = c.id
  WHERE p.price >= ${minPrice}
    AND c.slug = ${category}
    AND p.deleted_at IS NULL
  ORDER BY p.created_at DESC
  LIMIT 20
`;

// 내부적으로 변환:
// SELECT ... WHERE p.price >= $1 AND c.slug = $2 ...
// params: [10000, 'electronics']

Prisma.sql 헬퍼로 동적 쿼리 구성

Tagged template만으로는 동적 컬럼명, 테이블명, ORDER BY 방향 등을 처리할 수 없다. Prisma.sql, Prisma.join, Prisma.raw 헬퍼를 조합하면 안전하게 동적 SQL을 구성할 수 있다.

// Prisma.join: IN 절 동적 구성
const ids = ['id-1', 'id-2', 'id-3'];
const users = await prisma.$queryRaw`
  SELECT * FROM "User"
  WHERE id IN (${Prisma.join(ids)})
`;
// → WHERE id IN ($1, $2, $3)

// Prisma.raw: 식별자(컬럼·테이블명) 삽입 (주의: 이스케이프 안 됨)
const sortColumn = 'created_at'; // 화이트리스트 검증 필수!
const allowedColumns = ['created_at', 'price', 'name'];
if (!allowedColumns.includes(sortColumn)) {
  throw new Error('Invalid sort column');
}

const sorted = await prisma.$queryRaw`
  SELECT * FROM "Product"
  WHERE deleted_at IS NULL
  ORDER BY ${Prisma.raw(`"${sortColumn}"`)} DESC
  LIMIT 50
`;

// Prisma.sql: 쿼리 조각 조합
function buildWhereClause(filters: ProductFilters) {
  const conditions: Prisma.Sql[] = [Prisma.sql`deleted_at IS NULL`];

  if (filters.minPrice) {
    conditions.push(Prisma.sql`price >= ${filters.minPrice}`);
  }
  if (filters.category) {
    conditions.push(Prisma.sql`category_id = ${filters.category}`);
  }
  if (filters.search) {
    conditions.push(Prisma.sql`name ILIKE ${'%' + filters.search + '%'}`);
  }

  return Prisma.join(conditions, ' AND ');
}

const where = buildWhereClause({ minPrice: 5000, search: '노트북' });
const results = await prisma.$queryRaw`
  SELECT * FROM "Product"
  WHERE ${where}
  ORDER BY created_at DESC
`;

윈도우 함수 활용

Prisma Client로는 구현 불가능한 윈도우 함수를 Raw SQL로 활용하면 랭킹, 누적합, 이동평균 등을 효율적으로 계산할 수 있다.

// 카테고리별 매출 랭킹
interface SalesRank {
  product_id: string;
  product_name: string;
  category_name: string;
  total_sales: bigint;
  rank: bigint;
}

const rankings = await prisma.$queryRaw<SalesRank[]>`
  SELECT
    p.id as product_id,
    p.name as product_name,
    c.name as category_name,
    SUM(oi.quantity * oi.unit_price) as total_sales,
    RANK() OVER (
      PARTITION BY c.id
      ORDER BY SUM(oi.quantity * oi.unit_price) DESC
    ) as rank
  FROM "OrderItem" oi
  JOIN "Product" p ON oi.product_id = p.id
  JOIN "Category" c ON p.category_id = c.id
  WHERE oi.created_at >= ${startDate}
  GROUP BY p.id, p.name, c.id, c.name
  HAVING SUM(oi.quantity * oi.unit_price) > 0
`;

// 7일 이동평균 매출
const movingAvg = await prisma.$queryRaw`
  SELECT
    date,
    daily_revenue,
    AVG(daily_revenue) OVER (
      ORDER BY date
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7d
  FROM (
    SELECT
      DATE(created_at) as date,
      SUM(total_amount) as daily_revenue
    FROM "Order"
    WHERE created_at >= ${thirtyDaysAgo}
    GROUP BY DATE(created_at)
  ) daily
  ORDER BY date
`;

CTE(Common Table Expression) 패턴

CTE는 복잡한 쿼리를 가독성 있게 분해하고, 재귀 쿼리로 트리 구조를 탐색할 때 필수적이다.

// 재귀 CTE: 카테고리 트리 전체 조회
interface CategoryTree {
  id: string;
  name: string;
  parent_id: string | null;
  depth: number;
  path: string;
}

const tree = await prisma.$queryRaw<CategoryTree[]>`
  WITH RECURSIVE category_tree AS (
    -- 루트 노드
    SELECT id, name, parent_id, 0 as depth,
           name::text as path
    FROM "Category"
    WHERE parent_id IS NULL

    UNION ALL

    -- 자식 노드 재귀
    SELECT c.id, c.name, c.parent_id, ct.depth + 1,
           ct.path || ' > ' || c.name
    FROM "Category" c
    JOIN category_tree ct ON c.parent_id = ct.id
  )
  SELECT * FROM category_tree
  ORDER BY path
`;

// 복합 CTE: 고객 세그먼트 분석
const segments = await prisma.$queryRaw`
  WITH customer_stats AS (
    SELECT
      user_id,
      COUNT(*) as order_count,
      SUM(total_amount) as lifetime_value,
      MAX(created_at) as last_order_at
    FROM "Order"
    WHERE status = 'completed'
    GROUP BY user_id
  ),
  segmented AS (
    SELECT *,
      CASE
        WHEN lifetime_value >= 1000000 AND order_count >= 10 THEN 'VIP'
        WHEN lifetime_value >= 500000 THEN 'Premium'
        WHEN last_order_at < NOW() - INTERVAL '90 days' THEN 'Dormant'
        ELSE 'Regular'
      END as segment
    FROM customer_stats
  )
  SELECT segment, COUNT(*) as count,
         AVG(lifetime_value)::integer as avg_ltv
  FROM segmented
  GROUP BY segment
  ORDER BY avg_ltv DESC
`;

벌크 UPSERT와 성능 최적화

Prisma의 createMany는 UPSERT를 지원하지 않고, upsert는 단건만 처리한다. 대량 UPSERT는 Raw SQL이 압도적으로 빠르다. Drizzle ORM 동적 쿼리·필터 심화에서 다룬 성능 최적화 관점과도 일맥상통한다.

// 벌크 UPSERT: 재고 일괄 갱신
async function bulkUpsertInventory(
  items: { sku: string; quantity: number; warehouseId: string }[],
) {
  if (items.length === 0) return;

  const values = items.map(
    (item) => Prisma.sql`(${item.sku}, ${item.quantity}, ${item.warehouseId}, NOW(), NOW())`
  );

  await prisma.$executeRaw`
    INSERT INTO "Inventory" (sku, quantity, warehouse_id, created_at, updated_at)
    VALUES ${Prisma.join(values)}
    ON CONFLICT (sku, warehouse_id)
    DO UPDATE SET
      quantity = EXCLUDED.quantity,
      updated_at = NOW()
  `;
}

// 성능 비교 (10,000건 기준):
// prisma.upsert() 루프     → ~45초
// $executeRaw 벌크 UPSERT  → ~0.3초 (150배 빠름)

타입 안전성 확보

Raw SQL의 최대 약점은 타입 안전성 부재다. 반환 타입을 제네릭으로 명시하고, Zod 등으로 런타임 검증을 추가하면 안전성을 크게 높일 수 있다. NestJS Zod 스키마 검증 심화에서 다룬 Zod 패턴을 Raw Query 결과 검증에도 적용할 수 있다.

import { z } from 'zod';

// 1. 결과 스키마 정의
const SalesReportSchema = z.object({
  product_id: z.string(),
  product_name: z.string(),
  total_sales: z.coerce.number(), // bigint → number 변환
  order_count: z.coerce.number(),
});

type SalesReport = z.infer<typeof SalesReportSchema>;

// 2. 타입 안전 래퍼 함수
async function getSalesReport(startDate: Date): Promise<SalesReport[]> {
  const raw = await prisma.$queryRaw<unknown[]>`
    SELECT
      p.id as product_id,
      p.name as product_name,
      COALESCE(SUM(oi.quantity * oi.unit_price), 0) as total_sales,
      COUNT(DISTINCT oi.order_id) as order_count
    FROM "Product" p
    LEFT JOIN "OrderItem" oi ON p.id = oi.product_id
    WHERE oi.created_at >= ${startDate}
    GROUP BY p.id, p.name
    ORDER BY total_sales DESC
  `;

  // 3. 런타임 검증
  return z.array(SalesReportSchema).parse(raw);
}

// bigint 직렬화 문제 해결
// Prisma는 COUNT, SUM 결과를 BigInt로 반환
// JSON.stringify 시 에러 → Zod coerce로 변환

트랜잭션과 Raw SQL 조합

Prisma의 Interactive Transaction 안에서 Raw SQL을 사용하면 ORM 쿼리와 Raw SQL을 하나의 트랜잭션으로 묶을 수 있다.

await prisma.$transaction(async (tx) => {
  // 1. Prisma Client로 주문 생성
  const order = await tx.order.create({
    data: {
      userId: input.userId,
      status: 'pending',
      totalAmount: input.totalAmount,
    },
  });

  // 2. Raw SQL로 재고 차감 (원자적 감소)
  const updated = await tx.$executeRaw`
    UPDATE "Inventory"
    SET quantity = quantity - ${input.quantity}
    WHERE sku = ${input.sku}
      AND quantity >= ${input.quantity}
  `;

  if (updated === 0) {
    throw new Error('재고 부족');
  }

  // 3. Raw SQL로 통계 갱신
  await tx.$executeRaw`
    INSERT INTO "ProductStats" (product_id, total_sold, last_sold_at)
    VALUES (${input.productId}, ${input.quantity}, NOW())
    ON CONFLICT (product_id)
    DO UPDATE SET
      total_sold = "ProductStats".total_sold + ${input.quantity},
      last_sold_at = NOW()
  `;

  return order;
});

마무리

Prisma Raw SQL은 ORM의 한계를 넘어서는 탈출 해치(escape hatch)다. Tagged Template의 자동 파라미터 바인딩으로 보안을 유지하면서, 윈도우 함수·CTE·벌크 UPSERT 같은 고급 SQL을 자유롭게 활용할 수 있다. Prisma Client 쿼리를 기본으로 쓰되, 성능이나 기능 한계에 부딪힐 때 Raw SQL로 전환하는 것이 실전에서 가장 효과적인 전략이다.