junyeokk
Blog
MikroORM·2025. 11. 27

MikroORM Raw SQL 집계 쿼리

ORM은 일상적인 CRUD 작업에서 생산성을 크게 높여주지만, 모든 쿼리를 엔티티 기반 API로 해결할 수 있는 것은 아니다. 대시보드에서 "이번 달 카테고리별 매출 합계"를 보여줘야 하거나, "상태별 주문 건수를 한 번의 쿼리로 가져와야 하는" 상황에서는 ORM의 find()나 count()만으로는 부족하다.

이런 상황에서 MikroORM은 QueryBuilder, raw() 헬퍼, Knex 직접 접근 세 가지 방법으로 SQL의 모든 표현력을 활용할 수 있게 해준다.

ORM의 한계와 Raw SQL이 필요한 순간

MikroORM의 em.find()는 엔티티 단위로 결과를 반환한다. 하지만 집계 쿼리의 결과는 엔티티가 아니다.

sql
SELECT category, COUNT(*) as count, SUM(price) as total
FROM product
GROUP BY category

이 쿼리의 결과는 Product 엔티티가 아니라 { category, count, total } 형태의 집계 데이터다. ORM의 엔티티 매핑 레이어를 거칠 필요도 없고, 거치면 오히려 방해가 된다. 이럴 때 Raw SQL이나 QueryBuilder의 저수준 API가 필요하다.

방법 1: QueryBuilder로 집계 쿼리 작성

MikroORM의 QueryBuilder는 SQL에 가까운 체이닝 API를 제공한다. em.createQueryBuilder() 또는 축약형 em.qb()로 생성한다.

기본 count

typescript
const qb = em.createQueryBuilder(Order, 'o');
qb.count().where({ status: OrderStatus.COMPLETED });

const result = await qb.execute('get');
const count = result ? +result.count : 0;

qb.count()는 select count(primary_key)를 생성한다. execute('get')은 단일 객체를 반환하므로 .count 프로퍼티로 접근하면 된다. +를 붙이는 이유는 일부 드라이버가 count를 문자열로 반환하기 때문이다.

QB를 직접 await하면 자동으로 적절한 형태로 실행된다.

typescript
// CountQueryBuilder는 await 시 number를 반환
const count = await em.qb(Order).count().where({ status: OrderStatus.COMPLETED });
// count는 number 타입

GROUP BY와 집계 함수

typescript
const qb = em.createQueryBuilder(Order, 'o');
qb.select([
    'o.status',
    raw('count(*) as count'),
    raw('sum(o.total_amount) as total'),
  ])
  .groupBy('o.status');

const results = await qb.execute('all');
// [{ status: 'completed', count: '42', total: '128500' }, ...]

select()에 raw()를 섞어 쓸 수 있다. raw()는 MikroORM v6부터 SQL 프래그먼트를 안전하게 주입하는 공식 방법이다. execute('all')은 결과를 객체 배열로 반환한다.

HAVING 절

GROUP BY 후 집계 결과에 조건을 걸려면 having()을 사용한다.

typescript
const qb = em.createQueryBuilder(Order, 'o');
qb.select([
    'o.customerId',
    raw('count(*) as order_count'),
    raw('sum(o.total_amount) as total_spent'),
  ])
  .groupBy('o.customerId')
  .having(raw('count(*) >= ?', [5]));

const vipCustomers = await qb.execute('all');

raw()의 두 번째 인자로 파라미터를 전달하면 SQL injection을 방지할 수 있다.

방법 2: raw() 헬퍼와 sql 태그 함수

raw() 기본 사용법

raw()는 SQL 프래그먼트를 RawQueryFragment 인스턴스로 만든다. 이 인스턴스는 문자열로 직렬화 가능하므로 객체의 키와 값 양쪽에 사용할 수 있다.

typescript
import { raw } from '@mikro-orm/postgresql'; // 드라이버 패키지에서 임포트

// 값으로 사용
await em.find(User, { createdAt: raw('now()') });

// 키로 사용 (computed column 비교)
await em.find(User, { [raw('lower(name)')]: name.toLowerCase() });

// 연산자와 함께
await em.find(User, { [raw('lower(email)')]: { $like: '%@example.com' } });

