Transactions

Transactions ensure that a series of database operations either all succeed or all fail, maintaining data integrity. UQL provides several ways to handle transactions depending on your needs.

Functional Transactions

The functional approach is the most convenient way to run transactions. UQL handles the entire lifecycle automatically.

Using pool.transaction()

Obtains a fresh querier from the pool, runs the callback in a transaction, and releases automatically:

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

const result = await pool.transaction(async (querier) => {
  const user = await querier.findOne(User, { $where: { email: '...' } });
  const profileId = await querier.insertOne(Profile, { userId: user.id, bio: '...' });
  return { userId: user.id, profileId };
});
// The querier is automatically released after the transaction

Use the callback’s querier inside a transaction

Pool-level calls like pool.findMany(...), pool.insertOne(...) or pool.all(...) acquire their own connection, so inside a transaction callback they run outside the transaction: a read will not see its uncommitted changes, and a write will not be rolled back with it. Everything that belongs to the transaction goes through the callback’s querier.

Using querier.transaction()

If you already have an active querier instance (e.g. inside a pool.withQuerier() callback), you can use its transaction method to make just a section of the work transactional, with automatic commit/rollback:

const result = await pool.withQuerier(async (querier) => {
  // non-transactional read
  const user = await querier.findOne(User, { $where: { email: '...' } });

  // transactional section
  return querier.transaction(async () => {
    const userId = await querier.insertOne(User, { name: '...' });
    await querier.insertOne(Profile, { userId, bio: '...' });
    return userId;
  });
});
// the querier is automatically released by withQuerier, even on errors

Composing helpers into one transaction

Functions that take a UniversalQuerier run on whatever the caller hands them, so the caller decides how much is atomic. The helpers stay usable on their own:

import type { UniversalQuerier } from 'uql-orm';
import { raw } from 'uql-orm';

async function debitWallet(db: UniversalQuerier, userId: string, amount: number) {
  await db.updateOneById(Wallet, userId, { balance: raw(`balance - ${amount}`) });
}

async function recordPurchase(db: UniversalQuerier, purchase: Purchase) {
  await db.insertOne(Purchase, purchase);
}

// Independently: two units of work, each committing on its own.
await debitWallet(pool, userId, 20);

// Together: one connection, one commit, and a rollback undoes both.
await pool.transaction(async (querier) => {
  await debitWallet(querier, userId, 20);
  await recordPurchase(querier, purchase);
});

Nothing in the helpers changed between those two calls. That is the point: atomicity is the caller’s decision, not a property baked into each function.

---

Declarative Transactions

Use @Transactional() to wrap a method in a transaction, and currentQuerier() inside it to reach the connection UQL opened. The whole lifecycle is handled: acquiring the querier, beginning the transaction, committing on success, rolling back on error, and releasing the connection either way.

import { Transactional, currentQuerier } from 'uql-orm';
import { User, Profile } from './shared/models/index.js';

export class UserService {
  @Transactional()
  async register(userData: Partial<User>, profileData: Partial<Profile>) {
    const querier = currentQuerier();
    const userId = await querier.insertOne(User, userData);
    await querier.insertOne(Profile, { ...profileData, userId });
  }
}

Nested calls join the transaction already in flight rather than opening a second one, so a @Transactional() method can call another freely; the outermost call owns the commit and the release.

You can specify an isolation level at the decorator:

@Transactional({ isolationLevel: 'serializable' })
async transferFunds(fromId: string, toId: string, amount: number) {
  const querier = currentQuerier();
  // runs under serializable isolation
}

Replaces @InjectQuerier()

Earlier versions received the querier through an @InjectQuerier() parameter. Parameter decorators do not exist in the standard decorator spec (the TC39 proposal for them is separate and still Stage 1), so the querier travels through async-local storage and is read with currentQuerier(). The codemod performs this rewrite.

currentQuerier() throws outside a transaction, which is deliberate: it means a missing @Transactional() fails loudly instead of silently running unmanaged.

---

Imperative Transactions

For scenarios requiring granular control, you can manually manage the transaction lifecycle.

Bind the querier with await using and it is released when the block exits, however it exits:

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

async function countUsers() {
  await using querier = await pool.getQuerier();
  return querier.count(User);
}

