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[]; }[]

Static SQL 함수

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

문자열 함수

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

집계 함수

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 사용

// 쿼리 실행 계획 확인
const plan = await db
  .table("employees")
  .select({ id: "id" })
  .where("department_id", 1)
  .rawQuery()
  .explain();

console.log(plan);

인덱스 힌트 (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 사용 시 주의사항:

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

권장 사항:

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

다음 단계

Type Safety

Puri의 타입 안전성 이해하기

Advanced Patterns

서브쿼리와 트랜잭션

Aggregations

집계 함수 사용하기

Basic Queries

기본 쿼리로 돌아가기

시작하기

핵심 개념

데이터베이스

API 개발

프론트엔드 통합

테스팅

고급 기능

도구 & CLI

설정

API 레퍼런스

문제 해결

자주 묻는 질문

​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

다음 단계