Sqlite and Node-Red

Sqlite is an extremely light weight database that does not run a server component.

In this blog I wanted to document how I used Node-Red to create, insert and view SQL data on a Raspberry Pi. I also wanted to show how to reformat the SQL output so that it could be viewed in a Node-Red Dashboard line chart.

Installation

Node-Red is pre-installed on the Pi Raspian image. I wasn’t able to install the Sqlite node using the Node-Red palette manager. Instead I did a manual install as per the directions at: https://flows.nodered.org/node/node-red-node-sqlite .

cd ~/.node-red npm i --unsafe-perm node-red-node-sqlite npm rebuild

Create a Database and Table

It is possible to create a database and table structures totally in Node-Red.

I connected a manual inject node to a sqlite node.

sqlite_create_table

In the sqlite node an SQL create table command is used to make a new table. Note: the database file is automatically created.

For my example I used a 2 column table with a timestamp and a value

sqlite_db_config

Insert Data into Sqlite

Data can be inserted into Sqlite a number of different ways. A good approach for a Rasp Pi is to pass some parameters into an SQL statement.

sqlite_insert_flow

The sqlite node can use a “Prepared Statement” with a msg.params item to pass in data. For my example I created two variable $thetime and $thevalue.

sqlite_insert_conf

A function node can be used to format a msg.params item.


// Create a Params variable
// with a time and value component
//
msg.params = { $thetime:Date.now(), $thevalue:msg.payload }
return msg;

Viewing Sqlite Data

A “select” statement is used in an sqlite node to view the data.

A simple SQL statement to get all the data for all the rows in this example would be:

select * from temps;

A debug node can used to view the output.
sqlite_select

Custom Line Chart

Node-Red has a nice dashboard component that is well formatted for web pages on mobile devices.

To add the dashboard components use the Node-Red palette manager and search for: node-red-dashboard.

By default the chart node will create its own data vs. time storage. For many applications this is fine however if you want long term storage or customized historical plots then you will need to pass all the trend data to the chart node.

For some details on passing data into charts see: https://github.com/node-red/node-red-dashboard/blob/master/Charts.md#stored-data

Below is an example flow for creating a custom chart with 3 values with times.custom_chart_data

The JavaScript code needs to create a structure with: series, data and labels definitions


msg.payload = [{
"series": ["A"],
"data": [
[{ "x": 1577229315152, "y": 5 },
{ "x": 1577229487133, "y": 4 },
{ "x": 1577232484872, "y": 6 }
]
],
"labels": ["Data Values"]
}];

return msg;

This will create a simple chart:

custom_chart_image

For reference, below is an example of the data structure for three I/O points with timestamps:


// Data Structure for: Three data points with timestamps

msg.payload = [{
"series": ["A", "B", "C"],
"data": [
[{ "x": 1577229315152, "y": 5 },
{ "x": 1577229487133, "y": 4 },
{ "x": 1577232484872, "y": 2 }
],
[{ "x": 1577229315152, "y": 8 },
{ "x": 1577229487133, "y": 2 },
{ "x": 1577232484872, "y": 11 }
],
[{ "x": 1577229315152, "y": 15 },
{ "x": 1577229487133, "y": 14 },
{ "x": 1577232484872, "y": 12 }
]
],
"labels": ["Data Values"]
}];

Sqlite Data in a Line Chart

To manually update a line chart with some Sqlite data I used the following nodes:

sqlite_2_chartThe SQL select statement will vary based on which time period or aggregate data is required. For the last 8 values I used:

select * from temps LIMIT 8 OFFSET (SELECT COUNT(*) FROM temps)-8;

The challenging part is to format the SQL output to match the required format for the Line Chart. You will need to iterate over each data row (payload object) and format a JSON string.

 //  
 // Create a data variable   
 //  
 var series = ["temp DegC"];  
 var labels = ["Data Values"];  
 var data = "[[";  
   
 for (var i=0; i < msg.payload.length; i++) {  
   data += '{ "x":' + msg.payload[i].thetime + ', "y":' + msg.payload[i].thetemp + '}';  
   if (i < (msg.payload.length - 1)) {  
     data += ","  
   } else {  
     data += "]]"  
   }  
 }  
 var jsondata = JSON.parse(data);  
 msg.payload = [{"series": series, "data": jsondata, "labels": labels}];  
   
   
 return msg;  

