Edit mysql table

How to create an editable MySql table? I want CRUD operations: to list all the rows of table, add new rows, update values, and delete record.

Those are perfectly usual actions on an SQL database, accomplished by suitably formatted INSERT, SELECT, UPDATE and DELETE SQL statements.

If you install and use node-red-node-mysql the SQL should be created as msg.topic.

For example
msg.topic = "UPDATE employees SET salary = salary * 2 WHERE employeeId = 'jbudd';"

This is rather a non-trivial exercise I'm afraid. I am assuming that you want an end-user accessible page that displays the table and then allows changes to be made?

Displaying the table is pretty easy with a dashboard table node - as long as the source data isn't very large. If it is large, you will need pagination logic which will need to be built.

Adding edit features is harder and easiest done with the help of a front-end library. This would run in the browser. But, you would still need to write some front-end code to handle updates and send them back to Node-RED where you would need more logic to send them back to the table.

You would also need some validation code in the browser and probably some in Node-RED as well - or at least a way to handle a write failure and send that back to the browser where you would need to do something with it.

Oh, and you may also want some front-end code to handle authentication and authorisation - also non-trivial.

As far as I know, there is no equivalent of the mature PHP based database management tools for Node.js - if someone knows differently, please let the forum know and perhaps some kind heart will create a suitable Node-RED wrapper.

This post may give you some ideas. It shows how to add pagination in ui-table, and editing table data. both ui-table and a ui-template html table. UI Template Edit msg.payload then Output - #4 by E1cid