Callback-free interactive transactions

[millsp]

Problem

Some users are asking for a less restricted way for opening an interactive transaction. Instead of having a callback for the transaction, there would be manual calls to $begin, $commit, and $rollback.

Suggested solution

Example of a use-case

describe('some test', () => {
   let trx: Transaction;

   beforeEach(async () => {
      trx = await prisma.$begin();
      jest.mock('./prisma', () => trx);
   });

   afterEach(async () => {
      await trx.$rollback();
   });

   it('.....', () => {
      // ....
   });
});

Alternatives

Additional context

#1844 (comment)

[sijakubo]

That would be great to have. We are currently facing the exact same issue

[janpio]

Can you elaborate on your use case @sijakubo? That is always valuable to have in such issues as signal why someone wants a feature really.

[sroettering]

I would love this feature as well. In my use case we have integration tests running with jest that need to be wrapped in a transaction so we can run many tests in parallel without bothering about side effects for other tests. There are of course techniques like cloning databases or using multiple schemas but they have limits. So a simple transaction would in most cases be more than enough. With the callback solution this is not possible right now.
The proposed API already looks promising for this.

[sijakubo]

Hi @janpio,

We are trying to ramp up our CI speed and in order to do so, we need to run the database integration tests in parallel. This is currently not possible, due to the lack of manual transaction handling within prisma.

I have multiple years of experience in Java / Spring where it is almost default behavior to run your tests within a transaction, which rollbacks after the execution of the test to run several tests in parallel.

The suggested example is exactly what we would need to increase our CI speed significantly

[garrensmith]

I was thinking that instead of having a manual transactions, why not rather change the test to be something like:

  describeInTransaction('some test', (trx) => {
   
   beforeEach(async () => {
           jest.mock('./prisma', () => trx);
   });

   it('.....', () => {
      trx.user.findUnique(...)
   });
});

With the function describeInTransaction being a wrapper over a transaction and the jest test.

[millsp]

Hey folks, I prototyped something today (with type safety!). Do you mind giving it a try and share your feedback?

Prototype setup

import { PrismaClient } from "@prisma/client"

type CtorParams<C> = C extends new (...args: infer P) => any ? P[0] : never
type TxClient = Parameters<Parameters<PrismaClient['$transaction']>[0]>[0]
const ROLLBACK = { [Symbol.for('prisma.client.extension.rollback')]: true }

async function $begin(client: PrismaClient) {
    let setTxClient: (txClient: TxClient) => void
    let commit: () => void
    let rollback: () => void

    // a promise for getting the tx inner client
    const txClient = new Promise<TxClient>((res) => {
        setTxClient = (txClient) => res(txClient)
    })

    // a promise for controlling the transaction
    const txPromise = new Promise((_res, _rej) => {
        commit = () => _res(undefined)
        rollback = () => _rej(ROLLBACK)
    })

    // opening a transaction to control externally
    const tx = client.$transaction((txClient) => {
        setTxClient(txClient as TxClient)

        return txPromise.catch((e) => {
            if (e === ROLLBACK) return
            throw e
        })
    })

    return Object.assign(await txClient, {
        $commit: async () => { commit(); await tx },
        $rollback: async () => { rollback(); await tx }
    } as TxClient & { $commit: () => Promise<void>, $rollback: () => Promise<void> })
}

// patches the prisma client with a $begin method
function getTxClient(options?: CtorParams<typeof PrismaClient>) {
    const client = new PrismaClient(options)
    
    return Object.assign(client, {
        $begin: () => $begin(client)
    }) as PrismaClient & { $begin: () => ReturnType<typeof $begin> }
}

Example

const prisma = getTxClient()
await prisma.user.deleteMany()
const tx = await prisma.$begin()

await tx.user.create({
    data: {
        email: Date.now() + '@email.io'
    }
})

const users0 = await tx.user.findMany({})
console.log(users0)

await tx.$rollback()

const users1 = await prisma.user.findMany({})
console.log(users1)

[sroettering]

I was thinking that instead of having a manual transactions, why not rather change the test to be something like:

  describeInTransaction('some test', (trx) => {
   
   beforeEach(async () => {
           jest.mock('./prisma', () => trx);
   });

   it('.....', () => {
      trx.user.findUnique(...)
   });
});

