# MQTT to SQLite help please

**URL:** <https://discourse.nodered.org/t/mqtt-to-sqlite-help-please/42538>\
**Category:** Dashboard\
**Created:** [12 March 2021 13:01 UTC](https://discourse.nodered.org/t/mqtt-to-sqlite-help-please/42538 "2021-03-12T13:01:35Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Dgillett](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@Dgillett](https://discourse.nodered.org/u/Dgillett)\
**Post date:** [12 March 2021 13:01 UTC](https://discourse.nodered.org/t/mqtt-to-sqlite-help-please/42538/1 "2021-03-12T13:01:35Z")

</div>

So I am trying to store MQTT data into a SQLite, this data will not be RTD, it will only be stored ON VALUE CHANGE

 ![Node red](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/d/7de73b18de30f70fc319854fb3aa2e87fb89cf73.png)

This is what I have so far. I am using the Inject to create a data base  
Next I am Joining 4 MQTT In nodes in a Join node as an Array, and I'm getting my values  
Then I am going to a function Node

 ![Function](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/8/b8455977eb2a3f94340a594524215c33e21da54e.png)

And then going into my SQLite node

I keep getting the following Syntax error.

It says "ERROR: SQLITE\_ERROR: unrecognized token: "{""

Not sure where my Problem is

---

<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:** [12 March 2021 13:06 UTC](https://discourse.nodered.org/t/mqtt-to-sqlite-help-please/42538/2 "2021-03-12T13:06:35Z")

</div>

Firstly, please post code as code ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/7/77e263ed6e514c539d592971e54231476105c77f.png) it makes it a lot easier to read and to respond to.

Secondly, you haven't shown an example of the actual data you are getting back from MQTT. Most likely it is not quite what you think which is throwing out your insert.

Thirdly, you have multiple fields that you are updating but only one input.

My guess is that the msg.payload you are getting is an object and you need to pick it apart.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [12 March 2021 13:07 UTC](https://discourse.nodered.org/t/mqtt-to-sqlite-help-please/42538/3 "2021-03-12T13:07:37Z")

</div>

Using join node in array mode is dangerous as MQTT values can arrive in any order. Use key/Val mode to ensure values are named properties.

Also, you can't just append an array like that anyway.

---

<div class="post-metadata">

**Author:** ![Dgillett](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@Dgillett](https://discourse.nodered.org/u/Dgillett)\
**Post date:** [12 March 2021 13:16 UTC](https://discourse.nodered.org/t/mqtt-to-sqlite-help-please/42538/4 "2021-03-12T13:16:14Z")

</div>

Here is the data I am getting back after I switched to a key value Object

![data](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/0/c03583bdc06b4aacf4b87dca013b4a1721feea24.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [12 March 2021 13:22 UTC](https://discourse.nodered.org/t/mqtt-to-sqlite-help-please/42538/5 "2021-03-12T13:22:03Z")

</div>

You need to change the MQTT nodes to parse JSON. Then look at what comes out of the MQTT and JOIN nodes & use the `.value` properties in your SQL query.

---

<div class="post-metadata">

**Author:** ![Dgillett](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@Dgillett](https://discourse.nodered.org/u/Dgillett)\
**Post date:** [12 March 2021 13:27 UTC](https://discourse.nodered.org/t/mqtt-to-sqlite-help-please/42538/6 "2021-03-12T13:27:23Z")

</div>

This is what is coming out of the join node now after switching to JSON

![json values](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/d/4db785b852ed1d78f501bdbbce5ad38fcb4b6f74.png)

Does this look correct

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [12 March 2021 13:28 UTC](https://discourse.nodered.org/t/mqtt-to-sqlite-help-please/42538/7 "2021-03-12T13:28:56Z")

</div>

> [@Dgillett](#):
>
> Does this look correct

Only you know what is correct.

Expand all the items. Do you see the values you want to put in the database?

---

<div class="post-metadata">

**Author:** ![Dgillett](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@Dgillett](https://discourse.nodered.org/u/Dgillett)\
**Post date:** [12 March 2021 13:34 UTC](https://discourse.nodered.org/t/mqtt-to-sqlite-help-please/42538/8 "2021-03-12T13:34:46Z")

</div>

I'm still very new to node red, I am a controls engineer by trade, usually just program ladder logic, g-code etc. but yes it is right, did not know you could expand them. lol

> [@Steve-Mcl](#):
>
> use the `.value` properties in your SQL query.

not sure where i do this

So if i want to get this data into a SQLite table, I guess what is the next step, ive been working on this for a few shifts now and am not getting anywhere.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [12 March 2021 13:39 UTC](https://discourse.nodered.org/t/mqtt-to-sqlite-help-please/42538/9 "2021-03-12T13:39:04Z")

</div>

> [@Dgillett](#):
>
> So if i want to get this data into a SQLite table, I guess what is the next step, ive been working on this for a few shifts now and am not getting anywhere.

You are almost there.

Spend the next 5 minutes on [this](https://nodered.org/docs/user-guide/messages). Pay particular attention to the bit about the "copy path" button that appears under your mouse cursor when you hover over a value in the debug sidebar.

Then use that to help you build a SQL query.

e.g...

```javascript
msg.topic = `INSERT INTO myTable (col1, col2, col3, col4) VALUES (${msg.payload.sometinng.value}, ${msg.payload.somethingElse.value}, ${msg.payload.another.value}, ${msg.payload.i_dont_know.value});`

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [12 March 2021 13:39 UTC](https://discourse.nodered.org/t/mqtt-to-sqlite-help-please/42538/10 "2021-03-12T13:39:49Z")

</div>

> [@Dgillett](#):
>
> I'm still very new to node red

I recommend watching this playlist: [Node-RED Essentials](https://www.youtube.com/playlist?list=PLyNBB9VCLmo1hyO-4fIZ08gqFcXBkHy-6). The videos are done by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in about 1 hour. A small investment for a lot of gain.

---

<div class="post-metadata">

**Author:** ![Dgillett](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@Dgillett](https://discourse.nodered.org/u/Dgillett)\
**Post date:** [12 March 2021 14:06 UTC](https://discourse.nodered.org/t/mqtt-to-sqlite-help-please/42538/11 "2021-03-12T14:06:57Z")

</div>

I appreciate all the help!!

---

<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:** [12 March 2021 14:45 UTC](https://discourse.nodered.org/t/mqtt-to-sqlite-help-please/42538/12 "2021-03-12T14:45:51Z")

</div>

Note where your image says "Object" - that indicates to you that you have some _structured_ data. Typically SQL doesn't like that and your insert statement wants _simple_ data - a string, number, date, etc.

So you need to pick out the actual data values from those objects, that's what you need in your SQL statement.

---

<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:** [11 April 2021 14:46 UTC](https://discourse.nodered.org/t/mqtt-to-sqlite-help-please/42538/13 "2021-04-11T14:46:35Z")

</div>

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