SQLite and NodeRED
A sample flow to interact with SQLte database from NodeRED
[{"id":"7d551c48106025c7","type":"tab","label":"SQLite","disabled":false,"info":"","env":[]},{"id":"d92ef8b4bf25a897","type":"sqlite","z":"7d551c48106025c7","mydb":"dd7f8134e015a1cb","sqlquery":"fixed","sql":"SELECT * FROM sensors","name":"","x":420,"y":80,"wires":[["13483434271f74c2"]]},{"id":"13483434271f74c2","type":"debug","z":"7d551c48106025c7","name":"debug 1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":660,"y":80,"wires":[]},{"id":"9954bd383b4bf98e","type":"inject","z":"7d551c48106025c7","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":200,"y":80,"wires":[["d92ef8b4bf25a897"]]},{"id":"226b53bc2bed6ba7","type":"sqlite","z":"7d551c48106025c7","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"SELECT * FROM sensors","name":"","x":700,"y":140,"wires":[[]]},{"id":"22d73aca823239e9","type":"function","z":"7d551c48106025c7","name":"function 1","func":"msg.topic = 'INSERT INTO sensors (id, name, value, unit, timestamp) VALUES (null, $val1, $val2, $val3,datetime(\"now\"));'\nmsg.payload = ['SNSOR_3',23,'KW']\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":380,"y":140,"wires":[["226b53bc2bed6ba7"]]},{"id":"17fa730a310f8f54","type":"inject","z":"7d551c48106025c7","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":210,"y":140,"wires":[["22d73aca823239e9"]]},{"id":"a7231f61188ee96e","type":"sqlite","z":"7d551c48106025c7","mydb":"dd7f8134e015a1cb","sqlquery":"prepared","sql":"INSERT INTO sensors (id, name, value, unit, timestamp) VALUES (null, $val1, $val2, $val3,datetime(\"now\"))","name":"","x":680,"y":200,"wires":[[]]},{"id":"af489741c7a20e28","type":"function","z":"7d551c48106025c7","name":"function 2","func":"msg.params = {\n $val1:'SNSOR_4', \n $val2: 29,\n $val3: 'V'\n }\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":380,"y":200,"wires":[["a7231f61188ee96e"]]},{"id":"92ea53e226bff045","type":"inject","z":"7d551c48106025c7","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":190,"y":200,"wires":[["af489741c7a20e28"]]},{"id":"9a71b7ae1f625a85","type":"sqlite","z":"7d551c48106025c7","mydb":"dd7f8134e015a1cb","sqlquery":"batch","sql":"INSERT INTO sensors (id, name, value, unit, timestamp) VALUES (null, $val1, $val2, $val3,datetime(\"now\"))","name":"","x":680,"y":260,"wires":[[]]},{"id":"30b3db4d062ab1f9","type":"function","z":"7d551c48106025c7","name":"function 3","func":"msg.topic = \"INSERT INTO sensors (id, name, value, unit, timestamp) VALUES (null, 'SENSOR_1', 23.4, 'Volt',datetime(\\\"now\\\"));INSERT INTO sensors (id, name, value, unit, timestamp) VALUES (null, 'SENSOR_1', 23.4, 'Volt',datetime(\\\"now\\\"));\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":380,"y":260,"wires":[["9a71b7ae1f625a85"]]},{"id":"a1a68dd72ab960ec","type":"inject","z":"7d551c48106025c7","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":190,"y":260,"wires":[["30b3db4d062ab1f9"]]},{"id":"dd7f8134e015a1cb","type":"sqlitedb","db":"/tmp/sqlite/sensors.db","mode":"RWC"}]