With the function describeInTransaction being a wrapper over a transaction and the jest test.

Isn't this just mocking prisma again? It may depend on what describeInTransaction gives you as callback param. But regardless this feels like a solution for a very specific usecase. Manual transactions could also be used in regular code where you don't want to execute everything inside a callback.
NestJS does a lot of stuff with decorators for example and having manual transactions could allow for transaction decorators as well (also quite heavily used in the Java world).

[millsp]

Hey everyone, I am excited to share that we are working on a new proposal that will help in solving this. While we are not keen to add this to our API, we are very willing to allow you to create and share custom extensions. This is how you would do it:

const prisma = new PrismaClient().$extends({
	$client: {
		begin() { ... }, // the code I shared above
	}
})

const tx = await prisma.$begin()

await tx.user.create(...)
await tx.user.update(...)

await tx.$commit()

We would love to see what you can build with Prisma Client Extensions. I'd appreciate if you can take some time to read and share your feedback on the proposal.

[scootklein]

Same use case here, we want our test suite to be able to run hitting the actual database, but with each test isolated inside of a transaction, would mirror similar pattern in ruby/rails and java/sprint (as alluded to above).

Ideally the API and transaction effect is global, where the test runner beforeEach could call prisma.$begin(), followed by a mix of app and test code, and then afterEach could call prisma.$rollback(). Passing around a transaction handler isn't ideal, as the app code uses a singleton prisma client

Thanks in advance for any help!

[adrian-goe]

@millsp Today I had another UseCase for this not related to testing.

I am working in a NestJs project with the CleanArchitecture. That means, we have our business logic in a domain layer and call single accessors from there, which then call prisma functions around a model (e.g. user).

Since we have business logic that needs to access multiple accessors, we can't use the callback transaction at all.

Using the tx would not help here either, as the accessors already use the prisma client provided by nest with dependencie injection.

here is a super simplified example. (i know, create can be solved differently, but with more complex queries and possibly other services that have to do something between the calls, this becomes more difficult)

@Injectable()
export class UserAccessor {
  constructor(private prismaClient: PrismaClient) {
  }

  async create(...): Promise<User> {
    return this.prismaClient.user.create(...)
  }
}

@Injectable()
export class SettingsAccessor {
  constructor(private prismaClient: PrismaClient) {
  }

  async create(user: User): Promise<Settings> {
    return this.prismaClient.userSettings.create(...)
  }
}


@Injectable()
export class UserDomain {
  constructor(private readonly userAccessor: UserAccessor,
              private readonly settingsAccessor: SettingsAccessor,
  ) {
  }

  async createUser(...) {
    // start Transaction
    const user = await this.userAccessor.create(...)
    // maybe more stuff done between this accessor calls
    const settings = await this.settingsAccessor.create(user)
    // end Transaction
  }
}

As @sijakubo pointed this out, Java/Spring/Hibernate uses this too in form of the @Transactional() Annotation around a service or function. Spring Transaction Management: @Transactional In-Depth

So please reconsider your decision not to offer direct functionality here by default.

[tonivj5]

@adrian-goe you could use https://nodejs.org/api/async_context.html#class-asynclocalstorage to implement your own @Transactional() decorator

[millsp]

@adrian-goe We could definitely consider method decorators, but this specific feature request is about having a callback-free call. Please take a look at the issues to see if we have a feature request already, if not you're more than welcome to create one. Thank you :)

[zachequi]

Hey folks, I prototyped something today (with type safety!). Do you mind giving it a try and share your feedback?

Prototype setup

import { PrismaClient } from "@prisma/client"

type CtorParams<C> = C extends new (...args: infer P) => any ? P[0] : never
type TxClient = Parameters<Parameters<PrismaClient['$transaction']>[0]>[0]
const ROLLBACK = { [Symbol.for('prisma.client.extension.rollback')]: true }