키로 사용할 때 JavaScript의 computed property name 문법([...])을 쓴다. raw()가 반환하는 객체가 문자열로 직렬화되면서 MikroORM이 이를 SQL 프래그먼트로 인식한다.

콜백 시그니처

현재 엔티티의 별칭(alias)을 동적으로 참조해야 할 때 콜백을 사용한다.

typescript
await em.find(User, {
  [raw(alias => `lower(${alias}.name)`)]: name.toLowerCase(),
});
// select ... where lower(`e0`.name) = ?

QueryBuilder와 달리 em.find()에서는 별칭을 직접 지정할 수 없으므로, 콜백으로 MikroORM이 부여한 별칭을 받아 사용하는 것이다.

sql 태그 함수

sql 태그 템플릿은 raw()의 대안 문법이다. 템플릿 리터럴 안에서 보간(interpolation)된 값은 자동으로 파라미터화된다.

typescript
// raw()와 동일하지만 더 읽기 쉬움
await em.find(User, { time: sql`now()` });

// 파라미터 바인딩이 자동으로 처리됨
await em.find(User, { [sql`(select ${1} = ${1})`]: [] });

sql.ref()로 다른 컬럼을 참조하거나, sql.lower(), sql.upper()로 대소문자 변환을 할 수도 있다.

typescript
// 컬럼 간 비교
await em.find(User, { foo: sql.ref('bar') });
// where foo = bar

// 대소문자 무시 검색
await em.find(Book, { [sql.upper('title')]: 'HELLO WORLD' });

방법 3: Knex 직접 접근

MikroORM은 내부적으로 Knex를 쿼리 빌더로 사용한다. em.getKnex()로 Knex 인스턴스에 직접 접근할 수 있으며, 이를 통해 MikroORM의 추상화 없이 완전히 자유로운 쿼리를 작성할 수 있다.

typescript
const knex = em.getKnex();

const results = await knex('order')
  .select('status')
  .count('* as count')
  .sum('total_amount as total')
  .groupBy('status');

Knex 쿼리의 결과는 plain object이므로 엔티티 매핑이 필요하면 em.map()을 사용한다.

typescript
const rows = await knex.select('*').from('user').where('id', '>', 10);
const users = rows.map(row => em.map(User, row));

em.map()은 DB 컬럼명(created_at)을 엔티티 프로퍼티명(createdAt)으로 변환하고, 결과를 Identity Map에 등록하여 managed 상태로 만든다.

Knex raw()과 MikroORM raw()의 차이

typescript
// MikroORM raw() — em.find()와 QueryBuilder에서 사용
import { raw } from '@mikro-orm/postgresql';
await em.find(User, { time: raw('now()') });

// Knex raw() — Knex 인스턴스에서 사용
const knex = em.getKnex();
await knex.select(knex.raw('count(*) as count')).from('user');

두 raw()는 별개의 함수다. MikroORM의 raw()는 RawQueryFragment를 생성하고, Knex의 raw()는 Knex.Raw를 생성한다. 혼용하면 예상치 못한 에러가 발생하므로 각각의 컨텍스트에 맞는 것을 사용해야 한다.

CASE WHEN으로 조건부 집계

집계 쿼리에서 가장 자주 쓰는 SQL 패턴이 CASE WHEN이다. 하나의 쿼리로 여러 조건의 집계를 동시에 수행할 수 있다.

상태별 건수를 한 행으로

typescript
const qb = em.createQueryBuilder(Order, 'o');
qb.select([
  raw(`count(case when o.status = 'pending' then 1 end) as pending_count`),
  raw(`count(case when o.status = 'completed' then 1 end) as completed_count`),
  raw(`count(case when o.status = 'cancelled' then 1 end) as cancelled_count`),
  raw('count(*) as total_count'),
]);

const result = await qb.execute('get');
// { pending_count: '12', completed_count: '85', cancelled_count: '3', total_count: '100' }

GROUP BY 없이 CASE WHEN을 사용하면 전체 테이블에서 조건별 집계를 한 행으로 가져올 수 있다. 대시보드 상단의 요약 카드 같은 UI에 딱 맞는 패턴이다.

GROUP BY와 결합하면 더 세밀한 분석이 가능하다.

