> ## Documentation Index
> Fetch the complete documentation index at: https://sonamu.cartanova.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Raw Queries

> Writing SQL expressions and complex queries

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

## Raw Function Overview

<CardGroup cols={2}>
  <Card title="Raw Type Functions" icon="code">
    Type-specific Raw functions rawString, rawNumber
  </Card>

  <Card title="WHERE Raw" icon="filter">
    Complex conditions whereRaw
  </Card>

  <Card title="CASE WHEN" icon="code-branch">
    Conditional value selection CASE expressions
  </Card>

  <Card title="Subqueries" icon="layer-group">
    Writing nested queries Subquery
  </Card>
</CardGroup>

## Raw Type Functions

### rawString - Returns String

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
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.

```typescript theme={null}
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"`.

```typescript theme={null}
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.

```typescript theme={null}
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'])
```

<Warning>
  **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.
</Warning>

## Static SQL Functions

Built-in SQL functions provided by Puri.

### String Functions

<CodeGroup>
  ```typescript title="CONCAT" theme={null}
  const results = await db
    .table("users")
    .select({
      fullName: Puri.concat("first_name", "' '", "last_name"),
    });
  ```

  ```typescript title="UPPER / LOWER" theme={null}
  const results = await db.table("users").select({
    upperName: Puri.upper("username"),
    lowerEmail: Puri.lower("email"),
  });
  ```
</CodeGroup>

### Aggregate Functions

```typescript theme={null}
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

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

<Warning>
  **SQL Injection Warning**: Always use bindings (`?`) with `whereRaw`. Never put user input
  directly in strings.
</Warning>

### Complex Conditions

```typescript theme={null}
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

```typescript theme={null}
// 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

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={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'
      )
    `),
});
```

## Practical Examples

### User Statistics Dashboard

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
// Check query execution plan
const plan = await db
  .table("employees")
  .select({ id: "id" })
  .where("department_id", 1)
  .rawQuery()
  .explain();

console.log(plan);
```

### Index Hints (Not supported in PostgreSQL)

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

```sql theme={null}
ANALYZE employees;
```

## Type Safety

Raw functions specify return types.

```typescript theme={null}
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)**:

```typescript theme={null}
{
  id: 1,
  username: "john",
  employee__id: 10,
  employee__salary: "60000",
  employee__department__id: 5,
  employee__department__name: "Engineering"
}
```

**After hydrate (Nested)**:

```typescript theme={null}
{
  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.

```typescript theme={null}
// ✅ 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"
```

```typescript theme={null}
// ❌ 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)

```typescript theme={null}
// 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)

```typescript theme={null}
// 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.

```typescript theme={null}
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

```typescript theme={null}
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,
    }));
  }
}
```

<Warning>
  **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
</Warning>

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

## Next Steps

<CardGroup cols={2}>
  <Card title="Type Safety" icon="shield" href="/en/database/puri/type-safety">
    Understanding Puri's type safety
  </Card>

  <Card title="Advanced Patterns" icon="wand-magic-sparkles" href="/en/database/puri/advanced-patterns">
    Subqueries and transactions
  </Card>

  <Card title="Aggregations" icon="calculator" href="/en/database/puri/aggregations">
    Using aggregate functions
  </Card>

  <Card title="Basic Queries" icon="magnifying-glass" href="/en/database/puri/basic-queries">
    Back to basic queries
  </Card>
</CardGroup>