Transaction

Hight-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};
});

The transaction method have two overloads:

• transaction<T>(fn: () => Promise<T>[]): Promise<T[]>
• transaction<T>(fn: () => Promise<T>): Promise<T>

Low-level transaction management

Sometimes a fine-grain control over the transaction is required, in that situations ts-sql-query offer you the possibility to manually:

• begin the transaction

await connection.beginTransaction();

• commit the transaction

await connnection.commit();

• rollback the transaction

await connection.rollback();

When you use these methods, you must ensure the transaction begin before call commit or rollback.

Defering execution till transaction ends

You can defer the execution of a logic till the end of the transaction. This defered logic can be set calling the executeAfterNextCommit or executeAfterNextRollback of the ts-sql-query connection at any moment of the application execution; the only condition is there must be an active transaction. ts-sql-query offer as well defer the execution of a logic till just before the commit calling executeBeforeNextCommit.

connection.executeAfterNextCommit(async () => {
    // Logic defered till the commit is executed
    console.log('After next commit')
})

connection.executeAfterNextRollback(async () => {
    // Logic defered till the rollback is executed
    console.log('After next rollback')
})

connection.executeBeforeNextCommit(async () => {
    // Logic defered till just before the commit is executed
    console.log('Before next commit')
})

Note: The provided function can be a sync function that returns void or an async function that returns a promise of void.

Transaction metadata

You can set additional information to be consumed in other parts of the application during the same transaction. For this, you can call getTransactionMetadata of the ts-sql-query connection at any moment of the application execution; the only condition is there must be an active transaction. The getTransactionMetadata method will return a Map<unknown, unknown> where you can get or set values.

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

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