Raw SQL & CTE

Raw SQL이 필요한 이유

ORM의 find(), save(), SelectQueryBuilder로 대부분의 쿼리를 처리할 수 있어요. 하지만 SQL은 풍부한 언어이고, 엔티티 기반 API로는 표현할 수 없는 것들이 있어요:

관계가 없는 서로 다른 테이블의 결과를 합치기 (UNION)
조직도나 카테고리 트리 같은 계층 데이터 탐색 (recursive CTE)
행 간 순위 계산이나 누적 합계 (window functions)
여러 엔티티를 복잡하게 아우르는 크로스 데이터베이스 분석

이런 경우에 RawQueryBuilder를 사용하면 SQL의 모든 기능을 쓸 수 있으면서도 파라미터 바인딩(SQL injection 방지)과 조합 가능한 빌더 API(문자열 연결 대신 프로그래밍적으로 쿼리 구성)를 제공받을 수 있어요.

트레이드오프는 명확해요. 타입 안전한 keyof T 자동완성은 잃지만, SQL의 모든 표현력을 얻게 돼요. 테이블명과 컬럼명은 수동으로 따옴표 처리해야 해요 (PostgreSQL은 "double quotes", MySQL은 `backticks`). 다만 em.createQueryBuilder()로 빌더를 생성하면 ORM이 DB 타입을 자동 감지해요.

시작하기

em.createQueryBuilder() (인자 없이)로 RawQueryBuilder를 생성하고, 메서드를 체이닝한 후 build()를 호출해서 SQL을 얻어요. 그다음 em.query()로 실행하면 돼요.

typescript

import sql from "sql-template-tag";

const qb = em.createQueryBuilder();

const query = qb
  .select(['"id"', '"name"', '"email"'])
  .from('"users"')
  .where([sql`"is_active" = ${true}`])
  .orderBy([{ column: '"created_at"', direction: "DESC" }])
  .limit(10)
  .build();

const users = await em.query(query);

RawQueryBuilder의 where() 메서드는 조건의 배열을 받아서 AND로 결합해요. 값은 항상 sql-template-tag를 통해 파라미터 바인딩되며, 절대 SQL 문자열에 직접 연결되지 않아요.

타입 참조 (Typed References)

'"users"."id"' 같은 따옴표 문자열을 직접 손으로 적는 건 금방 지저분해지고, 컬럼 이름을 한 번 바꾸면 문자열이 조용히 깨져요. 두 헬퍼는 엔티티 클래스(또는 alias 이름)로부터 sql 태그와 호환되는 프록시를 반환해서, dialect에 맞는 SQL 조각을 자동으로 만들어 줘요. 같은 코드가 PostgreSQL, MySQL, SQLite에서 그대로 동작해요.

`em.ref(Entity, alias?)` -- 엔티티 바인딩

typescript

import sql from "sql-template-tag";

const U = em.ref(User, "u");

const query = em.createQueryBuilder()
  .selectFragments([U.id, U.email])
  .from(U)
  .where([sql`${U.isActive} = ${true}`])
  .build();
// PostgreSQL: SELECT u."id", u."email" FROM "user" AS u WHERE u."is_active" = ?
// MySQL:      SELECT u.`id`, u.`email` FROM `user` AS u WHERE u.`is_active` = ?

보간	alias `"u"` 있음	alias 없음
`${ref}`	`"user" AS u` (FROM/JOIN에 바로 사용)	`"user"`
`${ref.id}`	`u."id"`	`"id"`
`${ref.as("createdAt")}`	`u."created_at" AS "createdAt"`	`"created_at" AS "createdAt"`

프로퍼티 이름은 먼저 @Column 메타데이터로 해석돼요 (이름을 바꿔도 SQL이 따라가요). 그다음 관계의 FK backing 프로퍼티 (parent!: Issue 관계의 parentId는 @Column 없이도 FK 컬럼으로 해석돼요), 마지막으로 camelToSnakeCase fallback이 적용돼요. 멀티테넌트 테이블 wrapping도 자동으로 적용돼요.

서로 다른 alias를 가진 ref들은 self-join에 그대로 조합할 수 있어요:

typescript

const E = em.ref(Employee, "e");
const M = em.ref(Employee, "m");

sql`
  SELECT ${E.name}, ${M.as("name", "managerName")}
  FROM ${E}
  INNER JOIN ${M} ON ${E.managerId} = ${M.id}
`;

`em.aliasRef(name)` -- alias 전용

엔티티에 묶을 수 없는 CTE, derived table, 그 외의 구문에는 이걸 써요:

typescript

