Can I have a local SQL data base in Node-RED where to store data to?
Can I have a local SQL data base in Node-RED where to store data to?
Yes this is possible using the sqlite node.
The commands you want to address are set in the msg.topic of the input msg as a text string.
For example to create a table in accordance to the official command reference https://sqlite.org/lang.html you have to set a
msg.topic = "CREATE TABLE sqlite (data TEXT [ NOT NULL ],time TEXT [ NOT NULL ])"
to create a table that stores the a two dimensional string array data,time per entry. The following flow you can copy gives you a good example of how to handle the sqlite node:
[{"id":"dfb9b8a1.358038","type":"function","z":"3d6f010c.c8b95e","name":"Insert data","func":"var d = new Date();\nd = d.getTime();\nmsg.topic = 'INSERT INTO sqlite (data, time) VALUES ( ' +'\"'+ msg.payload +'\"'+ ', '+ d +')';\nreturn msg;","outputs":1,"noerr":0,"x":475,"y":621,"wires":[["a6648d21.a4c02"]]},{"id":"c73f3dff.7edd3","type":"function","z":"3d6f010c.c8b95e","name":"Create a table","func":"// create an database for storing {data, time} as strings per entry\n\nmsg.topic =\"CREATE TABLE sqlite (data TEXT [ NOT NULL ],time TEXT [ NOT NULL ])\";\nreturn msg;\n","outputs":1,"noerr":0,"x":481,"y":565,"wires":[["a6648d21.a4c02"]]},{"id":"f87545a7.af98e8","type":"inject","z":"3d6f010c.c8b95e","name":"-> CREATE","topic":"","payload":"","payloadType":"num","repeat":"","crontab":"","once":false,"x":297,"y":564,"wires":[["c73f3dff.7edd3"]]},{"id":"a6648d21.a4c02","type":"sqlite","z":"3d6f010c.c8b95e","mydb":"a4a7d318.bea42","name":"sqlite","x":707,"y":660,"wires":[["5d75a24f.e7d55c"]]},{"id":"ce65cb54.a4af38","type":"function","z":"3d6f010c.c8b95e","name":"Select all","func":"msg.topic = \"SELECT * FROM sqlite\";\nreturn msg;","outputs":1,"noerr":0,"x":464,"y":685,"wires":[["a6648d21.a4c02"]]},{"id":"62b8b47b.00d67c","type":"inject","z":"3d6f010c.c8b95e","name":"-> SELECT","topic":"","payload":"","payloadType":"num","repeat":"","crontab":"","once":false,"x":296,"y":682,"wires":[["ce65cb54.a4af38"]]},{"id":"5d75a24f.e7d55c","type":"debug","z":"3d6f010c.c8b95e","name":"","active":true,"console":"false","complete":"false","x":860,"y":659,"wires":[]},{"id":"528ae957.1d0508","type":"function","z":"3d6f010c.c8b95e","name":"Drop the table","func":"msg.topic =\"DROP TABLE sqlite\";\nreturn msg;","outputs":1,"noerr":0,"x":485,"y":740,"wires":[["a6648d21.a4c02"]]},{"id":"9a607bab.0566c8","type":"inject","z":"3d6f010c.c8b95e","name":"-> DROP","topic":"","payload":"","payloadType":"num","repeat":"","crontab":"","once":false,"x":287,"y":738,"wires":[["528ae957.1d0508"]]},{"id":"92db3ba2.3765a8","type":"inject","z":"3d6f010c.c8b95e","name":"-> INSERT","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":287.5,"y":621,"wires":[["dfb9b8a1.358038"]]},{"id":"a4a7d318.bea42","type":"sqlitedb","z":"","db":"/opt/node-red/.userdir/db.sqlite"}]