# Save MQTT data to SQLite
**URL:** https://discourse.nodered.org/t/save-mqtt-data-to-sqlite/12477
**Category:** General
**Created:** [22 June 2019 02:00 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-sqlite/12477 "2019-06-22T02:00:40Z")
**Posts on this page:** 14
**Page:** 1
### Author:  [@RickyChan21](https://discourse.nodered.org/u/RickyChan21)
#### Post date: [22 June 2019 02:00 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-sqlite/12477/1 "2019-06-22T02:00:40Z")
I have an MQTT client on Node-RED subscribed to a client that publish temperature. Now I want to save this value into SQLite. Any solutions?
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [22 June 2019 02:23 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-sqlite/12477/2 "2019-06-22T02:23:42Z")
Hi and welcome to the forum.
Have you looked at/tried the examples and available nodes?
[https://flows.nodered.org/?term=sqlite&num\_pages=1](https://flows.nodered.org/?term=sqlite&num_pages=1)
What have you tried so far?
Perhaps post your flow/screenshots and we can attempt to help?
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [22 June 2019 02:25 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-sqlite/12477/3 "2019-06-22T02:25:12Z")
Have you installed the node-red-node-SQLite node yet?
How much sql do you know?
What platform are you using?
Have you looked on the forums flows page (search for SQLite)?
What have you tried?
---
### Author:  [@RickyChan21](https://discourse.nodered.org/u/RickyChan21)
#### Post date: [22 June 2019 02:38 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-sqlite/12477/4 "2019-06-22T02:38:38Z")
I tried using the inject node but the thing is I don't know how to take the output of the mqtt node.
---
### Author:  [@RickyChan21](https://discourse.nodered.org/u/RickyChan21)
#### Post date: [22 June 2019 02:45 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-sqlite/12477/5 "2019-06-22T02:45:58Z")
Have you installed the node-red-node-SQLite node yet?
R. Yes
How much sql do you know?
R. I know the basics (Create tables, insert, delete, etc)
What platform are you using?
R. NodeMCU (ESP8266) to publish temperature, Raspberry Pi as a broker and Node-Red MQTT node subscribed to the NodeMCU to get the temperature.
What have you tried?
R.I tried using the inject node but the thing is I don't know how to take the output of the mqtt node.
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [22 June 2019 09:17 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-sqlite/12477/6 "2019-06-22T09:17:52Z")
Ricky,
If you have an `mqtt-in` node subscribed to the topic this will be your input. Now all you have to do is take the msg coming out of the `mqtt-in` node and format an sqlite insert. You can do it a couple of ways, but using the `template` node is an easy way because you can layout the sql on multiple lines. Here is an example:
```auto
[{"id":"8b833332.b4e368","type":"debug","z":"bc514887.098148","name":"TEMPLATE","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":670,"y":220,"wires":[]},{"id":"3a2719cb.35e3be","type":"template","z":"bc514887.098148","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO temperature (\n temperature,\n humidity,\n look,\n detail\n)\nVALUES (\n {{payload.tempc}},\n {{payload.humidity}},\n \"{{payload.weather}}\",\n \"{{payload.detail}}\"\n);","output":"str","x":400,"y":220,"wires":[["8b833332.b4e368"]]},{"id":"d3cac669.985868","type":"inject","z":"bc514887.098148","name":"","topic":"","payload":"{\"tempc\":45,\"humidity\":16.4,\"weather\":\"Clear\",\"detail\":\"Sunny all day\"}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":220,"wires":[["3a2719cb.35e3be"]]}]
```
See if this gets you going - your input to the `template` will be the output of `mqtt-in` node
---
### Author:  [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)
#### Post date: [22 June 2019 11:42 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-sqlite/12477/7 "2019-06-22T11:42:53Z")
Hi Paul,
That's a neat little idea of using a Template node.
---
### Author:  [@shrickus](https://discourse.nodered.org/u/shrickus)
#### Post date: [27 June 2019 00:37 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-sqlite/12477/8 "2019-06-27T00:37:18Z")
> [@dynamicdave](#):
>
> That's a neat little idea of using a Template node.
Yep -- much more readable that a bunch of JS string manipulations... (although I've heard that ES6 supports string templates with variable substitution -- just can't remember the syntax)
BUT, be aware that mustache substitution inside double-braces `{{ ... }}` **may** uri encode your data, which is probably NOT what you want when saving to a database. Safer to use the triple-braces instead (to avoid the encoding). And as with all SQL statements, you'll need to provide the right quotes (or not) to match the datatypes of the columns in your table.
---
### Author:  [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)
#### Post date: [27 June 2019 03:59 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-sqlite/12477/9 "2019-06-27T03:59:55Z")
Hi Steve,
I actually think you inroduced me to this neat trick many, many months ago.
I've used it quite a few times in various flows as it makes 'composing' the query a lot easier.
I'll take on board what you say about the use of the triple curly brackets.
Regards, David.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [27 June 2019 07:26 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-sqlite/12477/10 "2019-06-27T07:26:49Z")
> [@shrickus](#):
>
> I've heard that ES6 supports string templates with variable substitution -- just can't remember the syntax
To do this use backticks instead of quotes and `${expression)` as the value to inject. For example
```auto
msg.payload = `Some string with ${msg.payload} inserted`
```
then if before executing this msg.payload contained "my string" the result would be "Some string with my string inserted".
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [27 June 2019 11:30 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-sqlite/12477/11 "2019-06-27T11:30:52Z")
For the sake of complete transparency and credit, @dynamicdave showed me the use of the template and said he got it from @shrickus who deserves the credit...unless he got it from someone else 🤣
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [27 June 2019 12:01 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-sqlite/12477/12 "2019-06-27T12:01:03Z")
To expand on what @shrickus said about two brackets {{...}} verses three {{{..}}}:
In this example flow there is one set of data coming in the two template nodes. The top template uses double brackets {{...}} and the botom one uses three brackets {{{...}}}.
If you send in a text string like "Clear&Dry" or a string with an HTML character like "Sunny all day
Rain tomorrow"
you will see different results
This is what comes from the template with the double brackets:
Notice that the `&` has been encoded to `&` and the `
` has been encoded to `<br>`
Here is the output from the three brackets:
Notice the `&` and `
` have remained unchanged.
Here is the sample flow:
[Bracket\_Example.json](https://discourse.nodered.org/uploads/short-url/dDLPrR9H4k08eQJgMIZ574HPYoC.json) (1.9 KB)
---
### Author:  [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)
#### Post date: [27 June 2019 12:42 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-sqlite/12477/13 "2019-06-27T12:42:15Z")
Paul,
I think you have posted the wrong json file.
This one appears to be lots and lots of code-snips.
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [27 June 2019 12:43 UTC](https://discourse.nodered.org/t/save-mqtt-data-to-sqlite/12477/14 "2019-06-27T12:43:53Z")
oops...fixed now