AlaSQL is an open source SQL database for Javascript with a strong focus on query speed and data source flexibility for both relational data and schemaless data. It can be added to Node-RED by installing the node-red-contrib-alasql node. The following is a basic flow that adds DROP, CREATE, INSERT, SELECT and save-to-file SQL Operation to your Node-RED app:
[{"id":"e8fd0caa.f388f","type":"alasql","z":"7bbc769a.b98c68","name":"SELECT","query":"SELECT * FROM cities","x":450,"y":960,"wires":[["a31cc7c1.44d048"]]},{"id":"a31cc7c1.44d048","type":"debug","z":"7bbc769a.b98c68","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":710,"y":880,"wires":[]},{"id":"d2f66872.976ff8","type":"inject","z":"7bbc769a.b98c68","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":960,"wires":[["e8fd0caa.f388f"]]},{"id":"b6b560e9.ea05a","type":"alasql","z":"7bbc769a.b98c68","name":"CREATE","query":"CREATE TABLE cities (city string, population number)","x":450,"y":840,"wires":[["a31cc7c1.44d048"]]},{"id":"4e91d3d9.c1b24c","type":"inject","z":"7bbc769a.b98c68","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":840,"wires":[["b6b560e9.ea05a"]]},{"id":"93b165f8.f11298","type":"alasql","z":"7bbc769a.b98c68","name":"INSERT","query":"INSERT INTO cities VALUES ('Rome',2863223),('Paris',2249975),('Berlin',3517424),('Madrid',3041579)","x":450,"y":900,"wires":[["a31cc7c1.44d048"]]},{"id":"b400c605.38c058","type":"inject","z":"7bbc769a.b98c68","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":900,"wires":[["93b165f8.f11298"]]},{"id":"29212d3b.dfe2b2","type":"alasql","z":"7bbc769a.b98c68","name":"DROP","query":"DROP TABLE cities","x":450,"y":780,"wires":[["a31cc7c1.44d048"]]},{"id":"573de68a.035d88","type":"inject","z":"7bbc769a.b98c68","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":780,"wires":[["29212d3b.dfe2b2"]]},{"id":"82c921f4.99d2c","type":"alafile out","z":"7bbc769a.b98c68","name":"JSON","filename":"C:\\\\Users\\\\INSTAR\\\\.node-red\\\\db\\\\syslog","format":"json","columns":"*","headers":true,"x":630,"y":1040,"wires":[]},{"id":"562e7297.3cb8bc","type":"alasql","z":"7bbc769a.b98c68","name":"SELECT","query":"SELECT * FROM cities","x":450,"y":1040,"wires":[["82c921f4.99d2c"]]},{"id":"156fcb8c.12ea74","type":"inject","z":"7bbc769a.b98c68","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":1040,"wires":[["562e7297.3cb8bc"]]}]How to import a Flow
Open the top menu then select import -> clipboard, this will open a dialogue box where you can import the flow. Copy the JSON flow above to your system clipboard. When the file content has been pasted into the dialogue box press the import button.
As an example we can connect to the HTTP API of an INSTAR IP camera using a CGI command like:
http://user:password@192.168.2.115/param.cgi?cmd=getsaradcstateThe command will retrieve to current light level recorded by the internal brightness sensor of your camera. We need to add some RegEX var brightness = msg.payload.replace(/^\D+|\D+$/g, ""); to clean up the response, that will look something like var saradc_state="208";, extract the value (in this case 208) and set this value to the msg.payload. Which then can be used directly for a node-red-dashboard node. For the AlaSQL node we can use an insert command to add the measurement to our database table (CREATE TABLE lightSensor (camera string, brightness number)):
INSERT INTO lightSensor VALUES ('192.168.2.115',?)
Once we collected all the data we needed, we can use an AlaSQL node to select all data from one camera SELECT * FROM lightSensor WHERE camera IN ('192.168.2.115') and use an AlaSQL Outfile node to write this information to a file (xlsx, csv, json, etc.). Make sure that the folder /home/pi/.node-red/sql/ exists and NodeRED has the necessary rights to write to it.
[{"id":"85009c5a.844cc","type":"http request","z":"6c9ea0b0.ae5a5","name":"192.168.2.115","method":"GET","ret":"txt","paytoqs":false,"url":"http://admin:instar@192.168.2.115/param.cgi?cmd=getsaradcstate","tls":"","proxy":"","authType":"basic","x":358,"y":97,"wires":[["eed9600c.af335"]]},{"id":"eed9600c.af335","type":"function","z":"6c9ea0b0.ae5a5","name":"brightness","func":"msg.topic = \"brightness\";\n\nvar brightness = msg.payload.replace(/^\\D+|\\D+$/g, \"\");\n\nmsg.payload = brightness;\n\nreturn msg;","outputs":1,"noerr":0,"x":526,"y":97,"wires":[["489177ba.b22af8","f5b1afd6.8827c","9b9d695f.d5cfe8"]]},{"id":"489177ba.b22af8","type":"ui_chart","z":"6c9ea0b0.ae5a5","name":"Brightness","group":"58c9cffc.1484f","order":1,"width":"0","height":"0","label":"Brightness (IN-9008)","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":691,"y":96,"wires":[[]]},{"id":"f5b1afd6.8827c","type":"ui_text","z":"6c9ea0b0.ae5a5","group":"58c9cffc.1484f","order":3,"width":0,"height":0,"name":"","label":"Current Brightness","format":"{{msg.payload}}","layout":"row-spread","x":711,"y":136,"wires":[]},{"id":"dc131016.fa703","type":"http request","z":"6c9ea0b0.ae5a5","name":"192.168.2.116","method":"GET","ret":"txt","paytoqs":false,"url":"http://admin:instar@192.168.2.116/param.cgi?cmd=getsaradcstate","tls":"","proxy":"","authType":"basic","x":355,"y":237,"wires":[["8140be45.8e3bc"]]},{"id":"8140be45.8e3bc","type":"function","z":"6c9ea0b0.ae5a5","name":"brightness","func":"msg.topic = \"brightness\";\n\nvar brightness = msg.payload.replace(/^\\D+|\\D+$/g, \"\");\n\nmsg.payload = brightness;\n\nreturn msg;","outputs":1,"noerr":0,"x":523,"y":237,"wires":[["3c0affb.a49dc","50766ba6.9c0044","1ec06cf8.188b43"]]},{"id":"3c0affb.a49dc","type":"ui_chart","z":"6c9ea0b0.ae5a5","name":"Brightness","group":"7b7461fa.d46fe","order":1,"width":0,"height":0,"label":"Brightness (IN-8015)","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":691,"y":236,"wires":[[]]},{"id":"50766ba6.9c0044","type":"ui_text","z":"6c9ea0b0.ae5a5","group":"7b7461fa.d46fe","order":3,"width":0,"height":0,"name":"","label":"Current Brightness","format":"{{msg.payload}}","layout":"row-spread","x":711,"y":276,"wires":[]},{"id":"75dc136d.abd8dc","type":"http request","z":"6c9ea0b0.ae5a5","name":"192.168.2.117","method":"GET","ret":"txt","paytoqs":false,"url":"http://admin:instar@192.168.2.117/param.cgi?cmd=getsaradcstate","tls":"","proxy":"","authType":"basic","x":345,"y":376,"wires":[["509bfb53.a74f24"]]},{"id":"8c3c617e.2669e","type":"inject","z":"6c9ea0b0.ae5a5","name":"1min Trigger","topic":"","payload":"","payloadType":"date","repeat":"60","crontab":"","once":false,"onceDelay":0.1,"x":123,"y":415,"wires":[["75dc136d.abd8dc","1aa1b2b6.8c3bed","dc131016.fa703","85009c5a.844cc","76995be5.a15944","fd5074c1.1c09b8"]]},{"id":"509bfb53.a74f24","type":"function","z":"6c9ea0b0.ae5a5","name":"brightness","func":"msg.topic = \"brightness\";\n\nvar brightness = msg.payload.replace(/^\\D+|\\D+$/g, \"\");\n\nmsg.payload = brightness;\n\nreturn msg;","outputs":1,"noerr":0,"x":513,"y":376,"wires":[["1438b792.2a4f38","ac6c2bd1.f21e58","567ca3a4.57c61c"]]},{"id":"1438b792.2a4f38","type":"ui_chart","z":"6c9ea0b0.ae5a5","name":"Brightness","group":"912a3b2b.030088","order":1,"width":0,"height":0,"label":"Brightness (IN-8015)","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":691,"y":376,"wires":[[]]},{"id":"ac6c2bd1.f21e58","type":"ui_text","z":"6c9ea0b0.ae5a5","group":"912a3b2b.030088","order":3,"width":0,"height":0,"name":"","label":"Current Brightness","format":"{{msg.payload}}","layout":"row-spread","x":711,"y":416,"wires":[]},{"id":"1aa1b2b6.8c3bed","type":"http request","z":"6c9ea0b0.ae5a5","name":"192.168.2.118","method":"GET","ret":"txt","paytoqs":false,"url":"http://admin:instar@192.168.2.118/param.cgi?cmd=getsaradcstate","tls":"","proxy":"","authType":"basic","x":343,"y":519,"wires":[["9b876c46.68599"]]},{"id":"9b876c46.68599","type":"function","z":"6c9ea0b0.ae5a5","name":"brightness","func":"msg.topic = \"brightness\";\n\nvar brightness = msg.payload.replace(/^\\D+|\\D+$/g, \"\");\n\nmsg.payload = brightness;\n\nreturn msg;","outputs":1,"noerr":0,"x":511,"y":519,"wires":[["33adb414.e7bfbc","a4442e19.39007","61dcba0c.df68d4"]]},{"id":"33adb414.e7bfbc","type":"ui_chart","z":"6c9ea0b0.ae5a5","name":"Brightness","group":"c410f900.421e78","order":1,"width":0,"height":0,"label":"Brightness (IN-9020)","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":691,"y":519,"wires":[[]]},{"id":"a4442e19.39007","type":"ui_text","z":"6c9ea0b0.ae5a5","group":"c410f900.421e78","order":3,"width":0,"height":0,"name":"","label":"Current Brightness","format":"{{msg.payload}}","layout":"row-spread","x":711,"y":559,"wires":[]},{"id":"76995be5.a15944","type":"http request","z":"6c9ea0b0.ae5a5","name":"192.168.2.15","method":"GET","ret":"txt","paytoqs":false,"url":"http://admin:instar@192.168.2.15/param.cgi?cmd=getsaradcstate","tls":"","proxy":"","authType":"basic","x":335,"y":660,"wires":[["8bd51ff7.baa44"]]},{"id":"8bd51ff7.baa44","type":"function","z":"6c9ea0b0.ae5a5","name":"brightness","func":"msg.topic = \"brightness\";\n\nvar brightness = msg.payload.replace(/^\\D+|\\D+$/g, \"\");\n\nmsg.payload = brightness;\n\nreturn msg;","outputs":1,"noerr":0,"x":513,"y":660,"wires":[["dbf44ead.c0baa","71360442.bdc46c","b604b30f.95cef"]]},{"id":"dbf44ead.c0baa","type":"ui_chart","z":"6c9ea0b0.ae5a5","name":"Brightness","group":"29377a63.d6cca6","order":1,"width":0,"height":0,"label":"Brightness (IN-8001)","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":693,"y":660,"wires":[[]]},{"id":"71360442.bdc46c","type":"ui_text","z":"6c9ea0b0.ae5a5","group":"29377a63.d6cca6","order":3,"width":0,"height":0,"name":"","label":"Current Brightness","format":"{{msg.payload}}","layout":"row-spread","x":713,"y":700,"wires":[]},{"id":"caec44ca.2c06e8","type":"alasql","z":"6c9ea0b0.ae5a5","name":"SELECT","query":"SELECT * FROM lightSensor","x":1140,"y":220,"wires":[["5f76a41d.32f7cc"]]},{"id":"5f76a41d.32f7cc","type":"debug","z":"6c9ea0b0.ae5a5","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1330,"y":129,"wires":[]},{"id":"7d1ab3d1.8318fc","type":"inject","z":"6c9ea0b0.ae5a5","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":1003,"y":220,"wires":[["caec44ca.2c06e8"]]},{"id":"8458074c.e3ab48","type":"alasql","z":"6c9ea0b0.ae5a5","name":"CREATE","query":"CREATE TABLE lightSensor (camera string, brightness number)","x":1140,"y":100,"wires":[["5f76a41d.32f7cc"]]},{"id":"1b6757c5.2fd858","type":"inject","z":"6c9ea0b0.ae5a5","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":1003,"y":100,"wires":[["8458074c.e3ab48"]]},{"id":"7d9a6fc5.cd473","type":"alasql","z":"6c9ea0b0.ae5a5","name":"INSERT","query":"INSERT INTO lightSensor VALUES ('192.168.2.115',61),('192.168.2.116',66),('192.168.2.117',67),('192.168.2.118',2),('192.168.2.15',59)","x":1140,"y":160,"wires":[["5f76a41d.32f7cc"]]},{"id":"b03131ad.e8446","type":"inject","z":"6c9ea0b0.ae5a5","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":1003,"y":160,"wires":[["7d9a6fc5.cd473"]]},{"id":"bb2370ed.556d8","type":"alasql","z":"6c9ea0b0.ae5a5","name":"DROP","query":"DROP TABLE lightSensor","x":1140,"y":40,"wires":[["5f76a41d.32f7cc"]]},{"id":"c2af6193.b79b8","type":"inject","z":"6c9ea0b0.ae5a5","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":1003,"y":40,"wires":[["bb2370ed.556d8"]]},{"id":"fe94760c.6f3608","type":"alasql","z":"6c9ea0b0.ae5a5","name":"SELECT 115","query":"SELECT * FROM lightSensor WHERE camera IN ('192.168.2.115')","x":1210,"y":600,"wires":[["1c89c190.be967e"]]},{"id":"fc84ec70.3e682","type":"inject","z":"6c9ea0b0.ae5a5","name":"1 hr Trigger","topic":"","payload":"","payloadType":"date","repeat":"3600","crontab":"","once":false,"onceDelay":0.1,"x":1033,"y":720,"wires":[["fe94760c.6f3608","242eab4a.bc8fe4","d42674ee.901668","20c166a8.ca5c5a","47c21db9.10b4e4","984877e0.3e6b88"]]},{"id":"9b9d695f.d5cfe8","type":"alasql","z":"6c9ea0b0.ae5a5","name":"lightSensor","query":"INSERT INTO lightSensor VALUES ('192.168.2.115',?)","x":692,"y":43,"wires":[[]]},{"id":"1c89c190.be967e","type":"alafile out","z":"6c9ea0b0.ae5a5","name":"xlsx","filename":"/home/pi/.node-red/sql/lightSensor_115","format":"xlsx","columns":"*","headers":true,"x":1350,"y":600,"wires":[]},{"id":"1ec06cf8.188b43","type":"alasql","z":"6c9ea0b0.ae5a5","name":"lightSensor","query":"INSERT INTO lightSensor VALUES ('192.168.2.116',?)","x":692,"y":186,"wires":[[]]},{"id":"567ca3a4.57c61c","type":"alasql","z":"6c9ea0b0.ae5a5","name":"lightSensor","query":"INSERT INTO lightSensor VALUES ('192.168.2.117',?)","x":692,"y":328,"wires":[[]]},{"id":"61dcba0c.df68d4","type":"alasql","z":"6c9ea0b0.ae5a5","name":"lightSensor","query":"INSERT INTO lightSensor VALUES ('192.168.2.118',?)","x":692,"y":473,"wires":[[]]},{"id":"b604b30f.95cef","type":"alasql","z":"6c9ea0b0.ae5a5","name":"lightSensor","query":"INSERT INTO lightSensor VALUES ('192.168.2.15',?)","x":693,"y":610,"wires":[[]]},{"id":"242eab4a.bc8fe4","type":"alasql","z":"6c9ea0b0.ae5a5","name":"SELECT 116","query":"SELECT * FROM lightSensor WHERE camera IN ('192.168.2.116')","x":1210,"y":649,"wires":[["5dd6aa26.5337e4"]]},{"id":"5dd6aa26.5337e4","type":"alafile out","z":"6c9ea0b0.ae5a5","name":"xlsx","filename":"/home/pi/.node-red/sql/lightSensor_116","format":"xlsx","columns":"*","headers":true,"x":1350,"y":649,"wires":[]},{"id":"d42674ee.901668","type":"alasql","z":"6c9ea0b0.ae5a5","name":"SELECT 117","query":"SELECT * FROM lightSensor WHERE camera IN ('192.168.2.117')","x":1210.017333984375,"y":698.0104064941406,"wires":[["a712a51c.4598c8"]]},{"id":"a712a51c.4598c8","type":"alafile out","z":"6c9ea0b0.ae5a5","name":"xlsx","filename":"/home/pi/.node-red/sql/lightSensor_117","format":"xlsx","columns":"*","headers":true,"x":1350.017333984375,"y":698.0104064941406,"wires":[]},{"id":"20c166a8.ca5c5a","type":"alasql","z":"6c9ea0b0.ae5a5","name":"SELECT 118","query":"SELECT * FROM lightSensor WHERE camera IN ('192.168.2.118')","x":1209.017333984375,"y":746.0104064941406,"wires":[["d30160a9.d2c08"]]},{"id":"d30160a9.d2c08","type":"alafile out","z":"6c9ea0b0.ae5a5","name":"xlsx","filename":"/home/pi/.node-red/sql/lightSensor_118","format":"xlsx","columns":"*","headers":true,"x":1349.017333984375,"y":746.0104064941406,"wires":[]},{"id":"47c21db9.10b4e4","type":"alasql","z":"6c9ea0b0.ae5a5","name":"SELECT 15","query":"SELECT * FROM lightSensor WHERE camera IN ('192.168.2.15')","x":1210,"y":794,"wires":[["c8f24f8f.f5cb3"]]},{"id":"c8f24f8f.f5cb3","type":"alafile out","z":"6c9ea0b0.ae5a5","name":"xlsx","filename":"/home/pi/.node-red/sql/lightSensor_15","format":"xlsx","columns":"*","headers":true,"x":1350,"y":794,"wires":[]},{"id":"58c9cffc.1484f","type":"ui_group","z":"","name":"192.168.2.115","tab":"32dce8be.80f688","disp":true,"width":"6","collapse":false},{"id":"7b7461fa.d46fe","type":"ui_group","z":"","name":"192.168.2.116","tab":"32dce8be.80f688","disp":true,"width":"6","collapse":false},{"id":"912a3b2b.030088","type":"ui_group","z":"","name":"192.168.2.117","tab":"32dce8be.80f688","disp":true,"width":"6","collapse":false},{"id":"c410f900.421e78","type":"ui_group","z":"","name":"192.168.2.118","tab":"32dce8be.80f688","disp":true,"width":"6","collapse":false},{"id":"29377a63.d6cca6","type":"ui_group","z":"","name":"192.168.2.15","tab":"32dce8be.80f688","disp":true,"width":"6","collapse":false},{"id":"32dce8be.80f688","type":"ui_tab","z":"","name":"Brightness","icon":"fa-sun-o","order":11,"disabled":false,"hidden":false}]