Raw Queries - 🌲 Sonamu

Puri는 타입 안전한 쿼리 빌더이지만, 복잡한 SQL 표현식이 필요할 때는 Raw SQL을 사용할 수 있습니다.

Raw 함수 개요

Raw 타입 함수

타입별 Raw 함수 rawString, rawNumber

WHERE Raw

복잡한 조건문 whereRaw

CASE WHEN

조건부 값 선택 CASE 표현식

서브쿼리

중첩 쿼리 작성 Subquery

Raw 타입 함수

rawString - 문자열 반환

const results = await db.table("users").select({
  id: "id",
  fullName: Puri.rawString("CONCAT(first_name, ' ', last_name)"),
  upperName: Puri.upper("username"),
  lowerEmail: Puri.lower("email"),
});

// 타입: { id: number; fullName: string; upperName: string; lowerEmail: string; }[]

rawNumber - 숫자 반환

const results = await db.table("employees").select({
  id: "id",
  salary: "salary",
  yearsSince: Puri.rawNumber("EXTRACT(YEAR FROM AGE(NOW(), hire_date))"),
  roundedSalary: Puri.rawNumber("ROUND(salary, -3)"),
});

// 타입: { id: number; salary: string; yearsSince: number; roundedSalary: number; }[]

rawBoolean - 불린 반환

const results = await db.table("users").select({
  id: "id",
  isActive: "is_active",
  isAdmin: Puri.rawBoolean("role = 'admin'"),
  hasEmail: Puri.rawBoolean("email IS NOT NULL"),
});

// 타입: { id: number; isActive: boolean; isAdmin: boolean; hasEmail: boolean; }[]

rawDate - 날짜 반환

const results = await db.table("users").select({
  id: "id",
  createdAt: "created_at",
  nextWeek: Puri.rawDate("created_at + INTERVAL '7 days'"),
});

// 타입: { id: number; createdAt: Date; nextWeek: Date; }[]

rawStringArray - 문자열 배열 반환

const results = await db
  .table("projects")
  .join("projects__employees", "projects.id", "projects__employees.project_id")
  .join("users", "projects__employees.employee_id", "users.id")
  .select({
    projectId: "projects.id",
    memberNames: Puri.rawStringArray("ARRAY_AGG(users.username)"),
  })
  .groupBy("projects.id");

// 타입: { projectId: number; memberNames: string[]; }[]

파라미터 바인딩

Raw 함수는 Knex 스타일의 파라미터 바인딩을 지원합니다. SQL 문자열 내에 플레이스홀더를 사용하고, 두 번째 인자로 바인딩 값 배열을 전달합니다.

플레이스홀더 규칙

플레이스홀더	용도	이스케이핑
`?`	값 바인딩	값으로 이스케이핑 (`'hello'`)
`??`	식별자 바인딩	식별자로 이스케이핑 (`"table"."column"`)

값 바인딩 (`?`)

사용자 입력이나 동적 값은 반드시 ? 바인딩을 사용합니다.

const results = await db.table("employees").select({
  id: "id",
  isTarget: Puri.rawBoolean("salary > ? AND department_id = ?", [50000, 3]),
});

식별자 바인딩 (`??`)

테이블명이나 컬럼명을 동적으로 지정할 때는 ?? 바인딩을 사용합니다. table.column 형태의 문자열은 자동으로 "table"."column"으로 변환됩니다.

const results = await db.table("employees").select({
  id: "id",
  value: Puri.rawNumber("ROUND(??, ?)", ["employees.salary", -3]),
});
// SQL: ROUND("employees"."salary", -3)

혼합 사용

하나의 표현식에서 ?와 ??를 함께 사용할 수 있습니다.

const results = await db.table("documents").select({
  highlighted: Puri.rawString("pgroonga_highlight_html(??, ARRAY[?])", [
    "documents.title",
    "검색어",
  ]),
});
// SQL: pgroonga_highlight_html("documents"."title", ARRAY['검색어'])

