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.

[karljv]

Except it is not solved for larger applications that leverage service-repository architecture. I need to pass the transaction between multiple modules which can be achieved by passing the tx variable between different services and repositories, but:

1. I do not want every repository function to have a tx = null argument
2. I do not want to have a prisma variable on the service level at all, so async (tx) => is against my design

What @Papooch suggested works, but I would really love a good old beginTransaction and commit/rollback solution from you guys, thank you. I was actually very disappointed, when I was building our app with Prisma and suddenly realized that this is not an option.

[c-andrey]

I'm trying to do this context using ALS, but i havent been successful, someone has done it and can give me a light?

[fuvidani]

@c-andrey I've been trying to implement transparent transactions with Prisma and ALS for our organization too. I'm using #17215 as basis. We use the NestJs framework along with NestJs-CLS from @Papooch. Essentially, the idea is to create a Proxy around the PrismaClient and monkey-patching the $transaction operation. We also have a @Transactional method decorator in our NestJs code that accesses the actual Prisma client, starts a transaction in an ALS context and propagates it downstream (this step is skipped, if there's already a tx client stored in the ALS).

For the Proxy, we have the following so far:

export class InternalPrismaService
  extends PrismaClient
  implements OnModuleInit
{
  async onModuleInit() {
    await this.$connect();
  }

  async enableShutdownHooks(app: INestApplication) {
    process.on('beforeExit', async () => {
      await app.close();
    });
  }
}

const patchedPrisma = new InternalPrismaService();
// Patch PrismaClient.$transaction()
const patchedPrismaTransaction = patchedPrisma.$transaction;
patchedPrisma.$transaction = (...args: unknown[]) => {
  if (typeof args[0] === 'function') {
    const fn = args[0];
    args[0] = (txClient: Prisma.TransactionClient) => {
      const clsService = ClsServiceManager.getClsService();
      const maybeExistingTxClient = clsService.get<
        Prisma.TransactionClient | undefined
      >(TRANSACTION_CLIENT_KEY);

      if (maybeExistingTxClient) {
        // means that we're already running in a transaction, just pass the one from the ALS downstream
        return fn(maybeExistingTxClient);
      }

      if (clsService.isActive()) {
        // means that there is an open ALS context already, but without a tx client -> save it in the store and pass it down
        clsService.set(TRANSACTION_CLIENT_KEY, txClient);
        return fn(txClient);
      }
      
      // this is the default case; open up an ALS context, save the tx client and pass it down
      return clsService.run(async () => {
        clsService.set(TRANSACTION_CLIENT_KEY, txClient);
        return fn(txClient);
      });
    };
  }

  return patchedPrismaTransaction.apply(patchedPrisma, args);
};

// This is THE Prisma instance that every component uses
// Instead of returning a true PrismaClient, we create a Proxy around the internal patched Prisma, trap the accesses and use the tx client from the ALS if exists
@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit {
  constructor() {
    super();
    return new Proxy(patchedPrisma, {
      get(_, p, receiver) {
        const maybeExistingTxClient = ClsServiceManager.getClsService().get<
          Prisma.TransactionClient | undefined
        >(TRANSACTION_CLIENT_KEY);
     
        const client = maybeExistingTxClient || patchedPrisma;

        // we provide a custom root accessor so that we can start a true transaction from the @Transactional decorator or whatever
        if (p === '$root') {
          return patchedPrisma;
        }

        return Reflect.get(client, p, receiver);
      },
      set(_, p, newValue, receiver) {
        const maybeExistingTxClient = ClsServiceManager.getClsService().get<
          Prisma.TransactionClient | undefined
        >(TRANSACTION_CLIENT_KEY);
 
        const client = maybeExistingTxClient || patchedPrisma;
        return Reflect.set(client, p, newValue, receiver);
      },
      defineProperty(_, property, attributes) {
        const maybeExistingTxClient = ClsServiceManager.getClsService().get<
          Prisma.TransactionClient | undefined
        >(TRANSACTION_CLIENT_KEY);
      
        const client = maybeExistingTxClient || patchedPrisma;
        return Reflect.defineProperty(client, property, attributes);
      },
    });
  }

  async onModuleInit() {
    await this.$connect();
  }

  async enableShutdownHooks(app: INestApplication) {
    process.on('beforeExit', async () => {
      await app.close();
    });
  }
}

