🚀 7부.NestJS와 Prisma로 Raw SQL 쿼리 사용하기 (rowsql)

NestJS 환경에서 Prisma를 사용하여 Raw SQL(로우 쿼리)을 실행하는 방법은 크게 두 가지 헬퍼 함수를 이용하는 것입니다. 이는 SQL 인젝션 공격을 방지하는 안전한 방법으로 권장됩니다.

---

1. PrismaClient의 Raw 쿼리 메서드

PrismaClient 인스턴스에 내장된 $queryRaw 또는 $executeRaw 메서드를 사용하여 Raw SQL 쿼리를 실행할 수 있습니다. NestJS의 서비스 레이어에서 PrismaService를 통해 접근하게 됩니다.

📝 $queryRaw (데이터 조회)

SELECT 쿼리와 같이 데이터를 조회할 때 사용합니다. 결과를 Record<string, unknown>[] 형태로 반환합니다.

import { Injectable } from '@nestjs/common';
import { PrismaService } from './prisma.service'; // 예시 Prisma 서비스

@Injectable()
export class UserService {
  constructor(private prisma: PrismaService) {}

  async findUsersByRawQuery(name: string) {
    // 템플릿 리터럴(Template Literal)과 Prisma.sql 헬퍼를 사용해 안전하게 쿼리 작성
    const result = await this.prisma.$queryRaw`
      SELECT id, name, email
      FROM "User"
      WHERE name = ${name}
      ORDER BY id DESC;
    `;
    return result;
  }
}

🛠️ $executeRaw (데이터 변경/실행)

INSERT, UPDATE, DELETE, DDL과 같이 데이터를 변경하거나 명령을 실행할 때 사용합니다. 실행된 행의 개수(BigInt)를 반환합니다.

// ... UserService 내 메서드 예시
async updateUserStatusRaw(id: number, status: string) {
  const affectedRows = await this.prisma.$executeRaw`
    UPDATE "User"
    SET status = ${status}
    WHERE id = ${id};
  `;
  return affectedRows; // 업데이트된 행의 수
}

---

2. 매개변수 바인딩의 중요성

Raw SQL을 사용할 때, 사용자 입력 값(변수)을 쿼리 문자열에 직접 삽입하지 않고 템플릿 리터럴 문법(${variable})을 사용하여 매개변수를 바인딩해야 합니다.

• 안전한 방법 (권장):Prisma는 내부적으로 이 방식을 통해 매개변수를 데이터베이스 드라이버로 전달하여 SQL 인젝션을 방지합니다.
   

  await this.prisma.$queryRaw`SELECT * FROM "User" WHERE id = ${userId}`;
  

• 위험한 방법 (피해야 함):변수가 문자열에 직접 합쳐지면 악의적인 입력에 취약해집니다.
   

  const unsafeQuery = `SELECT * FROM "User" WHERE id = ${userId}`;
  await this.prisma.$queryRawUnsafe(unsafeQuery); // $queryRawUnsafe 사용도 권장되지 않음
  

---

3. Prisma.sql 헬퍼 사용

복잡한 쿼리나 쿼리 조각을 안전하게 구성할 때는 Prisma.sql 헬퍼를 사용할 수 있습니다. 이는 합성 가능한 쿼리 조각을 만들 때 유용합니다.

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

const client = new PrismaClient(); // 예시

async function getAdminUsers() {
  const baseQuery = Prisma.sql`
    SELECT id, name
    FROM "User"
  `;

  const condition = Prisma.sql`
    WHERE role = 'ADMIN' AND status = ${'ACTIVE'}
  `;

  const rawQuery = Prisma.sql`${baseQuery} ${condition} ORDER BY id DESC;`;

  const result = await client.$queryRaw(rawQuery);
  return result;
}

Prisma.sql을 사용하면 쿼리 조각을 안전하게 조합한 후, 이를 $queryRaw 또는 $executeRaw에 인자로 전달할 수 있습니다.

---

🛠️ 실무에서 유용한 복잡한 Prisma Raw SQL 쿼리 예제

실무에서는 단순히 SELECT *만 사용하는 것이 아니라, 집계(Aggregation), 조인(JOIN), 조건부 로직, 그리고 랭킹(Ranking) 등을 활용한 복잡한 쿼리가 필요합니다.

아래 예시들은 NestJS 서비스 레이어에서 PrismaService를 통해 접근한다고 가정하고 Prisma의 $queryRaw 메서드와 안전한 템플릿 리터럴 바인딩을 사용합니다.