??와 ?를 혼동하지 마세요. 컬럼명에 ?를 사용하면 값으로 이스케이핑되어 SQL 오류가 발생합니다. 반대로 사용자 입력에 ??를 사용하면 식별자로 이스케이핑되어 의도치 않은 동작이 발생할 수 있습니다.

Static SQL 함수

Puri가 제공하는 내장 SQL 함수들입니다.

문자열 함수

const results = await db
  .table("users")
  .select({
    fullName: Puri.concat("first_name", "' '", "last_name"),
  });

const results = await db.table("users").select({
  upperName: Puri.upper("username"),
  lowerEmail: Puri.lower("email"),
});

집계 함수

const results = await db.table("employees").select({
  total: Puri.count("id"),
  totalSalary: Puri.sum("salary"),
  avgSalary: Puri.avg("salary"),
  maxSalary: Puri.max("salary"),
  minSalary: Puri.min("salary"),
});

WHERE Raw

복잡한 WHERE 조건을 직접 작성할 수 있습니다.

기본 WHERE Raw

const results = await db
  .table("employees")
  .select({ id: "id", salary: "salary" })
  .whereRaw("salary > ?", [50000])
  .whereRaw("EXTRACT(YEAR FROM hire_date) = ?", [2023]);

SQL Injection 주의: whereRaw에서는 반드시 바인딩(?)을 사용하세요. 사용자 입력을 직접 문자열에 넣으면 안 됩니다.

복잡한 조건

const results = await db
  .table("employees")
  .select({ id: "id", name: "username" })
  .whereRaw(
    `
    (department_id = ? AND salary > ?)
    OR (department_id = ? AND salary > ?)
  `,
    [1, 60000, 2, 70000],
  );

날짜 함수

// 최근 30일 데이터
const results = await db
  .table("users")
  .select({ id: "id" })
  .whereRaw("created_at > NOW() - INTERVAL '30 days'");

// 특정 연도
const results = await db
  .table("users")
  .select({ id: "id" })
  .whereRaw("EXTRACT(YEAR FROM created_at) = ?", [2024]);

CASE WHEN - 조건부 값

CASE WHEN 표현식으로 조건에 따라 다른 값을 반환할 수 있습니다.

기본 CASE WHEN

const results = await db.table("employees").select({
  id: "id",
  name: "username",
  salaryLevel: Puri.rawString(`
      CASE
        WHEN salary < 50000 THEN 'Junior'
        WHEN salary < 70000 THEN 'Mid'
        ELSE 'Senior'
      END
    `),
});

숫자 계산

const results = await db.table("products").select({
  id: "id",
  name: "name",
  price: "price",
  discountedPrice: Puri.rawNumber(`
      CASE
        WHEN category = 'sale' THEN price * 0.8
        WHEN category = 'clearance' THEN price * 0.5
        ELSE price
      END
    `),
});

Boolean 결과

const results = await db.table("users").select({
  id: "id",
  name: "username",
  isPremium: Puri.rawBoolean(`
      CASE
        WHEN subscription_tier IN ('gold', 'platinum') THEN TRUE
        ELSE FALSE
      END
    `),
});

서브쿼리와 Raw SQL

Scalar 서브쿼리

const results = await db.table("users").select({
  id: "id",
  name: "username",
  postCount: Puri.rawNumber(`
      (SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id)
    `),
});

COALESCE - NULL 처리

const results = await db.table("employees").select({
  id: "id",
  departmentName: Puri.rawString(`
      COALESCE(
        (SELECT name FROM departments WHERE id = employees.department_id),
        'No Department'
      )
    `),
});

실전 예제

사용자 통계 대시보드

