6편. Prisma로 해결되지 않는 쿼리 다루기: Raw SQL 실전 활용
📚 목차
1. $queryRaw: 조회 전용 Raw SQL 다루기
2. $executeRaw: 데이터 변경 및 DDL 쿼리 실행하기
3. Prisma Raw SQL 보안 : SQL Injection 방지 원리
4. Prisma.sql: 유연하고 안전한 동적 쿼리 조립
📂 [GitHub 예시 코드 보러가기] (https://github.com/cericube/nodejs-tutorials) /Prisma
1. $queryRaw: 조회 전용 Raw SQL 다루기
Prisma Client는 훌륭한 쿼리 빌더를 제공하지만, 때로는 복잡한 통계, Window Function, 혹은 DB 전용 기능을 사용하기 위해 생생한 SQL(Raw SQL)을 직접 작성해야 할 때가 있습니다. 이때 사용하는 도구가 바로 $queryRaw입니다.
🔷 $queryRaw의 핵심 개념: 보안과 효율의 조화
$queryRaw는 단순히 문자열을 실행하는 것이 아니라, 보안과 타입 시스템을 연결하는 똑똑한 도구입니다.
▸ Tagged Template Literal 방식: $queryRaw는 괄호() 대신 백틱(`)을 사용하여 쿼리를 작성합니다.
▸ Parameterized Query (자동 보안): 백틱 내부에 ${variable}을 사용하면
Prisma가 이를 분석하여 DB 엔진에 파라미터로 전달합니다. 이를 통해 SQL Injection 공격을 원천 차단합니다.
▸ 결과 형식: 쿼리 결과는 항상 배열([]) 형태이며, 결과가 없으면 빈 배열이 반환됩니다.
🔷 타입 정의와 컬럼 매핑 (Type Casting)
Raw SQL은 Prisma가 결과를 미리 예측할 수 없으므로, TypeScript 개발자가 직접 타입을 정의하고 제네릭($queryRaw<T>)으로 알려주어야 합니다.
실무에서는 type을 조금 더 권장하는 추세지만, 확정성이 중요하다면 interface를 사용하는 것이 유리할 수 있습니다.
💡 주의사항
제네릭으로 넘기는 타입은 TypeScript 컴파일 단계에서의 '힌트'일 뿐입니다.
Prisma가 런타임에 실제 데이터를 해당 타입에 맞춰 변형(Transform)해주지는 않습니다.
따라서 SQL의 Alias(별칭)와 인터페이스의 프로퍼티명을 반드시 일치시켜야 합니다.
✔️ 추천 전략: camelCase 유지하기
PostgreSQL 같은 데이터베이스는 기본적으로 식별자를 소문자로 처리합니다.
TypeScript의 관례인 camelCase를 유지하려면 SQL 작성 시 큰따옴표("")를 활용하는 것이 가장 좋습니다.
//interface PostStats {
// postId: number;
// title: string;
// authorName: string;
//}
// 1. 타입을 읽기 전용으로 정의 (DTO 성격 강조)
type PostStats = {
readonly postId: number;
readonly title: string;
readonly authorName: string;
};
const posts = await prisma.$queryRaw<PostStats[]>`
SELECT
p.id AS "postId", -- 따옴표를 사용하여 대소문자 유지
p.title AS "title",
u.display_name AS "authorName"
FROM posts p
JOIN users u ON p.author_id = u.id
`;
🔷 특수 타입 처리 (BigInt 및 Decimal)
Raw SQL 사용 시 가장 흔히 발생하는 이슈가 바로 데이터 타입 불일치입니다.
🔸 BigInt
Raw SQL의 COUNT(*)나 BIGINT 결과는 JavaScript의 BigInt로 반환 되면, JSON.stringify가 지원되지 않습니다.
이를 해결하려면 SQL에서 ::int로 캐스팅하거나 코드에서 Number() 변환을 적용해야 합니다.
🔸 Decimal
Prisma 스키마의 Decimal 타입을 decimal.js 객체로 반환되므로,
연산이나 API 응답시 .toNumber() 또는 toString()으로 변환하는 것이 필수 입니다.
🔷 예제: 게시글 통계 조회 (JOIN & 집계)
여러 테이블을 조인하고 집계 함수를 사용하는 실제 비즈니스 로직 예시입니다.
import { prisma } from '../shared/database';
/**
* 게시글별 댓글 수와 작성자 정보를 포함한 통계를 가져옵니다.
*/
async function getPostStatistics() {
// 1. 반환받을 데이터의 구조를 정의합니다.
// SQL의 컬럼 alias("postId", "authorName" 등)와 인터페이스의 필드명이 정확히 일치해야 합니다.
interface PostSummary {
postId: number;
title: string;
authorName: string;
commentCount: number; // SQL에서 CAST하는 편이 좋습니다
createdAt: Date;
}
// 2. $queryRaw 실행
// SQL 내에서 명시적으로 ::int 캐스팅을 수행하여 BigInt 에러를 방지합니다.
const stats = await prisma.$queryRaw<PostSummary[]>`
SELECT
p.id AS "postId",
p.title,
u.display_name AS "authorName",
COUNT(c.id)::int AS "commentCount",
p.created_at AS "createdAt"
FROM study.posts p
-- 작성자는 항상 존재한다고 가정: INNER JOIN
inner JOIN study.users u ON p.author_id = u.id
-- 댓글은 없을 수 있으므로 LEFT JOIN (댓글 0개도 결과에 포함)
LEFT join study.comments c ON c.post_id = p.id
-- COUNT 집계를 쓰므로, SELECT에 나온 비집계 컬럼은 GROUP BY에 포함
GROUP BY p.id, p.title, p.created_at, u.display_name
ORDER BY p.created_at DESC
LIMIT 10;
`;
return stats;
}
2. $executeRaw: 데이터 변경 및 DDL 쿼리 실행하기
Prisma의 표준 메서드(update, delete 등)만으로 해결하기 힘든 대량의 데이터 업데이트나, 데이터베이스 스키마를 직접 제어해야 하는 상황에서는 $executeRaw가 해결사가 됩니다.
🔷 $executeRaw의 역할과 특징
$executeRaw는 데이터베이스의 상태를 변화시키는 CUD(Create, Update, Delete) 작업과 DDL(Data Definition Language) 명령에 특화되어 있습니다.
▸ 반환 값 (Promise<number>): 조회된 데이터를 반환하는 대신,
해당 쿼리로 인해 영향을 받은 행(Row)의 개수를 숫자로 반환합니다.
▸ 용도: UPDATE, DELETE, INSERT, TRUNCATE, ALTER TABLE 등
▸ 보안성: $queryRaw와 마찬가지로 Tagged Template Literal을 사용하여 SQL Injection 공격을 자동으로 방어합니다.
🔷 예: 복잡한 조건의 Soft Delete
// 특정 작성자의 게시글을 물리 삭제하지 않고 deleted_at 컬럼을 채워 소프트 삭제하는 함수
async function softDeletePostsByAuthor(authorId: number) {
// 로그 구분용 출력
console.log('\n=== 작성자 게시글 소프트 삭제 (executeRaw 예시) ===');
// Raw SQL 실행
// - deleted_at IS NULL 조건으로 이미 삭제된 게시글은 제외
// - $executeRaw는 영향을 받은 row 개수를 반환함
const resultCount = await prisma.$executeRaw`
UPDATE study.posts
SET
updated_at = NOW(), -- 수정 시각 갱신
deleted_at = NOW() -- 소프트 삭제 처리
WHERE author_id = ${authorId} -- 대상 작성자
AND deleted_at IS NULL -- 아직 삭제되지 않은 게시글만
`;
// 실제 소프트 삭제된 게시글 수 출력
console.log(`소프트 삭제된 게시글 수: ${resultCount}개`);
// 영향 받은 row 수 반환
return resultCount;
}
🔷 심화 활용: DDL 및 인덱스 관리
Prisma Migrate로 관리하기 까다로운 DB 고유의 최적화 작업(예: PostgreSQL의 CONCURRENTLY 인덱스 생성)을 수행할 때 $executeRaw를 활용할 수 있습니다.
// 운영/성능 개선 목적의 인덱스를 생성하는 예시 함수
// - "소프트 삭제"를 쓰는 테이블에서 deleted_at IS NULL(활성 데이터)만 대상으로 인덱스를 만들어
// 자주 쓰는 조회(예: published 필터 + 최신순 정렬 + LIMIT)의 성능을 개선한다.
async function createPerformanceIndex() {
// 로그 구분용 출력
console.log('\n=== 성능 향상을 위한 인덱스 생성 예시 ===');
// Raw SQL 실행: 인덱스 생성(DDL)
// - CONCURRENTLY: 인덱스 생성 중에도 테이블 읽기/쓰기를 최대한 막지 않음(운영 환경 고려)
// *주의: CONCURRENTLY는 트랜잭션 블록(예: prisma.$transaction 내부)에서 실행하면 실패함
// - (published, created_at DESC):
// published 조건으로 필터링 후, created_at 최신순 정렬을 인덱스 순서로 처리해
// ORDER BY created_at DESC 정렬 비용을 줄이고 LIMIT 조회(Top-N)를 빠르게 함
// - WHERE deleted_at IS NULL: 소프트 삭제된 행은 제외하고 "활성 행"만 인덱스에 포함(부분 인덱스)
const result = await prisma.$executeRaw`
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_post_published_active
ON study.posts(published, created_at DESC)
WHERE deleted_at IS NULL;
`;
console.log('활성 게시글(published + 최신순) 조회용 인덱스 생성 완료');
console.log(result);
}
🔷 주의사항 및 권장 패턴
1. 트랜잭션으로 안전 장치 마련하기
여러 개의 Raw SQL 작업을 수행할 때 하나라도 실패하면 원복되어야 합니다. 이때 prisma.$transaction을 사용하세요.
await prisma.$transaction([
prisma.$executeRaw`UPDATE "User" SET "status" = 'INACTIVE' WHERE "id" = ${id}`,
prisma.$executeRaw`INSERT INTO "Log" ("userId", "action") VALUES (${id}, 'DEACTIVATE')`
]);
2. DB 종속성 인식하기
$executeRaw 내부에 작성된 SQL은 특정 DB(PostgreSQL, MySQL 등)의 문법에 종속됩니다. 향후 데이터베이스 엔진을 교체할 계획이 있다면, 이러한 Raw SQL 코드가 교체 비용을 높일 수 있음을 인지해야 합니다.
3. 문자열 보간(Interpolation) 주의
반드시 ${variable} 형태의 템플릿 리터럴을 사용하세요.
만약 외부 입력값을 + 연산자로 직접 합쳐서 넣어야 한다면 $executeRawUnsafe를 사용해야 하지만, 이는 SQL Injection에 무방비하므로 극히 예외적인 상황이 아니면 피해야 합니다.
3. Prisma Raw SQL 보안 : SQL Injection 방지 원리
Raw SQL은 자유도가 높지만, 그만큼 SQL Injection(SQL 주입 공격)의 위험에 노출되기 쉽습니다.
Prisma는 이를 방지하기 위해 언어적 특성과 DB 엔진의 기능을 결합한 철저한 보안 모델을 제공합니다.
🔷 Tagged Template Literal: 안전한 쿼리의 핵심
Prisma에서 $queryRaw나 $executeRaw를 사용할 때 백틱(`)을 바로 붙여 쓰는 이유는 단순한 편의성 때문이 아닙니다.
이는 JavaScript의 Tagged Template Literal 기능을 활용해 쿼리를 준비된 문장(Prepared Statement)으로 변환하기 위함입니다.
✔️ 작동 원리 (Parameterized Query)
사용자가 입력한 변수를 SQL 문자열에 그대로 이어 붙이는 대신, Prisma 엔진은 이를 다음과 같은 단계를 거쳐 처리합니다.
1) 템플릿 분석: Prisma는 SQL 문장(뼈대)과 변수(${userId})를 즉시 분리합니다.
2) 플레이스홀더 치환: 실제 DB로 전달될 때는 SELECT * FROM User WHERE id = ?와 같이 값이 비어있는 형태로 전달됩니다.
3) 값 바인딩: 변수 값은 별도의 데이터 패킷으로 전달됩니다. DB 엔진은 이 값을 '명령어'가 아닌 순수한 '데이터'로만 취급하므로, 악성 스크립트가 실행될 여지가 없습니다..
🔷 $queryRawUnsafe: 보안 취약점의 통로
이름에 'Unsafe'가 붙은 메서드는 파라미터 바인딩을 강제하지 않습니다.
개발자가 일반 문자열을 조합해 전달할 수 있게 허용하므로, 외부 입력값이 포함될 경우 보안 사고로 직결됩니다.
✔️ 위험한 코드 예시
// 공격자가 입력한 값: "1 OR 1=1"
const userInput = "1 OR 1=1";
// 매우 위험: 입력값이 쿼리 문장에 그대로 병합됩니다.
const result = await prisma.$queryRawUnsafe(
`SELECT * FROM users WHERE id = ${userInput}`
);
// 실제 실행 쿼리: SELECT * FROM users WHERE id = 1 OR 1=1
// 결과: 인증을 우회하여 모든 사용자 정보가 유출될 수 있습니다.
🔷 고급 보안: Prisma.sql을 활용한 동적 쿼리
실무에서는 조건에 따라 쿼리가 변하는 동적 쿼리가 자주 필요합니다.
Prisma 7에서는 Prisma.sql 객체를 사용하여 보안을 유지하면서도 쿼리 조각(Snippet)을 유연하게 결합할 수 있습니다.
✔️ 안전한 동적 필터링 예제
Prisma.sql로 생성된 조각들은 최종 실행 시점에 하나의 안전한 Parameterized Query로 통합됩니다.
import { Prisma } from '../generated/client';
import type { Post } from '../generated/client';
async function searchPosts(authorId: number, published: boolean) {
console.log('\n=== 동적 필터링을 위한 안전한 동적 쿼리 빌드 예시 ===');
/**
* 1) 동적 WHERE 조건 조각을 Prisma.sql로 만듭니다.
*/
const condition = Prisma.sql`
author_id = ${authorId} AND published = ${published} AND deleted_at IS NULL
`;
/**
* 2) Raw SQL 결과를 Post 타입으로 받고 싶다면,
* SELECT 결과 컬럼명이 Post 타입 필드명과 일치해야 합니다.
* - DB 컬럼: created_at, author_id (snake_case)
* - Post 필드: createdAt, authorId (camelCase)
*/
const posts = await prisma.$queryRaw<Post[]>`
SELECT
p.id,
p.created_at AS "createdAt",
p.updated_at AS "updatedAt",
p.deleted_at AS "deletedAt",
p.title,
p.content,
p.published,
p.author_id AS "authorId"
FROM study.posts p
WHERE ${condition}
ORDER BY p.created_at DESC
`;
console.log(`작성자 ID=${authorId}의 게시글 조회 결과:`);
console.log(posts);
return posts;
}
🔷 Prisma Raw SQL API 비교 요약
| 구분 | $queryRaw / $executeRaw | RawUnsafe / $executeRawUnsafe |
| SQL 전달 방식 | Tagged Template Literalts(백틱 사용) | 일반 문자열(String) |
| 파라미터 바인딩 | 자동 바인딩 (Prepared Statement) | 바인딩 없음 |
| SQL Injection 방어 | 안전함 | 매우 위험 |
| 동적 값 사용 | 값(value)만 허용(숫자, 문자열 등) | 값·식별자 모두 가능 (위험) |
| 동적 테이블/컬럼명 | ❌ 불가 (의도적 제한) | ⭕ 가능 (보안 리스크) |
| 권장 사용 여부 | 무조건 사용 권장 | 사용 지양 / 특수 상황만 허용 |
✔️ 실무 팁: 동적 테이블/컬럼명이 꼭 필요하다면?
만약 테이블 이름 자체가 변수여야 하는 특수한 상황이라면 $queryRaw는 작동하지 않습니다.
이때는 어쩔 수 없이 $queryRawUnsafe를 쓰되, 입력값이 미리 정의된 안전한 리스트에 있는지(White-list) 반드시 검증해야 합니다.
// 예: 허용된 테이블명인지 검증 후 실행
const allowedTables = ['posts', 'comments'];
if (!allowedTables.includes(tableName)) throw new Error("Invalid Table");
await prisma.$queryRawUnsafe(`SELECT * FROM ${tableName}`);
4. Prisma.sql : 유연하고 안전한 동적 쿼리 조립
단순한 Raw SQL 실행을 넘어, 복잡한 비즈니스 로직에 맞춰 쿼리를 자유자재로 구성하고 싶을 때 Prisma SQL Tag(Prisma.sql)가 필요합니다.
이는 쿼리 조각(Fragment)을 안전하게 다루기 위한 전용 도구입니다.
🔷 SQL Tag란 무엇인가?
Prisma.sql은 단순히 문자열을 만드는 도구가 아니라, 보안(Security)과 조합성(Composability)을 동시에 제공하는 쿼리 빌더 객체를 생성합니다.
▸ 보안: 태그 내부에 전달된 ${변수}는 실행 시점에 자동으로 Prepared Statement 파라미터로 치환됩니다.
▸ 조합성: 여러 개의 Prisma.sql 조각을 배열에 담아 합치거나, 다른 쿼리문 내부에 중첩해서 삽입할 수 있습니다.
🔷 동적 조립의 핵심 도구: join과 empty
여러 조건을 상황에 따라 연결해야 할 때 다음 유틸리티들이 강력한 힘을 발휘합니다.
▸ Prisma.sql.join(조각들, '구분자'): 배열로 모인 쿼리 조각들을 AND, OR, , 등으로 안전하게 연결합니다.
▸ Prisma.empty: 조건이 없을 때 쿼리문에 아무것도 넣고 싶지 않을 경우 사용하는 '빈 조각'입니다. null이나 undefined 대신 사용하여 구문 오류를 방지합니다.
🔷 예제: 다중 조건 검색 엔진 (Dynamic Filter)
사용자가 선택한 필터(상태, 작성자, 키워드)가 있을 때만 WHERE 절을 구성하는 실무 패턴입니다.
import { Prisma } from '../generated/client';
import type { Comment } from '../generated/client';
async function searchComments(filters: {
posId: number; // 필수: 특정 게시글의 댓글만 조회
authorId?: number; // 옵션: 특정 작성자 댓글만 조회
keyword?: string; // 옵션: 내용 키워드 검색(ILIKE)
includeDeleted?: boolean; // 옵션: 삭제된 댓글 포함 여부(기본: 제외)
}) {
console.log('\n=== 동적 필터링을 위한 안전한 동적 쿼리 빌드 예시 ===');
// 동적으로 추가될 WHERE 조건들을 누적할 배열
// Prisma.sql 템플릿을 사용하면 파라미터 바인딩이 적용되어 SQL Injection을 방지할 수 있음
const conditions: Prisma.Sql[] = [];
// 1) 필수 조건: 게시글 ID
conditions.push(Prisma.sql`c.post_id = ${filters.posId}`);
// 2) 삭제 댓글 처리: includeDeleted가 true가 아니면(=false/undefined) 삭제되지 않은 것만
!filters.includeDeleted && conditions.push(Prisma.sql`c.deleted_at IS NULL`);
// 3) 작성자 필터: 값이 있을 때만 조건 추가
// (주의: authorId가 0일 가능성이 있다면 `filters.authorId != null` 형태가 더 안전)
if (filters.authorId) {
conditions.push(Prisma.sql`c.author_id = ${filters.authorId}`);
}
// 4) 키워드 필터: 값이 있을 때만 ILIKE 패턴 검색 조건 추가
if (filters.keyword) {
const pattern = `%${filters.keyword}%`; // 부분일치 패턴
conditions.push(Prisma.sql`c.content ILIKE ${pattern}`);
}
// 누적된 조건이 있으면 WHERE ... AND ... 로 조립, 없으면 WHERE 생략
const whereClause =
conditions.length > 0 ? Prisma.sql`WHERE ${Prisma.join(conditions, ' AND ')}` : Prisma.empty;
// 최종 Raw SQL 실행: whereClause가 템플릿에 그대로 삽입됨
// ${...}는 문자열 결합이 아니라 "바인딩"으로 들어가므로 안전하게 파라미터 처리됨
// JOIN을 포함한 쿼리인데도 결과를 Comment[]로 받고 싶다면,
// SELECT 결과를 "Comment 타입 필드"에 정확히 맞춰야 합니다.
const comments = await prisma.$queryRaw<Comment[]>`
SELECT
c.id,
c.created_at AS "createdAt",
c.updated_at AS "updatedAt",
c.deleted_at AS "deletedAt",
c.content,
c.post_id AS "postId",
c.author_id AS "authorId"
FROM study.comments c
JOIN study.users u ON c.author_id = u.id
${whereClause}
ORDER BY c.created_at DESC
LIMIT 20
`;
console.log(`게시글 ID=${filters.posId}의 댓글 조회 결과:`);
console.log(comments);
return comments;
}
🔷 동적 식별자(Table/Column) 처리: Prisma.raw
기본적으로 ${변수}는 값(Value)으로 취급되어 따옴표가 붙습니다.
하지만 테이블명이나 컬럼명은 DB 엔진에서 파라미터 바인딩 대상이 아니므로 일반적인 방식으로는 동적 처리가 불가능합니다.
이때 Prisma.raw()를 사용합니다.
💡 매우 중요:
보안 주의 Prisma.raw()는 입력값을 그대로 쿼리에 박아넣습니다. 따라서 반드시 사전에 정의된 허용 목록(Whitelist)과 대조하는 검증 과정이 필수입니다
// 정렬 기준 컬럼을 동적으로 변경하는 안전한 예시
async function getSortedPosts(sortBy: 'title' | 'created_at') {
// 1. 화이트리스트 검증 (Security Check)
const allowedColumns = ['title', 'created_at'];
if (!allowedColumns.includes(sortBy)) {
throw new Error('허용되지 않은 정렬 컬럼입니다.');
}
// 2. 검증된 문자열에 대해서만 Prisma.raw 적용
const sortColumn = Prisma.raw(sortBy);
return await prisma.$queryRaw`
SELECT id, title FROM posts
ORDER BY ${sortColumn} DESC
`;
}
✔️ 핵심 요약 및 권장 패턴
| 상황 | 도구 | 특징 |
| 단순한 값 전달 | ${value} (백틱 내) | 자동으로 바인딩 처리되어 SQL Injection 방지됨. 가장 안전하며 기본 권장 방식 |
| 복잡한 다중 조건 | Prisma.sql.join() | 여러 SQL 조각을 AND, OR 등으로 결합할 때 최적. 가독성과 유지보수성 우수 |
| 조건부 쿼리 생략 | Prisma.empty | 조건이 없을 경우 SQL 조각을 완전히 제거. 불필요한 AND로 인한 Syntax Error 방지 |
| 테이블/컬럼명 변경 | Prisma.raw() | 식별자 동적 생성 가능. SQL Injection 방지를 위해 화이트리스트 검증 필수 |
※ 게시된 글 및 이미지 중 일부는 AI 도구의 도움을 받아 생성되거나 다듬어졌습니다.
'4.Node.js > Prisma(ORM)' 카테고리의 다른 글
| [Prisma7] 8편. Prisma 데이터베이스 동기화(Migration & Sync)실습 : 개발 → 운영 (0) | 2026.01.22 |
|---|---|
| [Prisma7] 7편. Prisma 트랜잭션과 데이터 정합성: 실무 설계와 구현 (0) | 2026.01.21 |
| [Prisma7] 5편. Prisma 관계 조회 심화: include / select와 중첩 관계 탐색 (0) | 2026.01.19 |
| [Prisma7] 4편. 조회 쿼리 고급 옵션(where)과 관계 데이터 생성(create) 패턴 이해 (0) | 2026.01.16 |
| [Prisma7] 3편. Prisma Client Query 구조 와 CRUD API 이해하기 (0) | 2026.01.14 |