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 rebuildCreate 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.
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
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.
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.
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.
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.
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:
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:
The 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
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.
Fun Tech Projects 
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!
LikeLike
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
LikeLike
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
LikeLike
Could you please copy and paste your JSON code here to import.
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
Hi, thx fore succession but get this
sqlite> select time,leistung from solarstrom where column REGEXP ‘^[0-9]+$’;
Error: no such function: REGEXP
regards
LikeLike
Hi, try doing a CAST statement for example:
select time,liestung from solarstrom where
CAST(liestung as INT) > 0
This worked for me in Sqlite3
LikeLike
🙂 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.
LikeLike
have found it self 😉
it is at the chart parameter now i use dd MM.yy HH:mm and are happy…
LikeLike
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
LikeLike
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;
LikeLike
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.
LikeLike
Hi,
I would recommend looking at:
https://github.com/node-red/node-red-dashboard/blob/master/Charts.md#stored-data
This link lays out the data structure that you’ll need for all types of charts.
For example to do 3 bars with a label:
msg.payload = [{
“series”: [ “X”, “Y”, “Z”],
“data”: [ [5], [3], [6] ],
“labels”: [ “Jan” ]
}]
return msg;
LikeLike
thx
LikeLike
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
LikeLike
msg.payload = ‘select * daten gestern where “Time” < ' + delta;
LikeLike
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 🙂
LikeLike
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
LikeLike
Cool
LikeLike
Cara, muito obrigado, isso me ajudou muito!
LikeLiked by 1 person