async function $begin(client: PrismaClient) {
    let setTxClient: (txClient: TxClient) => void
    let commit: () => void
    let rollback: () => void

    // a promise for getting the tx inner client
    const txClient = new Promise<TxClient>((res) => {
        setTxClient = (txClient) => res(txClient)
    })

    // a promise for controlling the transaction
    const txPromise = new Promise((_res, _rej) => {
        commit = () => _res(undefined)
        rollback = () => _rej(ROLLBACK)
    })

    // opening a transaction to control externally
    const tx = client.$transaction((txClient) => {
        setTxClient(txClient as TxClient)

        return txPromise.catch((e) => {
            if (e === ROLLBACK) return
            throw e
        })
    })

    return Object.assign(await txClient, {
        $commit: async () => { commit(); await tx },
        $rollback: async () => { rollback(); await tx }
    } as TxClient & { $commit: () => Promise<void>, $rollback: () => Promise<void> })
}

// patches the prisma client with a $begin method
function getTxClient(options?: CtorParams<typeof PrismaClient>) {
    const client = new PrismaClient(options)
    
    return Object.assign(client, {
        $begin: () => $begin(client)
    }) as PrismaClient & { $begin: () => ReturnType<typeof $begin> }
}

Example

const prisma = getTxClient()
await prisma.user.deleteMany()
const tx = await prisma.$begin()

await tx.user.create({
    data: {
        email: Date.now() + '@email.io'
    }
})

const users0 = await tx.user.findMany({})
console.log(users0)

await tx.$rollback()

const users1 = await prisma.user.findMany({})
console.log(users1)

We've been doing local testing with this and found a very subtle bug. Prisma, it would seem, passes the same txClient object to the $transaction callback regardless of how many transactions are open at once (how it manages that behind the scenes is witchcraft to me, but irrelevant to this story). Notice that $begin returns txClient but with some new methods added, namely $commit and $rollback. The bug is if you call $begin twice, the second invocation will effectively call

txClient.$commit = TheCommitFuncOfTheLastInvocation()

and as a result calling $commit on ANY of the returns from $begin will always commit the last transaction.

I'm attaching here a slightly refactored version of $begin with updated variable names that made it a bit easier for me to understand as well as returning a brand new object on every call to avoid overriding $commit and $rollback.