Notes

So far I was able to verify that this is working with a couple of tests. However, I have difficulties with jest.spyOn. We have several existing test code where we mock Prisma returns like this:

jest
  .spyOn(prisma.entity, 'findFirst')
  .mockResolvedValue(whatever);

Unfortunately it doesn't work for some reason. As you can see in the above code I tried to implement the set and defineProperty Proxy traps that is allegedly used by Jest to provide the mocked behavior.

Maybe this helps someone to with the same challenge and has ideas on how to solve this. 🙇‍♂️

[nitedani]

@fuvidani Wouldn't this close the transaction early? I think, when fn(txClient) resolves, the transaction would be closed, and further calls on the stored maybeExistingTxClient would throw an error. The workaround would be to have a Transactional method decorator that would signal when the decorated method resolved, and this signal awaited in your patched $transaction function.
Something like:

import { aroundMethodDecarator } from "#root/src/utils/decorator";
import { Prisma, PrismaClient } from "@generated/prisma";
import { ClsServiceManager } from "nestjs-cls";

export const TRANSACTION_CLIENT_KEY = "prisma-transaction-client";

const prisma = new PrismaClient();

export const Transactional = () =>
  // Setup the transaction
  aroundMethodDecarator((args, name, next) => {
    const clsService = ClsServiceManager.getClsService();
    const txClient = clsService.get<Prisma.TransactionClient | undefined>(
      TRANSACTION_CLIENT_KEY
    );
    // There is an outer Transaction boundary, the transaction is already being handled by it
    if (txClient) {
      return next(...args);
    }

    const fn = async () => {
      // eslint-disable-next-line @typescript-eslint/no-unused-vars
      let commmitSignal = (_: boolean) => {};
      const canCommit = new Promise<boolean>((resolve) => {
        commmitSignal = resolve;
      });

      let txStartedSignal = () => {};
      const txStarted = new Promise<void>((resolve) => {
        txStartedSignal = resolve;
      });

      prisma
        .$transaction(async (_prisma) => {
          clsService.set(TRANSACTION_CLIENT_KEY, _prisma);
          txStartedSignal();
          if (!(await canCommit)) {
            throw new Error();
          }
        })
        .catch(() => {
          console.log("Rolling back transaction");
        })
        .finally(() => {
          clsService.set(TRANSACTION_CLIENT_KEY, null);
        });

      await txStarted;

      try {
        const result = await next(...args);
        commmitSignal(true);
        return result;
      } catch (error) {
        commmitSignal(false);
        throw error;
      }
    };

    if (clsService.isActive()) {
      return fn();
    }
    return clsService.run(fn);
  });

export const proxiedPrisma = new Proxy(prisma, {
  get(target, p) {
    // $transaction makes no sense for the Prisma.TransactionClient
    if (p === "$transaction") {
      return target[p];
    }

    const clsService = ClsServiceManager.getClsService();
    const txClient = clsService.get(TRANSACTION_CLIENT_KEY);
    if (txClient) {
      return txClient[p];
    }
    return target[p];
  },
});

export const PrismaService = Symbol.for("PrismaService");
export type PrismaService = typeof prisma;

export const PrismaServiceProvider = {
  provide: PrismaService,
  useValue: proxiedPrisma,
};

[fuvidani]

@nitedani Thanks for your detailed answer! I'll try out your approach too 🙇‍♂️

You're right about the missing txClient reset. In fact, in our @Transactional decorator we have it similar to your suggestion:

  cls.set(TRANSACTION_CLIENT_KEY, txClient);
  logger.debug('ALS context set');
  try {
    // We can now call the function that had been decorated with the Transactional decorator.
    return await method.apply(object, [...args]);
  } finally {
    // Finished working with our transaction: remove it rom the current context.
    cls.set(TRANSACTION_CLIENT_KEY, null);
    logger.debug('ALS context reset');
  }

[nitedani]

@fuvidani I'd like to clarify the issue with your example. Consider the following examples, with a similar code execution flow to your example:

1. example
let txClient: Prisma.TransactionClient;
prisma.$transaction(async (_txClient) => {
  // prisma started the transaction
  txClient = _txClient;

  //this will work as expected (in your example, this is `fn(txClient)`)
  const users = await txClient.user.findMany();

  // prisma commits the transaction
});