typescript
const qb = em.createQueryBuilder(Order, 'o');
qb.select([
    raw('date_trunc(\'month\', o.created_at) as month'),
    raw(`sum(case when o.status = 'completed' then o.total_amount else 0 end) as revenue`),
    raw(`sum(case when o.status = 'cancelled' then o.total_amount else 0 end) as lost`),
    raw('count(*) as order_count'),
  ])
  .groupBy(raw('date_trunc(\'month\', o.created_at)'))
  .orderBy({ [raw('month')]: 'ASC' });

const monthlyStats = await qb.execute('all');
// [{ month: '2025-01-01T00:00:00Z', revenue: '5200000', lost: '320000', order_count: '156' }, ...]

SUM + CASE vs COUNT + CASE

COUNT(CASE WHEN ... THEN 1 END)와 SUM(CASE WHEN ... THEN 1 ELSE 0 END)는 결과가 같지만 미묘한 차이가 있다.

sql
-- COUNT: NULL을 세지 않으므로 ELSE 생략 가능
COUNT(CASE WHEN status = 'active' THEN 1 END)

-- SUM: ELSE 0을 생략하면 NULL 행이 포함될 때 결과가 NULL이 됨
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END)

-- 금액 합산에는 SUM이 자연스러움
SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END)

건수를 셀 때는 COUNT + CASE가 깔끔하고, 금액이나 수량을 합산할 때는 SUM + CASE가 직관적이다.

동적 필터와 파라미터 바인딩

실제 서비스에서는 필터 조건이 고정되어 있지 않다. 사용자가 날짜 범위, 카테고리, 상태 등을 선택하면 WHERE 절을 동적으로 구성해야 한다.

QueryBuilder에서 동적 조건

typescript
interface StatsFilter {
  startDate?: Date;
  endDate?: Date;
  categoryId?: number;
  status?: OrderStatus;
}

async function getOrderStats(em: EntityManager, filter: StatsFilter) {
  const qb = em.createQueryBuilder(Order, 'o');
  qb.select([
    'o.status',
    raw('count(*) as count'),
    raw('sum(o.total_amount) as total'),
  ]);

  // 동적으로 조건 추가
  if (filter.startDate) {
    qb.andWhere({ createdAt: { $gte: filter.startDate } });
  }
  if (filter.endDate) {
    qb.andWhere({ createdAt: { $lte: filter.endDate } });
  }
  if (filter.categoryId) {
    qb.andWhere({ category: filter.categoryId });
  }
  if (filter.status) {
    qb.andWhere({ status: filter.status });
  }

  qb.groupBy('o.status');

  return qb.execute('all');
}

andWhere()를 조건부로 체이닝하면 된다. MikroORM의 스마트 쿼리 조건($gte, $lte, $in 등)을 그대로 사용할 수 있어서, raw()로 전체 WHERE를 작성하는 것보다 안전하고 읽기 쉽다.

Knex에서 동적 조건

Knex는 .modify() 패턴으로 동적 조건을 깔끔하게 처리한다.

typescript
const knex = em.getKnex();

const result = await knex('order as o')
  .select('o.status')
  .count('* as count')
  .sum('o.total_amount as total')
  .modify((qb) => {
    if (filter.startDate) qb.where('o.created_at', '>=', filter.startDate);
    if (filter.endDate) qb.where('o.created_at', '<=', filter.endDate);
    if (filter.categoryId) qb.where('o.category_id', filter.categoryId);
  })
  .groupBy('o.status');

SQL Injection 방지

raw()나 Knex raw()를 사용할 때 문자열 보간으로 값을 넣으면 SQL injection에 노출된다.

typescript
// ❌ 위험: 사용자 입력이 SQL에 직접 삽입됨
raw(`count(case when o.status = '${userInput}' then 1 end)`)

// ✅ 안전: 파라미터 바인딩
raw('count(case when o.status = ? then 1 end)', [userInput])

// ✅ sql 태그 함수도 자동 파라미터화
sql`count(case when o.status = ${userInput} then 1 end)`

? 플레이스홀더와 파라미터 배열을 사용하거나, sql 태그 함수의 자동 파라미터화를 활용하면 안전하다.

execute()의 세 가지 모드

QueryBuilder의 execute() 메서드는 첫 번째 인자로 결과 형태를 제어한다.

typescript
const qb = em.qb(Order).select('*').where({ status: 'completed' });

