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

Transaction Integration

When a transaction is already active for the requested preset, getPuri participates in that DB transaction. Otherwise, it accesses the DB configured for the preset.

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

Methods called inside a transaction participate in the same DB transaction when they request the same preset.

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

Within @transactional, calls to getPuri with the same dbPreset participate in the same DB transaction. The outermost decorated call owns commit and rollback.

When a transaction for the same dbPreset is already active, nested .transaction() creates a savepoint. Await nested calls sequentially; do not run sibling savepoints in parallel with Promise.all. Use a distinct dbPreset consistently for each configured DB; do not reuse one preset name for different DB connections. This partial-rollback guidance is for direct DB queries. For a shared UpsertBuilder batch or graph, propagate failures to the outermost transaction and roll back the whole unit instead of catching and continuing.

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. Using @transactional

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

​Transaction Integration

​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. Using @transactional

​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

Transaction Integration

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. Using @transactional

3. UpsertBuilder Requires Transaction

Performance Optimization

1. Use Indexes

2. Limit SELECT Fields

3. Batch Processing

Next Steps