Very good set of instructions. Very Detail explaination. I can not wait for next video in this series as I am working on Greenhouse automation project using ESP8266 & some sensors namely temperature, humidity, lux & soil moisture.Also I will be controlling a fan & a servo for this project.Until now I was lacking understanding of database storage in node red and from this video i think I will be using SQLite. Thanks very much.
Very good video. I learned a lot from this and your other videos on the same subject. Being new to using node red on a project the SQLite will come in very handy. Thanks to your help I have integrated node red, mqtt and SQLite to monitor my remote battery powered esp8266's with sensors which communicate with mqtt to node red and also sending the data to ThingSpeak. Recently I have developed the software so the esp's can be woken from deep sleep mode to enable OTA programming. A corresponding command from node red will put it back into deep sleep cycle again after programming.
Hi, the most instructive video on the topic of node-red+sqlite+chart that I found, it's a pity that I didn't find a continuation on the channel, or maybe I was looking badly? It would be great to add a base on github, for tests in node-red. Thanks for the video👍
@@csongorvarga Yes, it all depends on the tasks, I don't have many of them, so on the contrary I want to switch to a lighter and portable one instead of influx)) Good channel, good luck.
Nice video but i have small problem. I tried join on multiple data as you and it has weird behavior. Every click on refresh data in graph ti will create sql commands but output fromjoin has 2arrays, 1array, 3 arrays - i think it depends how fast will sqlite return result. I thought that join must wait until its complete but it seems that it wait for message complete but not for all data from sqlite. Can you please help ? Maybe i am blind and it is some stupid small error ...
Hmmm, the SQL does generate multiple SQL outputs, which will each generate and array. But in the line 148 of the SQL code, I am setting the complete attribute of the last message to true. The join will wait until it receives a message with .completed = true. It works for me. I am seeing multiple lines on my graph. I am not sure why it does not work for you. Maybe the last data set takes much less time to return and that gets to the join first? This is just a guess, but I don't have any other clue.
I have recently upgraded node red to v2 and now I find I get "Bad data inject" error from the chart node in the dynamic charts flow. I reverted back to v1.3.1 but still get the error which is strange. The charts have been working ok for a long time so I wonder if you are aware of this problem and perhaps you could help me to fix it. Thanks and stay safe.
I use a similar technique to you to preserve the graph data in a file then reload it on reboot. My interest in this article was to store the data in a database which seems logical. Is there a reason why you do not access the data from the database to reload the graph data rather than use the same data which is written to a separate file, this seems like a duplication of effort. Liking your videos, thank-you.
No, not really. I think I just did the chart backup before I started storing values. And I have not gone back to change that. But I use the same method ever since. Fewer number of nodes to be honest :)
@@csongorvarga It wasn't a criticism. My original approach was the same as yours but then I thought perhaps using a database was a more logical conclusion, which was why I was interested in your video. Then on watching I found you weren't using the database but the same method as me to retrieve chart data. I just wondered if there was a reason for it. Not trying to fix something that is working is a valid reason. Now I will try and get my head around SQLite, I haven't created a database since Dbase on the BBC Model B. Cheers
@@dikl2689 OK got you. Well, you can easily do it using the above flow. Use the SQL, DB and Prep nodes. In the SQL node prefix the filters and that's pretty much it. You can even omit the Join if your chart is using a single data source. The code in the Prep function can also be simplified as it is prepared to combine multiple data sources but deals with a single just fine.
Hi, I'm having trouble getting data from a mysql database into a chart. In this video you are talking about another video where you explain this. Can you please tell me which video that was?
Great Video. Would it be possible for you to share more information on setting the permissions for the database. I managed to create the database and able to create tables and data from phpliteadmin. However, when I try to insert data from node-red it tells me "Error: SQLITE_READONLY: attempt to write a readonly database". I know it's obviously a permissions issue, but being new to Linux, I'm having a hard time with this. I've changed permissions for all the folders and database file with chmod but still no luck. Perhaps creating a video on this alone would be great.
I am relatively new to Linux as well. I did chmod 777 before I created the database and everything was OK. So I don't really know what the solution is. By doing ls -al, I can see that the owner of the sqlite folder is root and for the database file it is www-data. Check what is the case in your system.
php runs under Apache, so you need the Apache as well. It has been some time since I did this but Apache was pre-install on my pi, so all I had to do is start up the service and it was working.
@@csongorvarga I created the database, the tables and inserted some random values using python script. I have replaced your "sqlite" node with "mysql" node, but the mysql node is not retrieving any data and debug screen is empty and the same is the case with UI dashboard...since the data retrieval is not happening, I am not getting the chart. But this is a nice and simple dashboard though. I have a similar requirement where i need to plot a sensor value in chart.
hello! I’m a beginner in programming so please excuse me for my ignorance:i need your precious help on a project. I have a beaglobone black which is supposed to get measurments from an amplifier and then save them in a new matrix( 2 columns of frequency f and time t ).I need the measurments imported into node red and then shown on a chart of frequency and time axes.could you help me? thanx a lot!
I will try to help. But how are you getting the readings into Node red? Displaying multiple values in the chart is easy, you just need to change the subject and the chart will keep track of the two data series automatically. Btw, there is a Node-Red Group where there are lot of people who can help. I can help here as well, but that platform is easier for communication than comments in UA-cam.
On node red i have used successfully the alafile node to pull data from an xls file. I would like those data displayed on a chart (X-Y data) which i keep falling short of doing. Any help would be really appreciated.Here are the xls files as well (www.dropbox.com/sh/z59s47b24uxm1ps/AACIBpMHQLgWfSjMX1Obsbhxa?dl=0) [{"id":"adc929dd.74c1c","type":"tab","label":"Flow 1"},{"id":"a293ab3c.c48d08","type":"inject","z":"adc929dd.74c1c","name":"","topic":"","payload":"","payloadType":"date","repeat":"5","crontab":"","once":false,"x":104.5,"y":187,"wires":[["8f3615ea.73b828","6391ad80.5e1324"]]},{"id":"8f3615ea.73b828","type":"alafile in","z":"adc929dd.74c1c","name":"X data","filename":"Desktop/NODE1","format":"xlsx","columns":"*A","headers":true,"x":331.5,"y":94,"wires":[["9ca08631.af42d","d7730091.04504"]]},{"id":"9ca08631.af42d","type":"ui_chart","z":"adc929dd.74c1c","name":"","group":"e50ac821.60ead","order":0,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"5","removeOlderPoints":"","removeOlderUnit":"60","cutout":0,"colors":["#1F77B4","#AEC7E8","#FF7F0E","#2CA02C","#98DF8A","#D62728","#FF9896","#9467BD","#C5B0D5"],"x":537.5,"y":196,"wires":[[],[]]},{"id":"d7730091.04504","type":"debug","z":"adc929dd.74c1c","name":"","active":true,"console":"false","complete":"false","x":752.5,"y":127,"wires":[]},{"id":"6391ad80.5e1324","type":"alafile in","z":"adc929dd.74c1c","name":"Y data","filename":"Desktop/NODE2","format":"xlsx","columns":"*A","headers":true,"x":366,"y":329,"wires":[["9ca08631.af42d"]]},{"id":"e50ac821.60ead","type":"ui_group","z":"","name":"Default","tab":"110b80c3.583e0f","disp":true,"width":"6"},{"id":"110b80c3.583e0f","type":"ui_tab","z":"","name":"XXX","icon":"dashboard","order":1}]
I put the create commands into the video description. My DB is currently 300MB, so I will no send that over. You don't need the chart dump files either to make the flow work.
For instance, with UML tools you can create/design a visual software architechture in diagrams and arrowed connections representing dependencies. Thus you can use that UML design to convert or generate the initial architectural code, then you can start populating that architechture with needed logic. Vise versa you can write your software in code, then use eclipse for instance, to generate a UML Diagram to get a visual representation of your architechtural design of that written code. So my question here is, can you use Node-Red to combine (custome) nodes so that its output could be generated code file(s) in python or C++ or in my case (Structured text) or whatever else?
Clarification: it is possible to build a custom node in node.js, but this way to advanced stuff for me. You can use the exec node to execute any phyton or C code and use the command line parameters, output, files etc. to exchange information. That's all I am aware of.
Hi, I liked your video very much, good work. I learned a lot of stuff especially SQLite. I have a question about your soil fertility function, what is the sensor or sensors you use to detect soil fertility?
Thanks for the quick answer. OK, so the fertility test is more like a TDS? How are you handing comm with your application? BLE doesn't have much range!
I do have a video on that in my Home Automation playlist. I have my raspberry pi in the same room as the sensor, so I have no issues with the communication. That is main drawback of this BLE module. The communication has been reverse engineered, and a python code reads the values from the sensor. And I have no idea how it measures the soil fertility.
Thanks for the info., I'm interested in the Xiaomi compared to the Parrot because of pricing. I will try to retrofit an ESP8285 to replace BLE but power and deep sleep will be issues I guess.
why stop? show please last join and prep node, searching about some hours to prepare data for chart, nothing work realy. there is no chart input data documentation: format? max size? x-axis setting?
This is a very old video, the chart format has changed since. Please have a look at this example flow: this contains data format examples for almost all chart types. I am using this all the time: flows.nodered.org/flow/2938d98507c81648a2ab5ac1ba6e7d31
Good topic and I look forward to going over it but for future videos, could you write down what your going to say as you reiterated quite a lot especially about why sqlite etc and please don't use "ah", "um" or "er", very distracting. Well done otherwise.
Why make a show video. With a complex behavior of the settings..Please do a really good beginner show and intro instead..Very difficult to follow such a complex setup ..cindly LG
Hi, I realize that the video is some years old, but it is still brilliant and saved me a ton of time. Thanks.
Very good set of instructions. Very Detail explaination. I can not wait for next video in this series as I am working on Greenhouse automation project using ESP8266 & some sensors namely temperature, humidity, lux & soil moisture.Also I will be controlling a fan & a servo for this project.Until now I was lacking understanding of database storage in node red and from this video i think I will be using SQLite.
Thanks very much.
Very good video. I learned a lot from this and your other videos on the same subject. Being new to using node red on a project the SQLite will come in very handy. Thanks to your help I have integrated node red, mqtt and SQLite to monitor my remote battery powered esp8266's with sensors which communicate with mqtt to node red and also sending the data to ThingSpeak. Recently I have developed the software so the esp's can be woken from deep sleep mode to enable OTA programming. A corresponding command from node red will put it back into deep sleep cycle again after programming.
Thanks for the comment, looks like an interesting implementation.
Hi, the most instructive video on the topic of node-red+sqlite+chart that I found, it's a pity that I didn't find a continuation on the channel, or maybe I was looking badly? It would be great to add a base on github, for tests in node-red. Thanks for the video👍
Well, SQLite started to be very big and slow after some time. So I also migrated over to influxdb database storage and Grafana for reporting.
@@csongorvarga Yes, it all depends on the tasks, I don't have many of them, so on the contrary I want to switch to a lighter and portable one instead of influx)) Good channel, good luck.
this tutorial is really wonderful
Thanks
Is there a way to export the data from the dashboard trends to excel or CSV?
Nice video but i have small problem. I tried join on multiple data as you and it has weird behavior. Every click on refresh data in graph ti will create sql commands but output fromjoin has 2arrays, 1array, 3 arrays - i think it depends how fast will sqlite return result. I thought that join must wait until its complete but it seems that it wait for message complete but not for all data from sqlite. Can you please help ? Maybe i am blind and it is some stupid small error ...
Hmmm, the SQL does generate multiple SQL outputs, which will each generate and array. But in the line 148 of the SQL code, I am setting the complete attribute of the last message to true. The join will wait until it receives a message with .completed = true. It works for me. I am seeing multiple lines on my graph. I am not sure why it does not work for you. Maybe the last data set takes much less time to return and that gets to the join first? This is just a guess, but I don't have any other clue.
I have the same problem, maybe do u have a solution? Best regards
I have recently upgraded node red to v2 and now I find I get "Bad data inject" error from the chart node in the dynamic charts flow. I reverted back to v1.3.1 but still get the error which is strange. The charts have been working ok for a long time so I wonder if you are aware of this problem and perhaps you could help me to fix it. Thanks and stay safe.
I use a similar technique to you to preserve the graph data in a file then reload it on reboot. My interest in this article was to store the data in a database which seems logical. Is there a reason why you do not access the data from the database to reload the graph data rather than use the same data which is written to a separate file, this seems like a duplication of effort.
Liking your videos, thank-you.
No, not really. I think I just did the chart backup before I started storing values. And I have not gone back to change that. But I use the same method ever since. Fewer number of nodes to be honest :)
@@csongorvarga It wasn't a criticism. My original approach was the same as yours but then I thought perhaps using a database was a more logical conclusion, which was why I was interested in your video. Then on watching I found you weren't using the database but the same method as me to retrieve chart data. I just wondered if there was a reason for it. Not trying to fix something that is working is a valid reason.
Now I will try and get my head around SQLite, I haven't created a database since Dbase on the BBC Model B.
Cheers
@@dikl2689 OK got you. Well, you can easily do it using the above flow. Use the SQL, DB and Prep nodes. In the SQL node prefix the filters and that's pretty much it. You can even omit the Join if your chart is using a single data source. The code in the Prep function can also be simplified as it is prepared to combine multiple data sources but deals with a single just fine.
Hi, I'm having trouble getting data from a mysql database into a chart. In this video you are talking about another video where you explain this. Can you please tell me which video that was?
This is the Part 2 of this video: ua-cam.com/video/nkKf26oKzhQ/v-deo.html
Great Video. Would it be possible for you to share more information on setting the permissions for the database. I managed to create the database and able to create tables and data from phpliteadmin. However, when I try to insert data from node-red it tells me "Error: SQLITE_READONLY: attempt to write a readonly database". I know it's obviously a permissions issue, but being new to Linux, I'm having a hard time with this. I've changed permissions for all the folders and database file with chmod but still no luck. Perhaps creating a video on this alone would be great.
I am relatively new to Linux as well. I did chmod 777 before I created the database and everything was OK. So I don't really know what the solution is. By doing ls -al, I can see that the owner of the sqlite folder is root and for the database file it is www-data. Check what is the case in your system.
Mister impression!
Hi, could you help me to using mysql as database ?
Hello Mr. Varga, do you have the full flows? I'm looking for the flow that you show in your video in the minute 8:40? Great job. Tanks
You mean the miflora flower sensor? I have a separate video on that: ua-cam.com/video/8-wwijM4W88/v-deo.html
Hi Csongor,
To get phpliteadmin working do you have to install a full Apache web server or can you just install PHP ?
Thanks
Stuart
php runs under Apache, so you need the Apache as well. It has been some time since I did this but Apache was pre-install on my pi, so all I had to do is start up the service and it was working.
Nice video! Is phpmyadmin and phpLiteadmin the same? I only have phpmyadmin installed on my raspberry will this flow work with my nodered?
I am using SQLite with a specially lightweight database. And that only works with phpliteadmin.
@@csongorvarga I created the database, the tables and inserted some random values using python script. I have replaced your "sqlite" node with "mysql" node, but the mysql node is not retrieving any data and debug screen is empty and the same is the case with UI dashboard...since the data retrieval is not happening, I am not getting the chart. But this is a nice and simple dashboard though. I have a similar requirement where i need to plot a sensor value in chart.
ты потрясающий!
hello! I’m a beginner in programming so please excuse me for my ignorance:i need your precious help on a project. I have a beaglobone black which is supposed to get measurments from an amplifier and then save them in a new matrix( 2 columns of frequency f and time t ).I need the measurments imported into node red and then shown on a chart of frequency and time axes.could you help me? thanx a lot!
I will try to help. But how are you getting the readings into Node red? Displaying multiple values in the chart is easy, you just need to change the subject and the chart will keep track of the two data series automatically. Btw, there is a Node-Red Group where there are lot of people who can help. I can help here as well, but that platform is easier for communication than comments in UA-cam.
On node red i have used successfully the alafile node to pull data from an xls file. I would like those data displayed on a chart (X-Y data) which i keep falling short of doing. Any help would be really appreciated.Here are the xls files as well (www.dropbox.com/sh/z59s47b24uxm1ps/AACIBpMHQLgWfSjMX1Obsbhxa?dl=0) [{"id":"adc929dd.74c1c","type":"tab","label":"Flow 1"},{"id":"a293ab3c.c48d08","type":"inject","z":"adc929dd.74c1c","name":"","topic":"","payload":"","payloadType":"date","repeat":"5","crontab":"","once":false,"x":104.5,"y":187,"wires":[["8f3615ea.73b828","6391ad80.5e1324"]]},{"id":"8f3615ea.73b828","type":"alafile in","z":"adc929dd.74c1c","name":"X data","filename":"Desktop/NODE1","format":"xlsx","columns":"*A","headers":true,"x":331.5,"y":94,"wires":[["9ca08631.af42d","d7730091.04504"]]},{"id":"9ca08631.af42d","type":"ui_chart","z":"adc929dd.74c1c","name":"","group":"e50ac821.60ead","order":0,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"5","removeOlderPoints":"","removeOlderUnit":"60","cutout":0,"colors":["#1F77B4","#AEC7E8","#FF7F0E","#2CA02C","#98DF8A","#D62728","#FF9896","#9467BD","#C5B0D5"],"x":537.5,"y":196,"wires":[[],[]]},{"id":"d7730091.04504","type":"debug","z":"adc929dd.74c1c","name":"","active":true,"console":"false","complete":"false","x":752.5,"y":127,"wires":[]},{"id":"6391ad80.5e1324","type":"alafile in","z":"adc929dd.74c1c","name":"Y data","filename":"Desktop/NODE2","format":"xlsx","columns":"*A","headers":true,"x":366,"y":329,"wires":[["9ca08631.af42d"]]},{"id":"e50ac821.60ead","type":"ui_group","z":"","name":"Default","tab":"110b80c3.583e0f","disp":true,"width":"6"},{"id":"110b80c3.583e0f","type":"ui_tab","z":"","name":"XXX","icon":"dashboard","order":1}]
The dropbox link is not working.
What about now? www.dropbox.com/sh/z59s47b24uxm1ps/AACIBpMHQLgWfSjMX1Obsbhxa?dl=0
I found your post on the group, let me respond there.
hello,
can you send me node files inclued with the database files?
because i have copy your code but i get no line in the chart
thanks
I put the create commands into the video description. My DB is currently 300MB, so I will no send that over. You don't need the chart dump files either to make the flow work.
Is it possible to generate some code for software architecture in whatever language, with Node-Red?
Sorry, but what do you mean? Generate code for what?
For instance, with UML tools you can create/design a visual software architechture in diagrams and arrowed connections representing dependencies. Thus you can use that UML design to convert or generate the initial architectural code, then you can start populating that architechture with needed logic.
Vise versa you can write your software in code, then use eclipse for instance, to generate a UML Diagram to get a visual representation of your architechtural design of that written code.
So my question here is, can you use Node-Red to combine (custome) nodes so that its output could be generated code file(s) in python or C++ or in my case (Structured text) or whatever else?
I see, I am not aware the something like this is available.
Clarification: it is possible to build a custom node in node.js, but this way to advanced stuff for me. You can use the exec node to execute any phyton or C code and use the command line parameters, output, files etc. to exchange information. That's all I am aware of.
Hi, I liked your video very much, good work. I learned a lot of stuff especially SQLite.
I have a question about your soil fertility function, what is the sensor or sensors you use to detect soil fertility?
Thanks. I am using a Xiaomi MiPlant bluetooth sensor which measures temperature, sunlight, soil moisture and soil fertility.
Thanks for the quick answer. OK, so the fertility test is more like a TDS?
How are you handing comm with your application? BLE doesn't have much range!
I do have a video on that in my Home Automation playlist. I have my raspberry pi in the same room as the sensor, so I have no issues with the communication. That is main drawback of this BLE module. The communication has been reverse engineered, and a python code reads the values from the sensor. And I have no idea how it measures the soil fertility.
Thanks for the info., I'm interested in the Xiaomi compared to the Parrot because of pricing. I will try to retrofit an ESP8285 to replace BLE but power and deep sleep will be issues I guess.
Can you say how you are customize the chart node.
I am not doing much there, just setting up the retention period. And maybe the formatting of the X axis. I leave the rest as default.
Csongor Varga nagyon hasznos videó, nem tudom mi történik de a node red en belül nem jelenik meg a sqlite, lehet verzió függő?
any similar one in mongodb
why stop? show please last join and prep node, searching about some hours to prepare data for chart, nothing work realy. there is no chart input data documentation: format? max size? x-axis setting?
This is a very old video, the chart format has changed since. Please have a look at this example flow: this contains data format examples for almost all chart types. I am using this all the time: flows.nodered.org/flow/2938d98507c81648a2ab5ac1ba6e7d31
@@csongorvarga ty, i will check
Good topic and I look forward to going over it but for future videos, could you write down what your going to say as you reiterated quite a lot especially about why sqlite etc and please don't use "ah", "um" or "er", very distracting. Well done otherwise.
Bad habit ("ah", "um"), I am trying to rid of it somehow. Not to mention the really bad ones were edited out.
Why make a show video. With a complex behavior of the settings..Please do a really good beginner show and intro instead..Very difficult to follow such a complex setup ..cindly LG