Add ability to avoid nested transaction by detecting DatabasePoolConnection is a transaction

[romandvoskin]

When writing re-usable code it is often the case that a function accepts a DatabasePoolConnection | DatabaseTransactionConnection, this can be a transaction or database connection. If the function needs to be execute in a transaction AND you want to avoid a nested transaction, it would be helpful to detect if the connection-like object is already a transaction. For reference pg-promise has [txif](https://vitaly-t.github.io/pg-promise/Database.html#txIf) for this purpose

Desired Behavior

Ability to detect that a connection like object is a transaction or the ability to only create a transaction if the given connection-like object is not already a transaction.

Motivation

There are workflows that need need to be transactional AND that use multiple functions requiring passing a connection-like object. It would be useful to only create a transaction if the connection-like object is not already a transaction thus avoiding nested transactions (where a SAVEPOINT is used) and allow an all-or-nothing transaction commit behavior.

Implementation

One idea is to have an overload for transaction like

readonly transaction: <T>(
    handler: TransactionFunction<T>,
    transactionRetryLimit?: number,
    existingConnection?: DatabasePoolConnection | DatabaseTransactionConnection
  ) => Promise<T>;

Another idea is to create a transactionIf function like

readonly transactionIf: <T>(
    handler: TransactionFunction<T>,
    existingConnection: DatabasePoolConnection | DatabaseTransactionConnection,
    transactionRetryLimit?: number
) => Promise<T>;

[gajus]

Sounds like this would be as simple as exposing connection.isTransaction() on the connection object.

[romandvoskin]

Adding connection.isTransaction() would certainly allow the caller to know if a connection is a transaction but it would still require the caller to create a custom function to chose to use an existing connection (that is already a transaction) or call connection.transaction() to create a new transaction. Something like transactionIf removes the need to expose isTransaction() and would use internal logic instead. transactionIf could be a feature for Slonik, for when nested transactions are undesired.

[gajus]

transactionIf as a name is super confusing. I think that's what threw me off from the idea.

I could see something like continueTransactionOrStartNew / useOrStartTransaction / ensureTransaction as a valuable addition.

[romandvoskin]

I'm glad you like the idea. The names you suggested are all good.

…

On Wed, Mar 27, 2024, 7:07 PM Gajus Kuizinas ***@***.***> wrote: transactionIf as a name is super confusing. I think that's what threw me off from the idea. I could see something like continueTransactionOrStartNew / useOrStartTransaction / ensureTransaction as a valuable addition. — Reply to this email directly, view it on GitHub <#556 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA2MQHMOHKTHC7JPL7JUOSTY2NGLDAVCNFSM6AAAAABFLGHAMWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAMRUGEZDQNZWHA> . You are receiving this because you authored the thread.Message ID: ***@***.***>