Support database transactions

[jakubjanis]

Based on the Discord chat I would like to propose an improvement of Payload to allow use of DB transactions.
I think there are many cases when there is a need to execute multiple DB operations as whole (committing a transaction) or not at all (transaction rollback - in case any of the operations fails).

For illustration, please see how transactions can be executed for example in Strapi (another headless CMS).

await strapi.connections.default.transaction(async (transacting) => {
  .....
  receipt = await strapi.query('receipt').create({
                      sender: fromUser.id,
                      recipient: toUser.id,
                      amount,
                  }, { transacting });
  
  .....
  await strapi.query('user', 'users-permissions').update({ id: from }, fromUser, { transacting });
  await strapi.query('user', 'users-permissions').update({ id: to }, toUser, { transacting });
  .....
 }

Source:
https://strapi.io/blog/using-database-transactions-to-write-queries-in-strapi

[DanRibbens]

Hey @jakubjanis!
I think this is an important step in making Payload a vaiable alternative to using full application frameworks.

To make this possible very quickly, our next step is going to be to expose the mongoose connection on the req.payload object. By making that available you will be able to add your own logic for transaction handling in premiddleware and postmiddleware, hooks, error handlers and custom endpoints.

After that we can assess how it fits into the existing API to be more friendly than handling it on your own.

How does this sound?

[jakubjanis]

Hello @DanRibbens , thank you for your reply. Yes, I think what you just proposed makes sense. It sounds very good.

[DanRibbens]

I ran into a couple issues.

1. There needs to be a way to tell Payload what session (if any) to use in operations. This is pretty easily overcome by adding req.session and passing it to mongoose.
2. Upload collections delete files as part of the operation. There is not a simple way to handle file deletion only after the calling commitTransaction(). This could have unexpected consequences for users. I'm interested in knowing if anyone has a solution around this. I looked at Mongoose/mongo for events to say if you're in a transaction and that transaction completes, then perform the delete. Unfortunately the events didn't seem to allow for this. This is also going to matter for filesystem changes on create for uploads too.

Before I finish the work I want to be sure the req.session is how we want to handle checking for sessions in the core of Payload and figure out a way to handle file system changes for uploads in a way that respects transactions.

Let me know your thoughts.
Thanks!

[DanRibbens]

This is what I have so far. #1527

It needs documentation and testing, but first I am looking for feedback from the community.

[dawidmachon]

Really waiting for that feature to go live. It's crucial for apps to have the option to rollback if something goes wrong across one transaction.
#2076

[DanRibbens]

Sorry the delay! We wanted to nail down our database adapter pattern to avoid introducing breaking changes right after rolling this out.

We're making progress, hang in just a little bit longer!

[WilsonLe]

Any further updates on this feature? It would be great if we could have a ball park estimation of when this feature is going live.

[DanRibbens]

Transactions will be ready by the end of July. It was bumped back while we work on the larger database adapter work which is in full swing now.

[dawidmachon]

@DanRibbens how is work going for transactions? That feature is so crucial in any complex commerce app ;)

[DanRibbens]

Transactions were released in v2 of Payload and improved further in 3.0 beta.