Feature Request: Transactional: Support Promises in `withTransaction` for Batch Query

[pknameer]

Current Behavior

The withTransaction function in transaction-host currently accepts a callback function that is executed within the transaction, and its result is returned.

Problem Statement

When using withTransaction, executing multiple queries results in each query being processed individually. This behavior is limiting in scenarios such as working with Row-Level Security (RLS), where configuration variables need to be set consistently across queries. Without batch execution, these variables must be set for each query, which increases complexity and overhead.

Proposed Solution

Allow withTransaction to accept promises, similar to how prisma.$transaction operates. By doing so, all queries in a batch can share the same RLS configuration, improving performance and reducing redundancy.

[Papooch]

Thank you for the suggestion, however, the main goal of @nestjs-cls/transactional is to provide a mechanism to span a single transaction across multiple components without the need to pass its reference around.

If you want to execute multiple queries within a single transaction in one statement, then I fail to see the role that this library plays in that scenario - you can simply use the native transaction method on the regular Prisma client.

All in all, the way @nestjs-cls/transactional works, is by storing some reference to the transaction in the CLS context (what that reference is, is dependent on the adapter), so the library we integrate with must expose this reference to us, which is only possible with "interactive" transactions in the case of Prisma.

---

The only way to support this API within TransactionHost is when Prisma itself starts allowing nested transactions

[pknameer]

Thanks for the prompt response and for the clarification!

I understand that the primary focus of the library is to provide a way to span a single transaction across multiple components using CLS. However, the motivation behind this feature request is to improve support for batching queries within a transaction, particularly in scenarios like RLS, where setting configuration variables consistently across multiple queries is critical.

Just to clarify, I do not use Prisma directly in my project. Instead, I rely on the proxying feature that transaction-host provides, where Prisma is used only when there is no active transaction. With that in mind, I was hoping for a way to leverage Prisma’s $transaction behavior for batching queries, even when a transaction is active, to avoid having to manage Prisma transactions manually.

The idea here is to extend the functionality of withTransaction (or introduce a new function) so that when no active transaction exists, it behaves like Prisma's $transaction and batches queries together. When there is an active transaction, it could provide the same batching behavior while staying within the context of that transaction.

This would allow transaction-host to support both single-query transaction spans and multi-query batching scenarios, maintaining consistency and reducing the need to switch between different methods.

I understand if this is outside the scope of the library, but I wanted to share this perspective in case it aligns with any future enhancements. Thanks again for considering this suggestion!

[Papooch]

I was hoping for a way to leverage Prisma’s $transaction behavior for batching queries, even when a transaction is active, to avoid having to manage Prisma transactions manually

It would be great if Prisma supported this natively, then we wouldn't need to have this discussion. However, it supports either interactive xor batching transactions and you can't nest them in each other.

---

You might be able to get the behaviour you want using a custom @nestjs-cls/transactional adapter with some custom logic, but it is definitely out of scope of the "official" Prisma adapter.