> ## 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.

# getPuri

> Getting the Puri Query Builder

`getPuri` is a method that retrieves the Puri query builder. It is necessary when writing direct SQL queries or using UpsertBuilder.

## Type Signature

```typescript theme={null}
getPuri(which: DBPreset): PuriWrapper
```

## Parameters

### which

Specifies the database preset.

**Type:** `DBPreset` (`"r"` | `"w"`)

* `"r"`: Read-only (Replica DB, for queries)
* `"w"`: Write-capable (Primary DB, for writes)

```typescript theme={null}
// Read-only
const rdb = this.getPuri("r");

// Write-capable
const wdb = this.getPuri("w");
```

<Info>`"r"` and `"w"` may point to the same DB depending on your configuration.</Info>

## Return Value

**Type:** `PuriWrapper`

Returns a Puri query builder wrapper object.

```typescript theme={null}
const wdb = this.getPuri("w");

// Use Puri query builder methods
const users = await wdb.table("users").where("status", "active").select();
```

## PuriWrapper Methods

### table / from

Starts a Puri query builder by specifying a table.

```typescript theme={null}
const wdb = this.getPuri("w");

// Using table()
const users = await wdb.table("users").where("status", "active").select();

// Using from() (equivalent)
const posts = await wdb.from("posts").where("published", true).select();
```

### transaction

Starts a transaction.

```typescript theme={null}
const wdb = this.getPuri("w");

await wdb.transaction(async (trx) => {
  // Execute queries within transaction
  await trx.table("users").insert({ ... });
  await trx.table("posts").insert({ ... });

  // All succeed or all fail
});
```

### UpsertBuilder Methods

#### ubRegister

Registers a record to UpsertBuilder.

```typescript theme={null}
const wdb = this.getPuri("w");

wdb.ubRegister("users", {
  email: "john@example.com",
  name: "John",
});
```

#### ubUpsert

Upserts the registered records.

```typescript theme={null}
await wdb.transaction(async (trx) => {
  const ids = await trx.ubUpsert("users");
  return ids;
});
```

#### ubInsertOnly

Inserts the registered records only (no UPDATE).

```typescript theme={null}
await wdb.transaction(async (trx) => {
  const ids = await trx.ubInsertOnly("users");
  return ids;
});
```

#### ubUpdateBatch

Batch updates the registered records.

```typescript theme={null}
wdb.ubRegister("users", { id: 1, name: "Updated" });
wdb.ubRegister("users", { id: 2, name: "Updated 2" });

await wdb.ubUpdateBatch("users", {
  chunkSize: 500,
  where: "id",
});
```

### raw

Executes raw SQL.

```typescript theme={null}
const wdb = this.getPuri("w");

const result = await wdb.raw(
  `
  SELECT * FROM users WHERE status = ?
`,
  ["active"],
);
```

## Basic Usage

### Writing Direct Queries

```typescript theme={null}
import { BaseModelClass } from "sonamu";

class UserModelClass extends BaseModelClass {
  async getActiveUsers() {
    const rdb = this.getPuri("r");

    const users = await rdb
      .table("users")
      .where("status", "active")
      .orderBy("created_at", "desc")
      .limit(10)
      .select();

    return users;
  }
}
```

### Complex Queries

```typescript theme={null}
async getUserStats() {
  const rdb = this.getPuri("r");

  const stats = await rdb.table("users")
    .leftJoin("posts", "users.id", "posts.user_id")
    .groupBy("users.id")
    .select({
      user_id: "users.id",
      user_name: "users.name",
      post_count: rdb.raw("COUNT(posts.id)")
    });

  return stats;
}
```

### Using Transactions

```typescript theme={null}
async transferPoints(fromUserId: number, toUserId: number, points: number) {
  const wdb = this.getPuri("w");

  await wdb.transaction(async (trx) => {
    // Deduct points
    await trx.table("users")
      .where("id", fromUserId)
      .decrement("points", points);

    // Add points
    await trx.table("users")
      .where("id", toUserId)
      .increment("points", points);

    // Record history
    await trx.table("point_history").insert({
      from_user_id: fromUserId,
      to_user_id: toUserId,
      points,
      created_at: new Date()
    });
  });
}
```

