# How to save a value from a node to database
**URL:** https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129
**Category:** General
**Tags:** home-assistant
**Created:** [1 April 2023 08:28 UTC](https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129 "2023-04-01T08:28:34Z")
**Posts on this page:** 15
**Page:** 1
### Author:  [@kroonp](https://discourse.nodered.org/u/kroonp)
#### Post date: [1 April 2023 08:28 UTC](https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129/1 "2023-04-01T08:28:34Z")
Hello all,
I have a flow and in this flow a can save numbers in a sql database. I want te replace the numbers in the flow to dynamic numbers from sensor. I think a need tot do this with an array.
I've tried different ways but it doesn't work. Is there anyone who wants to indicate how I can achieve this, or perhaps has an example?
Thanks in advance,
Peter
```auto
[{"id":"8b4e6a14e81f8a68","type":"tab","label":"invoer in database","disabled":false,"info":"","env":[]},{"id":"39a046a3a92a5c7f","type":"inject","z":"8b4e6a14e81f8a68","name":"Go","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"400","payloadType":"num","x":150,"y":125,"wires":[["6c2cdc1a2e01b7f3"]]},{"id":"6c2cdc1a2e01b7f3","type":"function","z":"8b4e6a14e81f8a68","name":"INSERT gas","func":"msg.topic = \"INSERT INTO meterstanden (gas) VALUES (300)\";\nmsg.payload = [300];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":345,"y":100,"wires":[["3c11cc576ec8a049"]]},{"id":"1e7bc0f02695eb01","type":"debug","z":"8b4e6a14e81f8a68","name":"","active":true,"console":"false","complete":"false","x":830,"y":150,"wires":[]},{"id":"3c11cc576ec8a049","type":"mysql","z":"8b4e6a14e81f8a68","mydb":"a351da32cbcc0b60","name":"","x":630,"y":150,"wires":[["1e7bc0f02695eb01"]]},{"id":"57068c50e1424f0c","type":"inject","z":"8b4e6a14e81f8a68","name":"Go","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"400","payloadType":"num","x":150,"y":175,"wires":[["7c03990938557fdd","766622559d9a88ab"]]},{"id":"7c03990938557fdd","type":"function","z":"8b4e6a14e81f8a68","name":"INSERT gas","func":"msg.topic = \"INSERT INTO meterstanden (gas) VALUES (555)\";\nmsg.payload = [350];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":345,"y":150,"wires":[["3c11cc576ec8a049"]]},{"id":"766622559d9a88ab","type":"function","z":"8b4e6a14e81f8a68","name":"SELECT Bind","func":"msg.topic = \"SELECT * FROM meterstanden \";\nmsg.payload = [msg.payload];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":345,"y":200,"wires":[["3c11cc576ec8a049"]]},{"id":"dcc3256d0ce552f5","type":"inject","z":"8b4e6a14e81f8a68","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":160,"y":225,"wires":[["766622559d9a88ab"]]},{"id":"e8e5b1e52ef8e6a6","type":"inject","z":"8b4e6a14e81f8a68","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"name\":\"John\",\"age\":30}","payloadType":"json","x":400,"y":250,"wires":[["3c11cc576ec8a049"]]},{"id":"99301d0f9b754bc5","type":"debug","z":"8b4e6a14e81f8a68","name":"","active":true,"console":"false","complete":"false","x":745,"y":475,"wires":[]},{"id":"f6bc45f84d3ec7b5","type":"mysql","z":"8b4e6a14e81f8a68","mydb":"a351da32cbcc0b60","name":"","x":555,"y":475,"wires":[["99301d0f9b754bc5"]]},{"id":"9b2fa9e4f573e509","type":"function","z":"8b4e6a14e81f8a68","name":"function 16","func":"msg.topic = \"INSERT INTO meterstanden (gas,dal) VALUES (300,200)\";\nmsg.payload = [300,500];\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":355,"y":475,"wires":[["f6bc45f84d3ec7b5"]]},{"id":"cb79abc2378959cf","type":"inject","z":"8b4e6a14e81f8a68","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":185,"y":475,"wires":[["9b2fa9e4f573e509"]]},{"id":"a351da32cbcc0b60","type":"MySQLdatabase","name":"Homeassistant ","host":"core-mariadb","port":"3306","db":"homeassistant","tz":"","charset":"UTF8"}]
```
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [1 April 2023 08:45 UTC](https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129/2 "2023-04-01T08:45:38Z")
If you are using [node-red-node-mysql (node) - Node-RED](https://flows.nodered.org/node/node-red-node-mysql)
then try
```auto
msg.topic = "INSERT INTO meterstanden (gas) VALUES (:gas)";
msg.payload = {gas: msg.payload};
return msg;
```
[edit] fixed error as msg.payload is a number, it has to be assign as an object.
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [1 April 2023 08:45 UTC](https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129/3 "2023-04-01T08:45:59Z")
Hi @kroonp
Your almost there:
instead of hard coding the values, replace them with a question mark.
The question marks will be used by the MySQL node - and will represent the ordinance, that is set in your `payload` array .
```auto
msg.topic = "INSERT INTO person(first_name,last_name) VALUES (?,?)";
msg.payload = ['Marcus','Davies'];
return msg;
```
If you are inserting these values based on an event, just create the array at that time
```auto
msg.topic = "INSERT INTO meterstanden(gas) VALUES (?)";
msg.payload = [msg.payload.someEvent.value];
return msg;
```
EDIT
@E1cid answer is prefered as it's clearer where the values are taken from.
---
### Author:  [@kroonp](https://discourse.nodered.org/u/kroonp)
#### Post date: [1 April 2023 14:13 UTC](https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129/4 "2023-04-01T14:13:38Z")
I made an input\_number.gas en give it number:777
in the table a field: gas - int - 11
```auto
msg.topic = "INSERT INTO meterstanden(gas) VALUES (?)";
msg.payload = [msg.payload.input_number.gas.value];
return msg;
```
```auto
TypeError: Cannot read properties of undefined (reading 'gas')
```
What do I wrong?
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [1 April 2023 14:16 UTC](https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129/5 "2023-04-01T14:16:48Z")
`msg.payload = [msg.payload.input_number.gas.value];`
Should probably be
`msg.payload = [msg.payload.input_number.gas];`
`value` was just my example
Just pass it the path to the value.
`msg.payload.Something`
---
### Author:  [@kroonp](https://discourse.nodered.org/u/kroonp)
#### Post date: [1 April 2023 15:30 UTC](https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129/6 "2023-04-01T15:30:37Z")
Thanks Marcus.
I have now omitted 'value'. Unfortunately I still get an error message.
```auto
[{"id":"02ee788a8ce3d967","type":"debug","z":"8b4e6a14e81f8a68","name":"","active":true,"console":"false","complete":"false","x":730,"y":700,"wires":[]},{"id":"5f319d3bd6902a02","type":"mysql","z":"8b4e6a14e81f8a68","mydb":"a351da32cbcc0b60","name":"","x":540,"y":700,"wires":[["02ee788a8ce3d967"]]},{"id":"904badc6413a0560","type":"function","z":"8b4e6a14e81f8a68","name":"function 18","func":"msg.topic = \"INSERT INTO meterstanden(gas) VALUES (?)\";\nmsg.payload = [msg.payload.input_number.gas];\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":700,"wires":[["5f319d3bd6902a02"]]},{"id":"542fa217a468162d","type":"inject","z":"8b4e6a14e81f8a68","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":160,"y":700,"wires":[["904badc6413a0560","2ab13501b2f0b12e"]]},{"id":"2ab13501b2f0b12e","type":"api-current-state","z":"8b4e6a14e81f8a68","name":"input_number.gas","server":"3c6b2d2d.b63e72","version":3,"outputs":1,"halt_if":"","halt_if_type":"num","halt_if_compare":"is","entity_id":"input_number.gas","state_type":"num","blockInputOverrides":false,"outputProperties":[{"property":"payload","propertyType":"msg","value":"","valueType":"entityState"},{"property":"data","propertyType":"msg","value":"","valueType":"entity"}],"for":"0","forType":"num","forUnits":"minutes","override_topic":false,"state_location":"payload","override_payload":"msg","entity_location":"data","override_data":"msg","x":360,"y":750,"wires":[["f8d9b6ff411aae65"]]},{"id":"f8d9b6ff411aae65","type":"debug","z":"8b4e6a14e81f8a68","name":"debug 395","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":730,"y":750,"wires":[]},{"id":"a351da32cbcc0b60","type":"MySQLdatabase","name":"Homeassistant ","host":"core-mariadb","port":"3306","db":"homeassistant","tz":"","charset":"UTF8"},{"id":"3c6b2d2d.b63e72","type":"server","name":"Home Assistant","version":5,"addon":true,"rejectUnauthorizedCerts":true,"ha_boolean":"y|yes|true|on|home|open","connectionDelay":true,"cacheJson":true,"heartbeat":false,"heartbeatInterval":30,"areaSelector":"friendlyName","deviceSelector":"friendlyName","entitySelector":"friendlyName","statusSeparator":"at: ","statusYear":"hidden","statusMonth":"short","statusDay":"numeric","statusHourCycle":"h23","statusTimeFormat":"h:m","enableGlobalContextStore":true}]
```
```auto
01/04/2023, 17:29:37node: function 18
function : (error)
"TypeError: Cannot read properties of undefined (reading 'gas')"
01/04/2023, 17:29:37node: debug 395
msg.payload : number
777
```
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [1 April 2023 16:00 UTC](https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129/7 "2023-04-01T16:00:48Z")
`msg.payload.input_number.gas` does not exist in your message
According to your payload it will be. `msg.payload`
`msg.payload.input_number.gas` will be a message of
```auto
{
payload: {
input_number: {
gas: 100
}
}
}
```
but your sending (Im using an example timestamp)
```auto
{
payload:1680364764
}
```
Therefore, it will be (based on your inject node)
```auto
msg.topic = "INSERT INTO meterstanden(gas) VALUES (?)";
msg.payload = [msg.payload];
return msg;
```
---
### Author:  [@kroonp](https://discourse.nodered.org/u/kroonp)
#### Post date: [3 April 2023 11:46 UTC](https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129/8 "2023-04-03T11:46:33Z")
Dear people, thanks for the help. Unfortunately, it didn't work out. I have tried all possibilities to get a working flow. I give up!
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [3 April 2023 14:24 UTC](https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129/9 "2023-04-03T14:24:22Z")
> [@kroonp](#):
>
> I have tried all possibilities to get a working flow.
I beg to disagree. You have not tried the one that works.
If you showed us the message you are feeding to the db node, that doesn't work, someone could probably help.
---
### Author:  [@kroonp](https://discourse.nodered.org/u/kroonp)
#### Post date: [3 April 2023 16:19 UTC](https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129/10 "2023-04-03T16:19:56Z")
I didn't want to bother you any further.
I'm now ready to store the values in the database table. However, I now get 5 new records with every run when the intention is to store the 5 values in 1 record. The stored values are correct and will be placed in the correct field of the database. Furthermore, my approach to the flow is probably much too cumbersome and it should be simpler.
My knowledge is still insufficient to get it done in the right way.
Is send along the flow.
```auto
[{"id":"403b34f4df79d237","type":"function","z":"a4d2bf7d268cee7f","name":"function 29","func":"var arrNum = [];\ncontext.node1 = context.node1 || 0;\ncontext.node2 = context.node2 || 0;\ncontext.node3 = context.node3 || 0;\ncontext.node4 = context.node4 || 0;\ncontext.node5 = context.node5 || 0;\n\n\nif (msg.topic == 'gas') {\n context.node1 = msg.payload;\n arrNum[0] = msg.payload;\n}\nelse if (msg.topic == 'dal_cons') {\n context.node2 = msg.payload;\n arrNum[1] = msg.payload;\n}\nelse if (msg.topic == 'dal_prod') {\n context.node3 = msg.payload;\n arrNum[2] = msg.payload;\n}\nelse if (msg.topic == 'normaal_cons') {\n context.node4 = msg.payload;\n arrNum[3] = msg.payload;\n}\nelse if(msg.topic == 'normaal_prod') {\n context.node5 = msg.payload;\n arrNum[4] = msg.payload;\n}\n\nmsg.payload = [arrNum[0], arrNum[1], arrNum[2], arrNum[3], arrNum[4] ];\nmsg.topic = \"INSERT INTO meterstanden (`gas`,`dal_cons`, `dal_prod`,`normaal_cons`, `normaal_prod`) VALUES (?,?,?,?,?);\"\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":620,"y":725,"wires":[["d4a4b586d527319c"]]},{"id":"5bf82d602a3c678e","type":"inject","z":"a4d2bf7d268cee7f","name":"","props":[{"p":"payload"},{"p":"topic","v":"33","vt":"num"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":135,"y":725,"wires":[["a747ac4a85b7ae7e","ce20a7827b1dde06","c38ed5c5513151d6","0b5e447ef0455e4e","ccfa296b4b817d86"]]},{"id":"a747ac4a85b7ae7e","type":"api-current-state","z":"a4d2bf7d268cee7f","name":"gas","server":"3c6b2d2d.b63e72","version":3,"outputs":1,"halt_if":"","halt_if_type":"num","halt_if_compare":"is","entity_id":"input_number.gas","state_type":"num","blockInputOverrides":false,"outputProperties":[{"property":"payload","propertyType":"msg","value":"","valueType":"entityState"},{"property":"data","propertyType":"msg","value":"","valueType":"entity"},{"property":"topic","propertyType":"msg","value":"gas","valueType":"str"}],"for":"0","forType":"num","forUnits":"minutes","override_topic":false,"state_location":"payload","override_payload":"msg","entity_location":"data","override_data":"msg","x":325,"y":625,"wires":[["403b34f4df79d237"]]},{"id":"ce20a7827b1dde06","type":"api-current-state","z":"a4d2bf7d268cee7f","name":"dal_cons","server":"3c6b2d2d.b63e72","version":3,"outputs":1,"halt_if":"","halt_if_type":"num","halt_if_compare":"is","entity_id":"sensor.energy_consumed_tariff_1","state_type":"num","blockInputOverrides":false,"outputProperties":[{"property":"payload","propertyType":"msg","value":"","valueType":"entityState"},{"property":"data","propertyType":"msg","value":"","valueType":"entity"},{"property":"topic","propertyType":"msg","value":"dal_cons","valueType":"str"}],"for":"0","forType":"num","forUnits":"minutes","override_topic":false,"state_location":"payload","override_payload":"msg","entity_location":"data","override_data":"msg","x":335,"y":675,"wires":[["403b34f4df79d237"]]},{"id":"c38ed5c5513151d6","type":"api-current-state","z":"a4d2bf7d268cee7f","name":"dal_prod","server":"3c6b2d2d.b63e72","version":3,"outputs":1,"halt_if":"","halt_if_type":"num","halt_if_compare":"is","entity_id":"sensor.energy_produced_tariff_1","state_type":"num","blockInputOverrides":false,"outputProperties":[{"property":"payload","propertyType":"msg","value":"","valueType":"entityState"},{"property":"data","propertyType":"msg","value":"","valueType":"entity"},{"property":"topic","propertyType":"msg","value":"dal_prod","valueType":"str"}],"for":"0","forType":"num","forUnits":"minutes","override_topic":false,"state_location":"payload","override_payload":"msg","entity_location":"data","override_data":"msg","x":335,"y":725,"wires":[["403b34f4df79d237"]]},{"id":"d4a4b586d527319c","type":"mysql","z":"a4d2bf7d268cee7f","mydb":"a351da32cbcc0b60","name":"","x":830,"y":725,"wires":[["8b52072e32428d4c"]]},{"id":"0b5e447ef0455e4e","type":"api-current-state","z":"a4d2bf7d268cee7f","name":"normaal_cons","server":"3c6b2d2d.b63e72","version":3,"outputs":1,"halt_if":"","halt_if_type":"num","halt_if_compare":"is","entity_id":"sensor.energy_consumed_tariff_2","state_type":"num","blockInputOverrides":false,"outputProperties":[{"property":"payload","propertyType":"msg","value":"","valueType":"entityState"},{"property":"data","propertyType":"msg","value":"","valueType":"entity"},{"property":"topic","propertyType":"msg","value":"normaal_cons","valueType":"str"}],"for":"0","forType":"num","forUnits":"minutes","override_topic":false,"state_location":"payload","override_payload":"msg","entity_location":"data","override_data":"msg","x":355,"y":775,"wires":[["403b34f4df79d237"]]},{"id":"ccfa296b4b817d86","type":"api-current-state","z":"a4d2bf7d268cee7f","name":"normaal_prod","server":"3c6b2d2d.b63e72","version":3,"outputs":1,"halt_if":"","halt_if_type":"num","halt_if_compare":"is","entity_id":"sensor.energy_produced_tariff_2","state_type":"num","blockInputOverrides":false,"outputProperties":[{"property":"payload","propertyType":"msg","value":"","valueType":"entityState"},{"property":"data","propertyType":"msg","value":"","valueType":"entity"},{"property":"topic","propertyType":"msg","value":"normaal_prod","valueType":"str"}],"for":"0","forType":"num","forUnits":"minutes","override_topic":false,"state_location":"payload","override_payload":"msg","entity_location":"data","override_data":"msg","x":355,"y":825,"wires":[["403b34f4df79d237"]]},{"id":"8b52072e32428d4c","type":"debug","z":"a4d2bf7d268cee7f","name":"debug 400","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":780,"y":800,"wires":[]},{"id":"3c6b2d2d.b63e72","type":"server","name":"Home Assistant","version":5,"addon":true,"rejectUnauthorizedCerts":true,"ha_boolean":"y|yes|true|on|home|open","connectionDelay":true,"cacheJson":true,"heartbeat":false,"heartbeatInterval":30,"areaSelector":"friendlyName","deviceSelector":"friendlyName","entitySelector":"friendlyName","statusSeparator":"at: ","statusYear":"hidden","statusMonth":"short","statusDay":"numeric","statusHourCycle":"h23","statusTimeFormat":"h:m","enableGlobalContextStore":true},{"id":"a351da32cbcc0b60","type":"MySQLdatabase","name":"Homeassistant ","host":"core-mariadb","port":"3306","db":"homeassistant","tz":"","charset":"UTF8"}]
```
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [3 April 2023 17:17 UTC](https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129/11 "2023-04-03T17:17:13Z")
> I now get 5 new records with every run when the intention is to store the 5 values in 1 record
Only today a similar "problem" occurred.
Your 5 states, DO NOT represent 1 message - they represent 5 messages = 5 executions of your `function 29` and `MySQL` node.
Messages in Node RED, are separate entities - and EACH message will travel down the flow - executing the nodes they travel through.
The Fix:
use a `JOIN` node to combine these 5 messages, allowing them to be delivered as 1 = 1 execution of the `function 29` and `MySQL` node.
Set the `JOIN` node to deliver the combined messages after the 5th message.
The thing to remember: Each message is a seperate run or instance of data tarvelling through your flow, you can of course join these messages together as I have done above.
EDIT:
You will need to rewrite your `function` node, to be able to extract the relevant parts out of the combined payload.
You can set the `JOIN` node to return an array of messages
```auto
payload:[
{topic'gas',payload: 45},
{topic'dal_cons',payload: 35},
]
```
or to combiner the properties of each message, but I don't think that will work work with you set up
(not many of use Home Assistant)
---
### Author:  [@kroonp](https://discourse.nodered.org/u/kroonp)
#### Post date: [3 April 2023 19:45 UTC](https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129/12 "2023-04-03T19:45:51Z")
The result of the join node is now:
{"gas":777,"dal\_cons":9220.795,"dal\_prod":1394.833,"normaal\_cons":7173.891,"normaal\_prod":3225.873}
So that works, but how do I get them into the database now.
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [3 April 2023 19:59 UTC](https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129/13 "2023-04-03T19:59:58Z")
Use the approach by @E1cid
pass the resulting `payload` created by the `JOIN` node into your `function` node with the following.
```auto
msg.topic = "INSERT INTO meterstanden (gas,dal_cons,dal_prod,normaal_cons,normaal_prod) VALUES(:gas,:dal_cons,:normaal_cons,:normaal_prod)";
return msg;
```
Explanation:
`:gas` means use the property in your `payload` named `gas`
Note: I have guess your column name here.
---
### Author:  [@kroonp](https://discourse.nodered.org/u/kroonp)
#### Post date: [3 April 2023 22:10 UTC](https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129/14 "2023-04-03T22:10:49Z")
Marcus,
Thanx for you help. All is working.
Top.
Peter
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [2 June 2023 22:10 UTC](https://discourse.nodered.org/t/how-to-save-a-value-from-a-node-to-database/77129/15 "2023-06-02T22:10:53Z")
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.