To view the Node-Red Dashboard enter: http://pi_address:1880/ui

Screen_chart_sqlite

Final Comments

For a small standalone Raspberry Pi project using sqlite as a database is an excellent option. Because a Pi is limited in data storage I would need to include a function to limit the amount of data stored.

22 thoughts on “Sqlite and Node-Red

  1. Hi! I could have sworn I’ve visited this web site before but after browsing
    through some of the articles I realized it’s new to me.

    Anyways, I’m certainly pleased I found it and I’ll be bookmarking it and
    checking back frequently!

    Like

  2. Hello, I want to insert multiple sub-sections in the table like, Timestamp and then Name, lastname, phonenumber. I tried storing this value in a variable and insert the specific variable using msg params but it won’t work

    Like

    1. Hi, for all our testing we first did everything in the “DB Browser for SQLite” app. This will verify that your query and naming convention is correct. Once we proved things in DBBrower we then used the queries in Node Red. Pete

      Like

  3. Hi, I’ll try to use this informations for my smart meter
    have this both with
    select Time,Leistung from solarstrom;

    Time Leistung
    1621237053112|348.22
    1621237058130|348.51
    1621237063164|348.55
    1621237068200|348.53
    1621237073270|348.61
    1621237078284|348.83
    1621237083298|348.86
    1621237088322|348.92
    1621237093363|339.42
    1621237098381|349.12
    1621237103470|349.4
    1621237108477|349.37
    1621237113504|349.11
    get one array

    [320 … 328]
    320: object
    Time: 1621237727466
    Leistung: 385.44
    321: object

    and use this from the website
    //
    // Create a data variable
    //
    var series = [“Leistung Watt”];
    var labels = [“Data Values”];
    var data = “[[“;

    for (var i=0; i < msg.payload.length; i++) {
    data += '{ "x":' + msg.payload[i].Time + ', "y":' + msg.payload[i].Leistung + '}';
    if (i < (msg.payload.length – 1)) {
    data += ","
    } else {
    data += "]]"
    }
    }
    var jsondata = JSON.parse(data);
    msg.payload = [{"series": series, "data": jsondata, "labels": labels}];

    return msg;

    but i get this error.

    "SyntaxError: Unexpected token L in JSON at position 27"

    can you psl give me a litte hint

    regards

    Like

  4. have found that i have wrong Data at my DB.
    1621254696852|610.77
    1621254701884|611.43
    1621254706976|611.54
    1621254707655|Leistung =
    1621254711951|610.95
    1621254716981|611.46
    1621254719968|Leistung =
    1621254722067|610.75
    1621254727063|611.48
    1621254732102|610.92
    1621254737117|611.01
    is there any way to ignore the lines with Leistung =

    regards

    Like

    1. Hi,
      I’d try to do a check if column2 is numeric, something. There are a few ways to do this, one way is to use filters with a regular expression function:

      SELECT * FROM myTable WHERE column REGEXP ‘^[0-9]+$’;

      Let me know if you get stuck.

      Like

  5. Hi, thx fore succession but get this

    sqlite> select time,leistung from solarstrom where column REGEXP ‘^[0-9]+$’;
    Error: no such function: REGEXP

    regards

    Like

  6. 🙂 great this will work thx.
    last question (hopefully) is it possible get the date within the x-axis? maybe at 00:00?
    sorry for the mass of questions, but i use sqlite3 since yesterday and have no skill.

    Like

  7. Hi, again. 😉
    have one more question.
    i won’t have a bar chart with my historical data.
    sqlite> SELECT * from daten_gestern
    …> ;
    1621615512513|0.14
    1621636200019|
    1621722600020|
    1621809000143|
    1621895400012|
    sqlite>
    this is for test, not real data but i have one data point per day and I’m unable to find a way bring this in a bar chart.
    triggers once per day.
    can anyone give me a hint, pls.

    thx and regards

    Like

    1. Hi, I’m not 100% sure what you are after. If you want to have a bar chart with the last value then I’d try writing an SQL query that is ordered in DESCending order with a limit of 1.

      In Sqlite3 it would be something like:

      select value from table order by thetime DESC limit 1;

      Like

  8. Hi Pete, thx.
    but not the sql statement is the problem.
    my problem are convert the the output from this select as input for the chart.

    Like

  9. Ok,
    now i have found a way.

    var jetzt = msg.payload;
    var date = (60*60*24*1000);
    var delta = jetzt – 3*date;
    msg.payload = ‘select * daten gestern where “Time” < ? var delta?;'
    return msg;

    but how i can put the "var delta" to the select string?

    regards

    Like

  10. hi, no but this works fine
    var jetzt = msg.payload;
    var date = (60*60*24*1000);
    var delta = jetzt – 3*date;
    msg.payload = delta;
    msg.topic = ‘select * from daten_gestern where “Time” > ‘ + (msg.payload) +’;’;
    return msg;

    thx for help and lend an ear 🙂

    Like

  11. so, now i have for sqlite a working drop down menue.
    this is the flow.

    [{“id”:”df49d3f6.889c4″,”type”:”inject”,”z”:”678c792c.bea1c8″,”name”:””,”props”:[],”repeat”:””,”crontab”:””,”once”:false,”onceDelay”:0.1,”topic”:””,”x”:150,”y”:640,”wires”:[[“7a467a30.b73034”]]},{“id”:”7a467a30.b73034″,”type”:”function”,”z”:”678c792c.bea1c8″,”name”:”vor x tagen”,”func”:”var jetzt = Date.now();\nvar date = (60*60*24*1000);\nvar dauer = flow.get(‘dauer’);\nvar delta = jetzt – dauer*date;\nmsg.payload = delta;\nmsg.topic = ‘select * from daten_gestern where \”Time\” > ‘ + (msg.payload) +’;’;\nreturn msg;”,”outputs”:1,”noerr”:0,”initialize”:””,”finalize”:””,”libs”:[],”x”:410,”y”:640,”wires”:[[“f486e9c2.30daa8”]]},{“id”:”f486e9c2.30daa8″,”type”:”sqlite”,”z”:”678c792c.bea1c8″,”mydb”:”419daf8f.67fb28″,”sqlquery”:”msg.topic”,”sql”:”insert into daten_gestern (Time, Gestern) values ($Time, $Gestern);”,”name”:”daten_gestern”,”x”:600,”y”:640,”wires”:[[“84db808d.c2874”]]},{“id”:”84db808d.c2874″,”type”:”debug”,”z”:”678c792c.bea1c8″,”name”:””,”active”:true,”tosidebar”:true,”console”:false,”tostatus”:false,”complete”:”payload”,”targetType”:”msg”,”statusVal”:””,”statusType”:”auto”,”x”:780,”y”:720,”wires”:[]},{“id”:”9d181d3f.56ccd8″,”type”:”ui_dropdown”,”z”:”678c792c.bea1c8″,”name”:””,”label”:”Dauer”,”tooltip”:””,”place”:”Select option”,”group”:”bf5c7942.04d048″,”order”:15,”width”:3,”height”:1,”passthru”:false,”multiple”:false,”options”:[{“label”:”1 Tag”,”value”:1,”type”:”num”},{“label”:”2 Tage”,”value”:2,”type”:”num”},{“label”:”5 Tage”,”value”:5,”type”:”num”}],”payload”:””,”topic”:””,”topicType”:”str”,”x”:130,”y”:720,”wires”:[[“fc9f9bb3.a71ac”]]},{“id”:”fc9f9bb3.a71ac”,”type”:”change”,”z”:”678c792c.bea1c8″,”name”:”Today”,”rules”:[{“t”:”set”,”p”:”dauer”,”pt”:”flow”,”to”:”msg.payload”,”tot”:”msg”}],”action”:””,”property”:””,”from”:””,”to”:””,”reg”:false,”x”:250,”y”:680,”wires”:[[“7a467a30.b73034”]]},{“id”:”419daf8f.67fb28″,”type”:”sqlitedb”,”db”:”/home/pi/Solar/test.db”,”mode”:”RWC”},{“id”:”bf5c7942.04d048″,”type”:”ui_group”,”name”:”Solar Strom Daten”,”tab”:”a13331.fb76ecd”,”order”:1,”disp”:true,”width”:20,”collapse”:false},{“id”:”a13331.fb76ecd”,”type”:”ui_tab”,”name”:”Daten Solarstromerzeugung”,”icon”:””,”disabled”:false,”hidden”:false}]

    regards

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s