---

1. 📊 월별 판매액 집계 및 정렬 (Sales Aggregation)

여러 테이블을 조인하여 특정 기간의 데이터를 집계(SUM)하고 그룹화(GROUP BY)하는 가장 흔한 실무 쿼리입니다.

// prismaService: PrismaService
async getMonthlySales(startDate: Date, endDate: Date) {
  // DATE_TRUNC 함수는 PostgreSQL 기준. MySQL/SQLite는 DATE_FORMAT, YEAR/MONTH 함수 사용.
  const result = await this.prisma.$queryRaw`
    SELECT
      DATE_TRUNC('month', o."createdAt") AS sale_month,
      SUM(oi.price * oi.quantity) AS total_monthly_sales,
      COUNT(DISTINCT o.id) AS total_orders
    FROM
      "Order" o
    JOIN
      "OrderItem" oi ON o.id = oi."orderId"
    WHERE
      o."createdAt" >= ${startDate} AND o."createdAt" <= ${endDate}
    GROUP BY
      sale_month
    ORDER BY
      sale_month DESC;
  `;
  return result;
}

• 핵심: JOIN을 통해 주문(Order)과 주문 상품(OrderItem)을 연결하고, DATE_TRUNC로 날짜를 월 단위로 자른 후, SUM()과 COUNT(DISTINCT)로 집계합니다.

---

2. 🥇 상위 N개 사용자 랭킹 (Window Function - RANK)

전체 사용자 중 구매액 기준으로 상위 10명을 랭킹과 함께 조회하는 쿼리입니다. 윈도우 함수(Window Function)를 사용합니다.

// prismaService: PrismaService
async getTopTenCustomersBySpending() {
  const result = await this.prisma.$queryRaw`
    WITH UserTotalSpending AS (
      -- 사용자별 총 지출액을 계산하는 CTE (Common Table Expression)
      SELECT
        u.id AS user_id,
        u.name AS user_name,
        SUM(o.totalAmount) AS total_spent
      FROM
        "User" u
      JOIN
        "Order" o ON u.id = o."userId"
      GROUP BY
        u.id, u.name
    )
    SELECT
      *,
      -- 윈도우 함수: total_spent를 기준으로 내림차순 랭킹 부여
      RANK() OVER (ORDER BY total_spent DESC) AS spending_rank
    FROM
      UserTotalSpending
    ORDER BY
      spending_rank
    LIMIT 10;
  `;
  return result;
}

• 핵심: WITH 절을 사용하여 중간 결과(CTE, UserTotalSpending)를 정의하고, RANK() OVER(...) 윈도우 함수로 순위를 매긴 후 LIMIT으로 상위 N개를 제한합니다.

---

3. 📉 재고 부족 상품 목록 조회 (Subquery/NOT EXISTS)

현재 재고가 특정 임계값(Threshold) 보다 낮고, 최근 30일 이내에 재입고되지 않은 상품 목록을 조회하는 쿼리입니다.

// prismaService: PrismaService
async getLowStockItems(threshold: number, days: number = 30) {
  // 날짜 계산은 DB 함수를 사용하는 것이 정확하지만, 여기서는 TIMESTAMP 차이 예시
  const thirtyDaysAgo = new Date(Date.now() - days * 24 * 60 * 60 * 1000);
  
  const result = await this.prisma.$queryRaw`
    SELECT
      p.id,
      p.name,
      p.stock
    FROM
      "Product" p
    WHERE
      p.stock < ${threshold} -- 1. 재고 임계값보다 낮은 상품
      AND NOT EXISTS (
        -- 2. 최근 30일 이내에 해당 상품의 재입고(StockAdjustment) 기록이 없는 경우
        SELECT 1
        FROM "StockAdjustment" sa
        WHERE sa."productId" = p.id
          AND sa.type = 'REPLENISH' -- '재입고' 타입
          AND sa."createdAt" >= ${thirtyDaysAgo}
      )
    ORDER BY
      p.stock ASC;
  `;
  return result;
}

• 핵심: NOT EXISTS 절을 사용하여 서브쿼리의 결과가 존재하지 않는 경우를 조건으로 활용합니다. 이는 LEFT JOIN ... WHERE T2.id IS NULL 패턴과 유사하게 사용될 수 있습니다.

---

4. 🗄️ JSON 데이터 내 조건 검색 (JSON Operators)

