Raw Queries - 🌲 Sonamu

Puri is a type-safe query builder, but when complex SQL expressions are needed, you can use Raw SQL.

Raw Function Overview

Raw Type Functions

Type-specific Raw functions rawString, rawNumber

WHERE Raw

Complex conditions whereRaw

CASE WHEN

Conditional value selection CASE expressions

Subqueries

Writing nested queries Subquery

Raw Type Functions

rawString - Returns String

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"),
});

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

rawNumber - Returns Number

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)"),
});

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

rawBoolean - Returns Boolean

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

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

rawDate - Returns Date

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

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

rawStringArray - Returns String Array

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");

// Type: { projectId: number; memberNames: string[]; }[]

Parameter Bindings

Raw functions support Knex-style parameter bindings. Use placeholders in the SQL string and pass binding values as the second argument.

Placeholder Rules

Placeholder	Purpose	Escaping
`?`	Value binding	Escaped as value (`'hello'`)
`??`	Identifier binding	Escaped as identifier (`"table"."column"`)

Value Bindings (`?`)

Always use ? bindings for user input or dynamic values.

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

Identifier Bindings (`??`)

Use ?? bindings when dynamically specifying table or column names. Strings in table.column format are automatically converted to "table"."column".

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

Mixed Usage

You can use ? and ?? together in a single expression.

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

Don’t confuse ?? and ?. Using ? for a column name will escape it as a value, causing SQL errors. Conversely, using ?? for user input will escape it as an identifier, leading to unintended behavior.

Static SQL Functions

Built-in SQL functions provided by Puri.

String Functions

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"),
});

Aggregate Functions

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

You can write complex WHERE conditions directly.

Basic 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 Warning: Always use bindings (?) with whereRaw. Never put user input directly in strings.

Complex Conditions

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],
  );

Date Functions

// Data from last 30 days
const results = await db
  .table("users")
  .select({ id: "id" })
  .whereRaw("created_at > NOW() - INTERVAL '30 days'");

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

CASE WHEN - Conditional Values

CASE WHEN expressions can return different values based on conditions.

Basic 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
    `),
});

Numeric Calculations

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 Results

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
    `),
});

Subqueries and Raw SQL

Scalar Subqueries

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 Handling

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

Practical Examples

User Statistics Dashboard

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

      // Post statistics
      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')
      `),

      // Activity level
      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
      `),

      // Days since joined
      daysSinceJoined: Puri.rawNumber(`
        EXTRACT(DAY FROM AGE(NOW(), users.created_at))
      `),
    })
    .where("users.id", userId)
    .first();

  return stats;
}

Hourly Statistics

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

Ranking Calculation

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

Window Functions

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 - Previous/Next Row

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 Functions (PostgreSQL)

JSON Field Extraction

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 Aggregation

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");

Performance Optimization

Using EXPLAIN

// Check query execution plan
// Puri has no explain(), so build the SQL with toQuery() and pass it to 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);

Index Hints (Not supported in PostgreSQL)

PostgreSQL’s optimizer automatically selects indexes. Instead, update statistics:

ANALYZE employees;

Type Safety

Raw functions specify return types.

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

// Types are automatically inferred
results[0].stringValue; // string
results[0].numberValue; // number
results[0].boolValue; // boolean

Raw Queries and Hydrate

When using Raw SQL, you must either manually call hydrate() or follow field naming conventions to properly structure JOIN data.

Subset vs Raw Puri Differences

Feature	Subset Query (`getSubsetQueries` + `executeSubsetQuery`)	Raw Puri Query (`getPuri("r")`)
Hydrate	✅ Automatic	❌ Manual call required
JOIN	✅ Auto-configured	⚠️ Manual setup
Type Inference	✅ Subset types	⚠️ Manual definition
Field Structuring	✅ Automatic (nested objects)	❌ Manual (flat)

What Hydrate Does

hydrate() transforms flat query results into nested object structures. 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"
    }
  }
}

Field Naming Convention: Double Underscore (`__`)

JOIN table fields must use the tableName__fieldName format.

// ✅ Correct naming
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", // double underscore
    employee__salary: "employees.salary",
    employee__department__id: "departments.id", // double underscore for nesting
    employee__department__name: "departments.name",
  });

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

// result: nested object structure
hydrated[0].employee.department.name; // "Engineering"

// ❌ Incorrect naming (single underscore)
const results = await db.table("users").join("employees", "users.id", "employees.user_id").select({
  id: "users.id",
  username: "users.username",
  employee_id: "employees.id", // ❌ wrong (single underscore)
  employee_salary: "employees.salary", // ❌ wrong
});

// hydrate won't create nested structure
const hydrated = results.map((row) => UserModel.hydrate(row));
hydrated[0].employee; // undefined

Automatic vs Manual Hydrate

Subset Query (Automatic Hydrate)

// Subset queries automatically call hydrate
const { qb } = UserModel.getSubsetQueries("P");
qb.where("users.role", "normal");

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

// Already hydrated into nested structure
result.rows[0].employee.department.name; // ✅ OK

Raw Puri Query (Manual Hydrate)

// Raw queries require manual hydrate call
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",
  });

// ❌ Before hydrate: flat structure
users[0].employee; // undefined
users[0].employee__id; // 10 (flat)

// ✅ After hydrate: nested structure
const hydrated = users.map((row) => UserModel.hydrate(row));
hydrated[0].employee.id; // 10
hydrated[0].employee.department.name; // "Engineering"

Automatic Hydrate in executeSubsetQuery

