# Simple CRUD with MariaDB

**URL:** <https://discourse.nodered.org/t/simple-crud-with-mariadb/74532>\
**Category:** Share Your Projects\
**Created:** [1 February 2023 03:24 UTC](https://discourse.nodered.org/t/simple-crud-with-mariadb/74532 "2023-02-01T03:24:35Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![allacmc](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/allacmc/32/73549_2.png) [@allacmc](https://discourse.nodered.org/u/allacmc)\
**Post date:** [1 February 2023 03:24 UTC](https://discourse.nodered.org/t/simple-crud-with-mariadb/74532/1 "2023-02-01T03:24:35Z")

</div>

I would like to share here a simple CRUD that I made using the DASHBOARD\_UI of NODE-RED.

This code serves as a reference for future colleagues (including myself) who need it.

Insert, show and delete data in a simple table.

Using all dasboard components from node red.

There is still no option to update the field, but this way it helps a lot.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/5/5568c3ebe3fed841726e9fa1a633631a073e0f70.png)

```auto
[{"id":"de76bac7029e67af","type":"group","z":"ea86445f05051d5d","name":"View Table","style":{"stroke":"#c8e7a7","label":true,"fill":"#e3f3d3"},"nodes":["acd4288657e0e1e9","dcf0460394397d3b","a87fae56abb17d99","6c44656e0b3661a7","c16f38761aea6e5e","c4d04057b8e30b1f","d19c329f17f15b9b"],"x":34,"y":19,"w":482,"h":222},{"id":"4639c7fae2510b5a","type":"subflow","name":"PrepBD","info":"","category":"","in":[{"x":80,"y":100,"wires":[{"id":"8cc0b55b7d0743c0"}]}],"out":[{"x":1020,"y":100,"wires":[{"id":"4135b832b386a610","port":0}]}],"env":[],"meta":{},"color":"#DDAA99"},{"id":"8cc0b55b7d0743c0","type":"change","z":"4639c7fae2510b5a","name":"copy flow/global to msg","rules":[{"t":"set","p":"payload","pt":"msg","to":"{}","tot":"json"},{"t":"set","p":"payload.ip","pt":"msg","to":"#:(persistent)::IP_vGlobalBD","tot":"global"},{"t":"set","p":"payload.porta","pt":"msg","to":"#:(persistent)::Porta_vGlobalBD","tot":"global"},{"t":"set","p":"payload.usuario","pt":"msg","to":"#:(persistent)::Usuario_vGlobalBD","tot":"global"},{"t":"set","p":"payload.senha","pt":"msg","to":"#:(persistent)::Senha_vGlobalBD","tot":"global"},{"t":"set","p":"payload.nomeBD","pt":"msg","to":"#:(persistent)::NomeBD_vGlobalBD","tot":"global"}],"action":"","property":"","from":"","to":"","reg":false,"x":290,"y":100,"wires":[["effa65099dd2a525"]]},{"id":"4135b832b386a610","type":"mysql2","z":"4639c7fae2510b5a","name":"","server":"","bind":"","topic":"","x":760,"y":100,"wires":[[]]},{"id":"effa65099dd2a525","type":"function","z":"4639c7fae2510b5a","name":"prep_MQTT_Conn","func":"\nconst ip = msg.payload.ip\nconst porta = msg.payload.porta\nconst usuario = msg.payload.usuario\nconst senha = msg.payload.senha\nconst nomeBD = msg.payload.nomeBD\n\nmsg.server = {\n \"host\": ip,\n \"port\": porta,\n \"username\": usuario,\n \"password\": senha,\n \"db\": nomeBD\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":550,"y":100,"wires":[["4135b832b386a610"]]},{"id":"eb96b8b79efaea17","type":"change","z":"4639c7fae2510b5a","name":"","rules":[{"t":"set","p":"server","pt":"msg","to":"{'host': $globalContext('IP_vGlobalBD'),\t 'port': $globalContext('Porta_vGlobalBD'),\t 'username': $globalContext('Usuario_vGlobalBD'),\t 'password': $globalContext('Senha_vGlobalBD'),\t 'bd': \"TelemetriaEnervision\"\t }","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":260,"y":260,"wires":[[]]},{"id":"4a50ea77c5d0ed5b","type":"comment","z":"4639c7fae2510b5a","name":"Quem sabe futuro usar esse Change","info":"Não consegui fazer funcionar com o mysql2, ele diz que o nome do banco de dados não está selecionado.","x":320,"y":220,"wires":[]},{"id":"acd4288657e0e1e9","type":"inject","z":"ea86445f05051d5d","g":"de76bac7029e67af","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":"3","topic":"","payload":"","payloadType":"date","x":150,"y":100,"wires":[["dcf0460394397d3b"]]},{"id":"dcf0460394397d3b","type":"function","z":"ea86445f05051d5d","g":"de76bac7029e67af","name":"f_SQL","func":"\n//msg.topic = \"Select Mergulhador.*, CONCAT('<a target=_blank href=apagarMergulhador', '', '>Apagar</a>') as Apagar from Mergulhador\"\n//msg.topic = \"Select Mergulhador.*, CONCAT('<a target=framename href=apagarMergulhador?nMergulhador=', nMergulhador, '>Apagar</a>') as Apagar from Mergulhador\"\n\nmsg.topic = \"Select Mergulhador.*, CONCAT('<a target=xApagar href=apagarMergulhador?nMergulhador=', nMergulhador, '>Apagar</a>') as Apagar from Mergulhador\"\n\n\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":430,"y":80,"wires":[["d19c329f17f15b9b"]]},{"id":"a87fae56abb17d99","type":"ui_table","z":"ea86445f05051d5d","g":"de76bac7029e67af","group":"972474baadf22d9f","name":"","order":10,"width":14,"height":11,"columns":[{"field":"Mergulhador","title":"Mergulhador","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"Apagar","title":"Apagar","width":"15%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}}],"outputs":1,"cts":true,"x":430,"y":200,"wires":[[]]},{"id":"6c44656e0b3661a7","type":"link in","z":"ea86445f05051d5d","g":"de76bac7029e67af","name":"Mostra_Tabela","links":["f7cecf442f2f57d9","c7578da38b131f5c"],"x":275,"y":120,"wires":[["dcf0460394397d3b"]]},{"id":"c16f38761aea6e5e","type":"ui_template","z":"ea86445f05051d5d","g":"de76bac7029e67af","group":"972474baadf22d9f","name":"","order":33,"width":3,"height":1,"format":"<iframe name=xApagar style=\"display: none;\"></iframe>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","className":"","x":140,"y":200,"wires":[[]]},{"id":"c4d04057b8e30b1f","type":"ui_button","z":"ea86445f05051d5d","g":"de76bac7029e67af","name":"","group":"972474baadf22d9f","order":7,"width":5,"height":1,"passthru":false,"label":"Atualizar","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":140,"y":60,"wires":[["dcf0460394397d3b"]]},{"id":"d19c329f17f15b9b","type":"subflow:4639c7fae2510b5a","z":"ea86445f05051d5d","g":"de76bac7029e67af","name":"","x":430,"y":140,"wires":[["a87fae56abb17d99"]]},{"id":"972474baadf22d9f","type":"ui_group","name":"","tab":"657fd39515811ee7","order":1,"disp":true,"width":16,"collapse":false,"className":""},{"id":"657fd39515811ee7","type":"ui_tab","name":"Mergulhadores","icon":"dashboard","order":5,"disabled":false,"hidden":false},{"id":"059f04f0839c5d5a","type":"group","z":"ea86445f05051d5d","name":"Insert Row","style":{"label":true,"stroke":"#0070c0","fill":"#bfdbef"},"nodes":["8757bcd1fc3cb715","8a4d2e4459a0b06e","acce5b075c104af8","167c0a8b5cd79911","aaaf375cf8695f44","ed7226ddb2937935","f7cecf442f2f57d9","376305b879dfdf48"],"x":34,"y":259,"w":812,"h":262},{"id":"8757bcd1fc3cb715","type":"ui_text_input","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"","label":"Mergulhador","tooltip":"","group":"972474baadf22d9f","order":3,"width":9,"height":1,"passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":690,"y":360,"wires":[["167c0a8b5cd79911"]]},{"id":"8a4d2e4459a0b06e","type":"ui_button","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"","group":"972474baadf22d9f","order":4,"width":5,"height":1,"passthru":false,"label":"Inserir","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":360,"wires":[["aaaf375cf8695f44"]]},{"id":"acce5b075c104af8","type":"function","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"f_SQL","func":"let vMergulhador = msg.payload\n\nmsg.topic = \"Insert INTO Mergulhador (Mergulhador) Values ('\" + vMergulhador + \"')\";\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":470,"y":420,"wires":[["376305b879dfdf48"]]},{"id":"167c0a8b5cd79911","type":"change","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"","rules":[{"t":"set","p":"vMergulhador","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":720,"y":420,"wires":[[]]},{"id":"aaaf375cf8695f44","type":"change","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"vMergulhador","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":320,"y":360,"wires":[["ed7226ddb2937935","acce5b075c104af8"]]},{"id":"ed7226ddb2937935","type":"change","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":500,"y":300,"wires":[["8757bcd1fc3cb715"]]},{"id":"f7cecf442f2f57d9","type":"link out","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"link out 1","mode":"link","links":["6c44656e0b3661a7"],"x":685,"y":480,"wires":[]},{"id":"376305b879dfdf48","type":"subflow:4639c7fae2510b5a","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"","x":470,"y":480,"wires":[["f7cecf442f2f57d9"]]},{"id":"b5f89aababd0dc2b","type":"group","z":"ea86445f05051d5d","name":"Delete Row","style":{"stroke":"#6f2fa0","fill":"#b797cf","label":true},"nodes":["5a6a5fd24ca74cfa","965b9fcea32fdc32","6047941a4bcb42c6","f6c496041602c3c0","34d8ce1f4f8b96b1","c7578da38b131f5c","2a1da7bed5e5e333","69f16a8e5cb36462"],"x":554,"y":19,"w":572,"h":202},{"id":"5a6a5fd24ca74cfa","type":"ui_toast","z":"ea86445f05051d5d","g":"b5f89aababd0dc2b","position":"dialog","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"SIM","cancel":"NÃO","raw":false,"className":"","topic":"","name":"","x":810,"y":100,"wires":[["34d8ce1f4f8b96b1"]]},{"id":"965b9fcea32fdc32","type":"change","z":"ea86445f05051d5d","g":"b5f89aababd0dc2b","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"Deseja apagar esse registro ?","tot":"str"},{"t":"set","p":"vnMergulhador","pt":"flow","to":"payload","tot":"msg"},{"t":"set","p":"payload","pt":"msg","to":"Esse registro será apagado definitivamente","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":820,"y":60,"wires":[["5a6a5fd24ca74cfa"]]},{"id":"6047941a4bcb42c6","type":"function","z":"ea86445f05051d5d","g":"b5f89aababd0dc2b","name":"f_SQL","func":"let vnMergulhador = msg.payload\n\nmsg.topic = \"DELETE FROM Mergulhador WHERE nMergulhador = \" + vnMergulhador ;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1030,"y":60,"wires":[["69f16a8e5cb36462"]]},{"id":"f6c496041602c3c0","type":"change","z":"ea86445f05051d5d","g":"b5f89aababd0dc2b","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"vnMergulhador.nMergulhador","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":820,"y":180,"wires":[["6047941a4bcb42c6"]]},{"id":"34d8ce1f4f8b96b1","type":"switch","z":"ea86445f05051d5d","g":"b5f89aababd0dc2b","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"SIM","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":790,"y":140,"wires":[["f6c496041602c3c0"]]},{"id":"c7578da38b131f5c","type":"link out","z":"ea86445f05051d5d","g":"b5f89aababd0dc2b","name":"link out 2","mode":"link","links":["6c44656e0b3661a7"],"x":1085,"y":160,"wires":[]},{"id":"2a1da7bed5e5e333","type":"http in","z":"ea86445f05051d5d","g":"b5f89aababd0dc2b","name":"Apagar","url":"/ui/apagarMergulhador","method":"get","upload":false,"swaggerDoc":"","x":630,"y":60,"wires":[["965b9fcea32fdc32"]]},{"id":"69f16a8e5cb36462","type":"subflow:4639c7fae2510b5a","z":"ea86445f05051d5d","g":"b5f89aababd0dc2b","name":"","x":1030,"y":100,"wires":[["c7578da38b131f5c"]]}]

```

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [1 February 2023 08:38 UTC](https://discourse.nodered.org/t/simple-crud-with-mariadb/74532/2 "2023-02-01T08:38:44Z")

</div>

Just in case anyone asks what CRUD means, here's a link to an explanation.

> **[Create, read, update and delete](https://en.wikipedia.org/wiki/Create,_read,_update_and_delete)**
>
> Pages for logged out editors learn more
> 
> In computer programming, create, read, update, and delete (CRUD) are the four basic operations of persistent storage. CRUD is also sometimes used to describe user interface conventions that facilitate viewing, searching, and changing information using computer-based forms and reports. The term was likely first popularized by James Martin in his 1983 book Managing the Data-base environment.
> Data can be put in a location/area of a storage mechanism...