Using Database Transactions to Write Queries in Strapi

1

When we write software, we always want to make it as flawless as possible. While we always strive for it hard enough, sometimes bugs still can appear in a production system. So this is why, when we write software, we have to prepare for the worst when things go wrong.

This is a companion discussion topic for the original entry at https://strapi.io/blog/using-database-transactions-to-write-queries-in-strapi
2

I’m have tested your code, but the rollback does not work.

          return await strapi.db.connection.transaction(async (transacting) => {
              // Find the user
              try{
                const fromUser = await strapi.query('api::local.local').create({ data:{id:444 }}, null, { transacting });
                const toUser = await strapi.query('api::local.local').create({ data:{id:999 }}, null, { transacting });

  
              // respond with the receipt (don't forget to sanitize our output!)
              return "worked"
              }
              catch(e){
                return e
              }
          });

here, the id 999 already exists in the database, and it returns duplicated key error, however, the id 444 is inserted before this error and not removed when the transaction fails.

I also tried the following approach:

      async transaction2(ctx){
        const transacting = await strapi.db.connection.transaction();
        try {
          const fromUser = await strapi.query('api::local.local').create({ data:{id:444}}, null, { transacting });
          const toUser = await strapi.query('api::local.local').create({ data:{id:999}}, null, { transacting });
          await transacting.commit();
          return "Deu"
      } catch (err) {
          await transacting.rollback();
          console.log("ROLLBACK")
          return err
      }
      }

The result is the same. When I call the rollback function, the first query is not reverted.
Any solution for this?

4

well… forum is dead I guess

2 Likes
5

Bump…
Guess I’m not using STRAPI, first question I ask cant even get an answer xD
sad

6

Try discord, although your experience will most likely be similar.
The question to answer ratio here and in discord is pretty sad, yes.

I found better results arrive if you mention people. Specifically Derrik (DMehaffy). He is the most responsive person out of entire company and out of entire forum to be honest. This guy clearly cares but he is just one human being and there are this many questions :slight_smile:

7

HI @Filipe_Cunha and sorry for the late reply. We are currently organizing a process to be more responsive and the author has been notified. If you’re stuck, as @aveprik mentioned, Discord is the best place to get help, our Solutions Engineers @DMehaffy, @Richard_Nsama and @bolg55 will do their best to help you out.

8

BTW, Transactions are not yet supported on Strapi v4.
There is a Github Issue: [v4] Support Database Transactions with Knex, please · Issue #11792 · strapi/strapi · GitHub
And there is this PR open for implementation: simple implementation of transactions by tuxuuman · Pull Request #12715 · strapi/strapi · GitHub

You can either wait for it to get merged or patch it over your version of strapi using the library patch-package - npm

9

+1 I’m waiting for this feature in v4

10

Could you elaborate on how to patch my strapi version with this pull request?
thank you

11

First you need to convert the PR into a patch file and apply it to your code. Here is an article: Apply a patch from a Github PR | kevingimbel.de — Web Development, DevOps, and everything in between. and apply it.

After you do so, use the patch-package library as explained in the docs (patch-package - npm) to generate a patch that contains a diff of the original @strapi/database package version and your local modified one.
Then you commit the generated patch to your repo so that whenever you run npm install the patch-package re-apply the changes on top of the clean strapi release.

12

Oh, nice. Currently, transactions available in latest version. I will update the version and try it))

13

Well, it was closed by this one.

14

Its not released yet, but you can try it as a patch. See the comment from @aveprik on how to do it.

15

Okay, will try understand it. In strapi source found this code:

//path server/migrations/field/utils.js

const getSortedLocales = async ({ transacting } = {}) => {
  const localeService = getService('locales');

  let defaultLocale;
  try {
    const storeRes = await strapi
      .query('strapi::core-store')
      .findOne({ key: 'plugin_i18n_default_locale' }, null, { transacting });
    defaultLocale = JSON.parse(storeRes.value);
  } catch (e) {
    throw new Error("Could not migrate because the default locale doesn't exist");
  }

...

In code we look a implementation of transaction usage. But, if try not work.

// get transaction from knex
const transacting = await strapi.db.transaction();

try {
  
  await strapi.query('api::payment.payment').create({
    data: {...}
  }, null, {transacting});

  // break transaction
  throw Error('exit...');
  
  await transacting.commit();
} catch (e) {
  await transacting.rollback();
}