JSONB/JSON 타입 필드(metadata) 내의 특정 키-값 쌍을 기준으로 필터링하는 쿼리입니다. (PostgreSQL 기준)

// prismaService: PrismaService
// 'metadata' 필드가 JSONB 타입이라고 가정
async findUsersWithSpecificPreference(key: string, value: string) {
  const result = await this.prisma.$queryRaw`
    SELECT
      id,
      name,
      metadata
    FROM
      "User"
    WHERE
      metadata ->> ${key} = ${value}; -- JSON 필드에서 키를 찾아 문자열 값을 비교
  `;
  return result;
}

• 핵심: PostgreSQL의 ->> 연산자를 사용하여 JSON 필드(metadata)에서 특정 키(key)에 해당하는 값을 텍스트로 추출하여 비교합니다.

---

5. 🔍 동적 검색 및 정렬

사용자가 입력한 검색어와 정렬 조건에 따라 동적으로 쿼리 조건을 구성하는 쿼리입니다. (쿼리 조건은 서비스 코드 내에서 안전하게 구성해야 합니다.)

// prismaService: PrismaService
async searchProducts(searchTerm: string, sortBy: 'price' | 'name' = 'name', limit: number = 10) {
  
  // ⚠️ 주의: 정렬 기준(sortBy)은 템플릿 리터럴로 바인딩할 수 없으므로,
  // 코드 레벨에서 화이트리스트(허용 목록)를 통해 안전성을 확보해야 합니다.
  const safeSortBy = ['price', 'name'].includes(sortBy) ? sortBy : 'name';
  const sortDirection = safeSortBy === 'price' ? 'DESC' : 'ASC';
  
  const result = await this.prisma.$queryRaw(Prisma.sql`
    SELECT
      id,
      name,
      price,
      description
    FROM
      "Product"
    WHERE
      name ILIKE ${'%' + searchTerm + '%'} -- ILIKE는 대소문자 무시 검색 (PostgreSQL)
      OR description ILIKE ${'%' + searchTerm + '%'}
    ORDER BY
      ${Prisma.raw(safeSortBy)} ${Prisma.raw(sortDirection)}
    LIMIT
      ${limit};
  `);
  return result;
}

• 핵심: ILIKE를 사용하여 대소문자 구분 없이 검색하며, Prisma.raw()를 사용하여 SQL 키워드(정렬 기준, 방향) 자체를 쿼리에 삽입합니다. Prisma.raw()는 바인딩을 건너뛰므로 반드시 신뢰할 수 있는 소스(화이트리스트)에서 온 값에만 사용해야 합니다.

---

⚠️ Raw SQL 쿼리 사용 시 인젝션 보안 문제와 주의할 점

NestJS와 Prisma를 사용하여 Raw SQL을 실행할 때, 가장 중요한 주의점은 SQL 인젝션(SQL Injection) 공격으로부터 코드를 보호하는 것입니다.

1. 🛡️ 인젝션 보안 문제: Prisma의 보호 메커니즘

Prisma는 $queryRaw와 $executeRaw 메서드를 제공할 때, 기본적인 Raw 쿼리 실행 방식을 SQL 인젝션으로부터 안전하게 설계했습니다.

• 매개변수화된 쿼리 (Parameterized Queries):
   

  // 안전함: name 변수는 쿼리 템플릿과 분리되어 DB에 전달됨
  await this.prisma.$queryRaw`
    SELECT * FROM "User" WHERE name = ${userName};
  `;
  

  이 방식은 사용자 입력 값과 SQL 명령어를 분리하여, 사용자가 입력한 값이 쿼리의 일부가 아닌 단순한 데이터 값으로만 처리되도록 합니다. 이것이 SQL 인젝션을 막는 가장 강력한 방법입니다.
