# How to insert in a database?

**URL:** <https://discourse.nodered.org/t/how-to-insert-in-a-database/66435>\
**Category:** General\
**Tags:** database\
**Created:** [15 August 2022 09:53 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435 "2022-08-15T09:53:54Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![mykerinos1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mykerinos1/32/46877_2.png) [@mykerinos1](https://discourse.nodered.org/u/mykerinos1)\
**Post date:** [15 August 2022 09:53 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/1 "2022-08-15T09:53:54Z")

</div>

Hello,  
i just created a new database and i'd like to insert data coming from a "http Request "that output is a parsed json object but i d on't know how to doCan you help me insert these data ?  
i created my sql node and connect is ok. but after i dont know what to do

 ![jsonparse](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/f/6fe9644c4d140308401ffb0ddd0a6df1bd64a2d2.jpeg)

 ![nodered](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/a/9aa46de825e8dcc0f8d452aa0ed5ac7c67394cd4.jpeg)

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [15 August 2022 10:10 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/2 "2022-08-15T10:10:12Z")

</div>

Assuming that it's a MySQL or Mariadb database, you need something like this, maybe in a function.

```auto
msg.payload = {temp: msg.payload.data.conditions[0].temp, hum: msg.payload.data.conditions[0].hum};
msg.topic = "INSERT INTO tablename (temp, hum) VALUES(:temp, :hum)";
return msg; 

```

Obviously use the proper table and field names.  
You can get the right path for each data value from the debug panel "Copy path" button.

 ![Untitled 2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/f/5fa743d6b71754042344f74d1170b9a1d3e45d06.jpeg)

---

<div class="post-metadata">

**Author:** ![mykerinos1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mykerinos1/32/46877_2.png) [@mykerinos1](https://discourse.nodered.org/u/mykerinos1)\
**Post date:** [15 August 2022 11:16 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/3 "2022-08-15T11:16:46Z")

</div>

Hello it work perfectly. Thanks a lot 😉  
one more question . i would like to modiffy this value before insert in database because temperature is in farenheit and i would like it in celsius . i have to do something like ("value"-32)/1.8  
Can you say me how to do ?

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [15 August 2022 12:03 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/4 "2022-08-15T12:03:57Z")

</div>

So at the top of the function node that sets up your SQL insert statement, define a function like this:

```auto
const FtoC = fahrenheit => (fahrenheit - 32) * 5 / 9;

```

And you use it by (for example) `FtoC(msg.payload.data.conditions[0].temp)`

(I don't really understand the syntax, I just copied from [https://quickref.me/convert-fahrenheit-to-celsius](https://quickref.me/convert-fahrenheit-to-celsius) )

---

<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:** [15 August 2022 12:28 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/5 "2022-08-15T12:28:50Z")

</div>

I think I can explain the maths.

The centigrade scale goes from 0 to 100 degrees C (freezing to boiling point)  
Obviously other values are available.  
The fahrenheit scale goes from 32 to 212 degrees F (freezing to boiling point).  
So there are 100 increments in the centigrade scale and 180 increments (i.e. 212 - 32) in the F scale.  
So if you have a value in F and want to convert it to C, you need to subtract 32 and then multiply by 100/180 (which is 5/9 when reduced to its lowest common denominator).

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [15 August 2022 12:58 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/6 "2022-08-15T12:58:28Z")

</div>

It's not so much the maths I don't grasp, it's the =\> operator !

---

<div class="post-metadata">

**Author:** ![mykerinos1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mykerinos1/32/46877_2.png) [@mykerinos1](https://discourse.nodered.org/u/mykerinos1)\
**Post date:** [15 August 2022 13:03 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/7 "2022-08-15T13:03:37Z")

</div>

i have a result but 2 troubles.  
First how can i round it ?  
second wich payload must i set because i ve an error

 ![nodered](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/1/512491fe4a06e6973d67514f3cb8c2c2a5d8c60b.jpeg)  
 ![node](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/a/ea2f3684077d9875398859ab8453db57e83a3af4.jpeg)

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [15 August 2022 13:08 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/8 "2022-08-15T13:08:59Z")

</div>

VALUES(:temp) assumes that your temperature value is in msg.payload.temp.

You need msg.payload.temp = FtoC( etc )

There was a thread in the forum very recently about rounding values.

---

<div class="post-metadata">

**Author:** ![mykerinos1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mykerinos1/32/46877_2.png) [@mykerinos1](https://discourse.nodered.org/u/mykerinos1)\
**Post date:** [15 August 2022 13:47 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/9 "2022-08-15T13:47:43Z")

</div>

For round i found a solution: in database i set the round;

i d like to insert also time that i format with a node , formatting is working well and can be send in database but when i want to add it with the current node 'temp' it doesn't work.  
how can i add more sensors and data because i can't add it with the FtoC const ?

 ![node](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/1/e161ae0b46dac44698b377ebac4e1483f6fce307.jpeg)

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [15 August 2022 14:54 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/10 "2022-08-15T14:54:27Z")

</div>

> [@mykerinos1](#):
>
> i d like to insert also time that i format with a node , formatting is working well and can be send in database but when i want to add it with the current node 'temp' it doesn't work.

I am not sure I understand the question. However this bit of your screen capture  
 ![Untitled 2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/0/d07a03c31e02b15bcbf1836cdabd6b74ceff5999.jpeg)  
looks wrong to me.

What you are doing is constructing a "Prepared Query". msg.payload contains an object whose elements are named in the INSERT statement. msg.topic contains the INSERT statement.  
So an example

```auto
msg.payload = {temperature: FtoC(msg.payload.data.conditions[0].temp), humidity: 92}; // NB this replaces the previous contents of msg.payload
msg.payload.windspeed = 18; // Since msg.payload is an object, you can create a new element like this
msg.payload.winddirection = "NNE";
msg.topic = "INSERT INTO observations (temperature, humidity, wind_speed, wind_direction) VALUES (:temperature, :humidity: :windspeed, :winddirection)"

```

> [@mykerinos1](#):
>
> how can i add more sensors and data because i can't add it with the FtoC const ?

Sorry, I do not understand this question. Can you explain more please?  
It would help if you share the structure of your database table, an export of your flow and the actual data that you get from your weather station. It's difficult when all we have to go on is pictures.

---

<div class="post-metadata">

**Author:** ![mykerinos1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mykerinos1/32/46877_2.png) [@mykerinos1](https://discourse.nodered.org/u/mykerinos1)\
**Post date:** [15 August 2022 15:09 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/11 "2022-08-15T15:09:13Z")

</div>

i don't understand but it ddoesn' work

 ![node](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/7/b7792090038a1f894ada2c4f34c03f98a527fead.jpeg)

each one separately it work but not both

---

<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:** [15 August 2022 15:09 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/12 "2022-08-15T15:09:26Z")

</div>

![mssing_comma](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/c/3c3fd3d845ea197997082fdac6d2870da31c91a4.png)  
I think you might have missed a comma after `:humidity` ??  
There seems to be a` :` at the end of humidity rather than a` ,`

---

<div class="post-metadata">

**Author:** ![mykerinos1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mykerinos1/32/46877_2.png) [@mykerinos1](https://discourse.nodered.org/u/mykerinos1)\
**Post date:** [15 August 2022 15:10 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/13 "2022-08-15T15:10:15Z")

</div>

this was just an exemple for mine i have comma

---

<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:** [15 August 2022 15:11 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/14 "2022-08-15T15:11:30Z")

</div>

> [@jbudd](#):
>
> `const FtoC = fahrenheit => (fahrenheit - 32) * 5 / 9;`

If you find that syntax tricky to understand (like many of us) then I recommend sticking to the more simple syntax, which is easier to understand. So instead of the above, put this at the end of the function node (_after_ `return msg`)

```auto
function FtoC( fahrenheit ) {
  return (fahrenheit - 32) * 5 / 9
}

```

Then use it exactly the same way as with the more compact syntax.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [15 August 2022 15:13 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/15 "2022-08-15T15:13:32Z")

</div>

Yes. It's the heat! 😂

---

<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:** [15 August 2022 15:14 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/16 "2022-08-15T15:14:21Z")

</div>

Same here - my fingers keep sticking to the keys.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [15 August 2022 15:18 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/17 "2022-08-15T15:18:55Z")

</div>

![Untitled 3](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/1/91939d2a012efaf09d1d812759a9faa72fa12eb5.jpeg)

This is a number: `14.7`  
This is a Javascript object, also known as a JSON object: `{temp: 14.7}`

Msg.payload.temp and msg.payload.hum should be numbers and you are making them objects.

---

<div class="post-metadata">

**Author:** ![mykerinos1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mykerinos1/32/46877_2.png) [@mykerinos1](https://discourse.nodered.org/u/mykerinos1)\
**Post date:** [15 August 2022 15:25 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/18 "2022-08-15T15:25:51Z")

</div>

Finally i have to change the ''{" with "(" and now it work.

you helped me so much this afternoon thanks a lot.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [15 August 2022 15:25 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/19 "2022-08-15T15:25:54Z")

</div>

> [@Colin](#):
>
> _after_ `return msg`

Oh wow, that goes against years of practise in other scripting languages!  
Thanks for providing the more intuitive function definition.

---

<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:** [15 August 2022 15:31 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/20 "2022-08-15T15:31:59Z")

</div>

In fact I just said after it as I find that the most convenient, it can go at the front if you prefer, but I find it gets in the way there and makes it more difficult to follow what is going on.

[Next page](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435.md?page=2)