Strapi V4 isn't ACID compliant and doesn't natively support transactions

Summary

To summarize, Strapi v4 doesn’t support transactions and to implement them you must manually write Knex queries or patch your app with one of the pull requests that implement transactions at a very low level, which defeats a lot of the usability of Strapi’s native entityManager and queryEngine APIs. This means that Strapi isn’t ACID compliant and data integrity is not guaranteed

Issues this causes

This raises a whole host of issues for apps and Strapi generally, primarily:

  • If your app has a high number of users doing any things at once on the same data, e.g. using the same orders table, data may get corrupt or lost
  • Mistakes and inconsistencies in your database can cost huge sums of money and damage customer relations for certain businesses (including the author’s), e.g. a customer’s order being wrong or not correctly charged
  • Lack of transactions causes issues with multi-tenant cloud services, e.g. containers trying to access the same table may write at the same time to the same index causing inconsistencies
  • Quoting this directly from stackoverflow “very few applications truly require ACID semantics everywhere. However, many applications will require them somewhere”. Missing transactions limits what applications can use Strapi and prevents adoption. Worse yet, this isn’t widely documented, not at all for the v3 → v4 migration, so it could take users a long time to realize this and cause them to really, really dislike Strapi when they find this out that could damage the likelihood users will stick with Strapi long term database - What Applications Don't Need ACID? - Stack Overflow
  • Affects developer experience as instead of using simple APIs, they have to write low-level queries with Knex, a library Strapi developers may not use or want to use

History

According to the team, Strapi did support transactions in v3 when they were using bookshelf.js as Strapi’s ORM, but now they have their own implementation that doesn’t have transactions.

There have been a number of pull requests and issues related to this that seem to have fizzled out.

Temporary Solutions

As you can read in the above, Strapi’s team has provided some temporary solutions

  • get your database connection, make the knex queries yourself and add the transactions to them
  • patch your version of strapi with that of this pull request which may or may not break things https://github.com/strapi/strapi/pull/12715
  • patch your version of strapi with a commit on the features branch that provides low level implementation (this may or may not have been merged, also may or may not break things) https://github.com/strapi/strapi/pull/13350

Reason for posting

I’m mainly posting this because I feel like this issue has got little traction, despite what a breaking issue this is for many applications in development if they were aware of this, and discussion on pull requests and issues has fizzled. This isn’t to denigrate the team in anyway, they have provided some temporary solutions and have reviewed pull requests and been involved in discussions. However, there are still two main problems with this

  1. Writing Knex queries adds development time for crucial features, involves delicate code that using Strapi was supposed to avoid in the first place and allows will likely need to be replaced in v5

  2. They have also said this issue isn’t a priority, despite that fact that I, and I feel like quite a few others, would argue it is and not just a minor feature requirement but a core feature that if we had know v4 didn’t have, wouldn’t have even used Strapi in the first place

Goals

I’m hoping that this post will raise awareness around this issue for those using Strapi who may have otherwise missed it, either coming from the v3 → v4 migration or those fresh to Strapi. More importantly, I’m hoping that this awareness can raise the priority of this issue and get it solved soon. If this issue affects you or will affect you, please vote for it on the strapi feedback Support Database Transactions with Knex | Developer Experience | Strapi. I don’t think this is exactly what we want but it’s a proxy for the same thing. Ideally, it would be good to just wrap all queries that need to be transacted in something akin to the v3 version found here Using Database Transactions to Write Queries in Strapi

Thanks to…

Finally worth mentioning that a lot of these issues were brought up by @willnode, @benderillo, and @tuxuuman and they actively have worked on these issues, I’m just summarising their arguments and adding a thing or two. Thank you also @alexandrebodin and @derrickmehaffy for the responses and solutions from Strapi so far

Mistakes

If I made any mistakes or missed anything, please let me know and I’ll amend the post

13 Likes

Bravo!

Thanks for writing this up.
For me transactions is a core functionality that simply must be there from day 1 if the software deals with databases.

Low-level transactions as they been suggested (i think I saw some support for it have been added to the database package in the latest release) are not the solution, unfortunately.

It is bad approach from software design perspective and is almost guaranteed to bite you later when Strapi change layout of stored data.

Not to mention that writing those knex transactions when you update something with relations and components is a fiddly process.

You are spot on when you say that because it is not documented it is quite a surprise for those who discover it: For a year I was building something based on Strapi v3 (with transactions) then I hear that I MUST migrate to v4 and V3 will go down eventually. I feel irritated but this is not a deal breaker. But then I learn that v4 does not have transactions! Yet I am forced to adopt it despite the fact that I NEED transactions. And what do I do with a year of development with Strapi? I really went through all the 5 phases of acceptance with Strapi through this experience :smiley:
Eventually I modified and patched one of the PRs with high level transactions to my code base. But instantly found that it is very dangerous when lifecycle hooks are used (causes locks, etc).
So I still use it but I am aware of issues and have to work around the poor design of the Database package for now.

I strongly believe that Strapi team should re-consider this as an absolute high priority and work on high-level transactions as soon as they can.

Before jumping on shiny features that bring commercial success, fundamentals should be done right.

3 Likes

I was planning to use Strapi on my new project, however moved away from it when I found this issue and that there were no plans to implement it. Its such a core requirement… Also, the way Strapi creates the database, junction tables for every relation, makes the knex approach even harder as one has to make more joins to get de data…

1 Like

I had a similar experience too! We updated to v4 thinking that it would only add to Strapi, not detract from it. We didn’t realise just how much we needed transactions until the migration had already taken place, which put us into a bind. It was too late to use something else now, so we’re going to have to do low level transactions and avoid lifecycle hooks which has created such headaches for us

Exactly, it really is a deal breaking issue. If we’d have realized in advance, it wasn’t supported from the get go with v4, we never would migrated and just used something else

Yeah, the decision to have junction tables for any kind of relation that came with the v4 was too a bummer.
Because of this new constraint I had to go back to the whiteboard to re-think my models. Because I relied on “unique” property for some one-to-one or one-to-many relation attributes to ensure no duplicates for some tables.

But with all relations now being done via junction tables, they removed the “unique” checkbox from the Content Builder UI :slight_smile: because you can’t easily do it any more with the new architecture.
So, when I discover that, I thought, “F*ck I am screwed, what’s left? Transactions? Well, it is a heavy hammer but do I have a choice?”
Ironically, I then discovered that there were no transactions too and that’s when I started sweating :smiley:

2 Likes

Bummer… I am already using v4 and now need transactions. Can you share what your solution is for low level transactions?

I also came across your PR @aveprik in Using Database Transactions to Write Queries in Strapi - #8 by aveprik

And I also can’t understand why strapi does not document the lack of transaction in v4.
You start updating and find out that more and more features were taken away from you OR have been changed in a fundamental way (components in lifecycle :raising_hand_man: ).

With the addiotion of folders strapi even by themself seems to see the lack of transaction…

 // fetch existing folder
      const existingFolder = await strapi.db
        .queryBuilder(FOLDER_MODEL_UID)
        .select(['uid', 'path'])
        .where({ id })
        .transacting(trx)
        .forUpdate()
        .first()
        .execute();
// update parent folder
      const joinTable = strapi.db.metadata.get(FOLDER_MODEL_UID).attributes.parent.joinTable;
      await strapi.db
        .queryBuilder(joinTable.name)
        .transacting(trx)
        .update({ [joinTable.inverseJoinColumn.name]: parent })
        .where({ [joinTable.joinColumn.name]: id })
        .execute();
      // fetch destinationFolder path
      let destinationFolderPath = '/';
      if (parent !== null) {
        const destinationFolder = await strapi.db
          .queryBuilder(FOLDER_MODEL_UID)
          .select('path')
          .where({ id: parent })
          .transacting(trx)
          .first()
          .execute();
        destinationFolderPath = destinationFolder.path;
      }

So I hope Strapi reconsiders its decision regarding transaction or atleast communicates much better with the developer community. (maybe start using this forum more, even if it is painful someday)
I am really looking forward to the improvement of the documentation until end (?) of september.

1 Like

Everyone interested, there is some movement on this. See https://github.com/strapi/strapi/pull/12715 for more discussion but there is now a new pull request here Use transactions and expose a transactional API by alexandrebodin · Pull Request #14389 · strapi/strapi · GitHub by Alex at Strapi so hopefully this will be resolved soon and we’ll have transactions :+1: