Mysql Transaction, how to do it

System Information
  • Strapi Version: 4
  • Operating System: windows
  • Database: mysql
  • Node Version: 14
  • NPM Version: 6.14
  • Yarn Version:

Hello,
I’m new on strapi and I’m investigating if I should start using it or not.
Until now, I could replicate most of the functionality I need, however, I was not able to create a MySQL transaction.
I have searched in the documentation and forums but I was not able to find an example of how to create a transaction so I can commit multiple queries and rollback if something has gone wrong.
Is there any solution for this?

Thank you.

You can create a transactions as follows

await strapi.connections.default.transaction(
          async (trx) => {
                //functions inside transaction
                await trx.raw("insert into table..............")
          });

Also, To call stored procedures on specific api calls you have to customize your Strapi app controller or service, depending on the situation. There is no way to provide you a solution, because there are too many variables in this equation. You need to start learning Strapi, I’m not an expert either, read this about how to customize Strapi controllers or services. 17.
For periodic calls Strapi has even cron task similar to those in Linux 2.

I think you should call stored procedures not in Strapi, but in your existing frontend web framework, I suspect that you use asp.net there which is way more apropriate for such jobs. I see this setup Strapi + mssql based web framework where Strapi is the place where you add and organize data, and the mssql based framework a place where you actually consume that data (and maybe, editing existing fields). If I remember correctly I worked on a chatbot development services project 2 years back and I was refactoring the strapi-database package to be able to support additional strapi-connector-* packages in the future to allow the community to build and manage their own connectors (Sequalize comes to mind as a previous request instead of Bookshelf/Knex). Hope it make sense.

Well, using the “raw” method just kills the purpose of using Strapi at all… Since strapi is handling all the db relations, I would need to get track of all database relation tables and their columns, increasing the problem complexity.

await strapi.connections.default.transaction
returns
TypeError: Cannot read property 'default' of undefined

Im using strapy 4…

Anyway, how would you handle the rollback exception?