Skip to main content

Transactions

Hysteria ORM provides robust transaction support for SQL databases, allowing you to group multiple operations into a single atomic unit. Transactions ensure data consistency and integrity, supporting features like rollback, isolation levels, nested, concurrent, and global transactions.

Setup​

import { SqlDataSource, defineModel, col } from "hysteria-orm";

const User = defineModel("users", {
  columns: {
    id: col.increment(),
    name: col.string(),
    email: col.string(),
  },
});

const sql = new SqlDataSource({ type: "postgres" /* ... */ });
await sql.connect();

Basic Usage​

Use transaction with a callback to run operations within a transaction. If an error is thrown, the transaction is rolled back automatically.

// Automatically commits the transaction
await sql.transaction(async (trx) => {
  await sql
    .from(User, { trx })
    .insert({ name: "John", email: "john@test.com" });
});

If an error occurs, changes are not committed:

await sql.transaction(async (trx) => {
  await sql
    .from(User, { trx })
    .insert({ name: "John", email: "john@test.com" });
  throw new Error("Test error"); // Transaction is rolled back automatically
});

Raw transaction without models:

await sql.transaction(async (trx) => {
  await trx.sql.from("users");
});

Custom Isolation Level​

You can specify a transaction isolation level:

await sql.transaction(
  async (trx) => {
    await sql
      .from(User, { trx })
      .insert({ name: "John", email: "john@test.com" });
  },
  { isolationLevel: "SERIALIZABLE" },
);

Manual Transaction Control​

Start, commit, and rollback transactions manually:

const trx = await sql.transaction();
await sql.from(User, { trx }).insert({ name: "John", email: "john@test.com" });
await trx.commit();

Rollback on error:

const trx = await sql.transaction();
try {
  await sql
    .from(User, { trx })
    .insert({ name: "John", email: "john@test.com" });
  throw new Error("fail");
  await trx.commit();
} catch {
  await trx.rollback();
}

Nested Transactions (Savepoints)​

Nested transactions are implemented using database savepoints on the same connection as the outer transaction (no new connections are opened). This enables partial rollbacks without affecting the outer scope.

  • No new connections: nested transactions reuse the outer transaction's connection
  • Commit: releases the savepoint (does not commit the outer transaction)
  • Rollback: rolls back to the savepoint (does not roll back the outer transaction)

Savepoint names are stable and driver-safe: sp_<nestingDepth>_<transactionIdPrefix> (for example: sp_2_AB12CD34).

const outerTrx = await sql.transaction();
await sql
  .from(User, { trx: outerTrx })
  .insert({ name: "John", email: "john@test.com" });

// Creates a savepoint on the same connection
const innerTrx = await outerTrx.savePoint();
try {
  await sql
    .from(User, { trx: innerTrx })
    .insert({ name: "Jane", email: "jane@test.com" });
  await innerTrx.commit(); // RELEASE SAVEPOINT <name>
} catch (e) {
  await innerTrx.rollback(); // ROLLBACK TO (savepoint)
  throw e;
}

await outerTrx.commit(); // commits the top-level transaction and releases the connection

Concurrent Transactions​

You can run multiple transactions in parallel on different connections:

const trx1 = await sql.transaction();
const trx2 = await sql.transaction();
await sql
  .from(User, { trx: trx1 })
  .insert({ name: "John", email: "john@test.com" });
await sql
  .from(User, { trx: trx2 })
  .insert({ name: "Jane", email: "jane@test.com" });
await trx1.commit();
await trx2.commit();

Global Transactions​

For integration tests, you can use global transactions on a SqlDataSource instance. Global transactions are not advised for production use.

await sql.startGlobalTransaction();
// All queries on this instance automatically use the global transaction
await sql.from(User).insert({ name: "John", email: "john@test.com" });
await sql.commitGlobalTransaction();

Rollback global transaction:

await sql.startGlobalTransaction();
await sql.from(User).insert({ name: "John", email: "john@test.com" });
await sql.rollbackGlobalTransaction();

Error Handling and Transaction State​

You can enforce error throwing if a transaction is inactive:

const trx = await sql.transaction();
await trx.rollback();
await trx.rollback({ throwErrorOnInactiveTransaction: true }); // Throws HysteriaError

Or suppress errors:

const trx = await sql.transaction();
await trx.rollback();
await trx.rollback({ throwErrorOnInactiveTransaction: false }); // No error

Notes​

  • Nested transactions never release the connection but only save points; only the top-level transaction releases it on commit/rollback.
  • Always pass { trx } as the second argument to sql.from() to ensure operations are part of the transaction.
  • Use isolation levels for advanced consistency requirements.

See also: