# Transaction, Commit, Rollback, Mysql

**URL:** <https://discourse.nodered.org/t/transaction-commit-rollback-mysql/20828>\
**Category:** General\
**Created:** [25 January 2020 02:45 UTC](https://discourse.nodered.org/t/transaction-commit-rollback-mysql/20828 "2020-01-25T02:45:43Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![glauberjnb](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@glauberjnb](https://discourse.nodered.org/u/glauberjnb)\
**Post date:** [25 January 2020 02:45 UTC](https://discourse.nodered.org/t/transaction-commit-rollback-mysql/20828/1 "2020-01-25T02:45:43Z")

</div>

Hello,

How can we use transaction control in mysql by Node-red

---

<div class="post-metadata">

**Author:** ![kuema](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/kuema/32/6542_2.png) [@kuema](https://discourse.nodered.org/u/kuema)\
**Post date:** [25 January 2020 08:14 UTC](https://discourse.nodered.org/t/transaction-commit-rollback-mysql/20828/2 "2020-01-25T08:14:15Z")

</div>

I haven't come up with a clean and readable flow-based solution using only Node-RED, either. Not to mention handling all error cases...

So I have put all complex database operations into separate services and use Node-RED only as data mediator between different systems.

The underlying mysql lib used by the node supports transactions in general. In fact, I am using them in my back-end services.

Maybe this documentation could be helpful: [https://github.com/mysqljs/mysql#transactions](https://github.com/mysqljs/mysql#transactions)

As stated there, it uses the standard SQL commands, so you could issue the START TRANSACTION, COMMIT, and ROLLBACK commands yourself in your queries from Node-RED. Tricky part will be proper flow and error handling, depending on the complexity of your transactions.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [25 January 2020 10:12 UTC](https://discourse.nodered.org/t/transaction-commit-rollback-mysql/20828/3 "2020-01-25T10:12:24Z")

</div>

A sensible and common approach. Effectively presenting an API as a middleware layer between your "business logic" and the database. That can also help if you ever need to replace the database engine or even when just facing a major upgrade of your database engine that breaks some interaction.

Just because Node-RED _can_ do pretty much anything doesn't mean that it is always the best tool for the job 😀

---

<div class="post-metadata">

**Author:** ![kuema](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/kuema/32/6542_2.png) [@kuema](https://discourse.nodered.org/u/kuema)\
**Post date:** [25 January 2020 10:20 UTC](https://discourse.nodered.org/t/transaction-commit-rollback-mysql/20828/4 "2020-01-25T10:20:04Z")

</div>

> [@TotallyInformation](#):
>
> Just because Node-RED _can_ do pretty much anything doesn't mean that it is always the best tool for the job 😀

Plus the benefit that you can keep your flows clean from clutter and concentrate on the data handling tasks.

> [@TotallyInformation](#):
>
> if you ever need to replace the database engine

Not even thinking of replacing, but supporting multiple different storage backends.  
That's why I had to go for that approach for our product at work, we have to support different DBMS, at the moment namely MSSQL and MySQL/MariaDB due to customer restrictions.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [25 January 2020 10:53 UTC](https://discourse.nodered.org/t/transaction-commit-rollback-mysql/20828/5 "2020-01-25T10:53:46Z")

</div>

> [@kuema](#):
>
> Not even thinking of replacing, but supporting multiple different storage backends.  
> That's why I had to go for that approach for our product at work, we have to support different DBMS, at the moment namely MSSQL and MySQL/MariaDB due to customer restrictions.

It is amazing how many developers overlook future operational requirements. No DB engine ever lasts forever and even version changes can have a big impact. As can moving platforms or other infrastructure changes. Disaggregating front-end, business logic and data stores is always a good idea for all but the simplest and most limited of projects.

---

<div class="post-metadata">

**Author:** ![kuema](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/kuema/32/6542_2.png) [@kuema](https://discourse.nodered.org/u/kuema)\
**Post date:** [25 January 2020 11:28 UTC](https://discourse.nodered.org/t/transaction-commit-rollback-mysql/20828/6 "2020-01-25T11:28:12Z")

</div>

We sell our machines to a range of completely different customers, where every machine is unique in complexity and devices/systems to communicate with. So I had to build our software platform with flexibility in mind, even back in the days when no Node-RED was involved.

Some of our systems had to "survive" multiple OS upgrades, ranging from Windows XP, 7 and even Windows 10 and their respective Server parts.

Exchanging parts of the system has certainly gotten a lot easier since we switched to a more decoupled approach, with Node-RED being a part of it. We have several backend services and can choose the best platform/language for each.  
So supporting a new DBMS requires us to just implement the respective API, no other changes in our flows are needed. 🙂

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [25 January 2020 11:49 UTC](https://discourse.nodered.org/t/transaction-commit-rollback-mysql/20828/7 "2020-01-25T11:49:22Z")

</div>

Very cool Matthias, can you share your business's name?

---

<div class="post-metadata">

**Author:** ![kuema](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/kuema/32/6542_2.png) [@kuema](https://discourse.nodered.org/u/kuema)\
**Post date:** [25 January 2020 12:15 UTC](https://discourse.nodered.org/t/transaction-commit-rollback-mysql/20828/8 "2020-01-25T12:15:07Z")

</div>

Well, it's not _my_ business. I am just an employee, so I'm a bit hesitant. 🙂

It's a medium-sized (~250 employees) mechanical engineering company from Germany.

Our software is just one small part and quite specialized. It isn't sold as a stand-alone product, but always tailored to the machine/production line. So there is no technical information publicly available.

And our team is really, really small. I am mainly doing the platform development, while the colleagues are commissioning it.

I think we already had a discussion a while ago where I gave some details of our use-case of Node-RED on this thread: [Increase canvas size? (supersize flows!)](https://discourse.nodered.org/t/increase-canvas-size-supersize-flows/14855/16)

---

<div class="post-metadata">

**Author:** ![glauberjnb](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@glauberjnb](https://discourse.nodered.org/u/glauberjnb)\
**Post date:** [28 January 2020 16:11 UTC](https://discourse.nodered.org/t/transaction-commit-rollback-mysql/20828/9 "2020-01-28T16:11:21Z")

</div>

Thank you for the informations. We will implement an API in our project to manage data manipulation.

TKS!! 🙂

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [11 February 2020 16:11 UTC](https://discourse.nodered.org/t/transaction-commit-rollback-mysql/20828/10 "2020-02-11T16:11:24Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.