@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

Same-preset DB transaction No automatic savepoint

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

Automatic Transaction Reuse

Same-preset nested @transactional calls participate in one DB transaction. The outermost same-preset call owns commit and rollback.

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
    // Participates in the same DB transaction
    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

Error and Savepoint Semantics

A same-preset nested @transactional call participates in the outer DB transaction. It does not create a new database transaction or savepoint, and the outermost call owns commit and rollback.

@transactional()
async outerMethod() {
  const wdb = this.getPuri("w");
  await wdb.table("users").insert({ id: 1 });

  try {
    await this.innerMethod();
  } catch (error) {
    // Catching this application-level error lets the outer transaction continue.
    console.error(error);
  }

  // Runs normally and may commit with earlier successful writes.
  await wdb.table("logs").insert({ action: "user_created" });
}

@transactional()
async innerMethod() {
  const wdb = this.getPuri("w");
  await wdb.table("profiles").insert({ user_id: 1 });
  throw new Error("Application validation failed");
}

This example catches an application-level error thrown after the DB statements succeeded. The outer transaction may continue and commit those writes. A PostgreSQL statement error, such as a constraint or SQL failure, aborts the current PostgreSQL transaction; catching it alone does not make that transaction usable again.

If a direct DB query may fail and the outer transaction must recover, run that query in a nested getPuri("w").transaction(...) savepoint. If the whole composition must remain atomic, let an application-level error propagate out of the outermost @transactional call to roll everything back.

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 application-level errors with throw (auto rollback) 4. Same-preset nested calls participate in the same DB transaction, and the outermost call owns commit/rollback

Common Mistakes

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

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

    // Hiding an application-level error after a successful DB write may commit that write.
    try {
      throw new Error("Application validation failed");
    } catch (error) {
      console.error(error);
      return id;
    }
  }

  // ✅ 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 Transaction Reuse

​Error and Savepoint Semantics

​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 Transaction Reuse

Error and Savepoint Semantics

Using with @api

Combining Decorators

Pros and Cons

Pros

Cons

Usage Guidelines

When to Use?

Pattern Comparison

Important Notes

Common Mistakes

Next Steps