• Prisma는 일반적인 템플릿 리터럴 문법(백틱 ``) 내에서 변수(${...}를 사용할 경우, 이를 매개변수(Parameter)로 인식하고 데이터베이스 드라이버에 전달합니다.
• 절대 피해야 할 위험한 방식:

  const userInput = "'; DROP TABLE \"User\"; --"; // 악의적인 입력
  const unsafeQuery = `SELECT * FROM "User" WHERE name = '${userInput}'`; // 입력이 쿼리 구조를 바꿈
  
  // ❌ 매우 위험함: Prisma의 보호 메커니즘을 우회
  await this.prisma.$queryRawUnsafe(unsafeQuery);
  

• 사용자 입력 값을 직접 문자열로 조합하여 $queryRawUnsafe나 일반 $queryRaw에 전달하는 것은 매우 위험합니다.

---

2. 🚨 주의할 점 (SQL 인젝션을 피하는 방법)

A. 모든 변수에는 템플릿 리터럴 바인딩(${...}) 사용

SQL 명령어의 일부가 아닌, 사용자 입력이나 변수로 들어오는 모든 데이터 값은 반드시 템플릿 리터럴 내부에서 ${variable} 형식으로 바인딩해야 합니다.

• 올바른 예시:
   

  await this.prisma.$executeRaw`UPDATE "Product" SET price = ${newPrice} WHERE id = ${productId}`;
  

B. SQL 키워드를 동적으로 사용할 때는 Prisma.raw()와 화이트리스트 활용

ORDER BY 컬럼 이름, ASC/DESC 정렬 방향, 테이블 이름 등 SQL 명령어의 구조 자체를 동적으로 변경해야 할 경우, 템플릿 리터럴 바인딩($ {...})으로는 안전하게 처리할 수 없습니다. 이 경우 Prisma.raw()를 사용해야 하는데, 이는 인젝션에 취약하므로 다음과 같은 조치가 필수적입니다.

1. 화이트리스트(허용 목록) 검증: 허용 가능한 컬럼 이름이나 키워드 리스트를 서비스 코드에 정의하고, 입력 값이 이 리스트에 포함되어 있는지 반드시 확인해야 합니다.
2. Prisma.raw() 사용: 검증된 값만 Prisma.raw()로 감싸 쿼리에 삽입합니다.

 

// 1. 허용 가능한 컬럼 이름 정의 (화이트리스트)
const allowedColumns = ['name', 'price', 'stock'];
const sortColumn = 'name'; // 사용자 입력이라고 가정

if (!allowedColumns.includes(sortColumn)) {
    throw new Error('Invalid sort column');
}

// 2. 안전성이 보장된 후에 Prisma.raw() 사용
await this.prisma.$queryRaw`
  SELECT * FROM "Product"
  ORDER BY ${Prisma.raw(sortColumn)} DESC
`;

C. Prisma.sql 헬퍼 사용

복잡한 쿼리 조각을 만들고 조합할 때, Prisma.sql 태그를 사용하여 쿼리를 작성하면 매개변수 바인딩이 일관되고 안전하게 유지됩니다.

const whereCondition = Prisma.sql`WHERE category = ${categoryId} AND status = 'ACTIVE'`;

await this.prisma.$queryRaw(Prisma.sql`
  SELECT name FROM "Product" ${whereCondition}
`);

요약

Prisma Raw 쿼리의 안전성은 전적으로 개발자가 변수를 쿼리에 데이터 값으로 바인딩하는지, 아니면 문자열로 직접 조합하는지에 달려 있습니다.

방식	설명	안전성
${변수} (템플릿 바인딩)	데이터 값 바인딩. 모든 사용자 입력 값에 사용.	매우 안전
Prisma.raw(키워드)	SQL 키워드(컬럼, 방향) 삽입 시 사용.	위험 (화이트리스트 검증 필수)
$queryRawUnsafe (or 문자열 조합)	쿼리 문자열을 직접 구성하여 전달.	매우 위험 (절대 사용 금지)

---

📦 MySQL에서 SELECT 결과를 JSON으로 반환하는 예제 (계층적 그룹핑)

MySQL에서 Raw SQL 쿼리 결과를 JSON 객체나 JSON 배열 형태로 직접 구성하여 반환하려면 JSON_OBJECT와 JSON_ARRAYAGG 함수를 사용합니다.

이 방법은 특히 그룹핑된 데이터를 계층적인 구조로 한 번의 쿼리로 가져올 때 유용하며, Node.js/NestJS 백엔드에서 데이터 구조화 작업을 줄여줍니다.

---

1. 📂 JSON 객체 및 배열 함수 개요 (MySQL)

함수	용도	설명
JSON_OBJECT(key, value, ...)	단일 JSON 객체 생성	키-값 쌍을 인수로 받아 { "key": "value" } 형태의 객체를 만듭니다.
JSON_ARRAYAGG(expression)	JSON 배열 집계	그룹 내의 행들을 모아 [val1, val2, ...] 형태의 JSON 배열을 만듭니다. (집계 함수)

---

2. 🌳 실무 예제: 카테고리별 상품 목록 계층 구조

목표: 카테고리 정보를 상위 레벨 객체로 만들고, 해당 카테고리에 속한 상품 목록을 그 안에 배열로 포함하는 JSON 구조를 만듭니다.

📝 SQL 쿼리 (MySQL)

SELECT
    -- 1. 카테고리 정보와, 해당 카테고리의 상품 배열을 포함하는 최종 JSON 객체 생성
    JSON_OBJECT(
        'categoryId', c.id,
        'categoryName', c.name,
        'productCount', COUNT(p.id),
        'products', (
            -- 2. 해당 그룹 내의 모든 상품 정보를 JSON 배열로 집계
            SELECT
                JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'productId', p_inner.id,
                        'productName', p_inner.name,
                        'price', p_inner.price,
                        'stock', p_inner.stock
                    )
                )
            FROM
                Product p_inner
            WHERE
                p_inner.categoryId = c.id
        )
    ) AS category_data
