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

# @transactional

> Database Transaction Management Decorator

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

## Basic Usage

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

```typescript theme={null}
type IsolationLevel =
  | "read uncommitted"
  | "read committed"
  | "repeatable read"
  | "serializable";
```

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

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

<Accordion title="Isolation Level Explanation" icon="shield-check">
  **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
</Accordion>

### readOnly

Sets the transaction as read-only.

**Default:** `false`

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

<Info>
  Using `readOnly: true` allows the database to perform optimizations, potentially improving performance.
</Info>

### dbPreset

Specifies the database preset to use.

**Default:** `"w"` (Write DB)

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

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

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

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

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

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

<Warning>
  Order matters: Write `@api` first, then `@transactional`.
</Warning>

### With @cache

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

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

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

```typescript theme={null}
@transactional()
async parent() {
  // Creates context
  await this.child();  // Uses same context
}

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

## Error Handling

### Automatic Rollback

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

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

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

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

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

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

## Examples

<Tabs>
  <Tab title="Money Transfer" icon="money-bill-transfer">
    ```typescript theme={null}
    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 };
      }
    }
    ```
  </Tab>

  <Tab title="Order Processing" icon="cart-shopping">
    ```typescript theme={null}
    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;
      }
    }
    ```
  </Tab>

  <Tab title="Complex Report" icon="file-chart-line">
    ```typescript theme={null}
    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
        };
      }
    }
    ```
  </Tab>

  <Tab title="Batch Processing" icon="layer-group">
    ```typescript theme={null}
    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;
      }
    }
    ```
  </Tab>
</Tabs>

## Next Steps

<CardGroup cols={2}>
  <Card title="@api" icon="plug" href="/en/api-reference/decorators/api">
    Create API endpoints
  </Card>

  <Card title="@upload" icon="upload" href="/en/api-reference/decorators/upload">
    Create file upload APIs
  </Card>

  <Card title="Database Guide" icon="database" href="/en/core-concepts/database/puri">
    Use Puri query builder
  </Card>

  <Card title="Error Handling" icon="triangle-exclamation" href="/en/core-concepts/error-handling">
    Learn error handling methods
  </Card>
</CardGroup>