Automation etc with Node Red - How to make your Dashboard2 chart data persistent using a SQLite database

 How to make your chart data persistent using a SQLite database

Reliable charts and graphs with Node Red

 So I've been using Node Red for quite some time now as part of my general IoT network, involving wireless radio modules and numerous sensors etc. One main drawback of using Node Red, is that by default, it doesn't store data long term, and your charts etc will go blank after a restart or shutdown.

 Using a SQLite database combined with Node Red, I've been able to store information for the long term that persists even after restarts and shutdowns and this is how I did it.

 

Above is an image of a simple Node Red flow, logging and retrieving information from a database.

  So first things first, I will go through the process of the above flow:

1. Arduino and other sensors send in data over MQTT (Purple "houseTemp" and "houseHum") 

2. We need to combine the information into a single record and insert it into a database. (Light orange "Filter Dups & Insert")

 3. The data is then logged to a SQLite database

.4 At the same time, we select data from the database, so we can insert the newest data into some charts

5.  The results of that selection are fed directly into some charts:

 

 

 Detailed Process & Code: 

1. An Arduino sends in a message via MQTT with the msg.topic "houseTemp" and a float that represents the temperature (msg.payload), and another message with the msg.topic "houseHum" and a float that represents the humidity as the msg.payload.

2.  Then we combine the 2 results into 1, and insert the data using a function node:

 var temp = context.get('temp3') || null;

var hum = context.get('hum3') || null;

var ctr = context.get('ctr3') || 0;

if (msg.topic == "houseTemp") {

   context.set('temp3', msg.payload);

} else {

   context.set('hum3', msg.payload);

}

var sendMsg = false;

ctr = ctr + 1;

if (ctr >= 2) {

   ctr = 0;

   sendMsg = true;

}

context.set('ctr3', ctr);

msg.topic = "INSERT INTO HOUSETEMP (TIMESTAMP, TEMPERATURE, HUMIDITY) VALUES (" + Date.now() + ", " + temp + ", " + hum + ")";

if (sendMsg == true) {   

if (hum != null && temp != null) {

      return msg;

   }

} else {

   return;

} 

3. The data is logged to a SQL database, per the "INSERT" command in the previous function. 

4. Lets "select" or retrieve all the data including the data that was just logged:

msg.topic = "SELECT * FROM HOUSETEMP ORDER BY TIMESTAMP DESC LIMIT 1000";

return msg;

 

4. The database node responds immediately to the previous function, returning data

 

5. Display data in the charts:

 The main things to consider are having all of your variable names correct, and

 using the correct "properties" configuration in your Chart Node.

 

General DB Maintenance: 

In order to create a table, you need to add an "inject" node and set the msg.topic to something like:

CREATE TABLE HOUSETEMP ( TIMESTAMP INT PRIMARY KEY NOT NULL, TEMPERATURE float NOT NULL, HUMIDITY float NOT NULL)

 Since we are using a Linux timestamp, we can use the following formula to clean up old data via another "inject" node:

DELETE FROM HOUSETEMP WHERE TIMESTAMP < strftime('%s', 'now', '-14 days'); 

  

 Overview:

This generally works pretty well and keeps the graphs & charts up to date with good data, storing it for up to 2 weeks. Of course, this is a heavy-handed approach, replacing all the chart data each time.

A bit better solution. would be to make the SELECT function node only return 1 record at a time. Then, add a blank inject node that triggers only once on startup, that both resets the charts ( send JSON msg.payload of []  ), and loads all existing data into the charts. The arrays coming out of the database at the end would need to be reversed as well, so the newest data is at the end of the array.

This is a much less intensive way to go about it, but a little more complicated to set up.