FROM
    Category c
JOIN
    Product p ON c.id = p.categoryId
GROUP BY
    c.id, c.name;

💻 NestJS/Prisma 적용 예시

NestJS 서비스 파일에서 $queryRaw를 사용하여 위 쿼리를 실행합니다.

import { Injectable } from '@nestjs/common';
import { PrismaService } from './prisma.service';
import { Prisma } from '@prisma/client'; // Prisma.sql 사용을 위해 임포트

@Injectable()
export class ProductService {
  constructor(private prisma: PrismaService) {}

  async getCategoryProductsJson() {
    const rawQuery = Prisma.sql`
        SELECT
            JSON_OBJECT(
                'categoryId', c.id,
                'categoryName', c.name,
                'productCount', COUNT(p.id),
                'products', (
                    SELECT
                        JSON_ARRAYAGG(
                            JSON_OBJECT(
                                'productId', p_inner.id,
                                'productName', p_inner.name,
                                'price', p_inner.price,
                                'stock', p_inner.stock
                            )
                        )
                    FROM
                        Product p_inner
                    WHERE
                        p_inner.categoryId = c.id
                )
            ) AS category_data
        FROM
            Category c
        JOIN
            Product p ON c.id = p.categoryId
        GROUP BY
            c.id, c.name;
    `;

    // 쿼리 실행
    const results = await this.prisma.$queryRaw<Prisma.JsonValue[]>(rawQuery);
    
    // 결과는 [{ category_data: JSON_VALUE }, { category_data: JSON_VALUE }, ...] 형태가 됩니다.
    // 결과의 category_data 필드 값은 이미 DB에서 JSON 객체로 반환됩니다.
    return results.map(row => row['category_data']);
  }
}

💡 반환되는 JSON 구조 예시

getCategoryProductsJson 함수가 반환하는 최종 데이터 구조는 다음과 같은 JSON 배열 형태가 됩니다.

[
  {
    "categoryId": 1,
    "categoryName": "전자제품",
    "productCount": 3,
    "products": [
      {
        "productId": 101,
        "productName": "노트북",
        "price": 1500000,
        "stock": 50
      },
      {
        "productId": 102,
        "productName": "스마트폰",
        "price": 800000,
        "stock": 120
      },
      // ... 기타 상품
    ]
  },
  {
    "categoryId": 2,
    "categoryName": "의류",
    "productCount": 5,
    "products": [
      // ... 의류 상품 목록
    ]
  }
]

3. 📝 단일 주문 상세 정보 조회 예제

목표: 특정 주문(Order)에 대한 상세 정보(주문자, 주문일)를 객체로 만들고, 주문에 포함된 상품(OrderItem) 목록을 하위 배열로 포함하는 단일 JSON 객체를 반환합니다.

📝 SQL 쿼리 (MySQL)

SELECT
    JSON_OBJECT(
        'orderId', o.id,
        'customerName', u.name,
        'orderDate', o.orderDate,
        'totalAmount', o.totalAmount,
        'items', (
            -- 주문 상품 목록을 JSON 배열로 집계 (서브쿼리)
            SELECT
                JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'itemName', oi.productName,
                        'quantity', oi.quantity,
                        'price', oi.price
                    )
                )
            FROM
                OrderItem oi
            WHERE
                oi.orderId = o.id
        )
    ) AS order_detail
FROM
    `Order` o
JOIN
    `User` u ON o.userId = u.id
WHERE
    o.id = 1001; -- 특정 주문 ID 바인딩 필요

이 쿼리는 단 하나의 레코드만 반환하며, 그 레코드는 order_detail이라는 필드에 전체 주문 상세 정보를 담은 JSON 객체를 포함합니다.