Transactions

Ensure data integrity with atomic database transactions.
Stabilize provides a unified API for transactions across PostgreSQL, MySQL, SQLite, SQL Server, and MongoDB. On MongoDB, transactions require a replica set.

Basic Transaction

transaction.ts

import { orm } from "./db";

await orm.transaction(async (txClient) => {
  const userRepo = orm.getRepository(User);
  const profileRepo = orm.getRepository(Profile);
  
  // All operations use the same transaction client
  const user = await userRepo.create(
    { name: "Lwazii Dlamini", email: "lwazicd@icloud.com" },
    {},
    txClient
  );
  
  await profileRepo.create(
    { userId: user.id, bio: "Hello Stabilize!" },
    {},
    txClient
  );
  
  // If any operation fails, everything is rolled back
});

Error Handling

transaction-error.ts

try {
  await orm.transaction(async (txClient) => {
  
    await userRepo.create(userData, {}, txClient);
    await profileRepo.create(profileData, {}, txClient);
  });
  console.log("Transaction committed successfully");
} catch (error) {
  console.error("Transaction rolled back:", error);
}

Nested Transactions

transaction() takes a single argument — the callback. There is no second parameter for an existing client. A nested call detects that it is already inside a transaction and reuses it, so the inner block joins the outer transaction rather than starting a new one:

nested-transaction.ts

await orm.transaction(async (txClient) => {
  // Outer transaction
  await orm.transaction(async (nestedTxClient) => {
    // nestedTxClient is the same client - this joins the outer transaction
    await doSomething(nestedTxClient);
  });

  // A throw anywhere inside rolls back everything, inner and outer
});

No savepoints

Because a nested call reuses the outer transaction, there are no savepoints and no partial rollback. An inner failure takes down the whole transaction — you cannot catch it and keep the outer work. If you need that granularity, restructure so the optional work is a separate transaction.

Supported Databases

client.ts

await dbClient.transaction(async (txClient) => {
  // Works with PostgreSQL, MySQL, SQLite, SQL Server, and MongoDB.
  await repo.create(data, {}, txClient);
});

The callback API is identical everywhere, but the mechanism underneath is not, and the difference matters if you drop to a lower level:

Dialect	How the transaction is held
PostgreSQL	One connection checked out of the `pg` pool for the duration, then `BEGIN` / `COMMIT` on it
MySQL / MariaDB	One connection checked out of the `mysql2` pool, then `START TRANSACTION`
SQLite	The single shared SQLite connection (`bun:sqlite` or `node:sqlite`), with `BEGIN` / `COMMIT`
SQL Server	An `mssql` `Transaction` object, and every statement issued through a `Request` built from it

That last row is the one to be careful with. T-SQL has no BEGIN statement to send as text, so a transaction is a server-side object bound to one connection — a Request built from the pool would run on an unrelated connection and commit on its own, silently escaping the transaction. Passing the txClient the callback gives you is what keeps statements inside it.

API Reference

client.ts

// DBClient
async transaction<T>(callback: (txClient: DBClient) => Promise<T>): Promise<T>

// Usage example:
await db.transaction(async (tx) => {
  // use tx for all DB operations within this transaction
});