# Storing NODE-RED input into an SQLite database

**URL:** <https://discourse.nodered.org/t/storing-node-red-input-into-an-sqlite-database/64874>\
**Category:** General\
**Tags:** function-node\
**Created:** [9 July 2022 12:19 UTC](https://discourse.nodered.org/t/storing-node-red-input-into-an-sqlite-database/64874 "2022-07-09T12:19:09Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![fadzfiras](https://avatars.discourse-cdn.com/v4/letter/f/c4cdca/32.png) [@fadzfiras](https://discourse.nodered.org/u/fadzfiras)\
**Post date:** [9 July 2022 12:19 UTC](https://discourse.nodered.org/t/storing-node-red-input-into-an-sqlite-database/64874/1 "2022-07-09T12:19:09Z")

</div>

I have been given a task to store a set of data into an SQLite database. I have to code the function node (format data) so the SQLite node is able to store my data. The 'database' node accepts SQL Query via msg.topic.

Here is the flow I have constructed

 ![Screenshot 2022-07-09 211531](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/8/f8f84b6303cc3eacc15b7acd4dfa47588a986bc7.png)

Below is the code for my 'input data' function node.

 ![Screenshot 2022-07-09 202015](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/9/f95cbea25bdf6127e75ec0302c3a3365216d4ec2.png)

Would greatly appreciate any help regarding the 'format data' node.

---

<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:** [9 July 2022 16:49 UTC](https://discourse.nodered.org/t/storing-node-red-input-into-an-sqlite-database/64874/2 "2022-07-09T16:49:26Z")

</div>

You would be better off using a `template` node to format the query. Here is a sample flow showing you how you could do it. NOTE: you will have to define the path to the database in the `sqlite` node.

```auto
[{"id":"7acc20b7.b27b9","type":"tab","label":"SQLite test","disabled":false,"info":""},{"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","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"","payloadType":"date","x":150,"y":260,"wires":[["1a35bc39.05bdd4"]]},{"id":"51107643.82d748","type":"inject","z":"7acc20b7.b27b9","name":"insert","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"","payloadType":"date","x":130,"y":320,"wires":[["b65516ba.aeb3e"]]},{"id":"a4ece8df.0e597","type":"inject","z":"7acc20b7.b27b9","name":"select","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"select * from greenhouse","payload":"","payloadType":"date","x":250,"y":200,"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","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"","payloadType":"date","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":"30750c40.20fb2c","type":"comment","z":"7acc20b7.b27b9","name":"SQLite Test flow","info":"","x":540,"y":140,"wires":[]},{"id":"3594a0ec.6329a8","type":"sqlitedb","db":"testdb"}]

```

---

<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:** [7 September 2022 16:50 UTC](https://discourse.nodered.org/t/storing-node-red-input-into-an-sqlite-database/64874/3 "2022-09-07T16:50:03Z")

</div>

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