await new Promise((r) => setTimeout(r, 100));
// the transaction is already committed, this would error out (in your example, this is `fn(maybeExistingTxClient)`)
const users = await txClient.user.findMany();

2. example
let commmitSignal = (_: boolean) => {};
const canCommit = new Promise<boolean>((resolve) => {
  commmitSignal = resolve;
});
let txClient: Prisma.TransactionClient;
prisma.$transaction(async (_txClient) => {
  // prisma started the transaction
  txClient = _txClient;
  await canCommit;
  // prisma commits the transaction
});

// the transaction is alive, this would work as expected
const users = await txClient.user.findMany();

commmitSignal(true);

// the transaction is already committed, this would error out
const users = await txClient.user.findMany();


3. example
let commmitSignal = (_: boolean) => {};
const canCommit = new Promise<boolean>((resolve) => {
  commmitSignal = resolve;
});
let txClient: Prisma.TransactionClient;
prisma
  .$transaction(async (_txClient) => {
    // prisma started the transaction
    txClient = _txClient;
    if (!(await canCommit)) {
      throw Error();
    }
    // prisma commits the transaction
  })
  .catch(() => {
    console.log("Rollback");
  });

// the transaction is alive, this would work as expected
await txClient.user.create({ data: { id: "1" } });

// << this would throw because id is unique.
// we need to catch this exception and re-throw the error inside the prisma $transaction callback,
// so prisma can rollback
try {
  await txClient.user.create({ data: { id: "1" } });
} catch (error) {
  canCommit(false);
}

// the transaction is already rollbacked, this would error out
const users = await txClient.user.findMany();

Basically, the transaction only lives while the async callback function(promise) passed to the original prisma.$transaction is pending, and for rollback, you need to throw inside the callback.
I also updated my example to handle more cases.

[fuvidani]

@nitedani I think that either there's some confusion here or we're misunderstanding each other. 😬 Your code examples make sense, but it seems you're forgetting an essential ingredient that unlocks the real magic here: the AsyncLocalStorage.

The idea behind the Proxy implementation is that once you start a real transaction block, subsequent calls to prisma.$transaction inside its call-chain are trapped, and its parameter is replaced with the very same tx client that was used to open the first transaction of the chain. This would ensure that no matter how many levels of prisma.$transaction or bare prisma.myTable calls there are in a given Promise chain wrapped into an ALS-context, they would all receive the very same tx client. Does this make sense?

Assuming that we had a working Proxy implementation, your example could be translated into this:

@Transactional() // decorator runs the function in an ALS context, opens a real tx and puts the client in the storage
// The ALS wraps the whole execution. Think of the transaction boundary starting here:
// ---------------------------------- Start --------------------------------------------
async topLevelFunction() {
  // Prisma is proxied, i.e. the $transaction is trapped. It does not return a new tx client, but the one from the ALS.
  const user = await this.prisma.$transaction(async (txClient) => {
    const newUser = await txClient.user.create({});
    return newUser;
    // because of the Proxy, leaving this block does not mean that tx is committed
  });

  // Due to the Proxy, this.prisma is actually accessing the txClient from ALS
  await this.prisma.user.update({
    where: {
      id: user.id,
    },
    data: {
      firstName: 'wtv',
    },
  });
  throw new Error('Nope'); // error is thrown -> tx is rolled back
  return user;
}
// If topLevelFunction() were to resolve, only then would we reach the end of the tx block and commit
// ---------------------------------- End --------------------------------------------

You might ask: If the proxy always traps $transaction calls, how do we start it? That's where the custom, hacky $root accessor comes in. If you look at the draft Proxy code, it exposes an undocumented $root property that allows to escape the Proxy and target the real Prisma. The @Transactional decorator then goes like this:

• am I part of an ALS context with a tx client stored in it?
  • yes -> it means this function is part of a composed transaction; nothing to do, call the procedure; The Prisma proxy will consistently return the tx client from the ALS
  • no -> is there an ALS context active?
    • yes -> it means that the function was already wrapped into a cls.run() block, but without a tx client stored
      • run (prisma['$root'] as PrismaClient).$transaction(async(tx) => {}), store tx in the ALS and execute the procedure inside the root transaction block
    • no -> default case; no ALS context, no store tx client
      • same as above, but wrap the transaction code into a cls.run() context

