getPuri

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

Type Signature

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)

// Read-only
const rdb = this.getPuri("r");

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

"r" and "w" may point to the same DB depending on your configuration.

Return Value

Type: PuriWrapper Returns a Puri query builder wrapper object.

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.

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.

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.

const wdb = this.getPuri("w");

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

ubUpsert

Upserts the registered records.

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

ubInsertOnly

Inserts the registered records only (no UPDATE).

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

ubUpdateBatch

Batch updates the registered records.

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.

const wdb = this.getPuri("w");

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

Basic Usage

Writing Direct Queries

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

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

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:

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

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

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.

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

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

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.

Practical Examples

Aggregate Queries
Raw SQL
Batch Operations
Complex Transactions

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

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
          to_char(created_at, 'YYYY-MM') 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];
  }
}

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

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

getPuri vs getDB

Provides Puri query builder + UpsertBuilder.

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.

const wdb = this.getDB("w");

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

In most cases, we recommend using getPuri. It provides type safety and UpsertBuilder functionality.

Transaction Isolation Levels

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"
"serializable"

Type Safety

Puri provides complete type safety.

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.

// ✅ 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.

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

3. UpsertBuilder Requires Transaction

// ❌ 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

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

// ❌ 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

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

Puri Query Builder

How to write Puri queries

UpsertBuilder

UpsertBuilder usage guide

@transactional

Transaction decorator

Transactions

Manual transaction management

​Type Signature

​Parameters

​which

​Return Value

​PuriWrapper Methods

​table / from

​transaction

​UpsertBuilder Methods

​ubRegister

​ubUpsert

​ubInsertOnly

​ubUpdateBatch

​raw

​Basic Usage

​Writing Direct Queries

​Complex Queries

​Using Transactions

​Automatic Transaction Context Detection

​How It Works

​Using Within @transactional

​Nested Method Call Safety

​Practical Examples

​getPuri vs getDB

​getPuri

​getDB

​Transaction Isolation Levels

​Type Safety

​Caveats

​1. Read/Write Separation

​2. Transaction Context

​3. UpsertBuilder Requires Transaction

​Performance Optimization

​1. Use Indexes

​2. Limit SELECT Fields

​3. Batch Processing

​Next Steps

Puri Query Builder

UpsertBuilder

@transactional

Transactions

Type Signature

Parameters

which

Return Value

PuriWrapper Methods

table / from

transaction

UpsertBuilder Methods

ubRegister

ubUpsert

ubInsertOnly

ubUpdateBatch

raw

Basic Usage

Writing Direct Queries

Complex Queries

Using Transactions

Automatic Transaction Context Detection

How It Works

Using Within @transactional

Nested Method Call Safety

Practical Examples

getPuri vs getDB

getPuri

getDB

Transaction Isolation Levels

Type Safety

Caveats

1. Read/Write Separation

2. Transaction Context

3. UpsertBuilder Requires Transaction

Performance Optimization

1. Use Indexes

2. Limit SELECT Fields

3. Batch Processing

Next Steps