const $begin = async (client: PrismaClient, data?: { txId: number }) => {
  const { txId } = data || ({} as { txId: number });
  let captureInnerPrismaTxClient: (txClient: TxClient) => void;
  let commit: () => void;
  let rollback: () => void;

  // a promise for getting the tx inner client
  const txClient = new Promise<TxClient>(res => {
    captureInnerPrismaTxClient = txClient => res(txClient);
  });

  // a promise for controlling the transaction
  const controlTxPromise = new Promise((_res, _rej) => {
    commit = () => {
      console.log(`Commit called, resolving for ${txId}`);
      _res(undefined);
    };
    rollback = () => _rej(ROLLBACK);
  });

  // opening a transaction to control externally
  const prismaTranactionResult = client.$transaction(prismaTxClient => {
    captureInnerPrismaTxClient(prismaTxClient);

    return controlTxPromise.catch(e => {
      if (e === ROLLBACK) throw new Error(PRISMA_ROLLBACK_MSG);
      throw e;
    });
  });

  const capturedPrismaTxClient = await txClient;

  return {
    txId,
    $commit: async () => {
      commit();
      await prismaTranactionResult;
    },
    $rollback: async () => {
      rollback();
      await prismaTranactionResult.catch(err => {
        // eslint-disable-next-line @typescript-eslint/no-unsafe-member-access
        if (err.message !== PRISMA_ROLLBACK_MSG) {
          console.log(`Rollback txn, cause: ${err}`);
        }
      });
    },
    ...capturedPrismaTxClient,
     $executeRaw: capturedPrismaTxClient.$executeRaw,
     $executeRawUnsafe: capturedPrismaTxClient.$executeRawUnsafe,
  };
};```

[selimb]

@Valerionn came up with a pretty neat pattern for this in https://github.com/chax-at/transactional-prisma-testing
I've also just published an article (https://selimb.hashnode.dev/speedy-prisma-pg-tests) and companion repo (https://github.com/selimb/fast-prisma-tests) for how we sped up integration tests where I work.

[bombillazo]

This feature is what is holding us back from using Prisma instead of TypeORM. They have a QueryRunner class that allows to control transactions with the same instance using a similar API to what OP suggested.

[millsp]

We have an example of callback-free itx via extensions over here, feedback welcome.
https://github.com/prisma/prisma-client-extensions/tree/main/callback-free-itx

[PiotrJozefow]

Maybe someone finds it useful but In my case I needed to wrap transaction object so I can pass it around different layers of my app and publish events after commit succeeds and I ended up using two promises to prevent premature commit:

export abstract class RepositoryBase {
  protected abstract client: PrismaClient

  public async createTransaction(): Promise<PrismaTransaction> {
    let awaiter;
    const instance = await new Promise<PrismaTransaction>(resolveInstance => {
      awaiter = this.client.$transaction(async tx => {
        const transaction = new PrismaTransaction(tx)
        resolveInstance(transaction)
        await new Promise<void>(resolveCommit => transaction.setCommitter(() => resolveCommit()))
      })
    })
    instance.setAwaiter(awaiter)
    return instance
  }
  ...
 }

And in the instance:

type Committer = () => void
type Awaiter = Promise<void>

export class PrismaTransaction implements TransactionInterface {
  private subscribers: CommitSubscriber[] = []
  private committer?: Committer
  private awaiter?: Awaiter

  constructor(private readonly trx: Prisma.TransactionClient) {}

  public get client(): Prisma.TransactionClient {
    return this.trx
  }

  public async commit(): Promise<void> {
    if (!this.committer) {
      throw AppError.internalServerError('PrismaTransaction committer not set')
    }
    if (!this.awaiter) {
      throw AppError.internalServerError('PrismaTransaction awaiter not set')
    }
    this.committer()
    await Promise.all(this.subscribers.map(cb => cb()))
    await this.awaiter
  }

  public setCommitter(cb: Committer) {
    if (this.committer) {
      throw AppError.internalServerError('PrismaTransaction committer already set')
    }
    this.committer = cb
  }

  public setAwaiter(cb: Awaiter) {
    if (this.awaiter) {
      throw AppError.internalServerError('PrismaTransaction awaiter already set')
    }
    this.awaiter = cb
  }

  public onCommit(cb: CommitSubscriber): void {
    this.subscribers.push(cb)
  }
}

[Omer-Shahar]

@adrian-goe you could use https://nodejs.org/api/async_context.html#class-asynclocalstorage to implement your own @Transactional() decorator

Thank you so much for the idea!
I think I managed to create my own Transactional decorator:

const asyncLocalStorage = new AsyncLocalStorage();

type TransactionalPrisma = Omit<
  PrismaClient<
    Prisma.PrismaClientOptions,
    never,
    Prisma.RejectOnNotFound | Prisma.RejectPerOperation | undefined
  >,
  "$connect" | "$disconnect" | "$on" | "$transaction" | "$use"
>;

const dbMap = new Map<string, TransactionalPrisma>();

export function getDB() {
  const id = z.string().parse(asyncLocalStorage.getStore());
  const db = dbMap.get(id);
  if (!db) throw new Error("No db found");
  return db;
}

export function Transactional(target: { prototype: Object }) {
  const prototype = target.prototype;
  // for each method
  for (const propertyName of Object.getOwnPropertyNames(prototype)) {
    const descriptor = Object.getOwnPropertyDescriptor(prototype, propertyName);
    const isMethod = descriptor?.value instanceof Function;
    if (!isMethod) continue;

    const originalMethod = descriptor.value as { apply: Function };
    // wrap method
    descriptor.value = async function (...args: unknown[]) {
      try {
        getDB(); // check if db exists, i.e. we are in a transaction
        return await originalMethod.apply(this, args);
      } catch (e) {
        return prisma.$transaction(async (db) => {
          const id = randomUUID();
          dbMap.set(id, db);
          const result = await asyncLocalStorage.run(id, async () => {
            return await originalMethod.apply(this, args);
          });
          dbMap.delete(id);
          return result;
        });
      }
    };

    Object.defineProperty(prototype, propertyName, descriptor);
  }
}

Note that this is a first draft, so improvements and corrections are more than welcome. 😅

[barnc]

Hi @Omer-Shahar - thanks a bunch for this, looks very interesting. Do you have an example of this being used anywhere?

[salahmedamin]

Any solution to this problem? I'd like to have understand how extensions can solve this:

const result = await prisma.$transaction(async (tx) => {
  const rating = await tx.rating.upsert({
    where: {},
    create: {},
    update: {},
    include: {},
  }); //update (user|product) overall rating
  overall_rating = ratedProduct
    ? await ratingService.updateProductOverallRating({
        product: ratedProduct,
        tx,
        //passing tx as a parameter to another function has no effect and gets db data messy
      })
    : await ratingService.updateUserOverallRating({
        user: ratedUser ?? -1,
        tx,
        //passing tx as a parameter to another function has no effect and gets db data messy
      });
  return { ...rating, overall_rating };
});
return result;

I'd like to be able to manually commit or rollback transactions within a given context of mine

[dvnkboi]

Hi @Omer-Shahar, it would really help if you could share an example of using this on a class.

[dvnkboi]

Hey, don't know if anyone will find this useful but after a bit of work, I found it hard to make @Omer-Shahar's version work, but using the same sort of idea I came up with this

import { PrismaClient } from "@prisma/client";
import { AsyncLocalStorage } from "async_hooks";
import { cuid } from "../generators/id.js";
import { Exception } from "~sdk";

const asyncLocalStorage = new AsyncLocalStorage();

export type TransactionalPrisma = Omit<
  PrismaClient,
  "$connect" | "$disconnect" | "$on" | "$transaction" | "$use"
>;

const dbMap = new Map<string, TransactionalPrisma>();

export function getDB() {
  const id = asyncLocalStorage.getStore() as string;
  const db = dbMap.get(id);
  if (!db) throw new Error("No db found");
  return db;
}

export function Tx(prisma: PrismaClient) {
  return function (target: Object, propertyKey: string | symbol, parameterIndex: number) {
    const originalMethod = target[propertyKey as string] as { apply: Function; };
    target[propertyKey as string] = async function (...args: unknown[]) {
      try {
        const db = getDB();
        args[parameterIndex] = db;
        return await originalMethod.apply(this, args);
      } catch (e) {
        return new Promise(async (res) => {
          try {
            await prisma.$transaction(async (db) => {
              const id = cuid();
              dbMap.set(id, db as TransactionalPrisma);
              const result = await asyncLocalStorage.run(id, async () => {
                try {
                  args[parameterIndex] = db;
                  const result = await originalMethod.apply(this, args);
                  if (result instanceof Exception) {
                    res(result);
                    throw result;
                  }
                  return result;
                }
                catch (e) {
                  console.log(e);
                  res(new Exception(e.message, 'transactionError'));
                  throw e;
                }
              });
              dbMap.delete(id);
              return result;
            });
          }
          catch (e) {
            console.log(e);
            res(new Exception(e.message, 'transactionError'));
          }
        });
      }
    };
    return target as any;
  };
};

---

This is an example of using it:

@Controller('/shipment')
export class ShipmentData {
  ...
  @Get('/test/:dataId')
  async test(@Req() req, @Params('dataId') dataId, @Tx(client) tx: PrismaClient) {
    const data = await tx.dataSource.create({
      data: {
        data: {},
        description: 'test',
        name: 'test',
        teamId: 'test',
        type: 'BAR',
        id: dataId,
      }
    });

    const data2 = await tx.dataSource.findUnique({
      where: {
        id: dataId,
      },
    });

    console.log(data2);

    throw new Error('test');
  }
}

---

Hope this helps anyone.

[janpio]

Related: #12975 + #13004

[vineboneto]

I've been trying to implement a solution based on @PiotrJozefow approach for managing transactions in Prisma 5.7.0, PostgreSQL 15, and Node.js 18. However, I'm encountering an error when attempting to execute more than 15 simultaneous transactions. The error is "PrismaClientKnownRequestError: Transaction API error: Unable to start a transaction in the given time." My implementation involves sharing a transaction across multiple repositories through a context. I'm looking for insights into this issue and any recommended strategies for handling multiple simultaneous transactions in a shared context. Here is my code for reference:

import { PrismaClient, Prisma } from "@prisma/client";

const prisma = new PrismaClient();

type Committer = () => void;
type AwaiterTransaction = Promise<void>;

export class PrismaTransaction {
  private committer?: Committer;
  private awaiter?: AwaiterTransaction;

  constructor(private readonly trx: Prisma.TransactionClient) {}

  public get client(): Prisma.TransactionClient {
    return this.trx;
  }

  public async commit(): Promise<void> {
    if (!this.committer) {
      throw Error("PrismaTransaction committer not set");
    }
    if (!this.awaiter) {
      throw Error("PrismaTransaction awaiter not set");
    }
    this.committer();
    // await Promise.all(this.subscribers.map((cb) => cb()))
    await this.awaiter;
  }

  public setCommitter(cb: Committer) {
    if (this.committer) {
      throw Error("PrismaTransaction committer already set");
    }
    this.committer = cb;
  }

  public setAwaiter(cb: AwaiterTransaction) {
    if (this.awaiter) {
      throw Error("PrismaTransaction awaiter already set");
    }
    this.awaiter = cb;
  }
}

async function createTransaction(): Promise<PrismaTransaction> {
  let awaiter: any;
  const instance = await new Promise<PrismaTransaction>((resolveInstance) => {
    awaiter = prisma.$transaction(async (tx) => {
      const transaction = new PrismaTransaction(tx);
      resolveInstance(transaction);
      await new Promise<void>((resolveCommit) =>
        transaction.setCommitter(() => resolveCommit())
      );
    });
  });
  instance.setAwaiter(awaiter);
  return instance;
}

class Context {
  private tx: PrismaTransaction | null = null;

  setTransaction(trx: PrismaTransaction) {
    this.tx = trx;
  }

  getTransaction(): PrismaTransaction {
    if (!this.tx) throw Error("Transaction not set");
    return this.tx;
  }
}

class RepositoryAccount {
  constructor(private readonly ctx: Context) {}

  async exec() {
    const p = this.ctx.getTransaction();
    const users = await p.client.tbl_user.create({
      data: {
        email: "johndoe@mail.com",
        username: "John Doe",
      },
    });
    console.log(users.id, "user");
  }
}

class RepositoryCategory {
  constructor(private readonly ctx: Context) {}

  async exec() {
    const p = this.ctx.getTransaction();
    const grupo = await p.client.tbl_category.create({
      data: {
        name: "Test",
      },
    });
    console.log(grupo.id, "create");
  }
}

class Controller {
  constructor(
    private readonly repoAccount: RepositoryAccount,
    private readonly repoCategory: RepositoryCategory
  ) {}

  async handle() {
    await this.repoCategory.exec();
    await this.repoAccount.exec();
  }
}

class Transaction {
  constructor(
    private readonly controller: Controller,
    private readonly ctx: Context,
    private readonly id: string
  ) {}

  async handle() {
    try {
      const trx = await createTransaction();
      this.ctx.setTransaction(trx);
      await this.controller.handle();
      await trx.commit();
    } catch (error) {
      throw error;
    }
  }
}

async function exec(idx: number) {
  const ctx = new Context();
  const repoAccount = new RepositoryAccount(ctx);
  const repoGrupo = new RepositoryCategory(ctx);
  const controller = new Controller(repoAccount, repoGrupo);
  const transaction = new Transaction(controller, ctx, String(idx));
  await transaction.handle();
}

const promises = Array(15)
  .fill(null)
  .map((_, idx) => exec(idx));

Promise.allSettled(promises)
  .then(async () => {
    console.log("ok");
  })
  .catch((error) => {
    console.error("error:", error);
  });

[gyunseo]

We have an example of callback-free itx via extensions over here, feedback welcome. https://github.com/prisma/prisma-client-extensions/tree/main/callback-free-itx

I tried to use db transaction rollback, but the code of main branch has the following problem.

• rollback was not working, when explicitly called.
  so check out this PR if you're trying to use rollback. It worked for me.