To make it super clear, this is how the above code with topLevelFunction() in the default case is conceptually executed if we unwrap the @Transactional decorator:

return cls.run(async () => {
  return (prisma['$root'] as PrismaClient).$transaction(
    async (txClient) => {
      cls.set(TRANSACTION_CLIENT_KEY, txClient);
      try {
        // We can now call the function that had been decorated with the Transactional decorator.
        return await topLevelFunction(); // everything inside this function will receive txClient from the ALS
      } finally {
        // Finished working with our transaction: remove it rom the current context.
        cls.set(TRANSACTION_CLIENT_KEY, null);
      }
    }
  );
});

Now of course the technical design is nowhere near complete.

One obvious challenge with this naive approach is that it wouldn't allow nested, isolated transactions: either every db call in a given call-chain is executed within the same tx, or none at all. For instance, Spring addresses this with the propagation enum as parameter in their @Transactional decorator. However this must be evaluated based on the application one's developing. If we wanted to simply wrap all incoming REST request handlers in a transaction (several other web frameworks support this, e.g. Django), something like this could be a good starting point.

Let me know what you think, I'm excited to uncover all the unknown unknowns here 🤓

[nitedani]

@fuvidani Thank you for the explanation! It makes sense to me now, with the Transactional decorator trapping the txClient.

[Papooch]

@fuvidani

One obvious challenge with this naive approach is that it wouldn't allow nested, isolated transactions

For nested transactions, you can use cls.run({ ifNested: 'inherit' }, () => {}). (Docs)which creates a new ALS context with a shallow copy of the store - this allows you to override the transaction parameter in nested contexts and you don't need to clear it afterwards when the nested context callback ends (and therefore run multiple transactions inside one request).

This could simplify your implementation to:

return cls.run({ ifNested: 'inherit' }, async () => {
  return (prisma['$root'] as PrismaClient).$transaction(
    async (txClient) => {
      cls.set(TRANSACTION_CLIENT_KEY, txClient);
      // We can now call the function that had been decorated with the Transactional decorator.
      return await topLevelFunction(); // everything inside this function will receive txClient from the ALS
    }
  );
});

[fuvidani]

@fuvidani

One obvious challenge with this naive approach is that it wouldn't allow nested, isolated transactions

For nested transactions, you can use cls.run({ ifNested: 'inherit' }, () => {}). (Docs)which creates a new ALS context with a shallow copy of the store - this allows you to override the transaction parameter in nested contexts and you don't need to clear it afterwards when the nested context callback ends (and therefore run multiple transactions inside one request).

This could simplify your implementation to:

return cls.run({ ifNested: 'inherit' }, async () => {
  return (prisma['$root'] as PrismaClient).$transaction(
    async (txClient) => {
      cls.set(TRANSACTION_CLIENT_KEY, txClient);
      // We can now call the function that had been decorated with the Transactional decorator.
      return await topLevelFunction(); // everything inside this function will receive txClient from the ALS
    }
  );
});

Hey @Papooch that's pretty cool! 🤩 Just to clarify: if we use inherit and set the same key it's not going to override the parent context's store, right? Here's a basic representation of what I mean:

return cls.run({ ifNested: 'inherit' }, async () => {
  return (prisma['$root'] as PrismaClient).$transaction(
    async (txClient) => {
      cls.set(TRANSACTION_CLIENT_KEY, txClient); // outer txClient
      return cls.run({ ifNested: 'inherit' }, async () => {
        return (prisma['$root'] as PrismaClient).$transaction(
          async (txClient) => {
            cls.set(TRANSACTION_CLIENT_KEY, txClient); // inner txClient -> this does not affect outer txClient, does it?
            return await whatever(); 
          }
        );
      });
    }
  );
});

[Papooch]

@fuvidani Yes, that is correct. Just keep in mind that since inherit does a shallow copy, this currently only works for top-level keys (which the transaction is).

[perfect]

#17215 (comment)

this is real work code

[renatoaraujoc]

Hey @fuvidani, I've been reading and trying code that you and @Papooch + @nitedani have been discussing around the @transactional decorator for Prisma.

Indeed this is a must have feature and I'm been trying some stuff, making modifications, testing, etc.

I was wondering if any of you guys came with a solid solution? Looks like having a Parent -> Nested Children is the most desirable here because if any child fails, the starting transaction should fail entirely.

It doesn't hurt to have a "propagation-like" feature like Spring does as well.

It'd be amazing if any could share a near-full working code.

[fuvidani]

Hey @renatoaraujoc, I believe I have most of it working. I need to finish a few remaining details. Once those are done and rigorous testing validates the solution, I'll share what I can. 😉

[renatoaraujoc]

Hey @fuvidani, looks awesome, can't wait!

[codechikbhoka]

Waiting for Spring like magical @transactional :)

[Papooch]

In the meantime (if you use NestJS), I released a new version of nestjs-cls with support for plugins together with a Transactional plugin and a Prisma adapter for it.

The important part is that it does not try to monkey-patch the internals of any library, but still makes propagating the Prisma transaction as easy as:

@Injectable()
class UserService {
    constructor(
        @InjectTransaction()
        private readonly prismaTx: Transaction<TransactionalAdapterPrisma>,
        private readonly accountService: AccountService,
    ) {}

    @Transactional()
    async createUser(name: string): Promise<User> {
        const user = await this.prismaTx.user.create({ data: { name } });
        await this.accountService.createAccountForUser(user.id);
        return user;
    }
}

@Injectable()
class AccountService {
    constructor(
        @InjectTransaction()
        private readonly prismaTx: Transaction<TransactionalAdapterPrisma>,
    ) {}

    async createAccountForUser(id: number): Promise<Account> {
        // if called within a transaction, `prismaTx` will reference the same transactional client
        return this.prismaTx.user.create({
            data: { userId: id, number: Math.random() },
        });
    }
}

[fuvidani]

Hi @renatoaraujoc, @codechikbhoka, @nitedani and everybody else! Let me share our current working version. If you find any issues, please leave a comment! 🙇‍♂️

Summary

We achieve seamless tx propagation by

• monkey-patching Prisma's $transaction(). On the top-level, we wrap the execution into an ALS context. Otherwise, we keep reusing the tx client from the ALS
• creating a Proxy around PrismaClient. This ensures that an arbitrary this.prisma.todo.findFirst() uses the active tx client if available

The @Transactional decorator only needs to check if it's being executed in an already running transaction context. To open a new transaction, it defers this responsibility to the patched and proxied Prisma.

The Prisma Patch

We can create a function that receives a PrismaClient, patches it and returns it.

import { Prisma, PrismaClient } from '@prisma/client';
import { ClsService, ClsServiceManager } from 'nestjs-cls';

export const TX_CLIENT_KEY = 'txClient';

export function patchPrismaTx<T extends PrismaClient>(prisma: T) {
  const original$transaction = prisma.$transaction;
  prisma.$transaction = (...args: unknown[]) => {
    if (typeof args[0] === 'function') {
      const fn = args[0] as (
        txClient: Prisma.TransactionClient
      ) => Promise<unknown>;
      args[0] = async (txClient: Prisma.TransactionClient) => {
        const clsService = ClsServiceManager.getClsService();

        const maybeExistingTxClient = clsService.get<
          Prisma.TransactionClient | undefined
        >(TX_CLIENT_KEY);

        if (maybeExistingTxClient) {
          console.log(`Return txClient from ALS`);
          return fn(maybeExistingTxClient);
        }

        if (clsService.isActive()) {
          // covering this for completeness, should rarely happen
          console.warn(`Context active without txClient`);
          return executeInContext({
            context: clsService,
            txClient,
            fn,
          });
        }

        // this occurs on the top-level
        return clsService.run(async () =>
          executeInContext({
            context: clsService,
            txClient,
            fn,
          })
        );
      };
    }

    return original$transaction.apply(prisma, args as any) as any;
  };

  return prisma;
}

type ExecutionParams = {
  context: ClsService;
  txClient: Prisma.TransactionClient;
  fn: (txClient: Prisma.TransactionClient) => Promise<unknown>;
};


