# Save MQTT data to MySQL

**URL:** <https://discourse.nodered.org/t/save-mqtt-data-to-mysql/23569>\
**Category:** General\
**Created:** [24 March 2020 14:45 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-mysql/23569 "2020-03-24T14:45:50Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![eduardoluiztodero](https://avatars.discourse-cdn.com/v4/letter/e/dc4da7/32.png) [@eduardoluiztodero](https://discourse.nodered.org/u/eduardoluiztodero)\
**Post date:** [24 March 2020 14:45 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-mysql/23569/1 "2020-03-24T14:45:51Z")

</div>

Hello guys, I'm doing a project to monitor temperature, humidity and air quality in chicken farms. One need is to save the data in a database to generate indicators. I am sending the data to an MQTT broker and need to save it in a database. I am not able to send the data in the format I receive it. This is my flow, in the first image is the data I get from MQTT (there are several devices, this is just an example of one), in the second photo it is what appears when it passes through the mysql node. I need your help to solve this problem. Thanks.

 ![duvidamysql2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/1/c1ee240cdcf97f71c1f18818ead68dd94a928e86.png) ![duvidamysql1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/7/f79f1b8fde26f8e4cf97b48003974b9a114ba4ae.png)

```auto
[{"id":"d15a8d98.892a7","type":"tab","label":"Teste MySQL","disabled":false,"info":""},{"id":"8e15704a.5251f","type":"function","z":"d15a8d98.892a7","name":"SQL query teste","func":"msg.topic = \"SELECT valores FROM dadosteste ORDER BY id DESC LIMIT 1\";\nreturn msg;","outputs":1,"noerr":0,"x":349.0174102783203,"y":165.00003814697266,"wires":[["4ee4064.61fcaf8"]]},{"id":"4ee4064.61fcaf8","type":"mysql","z":"d15a8d98.892a7","mydb":"e9f025e8.70ddf8","name":"teste","x":556.0173225402832,"y":165.00002765655518,"wires":[["12c4989a.f34b37"]]},{"id":"12c4989a.f34b37","type":"debug","z":"d15a8d98.892a7","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":748.0172080993652,"y":166.00006866455078,"wires":[]},{"id":"628f2721.50b168","type":"mqtt in","z":"d15a8d98.892a7","name":"TCC","topic":"device3Av1","qos":"0","datatype":"json","broker":"c7266bc3.062ef8","x":135.01736450195312,"y":166.01040840148926,"wires":[["8e15704a.5251f","5519b318.8e355c"]]},{"id":"5519b318.8e355c","type":"debug","z":"d15a8d98.892a7","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":327.576416015625,"y":243.5972499847412,"wires":[]},{"id":"e9f025e8.70ddf8","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"teste_nodered","tz":""},{"id":"c7266bc3.062ef8","type":"mqtt-broker","z":"","name":"TCC ","broker":"m24.cloudmqtt.com","port":"14910","clientid":"","usetls":false,"compatmode":true,"keepalive":"1","cleansession":true,"birthTopic":"esp/test","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""}]

```

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [24 March 2020 15:23 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-mysql/23569/2 "2020-03-24T15:23:45Z")

</div>

What is the query that you are passing to the database node? Feed it to a debug node set to Show Complete Message.

**[Edit]** I mean the one writing the data of course, not the one reading it.

---

<div class="post-metadata">

**Author:** ![eduardoluiztodero](https://avatars.discourse-cdn.com/v4/letter/e/dc4da7/32.png) [@eduardoluiztodero](https://discourse.nodered.org/u/eduardoluiztodero)\
**Post date:** [24 March 2020 15:37 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-mysql/23569/3 "2020-03-24T15:37:52Z")

</div>

what I'm sending to the mysql node is what I'm getting from MQTT

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [24 March 2020 16:24 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-mysql/23569/4 "2020-03-24T16:24:34Z")

</div>

Your flow is only doing a `select` where are you tryin to insert the data?

---

<div class="post-metadata">

**Author:** ![eduardoluiztodero](https://avatars.discourse-cdn.com/v4/letter/e/dc4da7/32.png) [@eduardoluiztodero](https://discourse.nodered.org/u/eduardoluiztodero)\
**Post date:** [24 March 2020 16:29 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-mysql/23569/5 "2020-03-24T16:29:37Z")

</div>

Zenofmud, I took this query function from an example .. I never did this kind of thing, I'm new using node red .. could you give me a light at the end of the tunnel?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [24 March 2020 17:38 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-mysql/23569/6 "2020-03-24T17:38:02Z")

</div>

You need to find a primer on how to write data to the database using SQL statements. Also you will first need to design and create the database. This isn't really anything to do with node-red.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [24 March 2020 17:46 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-mysql/23569/7 "2020-03-24T17:46:38Z")

</div>

Along with @Colin, my first suggestion would be to go take an [SQL tutorial](https://www.w3schools.com/sql/). Creating SQL statements really isn't NR.

Here is a sample of an `create`, `insert`, `select` and `delete` using SQLite that you can take a look of.

 ![Screen Shot 2020-03-24 at 1.45.25 PM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/2/7270ec94e72f29814bbbc537058f2458f86cf8e9.png)

```auto
[{"id":"a06bd52c.105218","type":"sqlite","z":"7acc20b7.b27b9","mydb":"3594a0ec.6329a8","sqlquery":"msg.topic","sql":"","name":"testdb","x":690,"y":320,"wires":[["9289f4ef.b44a9"]]},{"id":"6b5a0680.fcd06","type":"inject","z":"7acc20b7.b27b9","name":"create table","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"","x":150,"y":260,"wires":[["1a35bc39.05bdd4"]]},{"id":"51107643.82d748","type":"inject","z":"7acc20b7.b27b9","name":"insert","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"","x":130,"y":320,"wires":[["b65516ba.aeb3e"]]},{"id":"a4ece8df.0e597","type":"inject","z":"7acc20b7.b27b9","name":"select","topic":"select * from greenhouse","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"","x":230,"y":140,"wires":[["a06bd52c.105218"]]},{"id":"1a35bc39.05bdd4","type":"template","z":"7acc20b7.b27b9","name":"Create","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"CREATE TABLE greenhouse (\n id INTEGER PRIMARY KEY AUTOINCREMENT, \n temperature NUMERIC, \n humidity NUMERIC, \n datetime DATE, \n device TEXT\n)","output":"str","x":370,"y":260,"wires":[["a06bd52c.105218"]]},{"id":"b65516ba.aeb3e","type":"template","z":"7acc20b7.b27b9","name":"Insert manual","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO greenhouse (\n temperature, \n humidity, \n datetime, \n device\n ) \nVALUES (\n 22.4, \n 48, \n date('now'),\n \"room3\"\n )","output":"str","x":400,"y":320,"wires":[["a06bd52c.105218"]]},{"id":"c1f2f118.befeb8","type":"inject","z":"7acc20b7.b27b9","name":"insert","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"","x":130,"y":380,"wires":[["27490a6f.de7636"]]},{"id":"67e31919.5f33c8","type":"template","z":"7acc20b7.b27b9","name":"Insert manual","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO greenhouse (\n temperature, \n humidity, \n datetime, \n device\n ) \nVALUES (\n {{payload.temperature}}, \n {{payload.humidity}},\n {{payload.datetime}},\n \"{{payload.device}}\"\n )","output":"str","x":540,"y":380,"wires":[["a06bd52c.105218","dbbade4d.e358d8"]]},{"id":"27490a6f.de7636","type":"change","z":"7acc20b7.b27b9","name":"","rules":[{"t":"delete","p":"payload","pt":"msg"},{"t":"set","p":"date","pt":"msg","to":"","tot":"date"},{"t":"set","p":"payload.temperature","pt":"msg","to":"72.3","tot":"num"},{"t":"set","p":"payload.humidity","pt":"msg","to":" 64","tot":"num"},{"t":"set","p":"payload.datetime","pt":"msg","to":"","tot":"date"},{"t":"set","p":"payload.device","pt":"msg","to":"room1","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":340,"y":380,"wires":[["67e31919.5f33c8"]]},{"id":"9289f4ef.b44a9","type":"debug","z":"7acc20b7.b27b9","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":850,"y":320,"wires":[]},{"id":"dbbade4d.e358d8","type":"debug","z":"7acc20b7.b27b9","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":750,"y":420,"wires":[]},{"id":"3594a0ec.6329a8","type":"sqlitedb","z":"","db":"/Users/Paul/databases/testdb"}]

```

---

<div class="post-metadata">

**Author:** ![lizzardguki](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lizzardguki/32/103637_2.png) [@lizzardguki](https://discourse.nodered.org/u/lizzardguki)\
**Post date:** [24 March 2020 18:05 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-mysql/23569/8 "2020-03-24T18:05:31Z")

</div>

If you still need help, i have free time and i am able to help you 😃

---

<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:** [24 March 2020 21:05 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-mysql/23569/9 "2020-03-24T21:05:23Z")

</div>

You might also want to consider whether you really do want to use a SQL db for this as they are not always ideal for time series type data.

If you later need to analyse by time, something like InfluxDB might be a better fit.

---

<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:** [23 May 2020 21:08 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-mysql/23569/10 "2020-05-23T21:08:09Z")

</div>

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