@transactional - 🌲 Sonamu

The @transactional decorator wraps methods in a database transaction to ensure data consistency.

Basic Usage

import { BaseModelClass, api, transactional } from "sonamu";

class UserModelClass extends BaseModelClass<
  UserSubsetKey,
  UserSubsetMapping,
  UserSubsetQueries
> {
  @api({ httpMethod: "POST" })
  @transactional()
  async create(data: UserCreateParams) {
    const wdb = this.getDB("w");

    // All operations executed within transaction
    const user = await this.insert(wdb, data);
    await this.createProfile(user.id);
    await this.sendWelcomeEmail(user.id);

    // Commits if all succeed, rolls back if any fails
    return user;
  }

  @api({ httpMethod: "PUT" })
  @transactional()
  async update(id: number, data: UserUpdateParams) {
    const wdb = this.getDB("w");

    // Transaction guaranteed
    await this.upsert(wdb, { id, ...data });
    await this.updateRelatedData(id);

    return this.findById(id);
  }
}

Options

isolation

Specifies the transaction isolation level.

type IsolationLevel =
  | "read uncommitted"
  | "read committed"
  | "repeatable read"
  | "serializable";

Default: Database default isolation level (PostgreSQL: read committed)

@transactional({
  isolation: "serializable"
})
async processPayment(orderId: number) {
  // Strictest isolation level
  // Completely prevents concurrency issues
}

@transactional({
  isolation: "read committed"
})
async updateStatus(id: number) {
  // Common isolation level
  // Only reads committed data
}

Isolation Level Explanation

read uncommitted (Lowest)

Can read uncommitted data
Dirty reads possible
Fastest but risky

read committed (Default)

Only reads committed data
Prevents dirty reads
Non-repeatable reads possible

repeatable read

Same data always returns same value within transaction
Prevents non-repeatable reads
Phantom reads possible

serializable (Highest)

Transactions behave as if executed serially
Prevents all anomalies
Safest but slowest

readOnly

Sets the transaction as read-only. Default: false

@transactional({ readOnly: true })
async generateReport(startDate: Date, endDate: Date) {
  const rdb = this.getPuri("r");

  // Queries multiple tables but doesn't modify
  // Guarantees consistent snapshot
  const users = await rdb.table("users").select();
  const orders = await rdb.table("orders")
    .whereBetween("created_at", [startDate, endDate])
    .select();

  return this.calculateReport(users, orders);
}

Using readOnly: true allows the database to perform optimizations, potentially improving performance.

dbPreset

Specifies the database preset to use. Default: "w" (Write DB)

@transactional({ dbPreset: "w" })
async saveData(data: SaveParams) {
  // Uses write DB (default)
  const wdb = this.getDB("w");
  return this.insert(wdb, data);
}

@transactional({ dbPreset: "r", readOnly: true })
async complexQuery() {
  // Transaction on read DB
  const rdb = this.getPuri("r");
  return rdb.table("users").select();
}

Complete Options Example

@api({ httpMethod: "POST" })
@transactional({
  isolation: "serializable",
  readOnly: false,
  dbPreset: "w"
})
async criticalOperation(data: OperationParams) {
  // Executes safely with highest isolation
}

How Transactions Work

Automatic Commit/Rollback

@transactional()
async transfer(fromId: number, toId: number, amount: number) {
  const wdb = this.getDB("w");

  // 1. Withdraw
  await wdb.table("accounts")
    .where("id", fromId)
    .decrement("balance", amount);

  // 2. Automatic rollback on error
  if (amount > 1000000) {
    throw new Error("Amount too large");
    // Above decrement also rolled back
  }

  // 3. Deposit
  await wdb.table("accounts")
    .where("id", toId)
    .increment("balance", amount);

  // 4. Automatic commit if all succeed
}

Transaction Nesting

Transactions with the same dbPreset are reused:

class UserModelClass extends BaseModelClass {
  @transactional()
  async createUser(data: UserCreateParams) {
    const wdb = this.getDB("w");
    const user = await this.insert(wdb, data);

    // This method also has @transactional
    // but reuses the same transaction
    await this.createProfile(user.id);

    return user;
  }

  @transactional()
  async createProfile(userId: number) {
    const wdb = this.getDB("w");
    // Reuses createUser's transaction
    return wdb.table("profiles").insert({ user_id: userId });
  }
}

Log Output:

[DEBUG] transactional: UserModel.createUser
[DEBUG] new transaction context: w
[DEBUG] transactional: UserModel.createProfile
[DEBUG] reuse transaction context: w
[DEBUG] delete transaction context: w

Different Preset Transactions

Different dbPresets create separate transactions:

@transactional({ dbPreset: "w" })
async saveData(data: SaveParams) {
  const wdb = this.getDB("w");
  await wdb.table("logs").insert(data);

  // Different preset creates separate transaction
  await this.saveToReadReplica(data);
}

@transactional({ dbPreset: "r" })
async saveToReadReplica(data: SaveParams) {
  const rdb = this.getPuri("r");
  // Creates separate transaction
  return rdb.table("cache").insert(data);
}

