@transactional Decorator

The @transactional decorator automatically wraps methods in transactions, reducing boilerplate code.

Decorator Overview

Automatic Transactions

Automatically wraps method execution in transaction Commits on success, rollbacks on failure

Cleaner Code

No need to call transaction() Improved readability

Isolation Level

Configure transaction isolation level Concurrency control

Nested Transactions

Automatic context sharing Savepoint support

Basic Usage

Before: Manual Transactions

class UserModel extends BaseModelClass {
  async createUser(data: UserSaveParams): Promise<number> {
    const wdb = this.getPuri("w");

    // ❌ Need to call transaction() every time
    return wdb.transaction(async (trx) => {
      trx.ubRegister("users", data);
      const [userId] = await trx.ubUpsert("users");
      return userId;
    });
  }
}

After: @transactional Decorator

class UserModel extends BaseModelClass {
  @transactional()
  async createUser(data: UserSaveParams): Promise<number> {
    const wdb = this.getPuri("w");

    // ✅ Work directly without calling transaction()
    wdb.ubRegister("users", data);
    const [userId] = await wdb.ubUpsert("users");
    return userId;
  }
}

Methods decorated with @transactional() automatically run within a transaction.

How It Works

Decorator Options

dbPreset Setting

class UserModel extends BaseModelClass {
  // Default: dbPreset = "w" (write DB)
  @transactional()
  async method1() {
    const wdb = this.getPuri("w"); // write DB
    // ...
  }

  // Explicit specification
  @transactional({ dbPreset: "w" })
  async method2() {
    const wdb = this.getPuri("w");
    // ...
  }
}

Isolation Level Setting

class UserModel extends BaseModelClass {
  // READ UNCOMMITTED
  @transactional({ isolation: "read uncommitted" })
  async readUncommitted() {
    const wdb = this.getPuri("w");
    // Can read uncommitted data (Dirty Read)
  }

  // READ COMMITTED
  @transactional({ isolation: "read committed" })
  async readCommitted() {
    const wdb = this.getPuri("w");
    // Only read committed data (Non-repeatable Read possible)
  }

  // REPEATABLE READ
  @transactional({ isolation: "repeatable read" })
  async repeatableRead() {
    const wdb = this.getPuri("w");
    // Guarantees same results within same transaction
  }

  // SERIALIZABLE
  @transactional({ isolation: "serializable" })
  async serializable() {
    const wdb = this.getPuri("w");
    // Strictest isolation level (prevents Phantom Read)
  }
}

Isolation Level Considerations: - Higher isolation levels reduce concurrency - SERIALIZABLE has significant performance impact - REPEATABLE READ is appropriate for most cases

Practical Examples

Example 1: Simple Transaction

class UserModel extends BaseModelClass {
  @transactional()
  async createMultipleUsers(
    users: UserSaveParams[]
  ): Promise<number[]> {
    const wdb = this.getPuri("w");

    // Register multiple Users
    users.forEach((user) => {
      wdb.ubRegister("users", user);
    });

    // Batch save
    const ids = await wdb.ubUpsert("users");
    return ids;
  }
}

// Usage
const ids = await UserModel.createMultipleUsers([
  { email: "user1@test.com", username: "user1", ... },
  { email: "user2@test.com", username: "user2", ... },
]);

Example 2: Automatic Rollback

class UserModel extends BaseModelClass {
  @transactional()
  async createUserWithValidation(data: UserSaveParams): Promise<number> {
    const wdb = this.getPuri("w");

    // Duplicate check
    const existing = await wdb.table("users").where("email", data.email).first();

    if (existing) {
      // Error thrown → automatic rollback
      throw new Error("Email already exists");
    }

    // Create User
    wdb.ubRegister("users", data);
    const [userId] = await wdb.ubUpsert("users");

    return userId;
  }
}

Example 3: Complex Transaction (Company → Dept → Employee)

class CompanyModel extends BaseModelClass {
  @transactional({ isolation: "serializable" })
  async createOrganization(data: {
    companyName: string;
    departmentName: string;
    employees: Array<{
      email: string;
      username: string;
      salary: string;
    }>;
  }): Promise<{
    companyId: number;
    departmentId: number;
    employeeIds: number[];
  }> {
    const wdb = this.getPuri("w");

    // 1. Company
    const companyRef = wdb.ubRegister("companies", {
      name: data.companyName,
    });

    // 2. Department
    const deptRef = wdb.ubRegister("departments", {
      name: data.departmentName,
      company_id: companyRef,
    });

    // 3. Users & Employees
    data.employees.forEach((emp) => {
      const userRef = wdb.ubRegister("users", {
        email: emp.email,
        username: emp.username,
        password: "hashed",
        role: "normal",
      });

      wdb.ubRegister("employees", {
        user_id: userRef,
        department_id: deptRef,
        employee_number: `E${Date.now()}`,
        salary: emp.salary,
      });
    });

    // 4. Save in order
    const [companyId] = await wdb.ubUpsert("companies");
    const [departmentId] = await wdb.ubUpsert("departments");
    await wdb.ubUpsert("users");
    const employeeIds = await wdb.ubUpsert("employees");

    return { companyId, departmentId, employeeIds };
  }
}

Example 4: Concurrency Control

class UserModel extends BaseModelClass {
  @transactional({ isolation: "repeatable read" })
  async updateLastLogin(userId: number): Promise<void> {
    const wdb = this.getPuri("w");

    // 1. Get current login time
    const user = await wdb
      .table("users")
      .select({ last_login: "last_login_at" })
      .where("id", userId)
      .first();

    if (!user) {
      throw new Error("User not found");
    }

    // 2. Update login time
    await wdb.table("users").where("id", userId).update({
      last_login_at: new Date(),
    });

    // Repeatable Read: Not affected by other transaction changes
  }
}

