Transactions

Use withTransaction when you need to execute multiple service operations atomically. It is an instance method called from a controller endpoint — the execution context comes from the request.

The callback receives the active SqbConnection and a transaction-scoped copy of the service as its second argument. Use that instance directly inside the callback. For other services, pass { connection } explicitly to for().

@(HttpOperation({ method: 'POST', path: 'transfer-balance' })
  .QueryParam('fromId', Number)
  .QueryParam('toId', Number)
  .QueryParam('amount', Number))
async transferBalance(ctx: HttpContext) {
  const { fromId, toId, amount } = ctx.queryParams;
  await this.accountsService.for(ctx).withTransaction(async (connection, accountsSvc) => {
    await accountsSvc.updateOnly(fromId, { balance: sql('balance - ?', [amount]) });
    await accountsSvc.updateOnly(toId, { balance: sql('balance + ?', [amount]) });
  });
}

withTransaction wraps the callback in a database transaction with automatic commit and rollback on error. If a transaction is already active on the current context it joins that transaction instead of starting a new one.

---

Multi-table transactions

When multiple services are involved, pass connection explicitly to each for() call so they all join the same transaction:

async placeOrder(ctx: HttpContext) {
  const orderInput = await ctx.request.readBody<OrderInputDTO>();
  await this.ordersService.for(ctx).withTransaction(async (connection, ordersSvc) => {
    const order = await ordersSvc.create(orderInput);

    await this.inventoryService.for(ctx, { connection }).updateOnly(
      orderInput.productId,
      { quantity: sql('quantity - ?', [orderInput.quantity]) },
    );

    await this.customerService.for(ctx, { connection }).updateOnly(order.customerId, {
      orderCount: sql('order_count + 1'),
    });
  });
}

---

Transactions inside a service

Expose a dedicated method when a transaction needs to coordinate multiple operations internally. Inside service methods this already carries the context set by the caller — no need to pass ctx:

export class OrdersService extends SqbCollectionService<Order> {
  constructor(
    db: SqbClient,
    private readonly inventory: InventoryService,
  ) {
    super(Order, { db });
  }

  async createWithInventoryCheck(input: PartialDTO<Order>) {
    return this.withTransaction(async (connection, self) => {
      const order = await self.create(input);
      await this.inventory.for(this, { connection }).updateOnly(
        input.productId,
        { quantity: sql('quantity - ?', [input.quantity]) },
      );
      return order;
    });
  }
}

---

Full API reference

→ SqbServiceBase — withTransaction