async function executeInContext({ context, txClient, fn }: ExecutionParams) {
  context.set(TX_CLIENT_KEY, txClient);

  console.log(`Top-level: open context, store txClient and propagate`);
  try {
    return await fn(txClient);
  } finally {
    context.set(TX_CLIENT_KEY, undefined);
    console.log(`Top-level: ALS context reset`);
  }
}

The Prisma Proxy

We're essentially making sure that

• this.prisma.todo.findFirst() uses the active tx client if available
• no matter how deep you nest prisma.$transaction() calls, they all receive the same client.

export function createPrismaProxy<T extends PrismaClient>(target: T) {
  return new Proxy(target, {
    get(_, prop, receiver) {
      // provide an undocumented escape hatch to access the root PrismaClient and start top-level transactions
      if (prop === '$root') {
        console.log(`[Proxy] Accessing root Prisma`);
        return target;
      }

      const maybeExistingTxClient = ClsServiceManager.getClsService().get<
        Prisma.TransactionClient | undefined
      >(TX_CLIENT_KEY);

      const prisma = maybeExistingTxClient ?? target;

      if (
        prop === '$transaction' &&
        maybeExistingTxClient &&
        typeof target[prop] === 'function'
      ) {
        console.log(
          `[Proxy] $transaction called on a txClient, continue nesting it`
        );

        return function (...args: unknown[]) {
          // grab the callback of the native "prisma.$transaction(callback, options)" invocation and invoke it with the txClient from the ALS
          if (typeof args[0] === 'function') {
            return args[0](maybeExistingTxClient);
          } else {
            throw new Error(
              'prisma.$transaction called with a non-function argument'
            );
          }
        };
      }

      return Reflect.get(prisma, prop, receiver);
    },
    // following traps needed to make the Proxy work with jest.spyOn()
    set(_, prop, newValue, receiver) {
      if (prop === '$transaction') {
        console.log(`Please don't spy on $transaction.`);
        return false;
      }

      const maybeExistingTxClient = ClsServiceManager.getClsService().get<
        Prisma.TransactionClient | undefined
      >(TX_CLIENT_KEY);

      const prisma = maybeExistingTxClient ?? target;
      return Reflect.set(prisma, prop, newValue, receiver);
    },
    defineProperty(_, prop, attributes) {
      const maybeExistingTxClient = ClsServiceManager.getClsService().get<
        Prisma.TransactionClient | undefined
      >(TX_CLIENT_KEY);

      const prisma = maybeExistingTxClient ?? target;
      return Reflect.defineProperty(prisma, prop, attributes);
    },
  });
}

Putting it together

All we need to do is make sure that the Prisma client we're using in our application is patched and proxied properly. In this example, I assume a Nest PrismaService that is used across the application an a singleton.

@Injectable()
export class PrismaService extends PrismaClient {
  constructor() {
    super();

    const patchedPrisma = patchPrismaTx(new PrismaClient());

    return createPrismaProxy(patchedPrisma);
  }
}

Transactional Decorator

With the patched Prisma in place, the decorator's only responsibilities are to:

• make sure PrismaService is available (Inject)
• check if it's being executed in an active ALS context with a tx client
• "request" a real transaction by accessing the $root Prisma. The patch will take care of setting up the ALS context

export function Transactional(
  isolationLevel?: Prisma.TransactionIsolationLevel
): MethodDecorator {
  const injectPrisma = Inject(PrismaService);

  return (
    target: any,
    propertyKey: string | symbol,
    descriptor: PropertyDescriptor
  ) => {
    injectPrisma(target, 'prismaClient');

    const originalMethod = descriptor.value;

    descriptor.value = async function (...args: unknown[]) {
      const cls = ClsServiceManager.getClsService();

      if (cls.get(TX_CLIENT_KEY)) {
        // A transaction has already been started. Just call the function.
        // The top-level wrapper will take care of resetting the context.
        console.log('Already running in an active transaction');
        return originalMethod.apply(this, [...args]);
      }

      const prisma = (this as any).prismaClient as PrismaService;

      // defer the context setup to the patched prisma
      return (prisma['$root'] as PrismaClient).$transaction(
        async () => {
          return originalMethod.apply(this, [...args]);
        },
        {
          isolationLevel,
        }
      );
    };
  };
}

Limitations