await using needs Node 24 (or any bundler that downlevels it, which every current TypeScript setup does). The explicit form below works everywhere.

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

async function registerUser(userData: Partial<User>, profileData: Partial<Profile>) {
  const querier = await pool.getQuerier();

  try {
    await querier.transaction(async () => {
      const userId = await querier.insertOne(User, userData);
      await querier.insertOne(Profile, { ...profileData, userId });
    });
  } finally {
    await querier.release();
  }
}

transaction() still handles commit and rollback here; only the release is yours, which is the whole difference between this and await using.

Prefer it to writing beginTransaction / commitTransaction / rollbackTransaction by hand. A hand-rolled version has to remember that beginTransaction connects before it begins, so a connection failure leaves nothing to roll back: calling rollbackTransaction() anyway throws not a pending transaction, and that error replaces the one you actually wanted to see.

---

Isolation Levels

All transaction methods accept an optional TransactionOptions object to specify the isolation level. This controls the degree of visibility a transaction has to changes made by other concurrent transactions.

Supported Levels

Level	Description
read uncommitted	Allows dirty reads: can see uncommitted changes from other transactions.
read committed	Only sees data committed before the query began. Default for most databases.
repeatable read	Ensures repeated reads within the transaction return the same result.
serializable	Strictest level: transactions execute as if they were serial.

Usage

Pass isolationLevel in the options object to any transaction method:

// Functional: pool.transaction()
const result = await pool.transaction(async (querier) => {
  const account = await querier.findOne(Account, { $where: { id: accountId } });
  await querier.updateOneById(Account, accountId, {
    balance: account.balance - amount,
  });
  return account;
}, { isolationLevel: 'serializable' });

// Functional: querier.transaction()
await querier.transaction(async () => {
  // operations...
}, { isolationLevel: 'repeatable read' });

// Imperative
await querier.beginTransaction({ isolationLevel: 'read committed' });

Database Support

Database	Behavior
PostgreSQL	Full support: uses BEGIN TRANSACTION ISOLATION LEVEL ....
MySQL / MariaDB	Full support: uses SET TRANSACTION ISOLATION LEVEL before START TRANSACTION.
Bun SQL	Full support: passes through to underlying database (Postgres, MySQL, SQLite).
SQLite / LibSQL	Silently ignored (SQLite uses serializable by default).
MongoDB	Silently ignored.

Tip

The isolationLevel option is safely ignored on databases that don’t support it. Your code remains portable across all supported databases without conditional logic.

---

Nesting Behavior

When querier.transaction() or @Transactional() is called inside an existing transaction, UQL reuses the active transaction instead of starting a new one. This makes your code composable: a service method that uses querier.transaction() works correctly whether called standalone or from within another transaction.

const result = await pool.transaction(async (querier) => {
  await querier.insertOne(User, { name: 'Alice' });

  // This nested call reuses the outer transaction (no new BEGIN/COMMIT)
  await querier.transaction(async () => {
    await querier.insertOne(Profile, { userId: 1, bio: '...' });
  });

  return querier.count(User, {});
});
// Both inserts are committed together by the outer transaction

If the inner callback throws, the error propagates to the outer transaction which rolls back everything: both outer and inner operations.

Note

beginTransaction() is the lowest-level API and does not support composable transactions. It throws TypeError('pending transaction') if called inside an existing transaction. Use querier.transaction() or @Transactional() for that use case.

---

Transaction API Reference

Method	Lifecycle	Isolation Level	Nesting
pool.transaction(callback, opts?)	Automatic: acquires, commits/rollbacks, releases.	Yes, via opts	Fresh querier
querier.transaction(callback, opts?)	Semi-Automatic: commits/rollbacks (caller releases).	Yes, via opts	Reuses outer
querier.beginTransaction(opts?)	Manual: caller commits/rollbacks/releases.	Yes, via opts	Throws
querier.commitTransaction()	Commits the active transaction.	N/A	N/A
querier.rollbackTransaction()	Rolls back the active transaction.	N/A	N/A
@Transactional({ isolationLevel? })	Automatic: full lifecycle via decorator.	Yes, via options	Reuses outer

---

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.