Using with Other Decorators

With @api

@api({ httpMethod: "POST" })
@transactional()
async save(data: SaveParams) {
  // API endpoint with transaction
}

Order matters: Write @api first, then @transactional.

With @cache

@api({ httpMethod: "GET" })
@cache({ ttl: "10m" })
@transactional({ readOnly: true })
async findWithRelations(id: number) {
  // Read-only transaction + caching
  const rdb = this.getPuri("r");

  const user = await rdb.table("users").where("id", id).first();
  const posts = await rdb.table("posts").where("user_id", id).select();

  return { user, posts };
}

With @upload

@upload()
@transactional()
async uploadAvatar() {
  const { files } = Sonamu.getContext();
  const file = files?.[0]; // Use first file
  const wdb = this.getDB("w");

  // File save + DB update in transaction
  const url = await this.saveFile(file);
  await wdb.table("users")
    .where("id", userId)
    .update({ avatar_url: url });

  return { url };
}

AsyncLocalStorage

@transactional uses Node.js’s AsyncLocalStorage to manage transaction context.

Context Creation

// When no transaction exists
@transactional()
async save() {
  // 1. Create new AsyncLocalStorage context
  // 2. Start transaction
  // 3. Execute method
  // 4. Commit/rollback
  // 5. Clean up context
}

Context Reuse

@transactional()
async parent() {
  // Creates context
  await this.child();  // Uses same context
}

@transactional()
async child() {
  // Reuses parent's context
}

Error Handling

Automatic Rollback

@transactional()
async processOrder(orderId: number) {
  const wdb = this.getDB("w");

  try {
    await wdb.table("orders")
      .where("id", orderId)
      .update({ status: "processing" });

    // Payment failure
    throw new Error("Payment failed");

  } catch (error) {
    // Transaction automatically rolled back
    throw error;
  }
}

Manual Rollback

Throw an error when explicit rollback is needed:

@transactional()
async validateAndSave(data: SaveParams) {
  const wdb = this.getDB("w");

  const result = await this.insert(wdb, data);

  // Rollback on validation failure
  if (!this.validate(result)) {
    throw new Error("Validation failed");
  }

  return result;
}

Precautions

1. Only Available in BaseModelClass

// ❌ Not available in regular classes
class UtilClass {
  @transactional()
  async helper() {}
  // Error: modelName is required
}

// ✅ Requires BaseModelClass inheritance
class UserModelClass extends BaseModelClass {
  @transactional()
  async save() {}
}

2. Beware of Long Transactions

// ❌ Bad: Long-running operation
@transactional()
async processLargeData() {
  const wdb = this.getDB("w");

  // 10-minute operation
  for (let i = 0; i < 1000000; i++) {
    await wdb.table("logs").insert({ index: i });
  }
  // Transaction open too long
}

// ✅ Good: Split into batches
async processLargeData() {
  const batches = this.createBatches(1000);

  for (const batch of batches) {
    await this.processBatch(batch);
  }
}

@transactional()
async processBatch(batch: Item[]) {
  const wdb = this.getDB("w");
  // Execute transaction in small units
  return this.insertBatch(wdb, batch);
}

3. Beware of External API Calls

// ❌ Bad: External API in transaction
@transactional()
async createOrder(data: OrderCreateParams) {
  const wdb = this.getDB("w");

  const order = await this.insert(wdb, data);

  // External payment API call (slow)
  await this.paymentService.charge(order.amount);
  // Transaction open too long

  return order;
}

// ✅ Good: Handle outside transaction
async createOrder(data: OrderCreateParams) {
  // 1. Create order (transaction)
  const order = await this.saveOrder(data);

  // 2. Process payment (outside transaction)
  await this.paymentService.charge(order.amount);

  // 3. Update status (transaction)
  await this.updateOrderStatus(order.id, "paid");

  return order;
}

@transactional()
async saveOrder(data: OrderCreateParams) {
  const wdb = this.getDB("w");
  return this.insert(wdb, data);
}

@transactional()
async updateOrderStatus(id: number, status: string) {
  const wdb = this.getDB("w");
  return wdb.table("orders").where("id", id).update({ status });
}

Logging

The @transactional decorator automatically logs:

@transactional()
async save() {
  // Automatic logs:
  // [DEBUG] transactional: UserModel.save
  // [DEBUG] new transaction context: w
  // [DEBUG] delete transaction context: w
}

Examples

Money Transfer
Order Processing
Complex Report
Batch Processing

class AccountModelClass extends BaseModelClass {
  @api({ httpMethod: "POST" })
  @transactional({ isolation: "serializable" })
  async transfer(
    fromAccountId: number,
    toAccountId: number,
    amount: number
  ) {
    const wdb = this.getDB("w");

    // 1. Check sender balance
    const fromAccount = await wdb.table("accounts")
      .where("id", fromAccountId)
      .first();

    if (fromAccount.balance < amount) {
      throw new Error("Insufficient balance");
    }

    // 2. Withdraw
    await wdb.table("accounts")
      .where("id", fromAccountId)
      .decrement("balance", amount);

    // 3. Deposit
    await wdb.table("accounts")
      .where("id", toAccountId)
      .increment("balance", amount);

    // 4. Record transaction
    await wdb.table("transactions").insert({
      from_account_id: fromAccountId,
      to_account_id: toAccountId,
      amount,
      type: "transfer",
      created_at: new Date()
    });

    return { success: true };
  }
}