• No support for nested, isolated transactions. This version will either wrap everything or nothing. This should already cover the majority of cases. Nested, isolated transactions (preferably via CHECKPOINTs) are future work.
• You might notice that the patch does not take the array-based $transaction([]) usage into account. This is a conscious design decision. It's practically a legacy way of serially executing statements, most of us use the interactive transactions (callbacks) anyway. If you still need to support it, you should implement the handling.
• Spying on prisma.$transaction() is a no-go. The patch and the proxy are essential to make the propagation work. If you have tests where you change the behavior of $transaction() via Jest, it's going to break it. I'm going to leave it up to you to decide if it's a good practice to spy directly on a low-level operation like prisma.$transaction() on a day-to-day basis. Just be aware of the limitation.

Please leave feedback if you notice anything. 🙇‍♂️ Have fun! 🥳

[Shahor]

Wow :O

I had plans to implement exactly that on my nestjs codebase so this comment is an actual godsend!

@fuvidani Thank you so much for sharing with everyone, I'm sure it'll benefit the whole community!

[adamlamaa]

@fuvidani Been looking for a solution to handle prisma transactions in a clean way in NestJS, and at the bottom of my rabithole I have found your solution. Thank you for sharing, really awesome of you 💙

Bit new to NestJS still so appoligies if my follow up questions to your suggestion seem basic.

1. Would I be able to use use the Transactional decorator you have created both at the service level and/or the repository level if I end up splitting my prisma commands out on their own. Or would their be drawbacks to this?
   (As I write this I realize their might be numeral design drawbacks to adding the decorator on the repository level).

2. I am unsure of how to use the decorator, would something like below be correct usage of it?

export class UserService {

	constructor(private prismaService: PrismaService){}

	@Transactional()
	public updateUser(data){
		this.prismaService.user.update(...);
		this.prismaService.log.create(...);
	}
}

3. If I had a UserRepository would this be a correct usage?

export class UserService {

	constructor(
		private userRepository: UserRepository, 
		private logService: LogService
	){}

	@Transactional()
	public updateUser(data){
		this.userRepository.update(...);
		this.logService.log(...);
	}
}

export class UserRepository {

	constructor(private prismaService: PrismaService){}

	public update(data){
		this.prismaService.user.update(...);
	}
}

[myfunc]

@fuvidani
Faced with the issue:
When I trying access PrismaClient in debugger (VSCode) - debug server freezes.

As "access". Even "get" reading in "VSCode/Variables" panel makes UI frozen.

My guess, it happens when debugger tries get prisma, and createPrismaProxy returns something that node.js debugger doesn't expcect.

[myfunc]

@fuvidani Faced with the issue: When I trying access PrismaClient in debugger (VSCode) - debug server freezes.

As "access". Even "get" reading in "VSCode/Variables" panel makes UI frozen.

My guess, it happens when debugger tries get prisma, and createPrismaProxy returns something that node.js debugger doesn't expcect.

Added console.log and noticed that "Symbol()" is read just before freezing.

[myfunc]

PrismaClient has property with unknown for me Symbol key.
When debugger tries to read that it falls to an infinity loop.
I guess, the same happens when I tring to log value of the property.

After a 1-2 minutes console.log writes an extremely long stack trace.
That part repeates many times:

Also related issue: #21615

[myfunc]

Updating to "@prisma/client": "^5.12.1", fixed a problem

[fuvidani]

@fuvidani Been looking for a solution to handle prisma transactions in a clean way in NestJS, and at the bottom of my rabithole I have found your solution. Thank you for sharing, really awesome of you 💙

Bit new to NestJS still so appoligies if my follow up questions to your suggestion seem basic.

1. Would I be able to use use the Transactional decorator you have created both at the service level and/or the repository level if I end up splitting my prisma commands out on their own. Or would their be drawbacks to this?
   (As I write this I realize their might be numeral design drawbacks to adding the decorator on the repository level).
2. I am unsure of how to use the decorator, would something like below be correct usage of it?

export class UserService {

	constructor(private prismaService: PrismaService){}

	@Transactional()
	public updateUser(data){
		this.prismaService.user.update(...);
		this.prismaService.log.create(...);
	}
}

3. If I had a UserRepository would this be a correct usage?

export class UserService {