## Automatic Transaction Context Detection

`getPuri` automatically detects and reuses transaction contexts. This is an important feature when used with the `@transactional` decorator.

### How It Works

The `getPuri` method works internally as follows:

1. Check for an active transaction via `DB.getTransactionContext()`
2. If a transaction exists, reuse it
3. If no transaction exists, create a new PuriWrapper

```typescript theme={null}
// Internal logic of getPuri (for reference)
getPuri(which: DBPreset): PuriWrapper {
  // 1. Attempt to get transaction from transaction context
  const trx = DB.getTransactionContext().getTransaction(which);

  if (trx) {
    // 2. If transaction exists, reuse it
    return trx;
  }

  // 3. If no transaction, return a new PuriWrapper
  const db = this.getDB(which);
  return new PuriWrapper(db, new UpsertBuilder());
}
```

### Using Within @transactional

```typescript theme={null}
import { BaseModelClass, transactional, api } from "sonamu";

class UserModelClass extends BaseModelClass {
  @api({ httpMethod: "POST" })
  @transactional()
  async createUserWithProfile(params: { email: string; name: string; bio: string }) {
    // Call getPuri within @transactional
    const wdb = this.getPuri("w");

    // wdb automatically uses the transaction created by @transactional
    // No separate transaction management code needed
    const [user] = await wdb
      .table("users")
      .insert({
        email: params.email,
        name: params.name,
      })
      .returning("*");

    await wdb.table("profiles").insert({
      user_id: user.id,
      bio: params.bio,
    });

    return user;
  }
}
```

### Nested Method Call Safety

Thanks to this feature, it's safe to call other methods within a transaction. All `getPuri` calls use the same transaction.

```typescript theme={null}
class UserModelClass extends BaseModelClass {
  @transactional()
  async createUser(email: string, name: string) {
    const wdb = this.getPuri("w");

    const [user] = await wdb.table("users").insert({ email, name }).returning("*");

    // Call another method - uses the same transaction
    await this.createDefaultProfile(user.id);

    return user;
  }

  // No @transactional, but uses the same transaction when called from above
  async createDefaultProfile(userId: number) {
    const wdb = this.getPuri("w");

    // Automatically reuses the transaction from createUser
    await wdb.table("profiles").insert({
      user_id: userId,
      bio: "Welcome!",
    });
  }
}
```

<Info>
  The transaction context set by the `@transactional` decorator is automatically shared across all
  sub-method calls. This is implemented via AsyncLocalStorage.
</Info>

<Warning>
  A PuriWrapper obtained via `getPuri` within a transaction and a new transaction started with
  `wdb.transaction()` are **different transactions**. If you need nested transactions, you must
  manage them explicitly.
</Warning>

## Practical Examples

<Tabs>
  <Tab title="Aggregate Queries" icon="chart-bar">
    ```typescript theme={null}
    import { BaseModelClass } from "sonamu";

    class AnalyticsModelClass extends BaseModelClass {
      async getDailySales(startDate: Date, endDate: Date) {
        const rdb = this.getPuri("r");

        const sales = await rdb.table("orders")
          .whereBetween("created_at", [startDate, endDate])
          .groupBy(rdb.raw("DATE(created_at)"))
          .select({
            date: rdb.raw("DATE(created_at)"),
            total_orders: rdb.raw("COUNT(*)"),
            total_amount: rdb.raw("SUM(total_amount)"),
            avg_amount: rdb.raw("AVG(total_amount)")
          })
          .orderBy("date", "asc");

        return sales;
      }

      async getTopUsers(limit: number = 10) {
        const rdb = this.getPuri("r");

        const users = await rdb.table("users")
          .leftJoin("orders", "users.id", "orders.user_id")
          .groupBy("users.id")
          .select({
            user_id: "users.id",
            user_name: "users.name",
            order_count: rdb.raw("COUNT(orders.id)"),
            total_spent: rdb.raw("COALESCE(SUM(orders.total_amount), 0)")
          })
          .orderBy("total_spent", "desc")
          .limit(limit);

        return users;
      }
    }
    ```
  </Tab>

  <Tab title="Raw SQL" icon="code">
    ```typescript theme={null}
    import { BaseModelClass } from "sonamu";

    class ReportModelClass extends BaseModelClass {
      async getComplexReport() {
        const rdb = this.getPuri("r");

        // Complex query with raw SQL
        const [result] = await rdb.raw(`
          WITH monthly_sales AS (
            SELECT
              DATE_FORMAT(created_at, '%Y-%m') as month,
              COUNT(*) as order_count,
              SUM(total_amount) as total
            FROM orders
            WHERE status = 'completed'
            GROUP BY month
          )
          SELECT
            month,
            order_count,
            total,
            LAG(total) OVER (ORDER BY month) as prev_month_total,
            (total - LAG(total) OVER (ORDER BY month)) / LAG(total) OVER (ORDER BY month) * 100 as growth_rate
          FROM monthly_sales
          ORDER BY month DESC
          LIMIT 12
        `);

        return result;
      }

      async searchFullText(keyword: string) {
        const rdb = this.getPuri("r");

        // Full-text search
        const posts = await rdb.raw(`
          SELECT
            id,
            title,
            content,
            MATCH(title, content) AGAINST(? IN BOOLEAN MODE) as relevance
          FROM posts
          WHERE MATCH(title, content) AGAINST(? IN BOOLEAN MODE)
          ORDER BY relevance DESC
          LIMIT 20
        `, [keyword, keyword]);

        return posts[0];
      }
    }
    ```
  </Tab>

  <Tab title="Batch Operations" icon="layer-group">
    ```typescript theme={null}
    import { BaseModelClass } from "sonamu";

    class BatchModelClass extends BaseModelClass {
      async batchUpdateStatus(
        userIds: number[],
        status: string
      ) {
        const wdb = this.getPuri("w");

        // Process in batches of 500
        const chunkSize = 500;
        let updated = 0;

        for (let i = 0; i < userIds.length; i += chunkSize) {
          const chunk = userIds.slice(i, i + chunkSize);

          const count = await wdb.table("users")
            .whereIn("id", chunk)
            .update({
              status,
              updated_at: new Date()
            });

          updated += count;
        }

        return { updated };
      }

      async batchInsert(users: Array<{ email: string; name: string }>) {
        const wdb = this.getPuri("w");

        return wdb.transaction(async (trx) => {
          // Register batches with UpsertBuilder
          users.forEach(user => {
            trx.ubRegister("users", {
              email: user.email,
              name: user.name,
              status: "active",
              created_at: new Date()
            });
          });

          // Upsert all at once
          const ids = await trx.ubUpsert("users", {
            chunkSize: 500
          });

          return { count: ids.length, ids };
        });
      }
    }
    ```
  </Tab>

  <Tab title="Complex Transactions" icon="arrows-rotate">
    ```typescript theme={null}
    import { BaseModelClass } from "sonamu";

    class OrderModelClass extends BaseModelClass {
      async processOrder(orderId: number) {
        const wdb = this.getPuri("w");

        return wdb.transaction(async (trx) => {
          // 1. Fetch order
          const [order] = await trx.table("orders")
            .where("id", orderId)
            .forUpdate()  // Lock row
            .select();

          if (!order) {
            throw new Error("Order not found");
          }

          // 2. Fetch order items
          const items = await trx.table("order_items")
            .where("order_id", orderId)
            .select();

          // 3. Check stock and deduct
          for (const item of items) {
            const [product] = await trx.table("products")
              .where("id", item.product_id)
              .forUpdate()
              .select();

            if (product.stock < item.quantity) {
              throw new Error(`Insufficient stock for ${product.name}`);
            }

            await trx.table("products")
              .where("id", item.product_id)
              .decrement("stock", item.quantity);
          }

          // 4. Update order status
          await trx.table("orders")
            .where("id", orderId)
            .update({
              status: "processing",
              processed_at: new Date()
            });

          // 5. Record history
          await trx.table("order_history").insert({
            order_id: orderId,
            status: "processing",
            created_at: new Date()
          });

          return { success: true };
        });
      }
    }
    ```
  </Tab>
