Transaction¶

A transaction is a sequence of one or more operations that are executed as a single logical unit of work. In databases, transactions provide atomicity, consistency, isolation, and durability (ACID properties), ensuring that changes either complete fully or not at all. This is essential for maintaining data integrity in concurrent and failure-prone environments.

This page explains how to work with transactions in ts-sql-query, covering both high-level helpers for common workflows and low-level methods for fine-grained control. It also covers transaction isolation levels, read/write modes, deferring logic based on the transaction outcome, and storing temporary metadata during a transaction.

High-level transaction management¶

For simple transaction management, you can use the transaction method in the connection object. This method:

begins the transaction before calling the function received by argument
commits the transaction if the resulting promise returns a result
rollbacks the transaction if the resulting promise returns an error

const transactionResult = connection.transaction(async () => {
    const companyId = await connection.insertInto ...
    const customerId = await connection.insertInto ...

    return {companyId, customerId};
});

Low-level transaction management¶

Sometimes a fine-grained control over the transaction is required, In such situations, ts-sql-query offers you the possibility to manually:

begin the transaction

await connection.beginTransaction();

commit the transaction

await connection.commit();

rollback the transaction

await connection.rollback();

When you use these methods, you must ensure the transaction has begun before calling commit or rollback.

Transaction isolation¶

ts-sql-query allows you to indicate the transaction level when you start a new transaction.

Avaliable isolation levels:

Database	`read uncommitted`	`read committed`	`repeatable read`	`snapshot`	`serializable`
MariaDB	YES	YES*	YES	no	YES
MySql	YES	YES*	YES	no	YES
Oracle	no	YES*	no	no	YES
PostgreSql	YES	YES*	YES	no	YES
Sqlite	no	no	no	no	no*
SqlServer	YES	YES*	YES	YES	YES

* Default

Available access modes:

Database	`read write`	`read only`
MariaDB	YES*	YES
MySql	YES*	YES
Oracle	YES*	YES
PostgreSql	YES*	YES
Sqlite	no*	no
SqlServer	no*	no

* Default

You can set the transaction's isolation level by providing an additional argument to the transaction or beginTransaction method with a value created calling the connection.isolationLevel method. This function receives the isolation level as the first argument and the access mode as an optional second argument. You can also provide the access mode as the only argument.

Warning

The Oracle database doesn't support specifying the isolation level and the access mode simultaneously.

const transactionResult = connection.transaction(async () => {
    ...
}, connection.isolationLevel('serializable', 'read only'));

await connection.beginTransaction(connection.isolationLevel('serializable', 'read only'));

Deferring logic during a transaction¶

ts-sql-query allows you to register functions that will be executed at specific points during the current transaction. These hooks let you defer logic until:

just before the transaction is committed (executeBeforeNextCommit)
immediately after a successful commit (executeAfterNextCommit)
immediately after a rollback (executeAfterNextRollback)

This feature is useful for tasks such as cleaning up temporary resources, logging, updating caches, notifying external systems, or triggering other side effects — while ensuring they only run if the transaction reaches a specific outcome (commit or rollback). This guarantees consistency between your database state and any external systems that rely on the success or failure of the transaction.

Each of these methods accepts either a synchronous function (() => void) or an asynchronous one (() => Promise<void>).

connection.executeBeforeNextCommit(async () => {
    // Logic to run just before the commit
    console.log('Before next commit');
});

connection.executeAfterNextCommit(() => {
    // Logic to run after the transaction is successfully committed
    console.log('After next commit');
});

connection.executeAfterNextRollback(() => {
    // Logic to run if the transaction is rolled back
    console.log('After next rollback');
});

Note

These functions are registered only for the next transaction event, and are cleared after use.
They have no effect if called when there is no active transaction.

Transaction metadata¶

You can attach and retrieve metadata specific to the current transaction using getTransactionMetadata, which returns a Map<unknown, unknown> available throughout the transaction's duration.

// Setting a value
connection.getTransactionMetadata().set('my key', 'my value')

// Getting a value
const myKeyValue: unknown = connection.getTransactionMetadata().get('my key')