Transactions

In the Fragno database layer, transactions are built around a two-phase pattern:

• Retrieval phase: schedule reads, then execute them together
• Mutation phase: schedule writes, then execute them atomically

In terms of code organization, the important architectural rule is:

• Route handlers control transaction execution via this.handlerTx()
• Services define reusable operations via this.serviceTx(schema)

This lets you call multiple service methods inside one transaction, and the DB work will be batched.

Route handlers: control the transaction boundary

In route handlers, use this.handlerTx() to build and execute transactions:

const [subscribers] = await this.handlerTx()
  .withServiceCalls(() => [services.getSubscribers({ search, sortBy, pageSize, cursor })])
  .execute();

The handlerTx() builder:

• automatically retries on optimistic concurrency conflicts
• chains .retrieve(), .mutate(), .withServiceCalls() to compose operations
• ends with .execute() to run the transaction

Services: define reusable operations

Services define database operations using this.serviceTx(schema), returning a TxResult that handlers can execute:

Example pattern (mailing-list subscribe()):

subscribe: function (email: string) {
  return this.serviceTx(mailingListSchema)
    .retrieve((uow) =>
      uow.find("subscriber", (b) =>
        b.whereIndex("idx_subscriber_email", (eb) => eb("email", "=", email)),
      ),
    )
    .mutate(({ uow, retrieveResult: [existing] }) => {
      if (existing.length > 0) {
        return { alreadySubscribed: true, id: existing[0].id, email };
      }

      const id = uow.create("subscriber", { email, subscribedAt: new Date() });
      return { alreadySubscribed: false, id, email };
    })
    .build();
}

The serviceTx() builder:

• chains .retrieve() and .mutate() to define operations
• ends with .build() to return a TxResult
• does not execute — handlers execute service results via .withServiceCalls()

Why this design?

Because services return TxResult instead of executing directly, you can:

• call multiple service methods in one handler transaction
• batch all database operations together
• get automatic retry handling from the handler

As a result of these design decisions:

• transactions never block the database
• database round-trips are amortised
• every successful transaction only has one round-trip (read-only) or two (read-write)

Optimistic concurrency with .check()

Fragno DB supports optimistic concurrency control using a hidden _version column. When you read a row, its id is a FragnoId that also carries version information.

Calling .check() on a mutation tells Fragno:

"Only apply this update/delete if the row's version is still the one I read."

If the row was modified by another transaction in between, the mutation phase will fail and handlerTx() will retry the whole transaction (up to the retry policy).

update(...).check() / delete(...).check()

You can enable version checking on updates and deletes:

updateUser: function (userId: FragnoId) {
  return this.serviceTx(mySchema)
    .mutate(({ uow }) => {
      uow.update("users", userId, (b) => b.set({ name: "New name" }).check());
    })
    .build();
}

Standalone uow.check(table, id)

Sometimes you want to assert that a record hasn't changed without updating it (e.g. guard related rows before writing):

transfer: function (from: FragnoId, to: FragnoId) {
  return this.serviceTx(mySchema)
    .mutate(({ uow }) => {
      uow.check("accounts", from);
      uow.check("accounts", to);
      uow.create("transfers", { fromAccountId: from.toString(), toAccountId: to.toString() });
    })
    .build();
}

Important: .check() requires a FragnoId, not a string

.check() throws if you try to use it with a plain string ID, because a string doesn't contain version information:

• ✅ uow.update("users", user.id, (b) => b.set(...).check()) (where user.id came from a query)
• ❌ uow.update("users", "user-123", (b) => b.set(...).check())

You can update/delete using a string ID without .check() (no version guard), but that means last-write-wins.

End-to-end example: handler controls execution, service performs the transfer

• Route handler: owns the transaction boundary via handlerTx()
• Service: defines operations with .check() for optimistic locking

import { ExponentialBackoffRetryPolicy } from "@fragno-dev/db";

// Route handler - controls transaction execution
defineRoute({
  method: "POST",
  path: "/transfer",
  handler: async function ({ input }, { json }) {
    const { fromAccountId, toAccountId, amount } = await input.valid();

    const [result] = await this.handlerTx()
      .withServiceCalls(() => [
        services.transferBetweenAccounts({ fromAccountId, toAccountId, amount }),
      ])
      .execute({
        retryPolicy: new ExponentialBackoffRetryPolicy({
          maxRetries: 5,
          initialDelayMs: 10,
          maxDelayMs: 250,
        }),
      });

    return json(result);
  },
});

defineService({
  transferBetweenAccounts: function (args: {
    fromAccountId: string;
    toAccountId: string;
    amount: number;
  }) {
    const { fromAccountId, toAccountId, amount } = args;

    return this.serviceTx(mySchema)
      .retrieve((uow) =>
        uow
          .findFirst("accounts", (b) =>
            b.whereIndex("primary", (eb) => eb("id", "=", fromAccountId)),
          )
          .findFirst("accounts", (b) =>
            b.whereIndex("primary", (eb) => eb("id", "=", toAccountId)),
          ),
      )
      .mutate(({ uow, retrieveResult: [from, to] }) => {
        if (!from || !to) {
          return { ok: false as const };
        }

        if (from.balance < amount) {
          return { ok: false as const };
        }

        // Mutations with version checks
        uow.update("accounts", from.id, (b) => b.set({ balance: from.balance - amount }).check());
        uow.update("accounts", to.id, (b) => b.set({ balance: to.balance + amount }).check());

        return { ok: true as const };
      })
      .build();
  },
});

Retries and side effects

handlerTx() automatically retries the whole transaction on optimistic concurrency conflicts.

That means you should avoid side effects (sending emails, webhooks, etc.) inside service code that runs during the transaction.

Use Durable Hooks for that: they record the side effect request in the same transaction and execute it after commit.

Error handling

Handlers are responsible for error handling, not services:

• Handlers call .execute() and should catch errors
• Services return TxResult via .build() — they don't execute or handle errors
• If .execute() throws, the handler catches it and decides how to respond

This design keeps error handling centralized at the transaction boundary (the handler), where you have the full context to decide how to respond to the user.