Nested Transactions

class UserModel extends BaseModelClass {
  @transactional()
  async createUserWithProfile(userData: UserSaveParams, bio: string): Promise<number> {
    const wdb = this.getPuri("w");

    // Create User
    const [userId] = await wdb.table("users").insert(userData).returning("id");

    // Call another @transactional method
    // Shares same transaction context
    await this.updateBio(userId, bio);

    return userId;
  }

  @transactional()
  async updateBio(userId: number, bio: string): Promise<void> {
    const wdb = this.getPuri("w");

    await wdb.table("users").where("id", userId).update({ bio });
  }
}

Benefits of Nested Transactions: - Improved code reusability - Freedom to combine methods - Automatic transaction boundary management

Using with @api

Combining Decorators

class UserController extends BaseModelClass {
  // Can use both decorators together
  @api({ httpMethod: "POST" })
  @transactional()
  async createUser(params: UserSaveParams): Promise<{ userId: number }> {
    const wdb = this.getPuri("w");

    wdb.ubRegister("users", params);
    const [userId] = await wdb.ubUpsert("users");

    return { userId };
  }

  // Order doesn't matter
  @transactional()
  @api({ httpMethod: "PUT" })
  async updateUser(id: number, params: Partial<UserSaveParams>): Promise<void> {
    const wdb = this.getPuri("w");

    await wdb.table("users").where("id", id).update(params);
  }
}

Pros and Cons

Pros

Cleaner Code

No need for transaction() calls Removes boilerplate

Readability

Clear transaction boundaries Focus on business logic

Auto Management

Auto commit/rollback handling Prevents mistakes

Reusability

Easy method composition Nested transaction support

Cons

Constraints

Only usable at method level Partial transactions not possible

Debugging

Transaction boundaries are hidden May be harder to trace issues

Usage Guidelines

When to Use?

✅ Recommended:

Entire method is one transaction
Multiple DB operations require atomicity
Code reuse is important
API handler methods

❌ Not Recommended:

Partial transactions within method
Complex transaction control needed
Transaction boundaries need to be explicit

Pattern Comparison

// Pattern 1: @transactional (recommended)
@transactional()
async createUser(data: UserSaveParams): Promise<number> {
  const wdb = this.getPuri("w");
  wdb.ubRegister("users", data);
  const [id] = await wdb.ubUpsert("users");
  return id;
}

// Pattern 2: Manual transaction (for complex cases)
async createUserComplex(data: UserSaveParams): Promise<number> {
  const wdb = this.getPuri("w");

  return wdb.transaction(async (trx) => {
    // Complex transaction logic
    const [id] = await trx.table("users").insert(data).returning("id");

    // Conditional rollback
    if (someCondition) {
      await trx.rollback();
    }

    return id;
  });
}

Important Notes

Must Follow: 1. Method must be async function 2. Access DB with this.getPuri("r" or "w") 3. Propagate errors with throw (auto rollback) 4. Nested transactions share same context

Common Mistakes

class UserModel extends BaseModelClass {
  // ❌ Wrong usage
  @transactional()
  async wrongUsage1(data: UserSaveParams): Promise<number> {
    // Don't store getPuri in different variable
    const db = this.getPuri("w");

    // Hiding errors with catch prevents rollback
    try {
      db.ubRegister("users", data);
      const [id] = await db.ubUpsert("users");
      return id;
    } catch (error) {
      console.error(error);
      return 0; // ❌ Hiding error → no rollback
    }
  }

  // ✅ Correct usage
  @transactional()
  async correctUsage(data: UserSaveParams): Promise<number> {
    const wdb = this.getPuri("w");

    // Propagate errors with throw
    if (!data.email) {
      throw new Error("Email is required");
    }

    wdb.ubRegister("users", data);
    const [id] = await wdb.ubUpsert("users");
    return id;
  }
}

Next Steps

Manual Transactions

Using transaction() directly

Best Practices

Transaction usage guide

UpsertBuilder

Saving data in transactions

Decorators

Understanding Sonamu decorators

​Decorator Overview

Automatic Transactions

Cleaner Code

Isolation Level

Nested Transactions

​Basic Usage

​Before: Manual Transactions

​After: @transactional Decorator

​How It Works

​Decorator Options

​dbPreset Setting

​Isolation Level Setting

​Practical Examples

​Example 1: Simple Transaction

​Example 2: Automatic Rollback

​Example 3: Complex Transaction (Company → Dept → Employee)

​Example 4: Concurrency Control

​Nested Transactions

​Automatic Context Sharing

​Using with @api

​Combining Decorators

​Pros and Cons

​Pros

Cleaner Code

Readability

Auto Management

Reusability

​Cons

Constraints

Debugging

​Usage Guidelines

​When to Use?

​Pattern Comparison

​Important Notes

​Common Mistakes

​Next Steps

Manual Transactions

Best Practices

UpsertBuilder

Decorators

Decorator Overview

Basic Usage

Before: Manual Transactions

After: @transactional Decorator

How It Works

Decorator Options

dbPreset Setting

Isolation Level Setting

Practical Examples

Example 1: Simple Transaction

Example 2: Automatic Rollback

Example 3: Complex Transaction (Company → Dept → Employee)

Example 4: Concurrency Control

Nested Transactions

Automatic Context Sharing

Using with @api

Combining Decorators

Pros and Cons

Pros

Cons

Usage Guidelines

When to Use?

Pattern Comparison

Important Notes

Common Mistakes

Next Steps