const t = em.aliasRef("t");
sql`SELECT ${t.minDepth} FROM cte ${t}`;
// PostgreSQL: SELECT t."min_depth" FROM cte t
// MySQL:      SELECT t.`min_depth` FROM cte t

${ref} -> 따옴표 없는 bare alias 이름 (t) -- INNER JOIN cte t에 그대로 들어가요
${ref.col} -> alias."col", camelToSnakeCase 적용 + dialect 따옴표

엔티티 테이블에는 em.ref()를, CTE 본문 안에서만 존재하는 컬럼 (depth, path 등)이나 엔티티가 뒷받침하지 않는 CTE / derived table에 조인할 때는 em.aliasRef()를 써요.

WHERE, JOIN, Subquery

RawQueryBuilder는 SelectQueryBuilder와 동일한 WHERE 헬퍼를 제공해요 -- andWhere(), orWhere(), whereIn(), whereNull(), whereBetween() 등. 차이점은 컬럼명이 타입 안전하지 않은 raw 문자열이라는 거예요.

JOIN도 같은 방식으로 동작해요:

typescript

import sql, { raw } from "sql-template-tag";

const query = em.createQueryBuilder()
  .select(['"p".*', '"u"."name" AS "authorName"'])
  .from('"posts"', "p")
  .leftJoin('"users"', "u", sql`${raw('"p"')}."author_id" = ${raw('"u"')}."id"`)
  .where([sql`${raw('"p"')}."is_published" = ${true}`])
  .limit(20)
  .build();

const posts = await em.query(query);

서브쿼리의 경우, 빌더가 asInQuery(), asExists(), as()를 제공해서 하나의 쿼리를 다른 쿼리 안에 포함시킬 수 있어요.

typescript

// IN subquery -- find users who have at least one order
const orderUsers = em.createQueryBuilder()
  .select(['"user_id"'])
  .from('"orders"')
  .where([sql`"status" = ${"completed"}`])
  .asInQuery();

const query = em.createQueryBuilder()
  .select(["*"])
  .from('"users"')
  .where([])
  .appendSql(sql`AND "id" IN ${orderUsers}`)
  .build();

Set Operations -- UNION, INTERSECT, EXCEPT

완전히 다른 쿼리의 결과를 합쳐야 할 때가 있어요. 예를 들어 직원과 계약직을 하나의 목록으로 합치는 경우예요.

typescript

const query = em.createQueryBuilder()
  .select(['"id"', '"name"', '"email"'])
  .from('"employees"')
  .where([sql`"department" = ${"engineering"}`])
  .union()
  .select(['"id"', '"name"', '"email"'])
  .from('"contractors"')
  .where([sql`"department" = ${"engineering"}`])
  .build();

const allEngineers = await em.query(query);

union()을 호출하면 UNION의 두 번째 부분이 될 새로운 SELECT가 시작돼요. 결과에는 양쪽 쿼리의 행이 포함되고 중복은 제거돼요.

네 가지 set operation을 사용할 수 있어요:

Method	SQL	설명
`union()`	`UNION`	두 결과 집합을 합치고 중복 제거
`unionAll()`	`UNION ALL`	두 결과 집합을 합치고 중복 유지 (더 빠름)
`intersect()`	`INTERSECT`	양쪽 결과 집합에 모두 있는 행만 반환
`except()`	`EXCEPT`	첫 번째 집합에만 있고 두 번째에는 없는 행 반환

실용 예제: users 테이블에는 있지만 unsubscribed 테이블에는 없는 이메일 주소 찾기.

typescript

const query = em.createQueryBuilder()
  .select(['"email"']).from('"users"')
  .except()
  .select(['"email"']).from('"unsubscribed"')
  .build();

const subscribedEmails = await em.query(query);

Common Table Expressions (CTE)

CTE가 필요한 이유

CTE는 서브쿼리에 이름을 붙여 변수처럼 저장하는 거라고 생각하면 돼요. 일반 프로그래밍에서 50줄짜리 표현식을 인라인으로 쓰지 않고 이름 있는 변수로 분리하는 것과 같은 원리예요. CTE가 SQL에서 똑같은 역할을 해요.

CTE 없이는 서브쿼리 안에 서브쿼리를 중첩하게 되면서 금방 읽기 어려워져요. CTE를 쓰면 각 단계를 이름 있는 블록으로 정의한 다음 조합할 수 있어요. DB가 순서대로 실행하고, 각 단계에서 이전 단계를 참조할 수 있어요.

이런 느낌이에요:

// Pseudocode
const activeUsers = SELECT id, name FROM users WHERE is_active = true;
const result = SELECT * FROM activeUsers;

SQL로 쓰면 이렇게 돼요:

sql