async getUserStats(userId: number) {
  const stats = await this.getPuri("r")
    .table("users")
    .select({
      userId: "users.id",
      username: "users.username",

      // 게시글 통계
      totalPosts: Puri.rawNumber(`
        (SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id)
      `),

      recentPosts: Puri.rawNumber(`
        (SELECT COUNT(*)
         FROM posts
         WHERE posts.user_id = users.id
         AND posts.created_at > NOW() - INTERVAL '30 days')
      `),

      // 활동 레벨
      activityLevel: Puri.rawString(`
        CASE
          WHEN (SELECT COUNT(*) FROM posts WHERE user_id = users.id) > 100 THEN 'High'
          WHEN (SELECT COUNT(*) FROM posts WHERE user_id = users.id) > 10 THEN 'Medium'
          ELSE 'Low'
        END
      `),

      // 가입 경과 일수
      daysSinceJoined: Puri.rawNumber(`
        EXTRACT(DAY FROM AGE(NOW(), users.created_at))
      `),
    })
    .where("users.id", userId)
    .first();

  return stats;
}

시간대별 집계

async getHourlyStats(date: string) {
  const stats = await this.getPuri("r")
    .table("events")
    .select({
      hour: Puri.rawNumber("EXTRACT(HOUR FROM created_at)"),
      date: Puri.rawDate("DATE(created_at)"),
      eventCount: Puri.count("id"),
      uniqueUsers: Puri.rawNumber("COUNT(DISTINCT user_id)"),
    })
    .whereRaw("DATE(created_at) = ?", [date])
    .groupBy("hour", "date")
    .orderBy("hour", "asc");

  return stats;
}

순위 계산

async getTopUsers() {
  const results = await this.getPuri("r")
    .table("users")
    .select({
      userId: "users.id",
      username: "users.username",
      postCount: Puri.rawNumber(`
        (SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id)
      `),
      rank: Puri.rawNumber(`
        RANK() OVER (ORDER BY
          (SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id) DESC
        )
      `),
    })
    .whereRaw(`
      (SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id) > 0
    `)
    .orderBy("rank", "asc")
    .limit(10);

  return results;
}

윈도우 함수

ROW_NUMBER

const results = await db.table("employees").select({
  id: "id",
  name: "username",
  salary: "salary",
  rowNumber: Puri.rawNumber(`
      ROW_NUMBER() OVER (ORDER BY salary DESC)
    `),
});

RANK / DENSE_RANK

const results = await db.table("employees").select({
  id: "id",
  departmentId: "department_id",
  salary: "salary",
  rankInDept: Puri.rawNumber(`
      RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)
    `),
});

LAG / LEAD - 이전/다음 행

const results = await db
  .table("sales")
  .select({
    id: "id",
    month: "month",
    amount: "amount",
    previousMonth: Puri.rawNumber(`
      LAG(amount, 1) OVER (ORDER BY month)
    `),
    nextMonth: Puri.rawNumber(`
      LEAD(amount, 1) OVER (ORDER BY month)
    `),
  })
  .orderBy("month", "asc");

JSON 함수 (PostgreSQL)

JSON 필드 추출

const results = await db.table("users").select({
  id: "id",
  city: Puri.rawString("metadata->>'city'"),
  age: Puri.rawNumber("(metadata->>'age')::integer"),
  tags: Puri.rawStringArray("ARRAY(SELECT jsonb_array_elements_text(metadata->'tags'))"),
});

JSON 집계

const results = await db
  .table("employees")
  .select({
    departmentId: "department_id",
    employees: Puri.rawString(`
      JSON_AGG(JSON_BUILD_OBJECT(
        'id', id,
        'name', username,
        'salary', salary
      ))
    `),
  })
  .groupBy("department_id");

성능 최적화

EXPLAIN 사용

// 쿼리 실행 계획 확인
// Puri에는 explain()이 없으므로, toQuery()로 SQL을 만들어 EXPLAIN에 넘깁니다.
const query = db.table("employees").select({ id: "id" }).where("department_id", 1);

const plan = await db.raw(`EXPLAIN ANALYZE ${query.toQuery()}`);