executeSubsetQuery() internally calls hydrate automatically.

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

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

    // executeSubsetQuery calls hydrate automatically
    const users = await this.executeSubsetQuery({
      subset: "P",
      qb,
      params: { num: 20, page: 1 },
    });

    // Already hydrated
    return users.rows.map(user => ({
      id: user.id,
      username: user.username,
      department: user.employee?.department?.name, // ✅ OK
    }));
  }
}

Hydrate Call Summary

Method	Automatic Hydrate	Description
`getSubsetQueries` + `executeSubsetQuery`	✅ Yes	Subset queries are automatic
`executeSubsetQuery()`	✅ Yes	Called internally
`getPuri("r")`	❌ No	Raw queries are manual
`findById()`	✅ Yes	BaseModel methods are automatic
`findOne()`	✅ Yes	BaseModel methods are automatic
`findMany()`	✅ Yes	BaseModel methods are automatic

Practical Example: Raw Query + Hydrate

class UserModelClass extends BaseModelClass<...> {
  // Complex join with raw query
  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({
        // Basic fields
        id: "users.id",
        username: "users.username",
        email: "users.email",

        // JOIN fields (double underscore convention)
        employee__id: "employees.id",
        employee__salary: "employees.salary",
        employee__hire_date: "employees.hire_date",
        employee__department__id: "departments.id",
        employee__department__name: "departments.name",

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

    // Create nested structure with hydrate
    const hydrated = results.map(row => this.hydrate(row));

    // Safely access nested structure
    return hydrated.map(user => ({
      id: user.id,
      username: user.username,
      department: user.employee.department.name,  // ✅ OK
      salary: user.employee.salary,
      postCount: user.postCount,
    }));
  }
}

Cautions when using Hydrate: 1. Field naming: JOIN fields must use __ (double underscore) 2. Manual call: Raw Puri queries require manual hydrate() call 3. Type safety: Types after hydrate must be manually defined 4. Performance: hydrate has runtime overhead, so use Subset queries for simple cases

Recommendations: - Use Subset queries when possible (automatic hydrate) - Only use manual hydrate when complex Raw SQL is needed - Apply field naming conventions (__) consistently

Next Steps

Type Safety

Understanding Puri’s type safety

Advanced Patterns

Subqueries and transactions

Aggregations

Using aggregate functions

Basic Queries

Back to basic queries

​Raw Function Overview

Raw Type Functions

WHERE Raw

CASE WHEN

Subqueries

​Raw Type Functions

​rawString - Returns String

​rawNumber - Returns Number

​rawBoolean - Returns Boolean

​rawDate - Returns Date

​rawStringArray - Returns String Array

​Parameter Bindings

​Placeholder Rules

​Value Bindings (?)

​Identifier Bindings (??)

​Mixed Usage

​Static SQL Functions

​String Functions

​Aggregate Functions

​WHERE Raw

​Basic WHERE Raw

​Complex Conditions

​Date Functions

​CASE WHEN - Conditional Values

​Basic CASE WHEN

​Numeric Calculations

​Boolean Results

​Subqueries and Raw SQL

​Scalar Subqueries

​COALESCE - NULL Handling

​Practical Examples

​User Statistics Dashboard

​Hourly Statistics

​Ranking Calculation

​Window Functions

​ROW_NUMBER

​RANK / DENSE_RANK

​LAG / LEAD - Previous/Next Row

​JSON Functions (PostgreSQL)

​JSON Field Extraction

​JSON Aggregation

​Performance Optimization

​Using EXPLAIN

​Index Hints (Not supported in PostgreSQL)

​Type Safety

​Raw Queries and Hydrate

​Subset vs Raw Puri Differences

​What Hydrate Does

​Field Naming Convention: Double Underscore (__)

​Automatic vs Manual Hydrate

​Subset Query (Automatic Hydrate)

​Raw Puri Query (Manual Hydrate)

​Automatic Hydrate in executeSubsetQuery

​Hydrate Call Summary

​Practical Example: Raw Query + Hydrate

​Next Steps

Type Safety

Advanced Patterns

Aggregations

Basic Queries

Raw Function Overview

Raw Type Functions

rawString - Returns String

rawNumber - Returns Number

rawBoolean - Returns Boolean

rawDate - Returns Date

rawStringArray - Returns String Array

Parameter Bindings

Placeholder Rules

Value Bindings (`?`)

Identifier Bindings (`??`)

Mixed Usage

Static SQL Functions

String Functions

Aggregate Functions

WHERE Raw

Basic WHERE Raw

Complex Conditions

Date Functions

CASE WHEN - Conditional Values

Basic CASE WHEN

Numeric Calculations

Boolean Results

Subqueries and Raw SQL

Scalar Subqueries

COALESCE - NULL Handling

Practical Examples

User Statistics Dashboard

Hourly Statistics

Ranking Calculation

Window Functions

ROW_NUMBER

RANK / DENSE_RANK

LAG / LEAD - Previous/Next Row

JSON Functions (PostgreSQL)

JSON Field Extraction

JSON Aggregation

Performance Optimization

Using EXPLAIN

Index Hints (Not supported in PostgreSQL)

Type Safety

Raw Queries and Hydrate

Subset vs Raw Puri Differences

What Hydrate Does

Field Naming Convention: Double Underscore (`__`)

Automatic vs Manual Hydrate

Subset Query (Automatic Hydrate)

Raw Puri Query (Manual Hydrate)

Automatic Hydrate in executeSubsetQuery

Hydrate Call Summary

Practical Example: Raw Query + Hydrate

Next Steps