Hello,
How can we use transaction control in mysql by Node-red
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
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.
2 Likes
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 
2 Likes
Plus the benefit that you can keep your flows clean from clutter and concentrate on the data handling tasks.
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.
1 Like
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.
2 Likes
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. 
2 Likes
Very cool Matthias, can you share your business's name?
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!)
2 Likes
Thank you for the informations. We will implement an API in our project to manage data manipulation.
TKS!!
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