console.log(plan.rows);

인덱스 힌트 (PostgreSQL은 지원 안함)

PostgreSQL은 옵티마이저가 자동으로 인덱스를 선택합니다. 대신 통계 업데이트:

ANALYZE employees;

타입 안전성

Raw 함수들은 반환 타입을 명시합니다.

const results = await db.table("users").select({
  stringValue: Puri.rawString("'test'"), // string
  numberValue: Puri.rawNumber("123"), // number
  boolValue: Puri.rawBoolean("TRUE"), // boolean
  dateValue: Puri.rawDate("NOW()"), // Date
  arrayValue: Puri.rawStringArray("'{}'"), // string[]
});

// 타입이 자동으로 추론됨
results[0].stringValue; // string
results[0].numberValue; // number
results[0].boolValue; // boolean

Raw 쿼리와 Hydrate

Raw SQL을 사용할 때는 **hydrate()**를 수동으로 호출하거나, 필드 네이밍 규칙을 따라야 JOIN된 데이터를 올바르게 구조화할 수 있습니다.

Subset vs Raw Puri의 차이

기능	Subset 쿼리 (`getSubsetQueries` + `executeSubsetQuery`)	Raw Puri 쿼리 (`getPuri("r")`)
Hydrate	✅ 자동 호출	❌ 수동 호출 필요
JOIN	✅ 자동 설정	⚠️ 수동 설정
타입 추론	✅ Subset 타입	⚠️ 수동 정의
필드 구조화	✅ 자동 (중첩 객체)	❌ 수동 (flat)

Hydrate가 하는 일

hydrate()는 flat한 쿼리 결과를 중첩된 객체 구조로 변환합니다. Before hydrate (Flat):

{
  id: 1,
  username: "john",
  employee__id: 10,
  employee__salary: "60000",
  employee__department__id: 5,
  employee__department__name: "Engineering"
}

After hydrate (Nested):

{
  id: 1,
  username: "john",
  employee: {
    id: 10,
    salary: "60000",
    department: {
      id: 5,
      name: "Engineering"
    }
  }
}

필드 네이밍 규칙: 언더바(`__`) 사용

JOIN된 테이블의 필드는 테이블명__필드명 형식으로 선택해야 합니다.

// ✅ 올바른 네이밍
const results = await db
  .table("users")
  .join("employees", "users.id", "employees.user_id")
  .join("departments", "employees.department_id", "departments.id")
  .select({
    id: "users.id",
    username: "users.username",
    employee__id: "employees.id", // 언더바 2개
    employee__salary: "employees.salary",
    employee__department__id: "departments.id", // 중첩 시 언더바 2개씩
    employee__department__name: "departments.name",
  });

// hydrate 호출
const hydrated = results.map((row) => UserModel.hydrate(row));

// 결과: 중첩된 객체 구조
hydrated[0].employee.department.name; // "Engineering"

// ❌ 잘못된 네이밍 (언더바 1개)
const results = await db.table("users").join("employees", "users.id", "employees.user_id").select({
  id: "users.id",
  username: "users.username",
  employee_id: "employees.id", // ❌ 잘못됨 (언더바 1개)
  employee_salary: "employees.salary", // ❌ 잘못됨
});

// hydrate 호출해도 중첩 구조 생성 안됨
const hydrated = results.map((row) => UserModel.hydrate(row));
hydrated[0].employee; // undefined

자동 Hydrate vs 수동 Hydrate

Subset 쿼리 (자동 Hydrate)

// Subset 쿼리는 hydrate 자동 호출
const { qb } = UserModel.getSubsetQueries("P");
qb.where("users.role", "normal");

const result = await UserModel.executeSubsetQuery({
  subset: "P",
  qb,
  params: { num: 20, page: 1 },
});

// 이미 hydrate되어 중첩 구조
result.rows[0].employee.department.name; // ✅ OK

Raw Puri 쿼리 (수동 Hydrate)

// Raw 쿼리는 hydrate 수동 호출 필요
const users = await UserModel.getPuri("r")
  .table("users")
  .join("employees", "users.id", "employees.user_id")
  .join("departments", "employees.department_id", "departments.id")
  .select({
    id: "users.id",
    username: "users.username",
    employee__id: "employees.id",
    employee__salary: "employees.salary",
    employee__department__id: "departments.id",
    employee__department__name: "departments.name",
  });

// ❌ hydrate 전: flat 구조
users[0].employee; // undefined
users[0].employee__id; // 10 (flat)

// ✅ hydrate 후: 중첩 구조
const hydrated = users.map((row) => UserModel.hydrate(row));
hydrated[0].employee.id; // 10
hydrated[0].employee.department.name; // "Engineering"

executeSubsetQuery에서의 자동 Hydrate

executeSubsetQuery()는 내부적으로 hydrate를 자동 호출합니다.

class UserModelClass extends BaseModelClass<...> {
  async getUsersWithDepartment() {
    const { qb } = this.getSubsetQueries("P");

    qb.where("users.role", "normal");

    // executeSubsetQuery는 hydrate를 자동 호출
    const users = await this.executeSubsetQuery({
      subset: "P",
      qb,
      params: { num: 20, page: 1 },
    });

    // 이미 hydrate되어 있음
    return users.rows.map(user => ({
      id: user.id,
      username: user.username,
      department: user.employee?.department?.name, // ✅ OK
    }));
  }
}

Hydrate 호출 시점 정리

메서드	Hydrate 자동 호출	설명
`getSubsetQueries` + `executeSubsetQuery`	✅ Yes	Subset 쿼리는 자동
`executeSubsetQuery()`	✅ Yes	내부에서 자동 호출
`getPuri("r")`	❌ No	Raw 쿼리는 수동
`findById()`	✅ Yes	BaseModel 메서드는 자동
`findOne()`	✅ Yes	BaseModel 메서드는 자동
`findMany()`	✅ Yes	BaseModel 메서드는 자동

실전 예제: Raw 쿼리 + Hydrate

class UserModelClass extends BaseModelClass<...> {
  // Raw 쿼리로 복잡한 조인
  async getTopUsersWithStats() {
    const results = await this.getPuri("r")
      .table("users")
      .join("employees", "users.id", "employees.user_id")
      .join("departments", "employees.department_id", "departments.id")
      .select({
        // 기본 필드
        id: "users.id",
        username: "users.username",
        email: "users.email",

        // JOIN 필드 (언더바 2개 규칙)
        employee__id: "employees.id",
        employee__salary: "employees.salary",
        employee__hire_date: "employees.hire_date",
        employee__department__id: "departments.id",
        employee__department__name: "departments.name",

        // 집계 필드
        postCount: Puri.rawNumber(`
          (SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id)
        `),
      })
      .whereRaw("employees.salary > ?", [60000])
      .orderBy("employees.salary", "desc")
      .limit(10);

    // Hydrate 호출로 중첩 구조 생성
    const hydrated = results.map(row => this.hydrate(row));

    // 중첩 구조로 안전하게 접근
    return hydrated.map(user => ({
      id: user.id,
      username: user.username,
      department: user.employee.department.name,  // ✅ OK
      salary: user.employee.salary,
      postCount: user.postCount,
    }));
  }
}

Hydrate 사용 시 주의사항: 1. 필드 네이밍: JOIN 필드는 반드시 __(언더바 2개) 사용 2. 수동 호출: Raw Puri 쿼리는 hydrate() 수동 호출 필수 3. 타입 안전성: hydrate 후 타입은 수동으로 정의 필요 4. 성능: hydrate는 런타임 오버헤드가 있으므로, 간단한 쿼리는 Subset 사용 권장

