Transactions

A transaction is a unit of work that either lands completely or not at all. UQL has three ways to run one, and they differ in exactly one thing: who owns the commit and who owns the connection.

	Commit / rollback	Connection	Called inside an active transaction
pool.transaction(cb, opts?)	UQL	UQL acquires and releases	takes a fresh querier, so a separate transaction
querier.transaction(cb, opts?)	UQL	yours	joins it, no second BEGIN
beginTransaction + commit / rollback	yours	yours	throws pending transaction

Start with the first. Reach for the others when you already hold a querier, or when the commit point is a decision rather than the end of a block.

pool.transaction()

Takes a connection, runs the callback in a transaction, commits on return, rolls back on throw, and releases either way:

import { pool } from './uql.config.js';
import { Profile, User } from './shared/models/index.js';

const userId = await pool.transaction(async (querier) => {
  const id = await querier.insertOne(User, { name: 'Alice' });
  await querier.insertOne(Profile, { userId: id, bio: '...' });
  return id;
});

Everything atomic goes through the callback’s querier

pool.findMany(...), pool.insertOne(...) and pool.all(...) acquire their own connection, so inside the callback they run outside the transaction: a read will not see its uncommitted changes, and a write will not be rolled back with it.

Helpers that take a UniversalQuerier run on whatever the caller hands them, so the caller decides how much is atomic without any helper changing.

querier.transaction()

When you already hold a querier, this makes a section of its work atomic. It commits and rolls back; releasing stays with whoever acquired the connection:

const userId = await pool.withQuerier(async (querier) => {
  // a read with nothing to roll back, on the same pinned connection
  const taken = await querier.count(User, { $where: { email } });
  if (taken) {
    return undefined;
  }

  return querier.transaction(async () => {
    const id = await querier.insertOne(User, { name: 'Alice' });
    await querier.insertOne(Profile, { userId: id, bio: '...' });
    return id;
  });
});

A querier.transaction() inside an active one joins it instead of opening a second, so a helper that is atomic on its own is safe to call from inside a larger transaction. The outermost call owns the commit, and a throw anywhere rolls back everything.

Owning the connection yourself

await using releases the querier when the block exits, however it exits, so an early return or a throw between acquiring and releasing cannot leak a connection:

import { pool } from './uql.config.js';
import { Profile, User } from './shared/models/index.js';

async function registerUser(user: Partial<User>, profile: Partial<Profile>) {
  await using querier = await pool.getQuerier();

  await querier.transaction(async () => {
    const userId = await querier.insertOne(User, user);
    await querier.insertOne(Profile, { ...profile, userId });
  });
}

Every runtime UQL supports has it (Node 24+, Bun, Deno), and every current TypeScript setup downlevels it. A try / finally calling querier.release() is the same thing written out. Either way transaction() still handles commit and rollback; only the release is yours.

Releasing with a transaction still open rolls it back and warns, so a path you forgot cannot strand a connection with a live BEGIN on it. Roll back explicitly where you meant to and the warning stays quiet, and if that rollback fails the connection is discarded instead of reused. A released querier is finished either way: using it again throws.

Driving begin / commit / rollback

Worth the extra lines only when the commit point is a decision: a shortfall, a stale precondition, a step in a saga. Rolling back is the answer there, not a failure, so there is no exception to throw at a callback.

import { pool } from './uql.config.js';
import { Item, Order } from './shared/models/index.js';

async function placeOrder(
  itemId: number,
  quantity: number,
  customerId: number,
) {
  const querier = await pool.getQuerier();

  try {
    await querier.beginTransaction();

    const item = await querier.findOneById(Item, itemId, {
      $select: { stock: true, price: true },
      $lock: true,
    });
    const available = item?.stock ?? 0;

    if (available < quantity) {
      await querier.rollbackTransaction();
      return { placed: false, available };
    }

    await querier.updateOneById(Item, itemId, { stock: available - quantity });
    await querier.insertOne(Order, {
      customerId,
      status: 'pending',
      amount: quantity * item!.price!,
    });
    await querier.commitTransaction();

    return { placed: true, available: available - quantity };
  } catch (error) {
    await querier.rollbackTransaction();
    throw error;
  } finally {
    await querier.release();
  }
}

The catch needs no hasOpenTransaction check: rollbackTransaction() does nothing when none is open, so a connection that failed on beginTransaction cannot report that instead of the real error. commitTransaction() is strict, because a caller who believes their work was committed has to hear that it was not.

Isolation levels

All three methods take an isolationLevel, which sets how much of other concurrent transactions this one can see:

await pool.transaction(
  async (querier) => {
    /* ... */
  },
  { isolationLevel: 'serializable' },
);

Level
read uncommitted	Dirty reads: can see uncommitted changes from other transactions.
read committed	Only data committed before the query began. The default on most databases.
repeatable read	Repeated reads inside the transaction return the same rows.
serializable	Strictest: transactions behave as if they ran one after another.

PostgreSQL, MySQL, MariaDB and Bun SQL support all four. SQLite, LibSQL and MongoDB ignore the option: SQLite is serializable already, and MongoDB has no equivalent knob.

Set it on each transaction that needs it rather than assuming a connection carries it. MySQL and MariaDB apply the level as a statement of its own ahead of START TRANSACTION, so if that START TRANSACTION then fails, the level stays applied to whatever the pooled connection runs next.

Locking rows you are about to write

On the default level, nothing stops two transactions reading the same row and both writing it. A read-modify-write needs the read itself to take a lock:

await pool.transaction(async (querier) => {
  const item = await querier.findOneById(Item, id, {
    $select: { stock: true },
    $lock: true,
  });
  await querier.updateOneById(Item, id, { stock: item!.stock! - 1 });
});

See Row Locking for the wait policies, the SKIP LOCKED work-queue pattern, and which engines support it.

Next Steps

• Pool vs. Querier: Which entry point a unit of work needs.
• Lifecycle Hooks: Hooks run on the same querier, inside your transaction.
• Raw SQL: Raw statements participate in the active transaction.
• Streaming: Long-lived reads and connection lifetime.