// 'all' (기본값): 객체 배열 반환
const rows = await qb.execute('all');
// [{ id: 1, status: 'completed', ... }, { id: 2, ... }]

// 'get': 단일 객체 반환 (첫 번째 행)
const row = await qb.execute('get');
// { id: 1, status: 'completed', ... }

// 'run': 실행 결과 메타 반환 (INSERT/UPDATE/DELETE용)
const meta = await qb.execute('run');
// { affectedRows: 1, insertId: 42, row: {...} }

집계 쿼리에서는 보통 'all'(GROUP BY 결과 목록)이나 'get'(단일 집계 결과)을 사용한다.

두 번째 인자 mapResults는 DB 컬럼명을 엔티티 프로퍼티명으로 매핑할지 결정한다. 기본값이 true이므로 created_at이 createdAt으로 변환된다. 집계 쿼리의 커스텀 alias(as count, as total)는 매핑 대상이 아니므로 그대로 유지된다.

typescript
// 매핑 활성화 (기본값): created_at → createdAt
const mapped = await qb.execute('get', true);

// 매핑 비활성화: DB 컬럼명 그대로
const raw = await qb.execute('get', false);

persist: false 필드와 집계 결과 매핑

엔티티에 persist: false로 정의한 가상 필드에 집계 결과를 매핑할 수 있다. 이 패턴은 엔티티와 집계 데이터를 하나의 객체로 관리할 때 유용하다.

typescript
@Entity()
export class Author {
  @PrimaryKey()
  id!: number;

  @Property()
  name!: string;

  @Property({ persist: false })
  booksCount?: number;

  @Property({ persist: false })
  totalRevenue?: number;
}
typescript
const knex = em.getKnex();
const qb1 = em.createQueryBuilder(Book, 'b')
  .count('b.id', true)
  .where({ author: knex.ref('a.id') })
  .getKnexQuery();

const qb2 = em.createQueryBuilder(Author, 'a');
qb2.select(['*'])
  .withSubQuery(qb1, 'a.booksCount')
  .where({ 'a.booksCount': { $gt: 0 } });

const authors = await qb2.getResultList();
// authors[0].booksCount → 5 (persist: false 필드에 매핑됨)

withSubQuery()로 서브쿼리를 persist: false 필드에 연결하면, 결과가 엔티티 인스턴스로 매핑될 때 해당 필드에 값이 채워진다. 이렇게 하면 별도의 DTO를 만들지 않고도 엔티티에 집계 데이터를 붙일 수 있다.

실전 패턴: NestJS 서비스에서의 활용

대시보드 통계 서비스

typescript
@Injectable()
export class DashboardService {
  constructor(private readonly em: EntityManager) {}

  async getOverview(startDate: Date, endDate: Date) {
    const qb = this.em.createQueryBuilder(Order, 'o');
    qb.select([
      raw(`count(*) as total_orders`),
      raw(`count(case when o.status = 'completed' then 1 end) as completed`),
      raw(`count(case when o.status = 'pending' then 1 end) as pending`),
      raw(`count(case when o.status = 'cancelled' then 1 end) as cancelled`),
      raw('sum(case when o.status = \'completed\' then o.total_amount else 0 end) as revenue'),
      raw('avg(case when o.status = \'completed\' then o.total_amount end) as avg_order_value'),
    ]).where({
      createdAt: { $gte: startDate, $lte: endDate },
    });

    const result = await qb.execute('get');

    return {
      totalOrders: +result.total_orders,
      completed: +result.completed,
      pending: +result.pending,
      cancelled: +result.cancelled,
      revenue: +result.revenue,
      avgOrderValue: +(+result.avg_order_value).toFixed(2),
    };
  }

  async getCategorySales(startDate: Date, endDate: Date) {
    const knex = this.em.getKnex();

    return knex('order_item as oi')
      .join('product as p', 'oi.product_id', 'p.id')
      .join('category as c', 'p.category_id', 'c.id')
      .select('c.name as category')
      .sum('oi.quantity as total_quantity')
      .sum(knex.raw('oi.price * oi.quantity as total_amount'))
      .whereBetween('oi.created_at', [startDate, endDate])
      .groupBy('c.id', 'c.name')
      .orderBy('total_amount', 'desc');
  }
}

