Suggestion: using AsyncLocalStorage for transactions

[vdeturckheim]

Problem

First, a disclaimer: AsyncLocalStorage is not available in all supported versions of Node.js yet but this can either be discussed for the future or there are ways of having similar APIs in older version sof Node.js depending on the choices made.

Right now (if I understand the doc well), to build a transaction, one must keep a list of writes and commit all of them at once at the end. So let's imagine in a web server, when the user wants all the db writes linked with the HTTP requests to be in the same transaction, they would have to do something like attaching an array to a request object or something like that, add all the writes to it and commit before the response with prisma.$transaction(...).

Suggested solution

Using an API like AsyncLocalStorage can give the following UX:

prisma.$openTransaction()
...
write_1
...
write_n
prisma.$commitTransaction() <- all the writes are automatically linked with the current transaction

This has a few shortcomings:

• how do you do when you have multiple transactions at once?
• AsyncLocalStorage is still experiemental in node (well, I am working on that Proposal: mark AsyncResource and AsyncLocalStorage as stable nodejs/node#35286 (comment))

Alternatives

N/A

Additional context

So I am mostly trying the water to see if there would be interest and what would be the feedback over here on such feature :)

[psteinroe]

+1

We are currently using TypeORM and really want to migrate to prisma, but are still looking for a lean solution to handle transactions such as https://github.com/odavid/typeorm-transactional-cls-hooked is providing for TypeORM.

[sorenbs]

@vdeturckheim - I don't think you would need AsyncLocalStorage for something like this. As Node is single-threaded, it is safe to create a singleton array per request and append db actions to it. You could then execute them all in a transaction when the request ends. Does that make sense?

@steinroe - When Prisma introduces support for long-running transactions, it should be possible to create a third-party library like cls-hooked. I don't think it is likely that this functionality will be included directly in Prisma. Does that make sense?

[vdeturckheim]

@sorenbs this forces you to always have access to this singleton array, meaning you need to pass a context with all function calls. The point of AsyncLocalStorage is to be able not to do that and provide a UX where the code would be the same inside and outside transactions.

[BertholetDamien]

I agree with the proposition. Without that, it will be impossible for us to migrate or even used Prisma in any production application. It's an essential feature to manage transactions correctly without programmation overhead.

Other than that, Prisma fixes a lot of ORMs commons problems. I'm in love with what you've built, it's just amazing.

Let me know if I can help you to provide this feature, it is really essential for all large applications.
And Prisma deserves to be used by large applications.

[Papooch]

It would be awesome to have this feature, it wouldn't be actually so hard to implement. At the time being, you can use cls-hooked or pure AsyncLocalStorage to leverage async context with transactions (similarly how Sequelize does it) (if you use NestJS, you can use nestjs-cls to do the same)

/* cls setup */
import * as cls from 'cls-hooked'
export const transactionContext = cls.createNamespace('transaction-namespace')

/* some service that initiates the transaction */
    // ...
    await this.prisma.$transaction(async (prisma) => {
        // store the transaction prisma client inside the context
        transactionContext.set('prisma', prisma);
        const result = await serviceThatSouldUseTransaction.doTheThing('something');
       // ... more code
    })

/* service that should use transaction */
   // ...
   doTheThing(input: string) {
       // either reuse prisma client from context or use the default one
       const prisma = transactionContext.get('prisma') ?? this.prisma;
       // if the prisma client was retrieved form the context, the call will be run within the transaction
       return prisma.thing.findMany({ where: { what: input } })
   }

In real application, you could of course abstract it and make it more type safe, but this is the gist.

P.S. You need to enable interactive transactions to be able to pass a callback function to $transaction.

[matthewmueller]

Thanks for your suggestions everyone! We solved this issue a few months ago with Interactive Transactions, which is now in Preview.

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["interactiveTransactions"]
}

You can now pass a function to prisma.$transaction:

await prisma.$transaction(async (prisma) => {
  // 1. Decrement amount from the sender.
  const sender = await prisma.account.update({
    data: {
      balance: {
        decrement: amount,
      },
    },
    where: {
      email: from,
    },
  })
  // 2. Verify that the sender's balance didn't go below zero.
  if (sender.balance < 0) {
    throw new Error(`${from} doesn't have enough to send ${amount}`)
  }
  // 3. Increment the recipient's balance by amount
  const recipient = prisma.account.update({
    data: {
      balance: {
        increment: amount,
      },
    },
    where: {
      email: to,
    },
  })
  return recipient
})

Use it wisely 😉

If you have any feedback on this feature, please let us know in this issue. Now's a good time to give it a go, since we're actively resolving issues in preparation for making it Generally Available.