class OrderModelClass extends BaseModelClass {
  @api({ httpMethod: "POST" })
  @transactional()
  async createOrder(data: OrderCreateParams) {
    const wdb = this.getDB("w");

    // 1. Create order
    const order = await this.insert(wdb, {
      user_id: data.userId,
      total_amount: data.totalAmount,
      status: "pending"
    });

    // 2. Save order items
    await wdb.table("order_items").insert(
      data.items.map(item => ({
        order_id: order.id,
        product_id: item.productId,
        quantity: item.quantity,
        price: item.price
      }))
    );

    // 3. Decrease stock
    for (const item of data.items) {
      const updated = await wdb.table("products")
        .where("id", item.productId)
        .where("stock", ">=", item.quantity)
        .decrement("stock", item.quantity);

      if (updated === 0) {
        throw new Error(`Product ${item.productId} out of stock`);
      }
    }

    // 4. Use points
    if (data.usePoints > 0) {
      await wdb.table("users")
        .where("id", data.userId)
        .decrement("points", data.usePoints);
    }

    return order;
  }
}

class ReportModelClass extends BaseModelClass {
  @transactional({
    readOnly: true,
    isolation: "repeatable read"
  })
  async generateMonthlyReport(month: string) {
    const rdb = this.getPuri("r");

    // All queries executed from consistent snapshot

    // 1. Aggregate sales
    const sales = await rdb.table("orders")
      .whereRaw("DATE_FORMAT(created_at, '%Y-%m') = ?", [month])
      .sum("total_amount as total");

    // 2. New members
    const newUsers = await rdb.table("users")
      .whereRaw("DATE_FORMAT(created_at, '%Y-%m') = ?", [month])
      .count("* as count");

    // 3. Sales by product
    const productSales = await rdb.table("order_items")
      .join("orders", "orders.id", "order_items.order_id")
      .whereRaw("DATE_FORMAT(orders.created_at, '%Y-%m') = ?", [month])
      .groupBy("order_items.product_id")
      .select(
        "order_items.product_id",
        rdb.raw("SUM(order_items.quantity) as total_quantity"),
        rdb.raw("SUM(order_items.price * order_items.quantity) as total_amount")
      );

    return {
      month,
      sales: sales[0].total,
      newUsers: newUsers[0].count,
      productSales
    };
  }
}

class DataModelClass extends BaseModelClass {
  // Overall processing (no transaction)
  async processBulkData(items: Item[]) {
    const batches = this.createBatches(items, 100);
    const results = [];

    for (const batch of batches) {
      try {
        const result = await this.processBatch(batch);
        results.push(result);
      } catch (error) {
        console.error("Batch failed:", error);
        // Only failed batch rolled back
      }
    }

    return results;
  }

  // Per-batch transaction
  @transactional()
  async processBatch(batch: Item[]) {
    const wdb = this.getDB("w");

    // Transaction per batch
    const processed = [];
    for (const item of batch) {
      const result = await this.processItem(wdb, item);
      processed.push(result);
    }

    return processed;
  }

  private createBatches<T>(items: T[], size: number): T[][] {
    const batches: T[][] = [];
    for (let i = 0; i < items.length; i += size) {
      batches.push(items.slice(i, i + size));
    }
    return batches;
  }
}

Next Steps

@api

Create API endpoints

@upload

Create file upload APIs

Database Guide

Use Puri query builder

Error Handling

Learn error handling methods

​Basic Usage

​Options

​isolation

​readOnly

​dbPreset

​Complete Options Example

​How Transactions Work

​Automatic Commit/Rollback

​Transaction Nesting

​Different Preset Transactions

​Using with Other Decorators

​With @api

​With @cache

​With @upload

​AsyncLocalStorage

​Context Creation

​Context Reuse

​Error Handling

​Automatic Rollback

​Manual Rollback

​Precautions

​1. Only Available in BaseModelClass

​2. Beware of Long Transactions

​3. Beware of External API Calls

​Logging

​Examples

​Next Steps

@api

@upload

Database Guide

Error Handling

Basic Usage

Options

isolation

readOnly

dbPreset

Complete Options Example

How Transactions Work

Automatic Commit/Rollback

Transaction Nesting

Different Preset Transactions

Using with Other Decorators

With @api

With @cache

With @upload

AsyncLocalStorage

Context Creation

Context Reuse

Error Handling

Automatic Rollback

Manual Rollback

Precautions

1. Only Available in BaseModelClass

2. Beware of Long Transactions

3. Beware of External API Calls

Logging

Examples

Next Steps