집계 결과는 항상 문자열로 올 수 있으므로 + 단항 연산자나 Number()로 변환하는 것이 안전하다. 특히 PostgreSQL의 count()는 bigint 타입을 반환하기 때문에 JavaScript의 Number 범위를 초과할 수 있다는 점도 인지해야 한다(일반적인 서비스에서는 문제 되지 않지만).

em.execute()로 완전한 Raw SQL

QueryBuilder조차 제약이 있는 복잡한 쿼리는 em.execute()로 순수 SQL을 실행할 수 있다.

typescript
const result = await this.em.execute(`
  WITH monthly AS (
    SELECT
      date_trunc('month', created_at) AS month,
      status,
      count(*) AS cnt,
      sum(total_amount) AS amount
    FROM "order"
    WHERE created_at >= ?
    GROUP BY 1, 2
  )
  SELECT
    month,
    max(case when status = 'completed' then cnt end) as completed,
    max(case when status = 'cancelled' then cnt end) as cancelled,
    max(case when status = 'completed' then amount end) as revenue
  FROM monthly
  GROUP BY month
  ORDER BY month
`, [startDate]);

CTE(Common Table Expression)나 윈도우 함수처럼 QueryBuilder가 직접 지원하지 않는 SQL 기능을 사용할 때 em.execute()가 유일한 선택지다.

QueryBuilder vs Knex vs em.execute() 선택 기준

기준QueryBuilderKnexem.execute()
엔티티 매핑✅ getResultList()❌ (em.map 필요)❌ (em.map 필요)
자동 조인✅ 관계 기반❌ 수동❌ 수동
SQL 자유도중간높음최대
타입 안전성높음중간낮음
CTE/윈도우 함수❌제한적✅

결론: 엔티티를 반환해야 하면 QueryBuilder, 유연한 집계가 필요하면 Knex, CTE나 복잡한 분석 쿼리는 em.execute(). 실무에서는 세 가지를 상황에 맞게 섞어 쓰는 것이 자연스럽다.

주의사항

Identity Map과 Raw 쿼리

em.execute()나 Knex로 직접 실행한 쿼리의 결과는 Identity Map에 등록되지 않는다. 같은 엔티티를 이후에 em.find()로 가져오면 별개의 인스턴스가 된다. 결과를 managed 엔티티로 만들어야 한다면 em.map()을 사용해야 한다.

트랜잭션 안에서의 Raw 쿼리

em.transactional() 안에서 Knex나 em.execute()를 사용할 때는 같은 트랜잭션 컨텍스트를 공유해야 한다. em.getKnex()로 가져온 Knex 인스턴스가 아닌, 트랜잭션의 EntityManager에서 가져온 Knex를 사용해야 한다.

typescript
await em.transactional(async (em) => {
  // ✅ 트랜잭션 안의 em에서 knex를 가져옴
  const knex = em.getKnex();
  await knex('order').where('id', orderId).update({ status: 'completed' });

  // em.flush()와 같은 트랜잭션에서 실행됨
  const order = await em.findOneOrFail(Order, orderId);
  order.completedAt = new Date();
  await em.flush();
});

성능 고려사항

집계 쿼리는 대량의 데이터를 스캔하므로 인덱스 설계가 중요하다. EXPLAIN ANALYZE로 실행 계획을 확인하고, 필요하면 복합 인덱스나 partial index를 추가하자. 특히 GROUP BY에 사용되는 컬럼과 WHERE에 사용되는 컬럼을 함께 묶은 복합 인덱스가 효과적이다.

정리

  • QueryBuilder는 엔티티 매핑이 필요한 집계에, Knex는 자유로운 JOIN/서브쿼리에, em.execute()는 CTE·윈도우 함수 등 SQL 전체 표현력이 필요할 때 사용한다.
  • raw()와 sql 태그 함수는 반드시 파라미터 바인딩(? 또는 템플릿 보간)을 사용하고, 문자열 보간으로 사용자 입력을 넣지 않는다.
  • 집계 결과는 Identity Map에 등록되지 않으므로, managed 엔티티가 필요하면 em.map()을 거치고, 트랜잭션 안에서는 해당 em에서 Knex를 가져와야 컨텍스트가 공유된다.

관련 문서