WITH active_users AS (
  SELECT "id", "name" FROM "users" WHERE "is_active" = true
)
SELECT * FROM active_users

사용법

typescript

const query = em.createQueryBuilder()
  .with("active_users", (sub) =>
    sub.select(['"id"', '"name"']).from('"users"').where([sql`"is_active" = ${true}`])
  )
  .select(["*"])
  .from(sql`active_users`)
  .build();

const users = await em.query(query);

with() 메서드는 이름과 Sql 객체 또는 새 RawQueryBuilder를 받는 콜백을 인자로 받아요. CTE 결과는 메인 쿼리의 FROM 절에서 일반 테이블처럼 참조할 수 있어요.

Recursive CTE

조직도, 카테고리 트리, 스레드 댓글처럼 본질적으로 계층적인 데이터가 있어요. Recursive CTE를 사용하면 레벨마다 쿼리를 보내는 대신 하나의 쿼리로 계층 구조를 탐색할 수 있어요.

Employee 엔티티에서 각 직원이 매니저를 가리키는 managerId를 가진다고 가정해 볼게요. CEO부터 시작하는 전체 조직 트리를 가져오려면, 엔티티 컬럼은 em.ref()로, CTE 안에서만 존재하는 합성 depth 컬럼은 em.aliasRef()로 CTE 본문을 조립해요:

typescript

import sql from "sql-template-tag";

const E = em.ref(Employee);            // base case -- alias 불필요
const Ec = em.ref(Employee, "e");      // recursive step -- self-join용 alias
const ot = em.aliasRef("ot");          // CTE alias; `depth`는 CTE 안에만 존재

const query = em.createQueryBuilder()
  .withRecursive("org_tree", sql`
    SELECT ${E.id}, ${E.name}, ${E.managerId}, 1 AS depth
    FROM ${E}
    WHERE ${E.managerId} IS NULL

    UNION ALL

    SELECT ${Ec.id}, ${Ec.name}, ${Ec.managerId}, ${ot.depth} + 1
    FROM ${Ec}
    INNER JOIN org_tree ${ot} ON ${Ec.managerId} = ${ot.id}
  `)
  .select("*")
  .from("org_tree")
  .orderBy([{ column: "depth", direction: "ASC" }])
  .build();

const orgChart = await em.query(query);
// [{ id: 1, name: "CEO", depth: 1 }, { id: 2, name: "VP Eng", depth: 2 }, ...]

dialect 따옴표는 자동으로 골라져요. PostgreSQL은 "id", MySQL은 `id`로 나가고, ${ot}는 따옴표 없는 bare alias ot로 렌더링돼서 INNER JOIN org_tree ot가 모든 지원 DB에서 파싱돼요. TypeScript에서 Employee.managerId를 리네임하면 SQL에도 그대로 반영돼요.

Recursive CTE는 UNION ALL로 결합된 두 부분으로 구성돼요:

Base case -- 시작 행 (매니저가 없는 직원 = CEO)
Recursive step -- CTE 결과를 원래 테이블에 다시 조인해서 하위 항목을 찾기

DB가 새로운 행이 더 이상 생성되지 않을 때까지 recursive step을 반복 실행해요. 각 반복마다 트리에서 한 단계 더 깊이 들어가요.

Window Functions

Window Functions가 필요한 이유

부서별로 직원의 급여 순위를 매기고 싶다고 가정해 볼게요. GROUP BY를 쓰면 한 부서의 모든 직원이 하나의 행으로 합쳐지면서 개별 직원 데이터를 잃게 돼요. Window function은 모든 행을 유지하면서 계산된 값을 옆에 추가할 수 있어요.

이렇게 생각하면 돼요: GROUP BY는 블렌더(모든 게 그룹당 하나의 결과로 합쳐짐)예요. Window function은 각 행 옆에 앉아서 "창문"을 통해 관련 행을 보고, 아무것도 변경하지 않고 답을 적어두는 계산기예요.

사용법

전형적인 예제는 순위 매기기예요. 부서별로 급여 순위를 매기는 경우를 볼게요.

typescript

const query = em.createQueryBuilder()
  .selectWithWindow([
    '"name"',
    '"department"',
    '"salary"',
    {
      expr: "ROW_NUMBER()",
      over: { partitionBy: '"department"', orderBy: '"salary" DESC' },
      alias: "rank",
    },
  ])
  .from('"employees"')
  .build();

const ranked = await em.query(query);
// [
//   { name: "Alice", department: "eng", salary: 150000, rank: 1 },
//   { name: "Bob",   department: "eng", salary: 130000, rank: 2 },
//   { name: "Carol", department: "sales", salary: 140000, rank: 1 },
//   ...
// ]

