---
url: /guide/transactions.md
description: >-
  Database transactions with $transaction, nested transactions,
  ensureTransaction, isInTransaction, and testTransaction.
---

# Transactions

All queries within a transaction are executed on the same database connection and run the entire set of queries as a single unit of work.
Any failure will mean the database will rollback any queries executed on that connection to the pre-transaction state.

You can define a code to be run on commit, see [after-commit hooks](/guide/hooks.html#after-commit-hooks) and a standalone [$afterCommit](#aftercommit).

## transaction

[//]: # "has JSDoc"

In Orchid ORM the method is `$transaction`, when using `pqb` on its own it is `transaction`.

`COMMIT` happens automatically after the callback was successfully resolved, and `ROLLBACK` is done automatically if the callback fails.

Let's consider the case of transferring money from one user to another:

```ts
export const transferMoney = async (
  fromId: number,
  toId: number,
  amount: number,
) => {
  try {
    // db.$transaction returns data that is returned from the callback
    // result here is senderRemainder
    const result = await db.$transaction(async () => {
      const sender = await db.user.find(fromId);
      const senderRemainder = sender.balance - amount;
      if (senderRemainder < 0) {
        throw new Error('Sender does not have enough money');
      }

      await db.user.find(fromId).decrement({
        balance: amount,
      });
      await db.user.find(toId).increment({
        balance: amount,
      });

      return senderRemainder;
    });
  } catch (error) {
    // handle transaction error
  }
};
```

It performs 3 queries in a single transaction: load sender record, decrement sender's balance, increment receiver's balance.

If sender or receiver record doesn't exist, it will throw `NotFound` error, and there is an error thrown when sender's balance is too low.
In such case, the transaction will be rolled back and no changes will be applied to the database.

Internally, ORM relies on [AsyncLocalStorage](https://nodejs.org/api/async_context.html#class-asynclocalstorage) feature of node.js,
it allows passing the transaction object implicitly. So that any query that is done inside of callback, will run inside a transaction.

## nested transactions

[//]: # "has JSDoc"

Transactions can be nested one in another.
The top level transaction is the real one,
and the nested ones are emulated with [savepoint](https://www.postgresql.org/docs/current/sql-savepoint.html) instead of `BEGIN`
and [release savepoint](https://www.postgresql.org/docs/current/sql-release-savepoint.html) instead of `COMMIT`.

Use [ensureTransaction](#ensuretransaction) to run all queries in a single transaction.

```ts
const result = await db.$transaction(async () => {
  await db.table.create(...one);

  const result = await db.$transaction(async () => {
    await db.table.create(...two);
    return 123;
  });

  await db.table.create(...three);

  return result;
});

// result is returned from the inner transaction
result === 123;
```

If the inner transaction throws an error, and it is caught by `try/catch` of outer transaction,
it performs [rollback to savepoint](https://www.postgresql.org/docs/current/sql-rollback-to.html)
and the outer transaction can continue:

```ts
class CustomError extends Error {}

await db.$transaction(async () => {
  try {
    await db.$transaction(async () => {
      throw new CustomError();
    });
  } catch (err) {
    if (err instanceof CustomError) {
      // ignore this error
      return;
    }
    throw err;
  }

  // this transaction can continue
  await db.table.create(...data);
});
```

If the error in the inner transaction is not caught, all nested transactions are rolled back and aborted.

## SQL session context in transactions

Pass `role` and `setConfig` to `$transaction` to apply Postgres SQL session context for the whole transaction.
This is useful for row-level security when request DB work should run in one explicit transaction:

```ts
await db.$transaction(
  {
    role: 'app_user',
    setConfig: {
      'app.tenant_id': tenantId,
      'app.user_id': userId,
    },
  },
  async () => {
    const project = await db.project.find(projectId);

    await db.project.find(projectId).update({ lastViewedAt: new Date() });

    return project;
  },
);
```

`role` is applied with transaction-local Postgres role semantics, and `setConfig` values are available to policies through `current_setting(...)`.
The context lasts until the top-level transaction commits or rolls back.

Nested transactions can temporarily override the parent transaction role and config:

```ts
await db.$transaction(
  {
    role: 'app_user',
    setConfig: { 'app.tenant_id': tenantId },
  },
  async () => {
    await db.project.find(projectId);

    await db.$transaction(
      {
        role: 'project_admin',
        setConfig: { 'app.audit_reason': 'manual-review' },
      },
      async () => {
        await db.project.find(projectId).update({ reviewedAt: new Date() });
      },
    );

    // Back to role app_user and the outer transaction config.
    await db.project.find(projectId);
  },
);
```

A nested transaction replaces the parent role only while its callback runs, and shallow-merges its `setConfig` over the parent config.
When the nested transaction finishes successfully, Orchid restores the parent transaction context before the outer callback continues.
If the nested transaction rolls back to its savepoint, Postgres rolls back the nested transaction-local role/config changes and Orchid restores its tracked parent context.

This is different from `$withOptions({ role, setConfig }, cb)`.
`$withOptions` is query-scoped: it reconciles SQL session state around each query, so it is useful when the work is not intentionally wrapped in one transaction.
Transaction options are applied once for a transaction and are lower overhead for request-scoped RLS work that is already transaction-bound.

Explicit transactions opened inside a `$withOptions` callback still inherit the query-scoped SQL session context:

```ts
await db.$withOptions(
  {
    role: 'app_user',
    setConfig: { 'app.tenant_id': tenantId },
  },
  async () => {
    await db.$transaction(async () => {
      // This query uses the $withOptions role and config.
      await db.project.find(projectId).update({ lastViewedAt: new Date() });
    });
  },
);
```

This works consistently across supported adapters (`node-postgres`, `postgres-js`, and Bun SQL) even though their internal connection handling differs.

For query-scoped SQL session options, see [$withOptions](/guide/orm-methods.html#withoptions).

## ensureTransaction

[//]: # "has JSDoc"

Use the `$ensureTransaction` when you want to ensure the sequence of queries is running in a transaction, but there is no need for Postgres [savepoints](https://www.postgresql.org/docs/current/sql-savepoint.html).

```ts
async function updateUserBalance(userId: string, amount: number) {
  await db.$ensureTransaction(async () => {
    await db.transfer.create({ userId, amount })
    await db.user.find(userId).increment({ balance: amount })
  })
}

async function saveDeposit(userId: string, deposit: { ... }) {
  await db.$ensureTransaction(async () => {
    await db.deposit.create(deposit)
    // transaction in updateUserBalance won't be started
    await updateUserBalance(userId, deposit.amount)
  })
}
```

## isInTransaction

Returns `true` or `false` to check if inside a transaction.

Ignores opened [test transaction](#testtransaction).

```ts
db.$isInTransaction(); // -> false

db.$transaction(async () => {
  db.$isInTransaction(); // -> true
});
```

## $afterCommit

[//]: # "has JSDoc"

Schedules a hook to run after the outermost transaction commits:

```ts
await db.$transaction(async () => {
  await db.table.create(data);
  await db.table.where({ ...conditions }).update({ key: 'value' });

  db.$afterCommit(() => {
    // can be sync or async
    console.log('after commit');
  });
});
```

If used outside the transaction, the hook will be executed almost immediately, on the next microtask:

```ts
db.$afterCommit(async () => {
  // can be sync or async
  console.log('after commit');
});
```

If the callback has no `try/catch` and throws an error,
this will cause `uncaughtException` if the callback is sync and `unhandledRejection` if it is async.

## recoverable query

[//]: # "has JSDoc"

Whenever a query in a transaction fails, the transaction is transitioned to a failed state, no further queries are possible.

Use [catchUniqueError](/guide/error-handling.html#catchuniqueerror) to handle uniqueness errors,
the following examples do not use it to illustrate error handling.

```ts
// This transaction is going to fail
db.$transaction(async () => {
  try {
    // imagine it fails because the username is already taken
    await db.user.insert({ username: 'taken' });
  } catch (err) {
    // even though the query is wrapped in a try-catch, the transaction fails anyway
  }
});
```

You can use `catch` method on a query instead of `try-catch` to surpass the problem.
The following transaction won't fail:

```ts
// Transaction succeeds
db.$transaction(async () => {
  const result = await db.user
    .insert({ username: 'taken' })
    .catch(() => 'failed');

  if (result === 'failed') {
    await db.user.insert({ username: 'another' });
  }
});
```

This is because when using `catch` method, `OrchidORM` will wrap the query with a savepoint.

Alternatively, you can use the `recoverable()` method:

```ts
// Transaction succeeds
db.$transaction(async () => {
  try {
    await db.user.insert({ username: 'taken' }).recoverable();
  } catch (err) {
    await db.user.insert({ username: 'another' });
  }
});
```

## testTransaction

`Orchid ORM` has a special utility to wrap your tests in transactions which are rolled back after each test.
This allows to keep the database state unchanged between test runs.
In such way, tests runs very fast, because data is never saved to disc, all data changes are handled by Postgres in memory.

Create a separate file for test utilities, let's say it is located in `src/lib/test-utils.ts`, and export such "hook":

```ts
// src/lib/test-utils.ts
import { testTransaction } from 'orchid-orm';
import { db } from './path-to-your-db';

// `setup` creates data for all tests of a `describe` inside its test transaction
export const useTestDatabase = (setup?: () => unknown) => {
  const all = testTransaction.hooks(db, { setup });
  const each = testTransaction.hooks(db);

  beforeAll(all.start);
  beforeEach(each.start);
  afterEach(each.rollback);
  afterAll(all.close);
};
```

`testTransaction.hooks` returns functions for a pair of test hooks:

* `start` starts a new test transaction and runs the `setup` inside it
* `rollback` rolls back the test transaction started by `start` of the same hooks
* `close` performs a rollback, and when no test transaction is left open, it closes `db`

Now, we can use it in our tests in such way:

```ts
import { useTestDatabase } from '../lib/test-utils';
import { db } from '../path-to-your-db';

describe('title', () => {
  useTestDatabase();

  it('should create a record', async () => {
    await db.table.create({ ...data });

    const count = await db.table.count();
    // record was successfully created:
    expect(count).toBe(1);
  });

  it('should run a nested transaction', async () => {
    // the record from the previous test disappeared
    expect(await db.table.count()).toBe(0);

    // nested transactions works just fine
    await db.$transaction(async () => {
      await db.table.create({ ...data });
    });

    // record in a nested transaction was saved and is available until the end of this `it` test block
    const count = await db.table.count();
    expect(count).toBe(1);
  });
});
```

Additionally, you can use `useTestDatabase` in the nested `describe` with a `setup` to have a data created only in the scope of this `describe`:

```ts
import { useTestDatabase } from './test-utils';

describe('outer', () => {
  useTestDatabase();

  it('should have no records', async () => {
    expect(await db.table.count()).toBe(0);
  });

  describe('inner', () => {
    useTestDatabase(() => db.table.create(data));

    // all `it` block in the inner describe will have a created record in the db
    it('should have the created record', async () => {
      expect(await db.table.count(1)).toBe(1);
    });
  });

  // data was cleared in the end of inner describe
  it('should have no records again', async () => {
    expect(await db.table.count()).toBe(0);
  });
});
```

Check out [test factories](/guide/test-factories), a perfect pair with `testTransaction` to use for testing.

### after-commit hooks in tests

A rollback waits for running [after-commit hooks](/guide/hooks#after-commit-hooks), so that their queries are rolled back with the test.
Use an async hook and await its work:

```ts
db.$afterCommit(async () => {
  await db.emailLog.create({ id: 1 });
});
```

A hook that never ends holds the rollback, see [teardown timeout](#teardown-timeout).

### timed-out hooks

A hook or test can keep running after its timeout, during later tests.
With `testTransaction.hooks`:

* `rollback` and `close` end the test transaction of their own `start`.
* They wait for `setup`, queries in flight, and running after-commit hooks.
* Create data for a `describe` in `setup` rather than in a separate `beforeAll`.

Late queries are rejected when possible, but may run in another test transaction or outside of one.

If a test transaction is rolled back while transactions started inside it are still open, including inner test transactions, `db` and instances sharing its adapter are blocked until the process ends.
Their queries reject with a `TestTransactionError` of kind `blocked`; its `cause` tells what happened.
Stop the test run on such errors, for example with the runner's `bail` option.

### teardown timeout

Set `teardownTimeout` in milliseconds to limit how long `rollback` or `close` of `testTransaction.hooks` may take.
Choose a value shorter than your test runner's hook timeout:

```ts
const each = testTransaction.hooks(db, { teardownTimeout: 4000 });

beforeEach(each.start);
afterEach(each.rollback);
```

Without this option, teardown has no time limit.

When the limit is exceeded, the operation rejects with a `TestTransactionError` of kind `teardownTimeout` and [blocks `db`](#timed-out-hooks).
The teardown itself isn't cancelled.

### start, rollback, and close

`testTransaction.start`, `testTransaction.rollback`, and `testTransaction.close` are a lower-level API for hooks that run strictly one after another:

```ts
beforeEach(async () => {
  await testTransaction.start(db);
});

afterEach(async () => {
  await testTransaction.rollback(db);
});
```

* `testTransaction.rollback(db)` rolls back the latest started test transaction, and `testTransaction.close(db)` also closes `db` when no test transaction is left open.
* `testTransaction.start` resolves with the started test transaction, its `rollback()` and `close()` target that test transaction.
  `start` accepts a `signal` to cancel the start before its transaction has started.
  A rollback of a test transaction with open inner ones rolls them back as well and blocks `db`, see [timed-out hooks](#timed-out-hooks).
* When test transactions of several instances share an adapter, roll them back in the reverse order of their start, whichever instance started them.
  Within one `describe`, Jest and Bun run `afterEach` hooks in the order of declaration, Vitest in reverse by default.

## transaction options

### isolation level

By default, transaction isolation level is `SERIALIZABLE`, it is the strictest level and suites most cases.

You can choose other level by passing it as a string to `$transaction` (this is ignored for nested transactions):

```ts
// allowed levels:
type IsolationLevel =
  | 'SERIALIZABLE'
  | 'REPEATABLE READ'
  | 'READ COMMITTED'
  | 'READ UNCOMMITTED';

db.$transaction('REPEATABLE READ', async () => {
  // ...
});
```

### read only, deferrable

Transactions in Postgres can accept `READ WRITE` | `READ ONLY` and `[ NOT ] DEFERRABLE` options ([Postgres docs](https://www.postgresql.org/docs/current/sql-set-transaction.html)).

You can set it by passing such object (this is ignored for nested transactions):

```ts
db.$transaction(
  {
    // optionally, you can set level here:
    // level: 'REPEATABLE READ',
    readOnly: true,
    deferrable: true,
  },
  async () => {
    // ...
  },
);
```

### log transaction queries

Pass `{ log: true }` to the transaction to turn logging on for all its queries, including `BEGIN` and `COMMIT`.

Note that setting log on a transaction will override the log setting of a particular query.

```ts
await db.$transaction({ log: true }, async () => {
  await db.table.insert(data);
  // raw SQL queries will also be logged
  await db.$query`SELECT 1`;
});
```

## query for

### forUpdate

To be used in select queries inside the transaction adds the `FOR UPDATE` table lock modifier.

```ts
await db.$transaction(async () => {
  await db.table.forUpdate();

  // Can specify tables for the lock (FOR UPDATE OF table list)
  await db.table.forUpdate(['someTable', 'otherTable']);
});
```

### forNoKeyUpdate

To be used in select queries inside the transaction adds the `FOR NO KEY UPDATE` table lock modifier.

```ts
await db.$transaction(async () => {
  await db.table.forUpdate();

  // Can specify tables for the lock (FOR NO KEY UPDATE OF table list)
  await db.table.forNoKeyUpdate(['someTable', 'otherTable']);
});
```

### forShare

To be used in select queries inside the transaction adds the `FOR SHARE` table lock modifier.

```ts
await db.$transaction(async () => {
  await db.table.forUpdate();

  // Can specify tables for the lock (FOR SHARE OF table list)
  await db.table.forShare(['someTable', 'otherTable']);
});
```

### forKeyShare

To be used in select queries inside the transaction adds the `FOR KEY SHARE` table lock modifier.

```ts
await db.$transaction(async () => {
  await db.table.forUpdate();

  // Can specify tables for the lock (FOR KEY SHARE OF table list)
  await db.table.forKeyShare(['someTable', 'otherTable']);
});
```

### skipLocked

This method can be used after a lock mode has been specified with either `forUpdate` or `forShare`, and will cause the query to skip any locked rows, returning an empty set if none are available.

```ts
await db.$transaction(async () => {
  await db.table.forUpdate().skipLocked();
});
```

### noWait

This method can be used after a lock mode has been specified with either forUpdate or forShare, and will cause the query to fail immediately if any selected rows are currently locked.

```ts
await db.$transaction(async () => {
  await db.table.forUpdate().noWait();
});
```