	constructor(
		private userRepository: UserRepository, 
		private logService: LogService
	){}

	@Transactional()
	public updateUser(data){
		this.userRepository.update(...);
		this.logService.log(...);
	}
}

export class UserRepository {

	constructor(private prismaService: PrismaService){}

	public update(data){
		this.prismaService.user.update(...);
	}
}

Hi @adamlamaa! 👋 Apologies for the delayed response 😔

1. Would I be able to use use the Transactional decorator you have created both at the service level and/or the repository level if I end up splitting my prisma commands out on their own. Or would their be drawbacks to this?
   (As I write this I realize their might be numeral design drawbacks to adding the decorator on the repository level).

Of course! The beauty of the decorator is that it can be freely nested/composed. If you check its implementation you'll see that if the decorator detects an active tx client in the call-chain, it simply executes the decorated method: any prisma command later on will automatically reuse the same tx client.

About where to place the decorators: we have cases where we use it on a repository method. For instance you could have a model backed by 2 tables that are kept in sync: a read-optimized and an append-only one for historization. The double-table approach is abstracted by the repository, and the @Transactional decorator on writing methods ensure atomicity across the two tables.

Nevertheless, you're right that most of the time the decorator finds its usage on the service layer because it is the layer responsible for fulfilling use-cases, orchestrating changes across different models (== tables).

Warning: isolation levels

In case you're familiar with isolation levels and apply them consciously, be aware that the automatic transaction propagation affects them too. If you compose multiple service methods that have the decorator on top but with different isolation levels, the top-level one will be applied, always. This is because in most databases you cannot change the isolation level of a transaction as soon as the first query statement is executed. Note: It can only become an issue if you have an inner method decorated with a stricter isolation level than the top-level one. Let me know if this is not clear and I can provide a deeper explanation. 🙇‍♂️

2. I am unsure of how to use the decorator, would something like below be correct usage of it?

Yes, perfect!

3. If I had a UserRepository would this be a correct usage?

Works too!

Btw., you can test atomicity by calling several repository operations from your updateUser method, but before it returns, add throw new Error(). With a Jest test you can verify that the number of rows in the corresponding tables remained the same before and after the execution (i.e. the tx rolled back everything). 😉

[myfunc]

I successfully used that approach with minor changes and fixes in the production project for a site with 2-5k online.
But there are a problems.

Warning: undesired transaction wrapping

In a team of 2+ developers, code is being changed, and there is a case when another developer adds a new prisma query to the service's method that could be executed in the @transactional stack.

For example, I have the method createGame (block 1), which is called by service (block 2).

// GameService
  async createGame(game: CreateGameArgs) {
    const data = await this.prismaService.pvpGame.create({
      data: {
        ...game,
        state: GameState.IN_PROGRESS,
      },
    });
    // tslint:disable:no-floating-promises
    this.setCurrentGame(data);
    return data;
  }

// UserGameService
  @Transactional()
  private async startNewGame(futureBlock?: number, user: User) {
    const currentBlock = await this.eosService.getCurrentBlock();
    if (!futureBlock || futureBlock <= currentBlock.block_num) {
      futureBlock = (await this.waitAnyNextBlock()).future_block_num;
    }
    const game = await this.gameService.createGame({ // Calling method from previous block
      secret: this.generateSecret(),
      futureBlock,
      userId: user.id
    });

    return game;
  }

All seems to be good. But if another developer decides inside of GameService verify that the user isn't blacklisted (just an example).
verifyUserOrThrow method can be implicitly executed inside of a transaction,
That call may execute many queries to $transactions that can lead to deadlocks caused by attempts to read/update locked DB rows.

// GameService
  async createGame(game: CreateGameArgs) {
    await this.userService.verifyUserOrThrow(game.user); // Dangerous code!!!. Can be executed inside of transaction.
    const data = await this.prismaService.pvpGame.create({
      data: {
        ...game,
        state: GameState.IN_PROGRESS,
      },
    });
    // tslint:disable:no-floating-promises
    this.setCurrentGame(data);
    return data;
  }

I wondering how to use that decorator in a safe way.
Currently, I am thinking about an eslint extension that will highlight prisma calls that are inside of possible transactions or another way to make nested queries forbidden with no special decorator or method's call.