selectWithWindow() 배열의 각 요소는 plain 컬럼 문자열이거나 window function을 설명하는 객체예요:

expr -- 적용할 함수 (ROW_NUMBER(), RANK(), SUM(salary) 등)
over.partitionBy -- 그룹을 나눌 컬럼 (GROUP BY와 비슷하지만 행을 합치지 않아요)
over.orderBy -- 각 그룹 내 정렬 순서
alias -- 계산된 컬럼의 이름

자주 사용되는 window function 목록이에요:

Function	설명	예시
`ROW_NUMBER()`	파티션 내 순차 번호	그룹 내 페이지네이션
`RANK()`	동점 시 건너뛰는 순위 (1, 2, 2, 4)	리더보드
`DENSE_RANK()`	동점 시 건너뛰지 않는 순위 (1, 2, 2, 3)	카테고리별 Top-N
`SUM(col)`	파티션 내 누적 합계	누적 매출
`AVG(col)`	이동 평균	Moving average
`LAG(col)`	이전 행의 값	전일 대비 비교
`LEAD(col)`	다음 행의 값	예측

실용 예제 -- 월별 누적 매출 계산:

typescript

const query = em.createQueryBuilder()
  .selectWithWindow([
    '"month"',
    '"revenue"',
    {
      expr: "SUM(revenue)",
      over: { orderBy: '"month" ASC' },
      alias: "cumulative_revenue",
    },
  ])
  .from('"monthly_sales"')
  .build();

partitionBy를 생략하면 window가 전체 결과 집합에 걸쳐요. 누적 합계가 그룹별이 아니라 모든 행에 대해 누적돼요.

DISTINCT -- 중복 제거

RawQueryBuilder는 DISTINCT 변형도 지원해요.

typescript

// SELECT DISTINCT
qb.selectDistinct(['"city"', '"country"']).from('"users"');

// DISTINCT ON (PostgreSQL only) -- keep only the first row per group
qb.selectDistinctOn(['"department"'], ['"id"', '"name"', '"salary"'])
  .from('"employees"');
// SELECT DISTINCT ON ("department") "id", "name", "salary" FROM "employees"

DISTINCT ON은 PostgreSQL 전용 확장으로 지정된 컬럼의 고유 값마다 하나의 행만 반환해요. GROUP BY와 비슷하지만 어떤 행을 유지할지 선택할 수 있어요 (ORDER BY로 결정).

두 빌더 중 선택하기

질문	SelectQueryBuilder	RawQueryBuilder
등록된 엔티티를 쿼리하나요?	Yes	필수 아님
`keyof T` 자동완성이 필요한가요?	Yes	No
UNION / INTERSECT / EXCEPT가 필요한가요?	No	Yes
CTE (WITH / WITH RECURSIVE)가 필요한가요?	No	Yes
Window function이 필요한가요?	No	Yes
클래스 인스턴스를 반환하나요?	`getMany()` yes / `getPartialMany()` no	No -- `em.query()`로 raw 객체 반환

실전에서는 일상적인 쿼리에 SelectQueryBuilder부터 시작하세요. 한계에 부딪히면 -- UNION, 재귀 계층 탐색, window 분석이 필요하면 -- 해당 쿼리에만 RawQueryBuilder로 전환하면 돼요.

Next Steps

Query Builder -- Type-safe SelectQueryBuilder with keyof T auto-complete
EntityManager -- Basic CRUD with find(), save(), etc.
API Reference -- Quick reference for all method signatures

Raw SQL & CTE ​

Raw SQL이 필요한 이유 ​

시작하기 ​

타입 참조 (Typed References) ​

em.ref(Entity, alias?) -- 엔티티 바인딩 ​

em.aliasRef(name) -- alias 전용 ​

WHERE, JOIN, Subquery ​

Set Operations -- UNION, INTERSECT, EXCEPT ​

Common Table Expressions (CTE) ​

CTE가 필요한 이유 ​

사용법 ​

Recursive CTE ​

Window Functions ​

Window Functions가 필요한 이유 ​

사용법 ​

DISTINCT -- 중복 제거 ​

두 빌더 중 선택하기 ​

Next Steps ​

Raw SQL & CTE

Raw SQL이 필요한 이유

시작하기

타입 참조 (Typed References)

`em.ref(Entity, alias?)` -- 엔티티 바인딩

`em.aliasRef(name)` -- alias 전용

WHERE, JOIN, Subquery

Set Operations -- UNION, INTERSECT, EXCEPT

Common Table Expressions (CTE)

CTE가 필요한 이유

사용법

Recursive CTE

Window Functions

Window Functions가 필요한 이유

사용법

DISTINCT -- 중복 제거

두 빌더 중 선택하기

Next Steps