</Tabs>

## getPuri vs getDB

### getPuri

Provides Puri query builder + UpsertBuilder.

```typescript theme={null}
const wdb = this.getPuri("w");

// Puri query builder
await wdb.table("users").where("id", 1).select();

// UpsertBuilder
wdb.ubRegister("users", { ... });
await wdb.ubUpsert("users");
```

### getDB

Provides a pure Knex instance.

```typescript theme={null}
const wdb = this.getDB("w");

// Use Knex directly
await wdb("users").where("id", 1).select();
```

<Info>
  In most cases, we recommend using `getPuri`. It provides type safety and UpsertBuilder
  functionality.
</Info>

## Transaction Isolation Levels

```typescript theme={null}
const wdb = this.getPuri("w");

await wdb.transaction(
  async (trx) => {
    // Transaction work
  },
  {
    isolation: "serializable", // Isolation level
    readOnly: false, // Read-only mode
  },
);
```

**Isolation levels:**

* `"read uncommitted"`
* `"read committed"` (PostgreSQL default)
* `"repeatable read"` (MySQL default)
* `"serializable"`

## Type Safety

Puri provides complete type safety.

```typescript theme={null}
const wdb = this.getPuri("w");

// ✅ Type-safe
const users = await wdb
  .table("users")
  .where("status", "active") // status field auto-completion
  .select();

users[0].email; // ✅ Type inferred

// ❌ Type error
await wdb.table("users").where("unknown_field", "value"); // Non-existent field
```

## Caveats

### 1. Read/Write Separation

Use `"r"` for read operations and `"w"` for write operations.

```typescript theme={null}
// ✅ Correct: "r" for reads
const rdb = this.getPuri("r");
const users = await rdb.table("users").select();

// ✅ Correct: "w" for writes
const wdb = this.getPuri("w");
await wdb.table("users").insert({ ... });
```

### 2. Transaction Context

Within `@transactional`, transactions are automatically managed.

```typescript theme={null}
@transactional()
async method() {
  const wdb = this.getPuri("w");
  // wdb automatically uses transaction
}
```

### 3. UpsertBuilder Requires Transaction

```typescript theme={null}
// ❌ Error: No transaction
const wdb = this.getPuri("w");
wdb.ubRegister("users", { ... });
await wdb.ubUpsert("users");  // Error!

// ✅ Correct: Within transaction
await wdb.transaction(async (trx) => {
  trx.ubRegister("users", { ... });
  await trx.ubUpsert("users");
});
```

## Performance Optimization

### 1. Use Indexes

```typescript theme={null}
// Use indexes in WHERE conditions
const users = await rdb
  .table("users")
  .where("status", "active") // Index needed on status
  .where("created_at", ">", startDate) // Index needed on created_at
  .select();
```

### 2. Limit SELECT Fields

```typescript theme={null}
// ❌ Select all fields
const users = await rdb.table("users").select();

// ✅ Select only needed fields
const users = await rdb.table("users").select("id", "email", "name");
```

### 3. Batch Processing

```typescript theme={null}
// Process large data in batches
for (let i = 0; i < ids.length; i += 500) {
  const chunk = ids.slice(i, i + 500);
  await wdb.table("users").whereIn("id", chunk).update({ ... });
}
```

## Next Steps

<CardGroup cols={2}>
  <Card title="Puri Query Builder" icon="code" href="/en/database/puri/basic-queries">
    How to write Puri queries
  </Card>

  <Card title="UpsertBuilder" icon="layer-group" href="/en/database/upsert-builder/basic-usage">
    UpsertBuilder usage guide
  </Card>

  <Card title="@transactional" icon="arrows-rotate" href="/en/api-reference/decorators/transactional">
    Transaction decorator
  </Card>

  <Card title="Transactions" icon="database" href="/en/database/transactions/manual-transactions">
    Manual transaction management
  </Card>
</CardGroup>