권장 사항: - 가능하면 Subset 쿼리를 사용하세요 (hydrate 자동) - 복잡한 Raw SQL이 필요할 때만 수동 hydrate 사용 - 필드 네이밍 규칙(__)을 일관되게 적용

다음 단계

Type Safety

Puri의 타입 안전성 이해하기

Advanced Patterns

서브쿼리와 트랜잭션

Aggregations

집계 함수 사용하기

Basic Queries

기본 쿼리로 돌아가기

​Raw 함수 개요

Raw 타입 함수

WHERE Raw

CASE WHEN

서브쿼리

​Raw 타입 함수

​rawString - 문자열 반환

​rawNumber - 숫자 반환

​rawBoolean - 불린 반환

​rawDate - 날짜 반환

​rawStringArray - 문자열 배열 반환

​파라미터 바인딩

​플레이스홀더 규칙

​값 바인딩 (?)

​식별자 바인딩 (??)

​혼합 사용

​Static SQL 함수

​문자열 함수

​집계 함수

​WHERE Raw

​기본 WHERE Raw

​복잡한 조건

​날짜 함수

​CASE WHEN - 조건부 값

​기본 CASE WHEN

​숫자 계산

​Boolean 결과

​서브쿼리와 Raw SQL

​Scalar 서브쿼리

​COALESCE - NULL 처리

​실전 예제

​사용자 통계 대시보드

​시간대별 집계

​순위 계산

​윈도우 함수

​ROW_NUMBER

​RANK / DENSE_RANK

​LAG / LEAD - 이전/다음 행

​JSON 함수 (PostgreSQL)

​JSON 필드 추출

​JSON 집계

​성능 최적화

​EXPLAIN 사용

​인덱스 힌트 (PostgreSQL은 지원 안함)

​타입 안전성

​Raw 쿼리와 Hydrate

​Subset vs Raw Puri의 차이

​Hydrate가 하는 일

​필드 네이밍 규칙: 언더바(__) 사용

​자동 Hydrate vs 수동 Hydrate

​Subset 쿼리 (자동 Hydrate)

​Raw Puri 쿼리 (수동 Hydrate)

​executeSubsetQuery에서의 자동 Hydrate

​Hydrate 호출 시점 정리

​실전 예제: Raw 쿼리 + Hydrate

​다음 단계

Type Safety

Advanced Patterns

Aggregations

Basic Queries

Raw 함수 개요

Raw 타입 함수

rawString - 문자열 반환

rawNumber - 숫자 반환

rawBoolean - 불린 반환

rawDate - 날짜 반환

rawStringArray - 문자열 배열 반환

파라미터 바인딩

플레이스홀더 규칙

값 바인딩 (`?`)

식별자 바인딩 (`??`)

혼합 사용

Static SQL 함수

문자열 함수

집계 함수

WHERE Raw

기본 WHERE Raw

복잡한 조건

날짜 함수

CASE WHEN - 조건부 값

기본 CASE WHEN

숫자 계산

Boolean 결과

서브쿼리와 Raw SQL

Scalar 서브쿼리

COALESCE - NULL 처리

실전 예제

사용자 통계 대시보드

시간대별 집계

순위 계산

윈도우 함수

ROW_NUMBER

RANK / DENSE_RANK

LAG / LEAD - 이전/다음 행

JSON 함수 (PostgreSQL)

JSON 필드 추출

JSON 집계

성능 최적화

EXPLAIN 사용

인덱스 힌트 (PostgreSQL은 지원 안함)

타입 안전성

Raw 쿼리와 Hydrate

Subset vs Raw Puri의 차이

Hydrate가 하는 일

필드 네이밍 규칙: 언더바(`__`) 사용

자동 Hydrate vs 수동 Hydrate

Subset 쿼리 (자동 Hydrate)

Raw Puri 쿼리 (수동 Hydrate)

executeSubsetQuery에서의 자동 Hydrate

Hydrate 호출 시점 정리

실전 예제: Raw 쿼리 + Hydrate

다음 단계