Posted by kll on August 11 2022 09:54:17
i used MQTT already, with a broker on a RPI locally, but
if you want to share data you need a broker in the cloud.

HIVEMQ broker to local NODE RED webserver dashboard

( check also: Adafruit IO FREE: 30days history and dashboard )

Node Red to SQLite3

Node Red hist trend from SQLite

Node Red dashboard operation

install tests

node-red little steps

MQTT little steps

use ESP for MQTT client

HIVEMQ broker to local NODE RED webserver dashboard

i read a example i like, even it is about a use of a local
+ + RP2040 W with ( temp humid ) sensors
+ + RPI4 local webserver get the data via cloud MQTT broker
where all this not make much sense...

BUT good for learning

the article i read is
raspberry-pi-pico-w-node-red from Les Pounder / tom's Hardware

and it uses / requires a free account at

besides it states you can use up to 100 devices,
it limits to 10GB DATA LIMIT
and i have no idea what that means?
if i use 1 device and 1 topic ( temperature sensor )
and i send that every minute
- - this example uses 5sec per a micropython 'time.sleep(5.0)'
? are that values stored or only counted for that limit?
and what happens after that limit is reached?

setup a account ( signup ) at HIVEMQ
-1- signup: user password email

-2- get verification email ( press confirm )

-3- fill-in profile

-4- select a cloud provider ( aws or azure )

-5- make a credential for a device
user password

at cluster capacity see:
Cluster Capacity
Cluster capacity data
MQTT Client Sessions: 100 *
Data Traffic: 10 GB *
Data Retention Time: 3 Days
Max Message Size: 5 MB

for the raspberry pi i use a old RPI2

+ + where i have a project in work about getting data via
python BLINKA I2C ( like ADC breakout or linked Arduino... )

the install not work ? i have nvm...
error about sudo npm

sudo apt install nodered
reboot, enable autostart,
sudo systemctl enable nodered.service
( but get the old 2.12 version only )

call in browser from PC
( RPI2 ethernet is 102 )

try to generally follow that teaching,
-1- mqtt in
( here the setup info of security tab is missing with 'device' user password /
then i get a 'connected' )
as subscription topic i use 'KLL/RPI2/getvals'
what now i have to feed somehow
( as i not have the PICO W ( hardware or setup )
-2- debug
-3- install and add dashboard
+ + i just use a text box to show whats last payload
to show the dashboard ( in a add browser tab )
there is a layout / site / theme menu
with a rectangle with uparrow, click it

now i need to send MQTT data, it might come from any online MQTT publish
on an other continent, but for play, this
RPI2 also should publish the data:

make new project path and install mqtt
pi@RPI2:~/projects/BLINKA/mqtt_service $ pip3 install paho-mqtt
Looking in indexes:,
Collecting paho-mqtt
Downloading (75 kB)
|████████████████████████████████| 75 kB 163 kB/s
Installing collected packages: paho-mqtt
Successfully installed paho-mqtt-1.6.1
pi@RPI2:~/projects/BLINKA/mqtt_service $

now take the example code from hiveMQ python
but change back to mqtt 3.1.1 instead of 5 ???

the node red thing seems not to work,
but using a python send and python get code ( in 2 terminal windows )
i could verify that the MQTT cloud broker worked fine

oh, i see already
cluster detailsCluster Capacity
Cluster capacity data
MQTT Client Sessions: 3 / 100 *
Data Traffic: 18.28 KB / 10 GB *

so better look for a free forever provider with different type of limits ( like slow update rate... bandwidth limit only)

so now i can try to get the node_red service running
-1- check again the credentials and settings same as in python
( the simple settings from tutorial ( ) could work too check)
-2- oh yes, the debugger has a little activate button on the right ( so i never see anything )
-3- the dashboard page need some more work

Node Red Import json

just for understanding..
a MQTT client device can be small ESP8266 ( like a SonOff ) or wifi arduino
and i not expect that that client has RTC or wifi net time.
so i not send time!
but in the nodeRed and my sqlite3 database record / and Adafruit
the receiving client adds a timestamp ( yes not too accurate as it requires 2 MQTT transfers to get there )
also thinking in epoch ( and not local time ) seems correct.

for some of the testing this tool might be good:
online client

b4 i play more with node red i give it a other try to update it.

bash <(curl -sL

this time it worked ( incl. my flow1 sketch survived )

in Node Red i added a 'Flow 2'
to test a MQTT send from there, easy as the credentials seems to be copied internally.

and for the dash board play with layout and add a graph / trend

try construct a JSON record ( but only show as text / not ?understood? as record )

now from JSON to values to trend


when i do a system update it told me to do a
sudo apt autoremove ( about some old node red stuff )
after i cleaned up it did not work anymore.
i needed to go the full way:
( INSTALL NODE RED 2 / update to 3 / on a RPI2 took about an hour )

Adafruit IO FREE: 30days history and dashboard

better have a account where you can feed MQTT and it stores and trends data there:
now i try

adafruitIOCurrent Plan
IO Free

30 data points per minute
30 days of data storage
Actions every 15 minutes
5 dashboards
2 WipperSnapper devices
5 groups
10 feeds
Community support
Projects and guides

and for the paid version:
Adafruit IO PlusPower Up
$10 per month or $99 per year

60 data points per minute
60 days of data storage
Actions every 5 seconds
Unlimited dashboards
Unlimited WipperSnapper devices
Unlimited groups
Unlimited feeds

under< user >/feeds [IO][FEEDS]
i make a new GROUP 'rpi2' and a new FEED 'getvals'

and copy the python example here
after install the library with
sudo pip3 install adafruit-io

so use
in line 67 publish replace 'DemoFeed' with 'rpi2.getvals'
in line 19 my username 'kll'
in line 15 my key 'xx' , this you find when press the yellow KEY symbol 'API key'

now make a dashboard from the data feed ( RPI2 / python MQTT random value )

do some cleanup:
-1- make the same for the Adafruit user settings in
-2- make also a to show send data ( and confirm Adafruit works like a full MQTT broker )
( i did not check if that 'feed' declaration ( like for the show, trend and dashboard ) is needed ( if use it just as broker )

Node Red to SQLite3

back to NodeRed
install node red on RPI4
and copy the flow.json between RPI2 and RPI4
i did try to copy the credentials file 'flows_cred.json' too but it not worked ( differently encrypted )
so fill MQTT user password manually

i see the problem about long time storage
and think to use LOCAL SQLite ( here play on RPI4 )
as i have no idea if there is a free service ( same like google cloud / but without credit card ... )

INSTALLsudo apt-get install -y sqlite3

sudo apt install -y sqlitebrowser

cd .node-red
npm install node-red-node-sqlite
(##################) ⠏ reify:node-gyp: timing reifyNode:node_modules/sqlite3 Completed in 1837ms

< wait long time >

changed 90 packages, and audited 233 packages in 5m

32 packages are looking for funding
run `npm fund` for details

found 0 vulnerabilities

sudo reboot now

make a database and a table using the RPI APP: [Start][Programming][DB Browser for SQLite]
but it is also possible to send some manual triggered SQL " create table " from node red..
generally SQLite is very easy from admin few,
when i play it from python, storing the first record created automatically database file and table...
( that i did not try here.. )
anyhow good way:

now i got running to store / readback a mqtt.payload JSON into a SQLite TEXT column
by converting it on both end using : replace inner " by '
but i found a info what talks about a JSON column type,
that i want play now.
-a- get again create database and table / delete table / empty table / store record
( here i use /home/pi/.node-red/data.sqlite3 )

the SQL is tricky like have to use \" for topic to topic TEXT column and ' for object into JSON column
but not sure there are real data in? as the sqlite browser just say [object Object] DONT TRUST THAT
ok try to read back.
yes, no object, no idea : stop it and leave the TEXT column way

Node Red dashboard operation

-1- Power supply On Off
-2- Volt DC Setpoint 0 ..12

send via MQTT

hm.. how to test all this?
i write a little python emulator:
AC Volt = 233.5
DC Volt == MQTT setpoint 0 .. 15
DC OHM = 1.0
DC Amp = DC Volt / DC OHM * ( 0 || 1 ) PS On Off
DC Watt = DC Volt * DC Amp
AC Watt = DC Watt * 1.15 ( about PS efficiency )
AC Amp = AC Watt / AC Volt

i report ever 10 min AC V A W via KLL/rpi2/getvals
and listen to MQTT KLL/rpi2/setvals : dashboard setpoints OnOff and DC Volt changes


Node Red hist trend from SQLite

next thing is to check on the SQLite3 database and read it back to node red
and show a data table & hist trend in ( new ) dashboard

but there was a startup problem for the table..
try to init nicely by selecting the last 30 records ( after boot / autostart node red / first "HIST" page open )

and add a HISTORIC trend

the coding is tricky ( lucky i found it online )
function node ( to trend node )msg.topic = "Volt"
//msg.payload = [{
// "series": ["A","B","C"],
// "data": [
// [ {"x":15040209632890,"y":5} ,
// {"x":15040209634890,"y":3}
// ],
// [ {"x":15040209632890,"y":1} ,
// {"x":15040209634890,"y":2}
// ],
// [ {"x":15040209632890,"y":15} ,
// {"x":15040209634890,"y":13}
// ]
// ],
//"labels": ["A","B","C"]

//msg.payload = [{
// "series": ["Volt"],
// "data": [
// [ {"x":15040209632890,"y":5} ,
// {"x":15040209633890,"y":7} ,
// {"x":15040209634890,"y":3}
// ]
// ]

// Create a data variable
var series = ["Volt AC"];
var labels = ["Data"];
var data = "[[";

for (var i=0; i < msg.payload.length; i++) {
data += '{ "x":' + msg.payload[i].timedate + ', "y":' + msg.payload[i].Volt + '}';
if (i < (msg.payload.length - 1)) {
data += ","
} else {
data += "]]"

var jsondata = JSON.parse(data);

msg.payload = [{"series": series, "data": jsondata, "labels": labels}];

return msg;

install tests

some final thoughts,
** the node red 3 install on RPI is buggy
** the node red flows.json copy miss the MQTT passwords (good)
** the python tools ( data source mqtt client ) are to be on a other RPI ...
"in a galaxy far far away"
use same broker / credentials
( real test needed )
** need to dig into mqtt security ( client_id ? ) ( also change to MQTT5.0 )
** automated SQLite data file / table creation ( if not exists )

*** lets say you have 2 devices sending ( same structured ) data?
means they must have a different TOPIC (node red dropdown device==topic selector? )
*** subscribe use _*_, and store same database, so need select device in readback SQL

so next i will do a new setup ( "RASPBIAN" bullseye 32 )
to make a more condensed / snappy setup/install tutorial here.

so for the pros that might sound funny,
but i google about install node red on a NON DESKTOP SERVER
and not come up with an fast answer..
ok, boot RPI3 with a RASPBIAN Lite BUSTER 4 GB SD
** update ( to 5.10.103 -v7 )
try to get nodejs 16
curl -sL | sudo -E bash -

sudo apt install -y nodejs

install node red 3
bash <(curl -sL
enable autostart
sudo systemctl enable nodered.service
spaceFilesystem Type Size Used Avail Use% Mounted on
/dev/root ext4 3.4G 1.6G 1.7G 49% /

oh, could have done it with a 2GB SD card too, but already difficult to get 8GB SD or USB stick

and try that again ( my RPI3 has USB boot enable )
RPI imager burn new RASPBIAN desktop to 8GB USB stick ( with ssh enable, new password... )
boot in RPI3 and come up on "FING" as
ssh into it, update, sudo raspi-config enable VNC / set VNC resolution fullHD
now try that 3 step / curl / nodejs / curl nodered3 / install on that fresh system

++ burn RASPBIAN desktop 32bit to USB2 stick 8GB
++ burn RASPBIAN lite 32bit to SD 4GB
(using RPI imager incl. ssh enable and password change )

++ boot in RPI3 ethernet
++ ssh in enable VNC / VNC fullHD display/
++ update

curl -sL | sudo -E bash -

sudo apt install -y nodejs

bash <(curl -sL

sudo systemctl enable nodered.service

sudo reboot now

external browser try node red:
right top menu: manage palette / install / node-red-dashboard 3.1.7

SQL preparations:
cd .node-red
sudo apt-get install -y sqlite3
sudo apt install -y sqlitebrowser ( not on the light version )
npm install node-red-node-sqlite ( takes very long )

reboot and check node-red on SQL node

for a python MQTT client:
sudo apt install python3-pip
pip3 install paho-mqt

node-red little steps

one of the above questions was about a topic selector
in case you save records from several device in one database
and want later show trend ( but obviously only of one )

test a drop-down selector

add i wanted to learn to make the UI different
like using a background image on its page.
using the ui template inject a style into the page header
linking to a picture,
but that must be enabled in the settings first: in /home/pi/.node-red/settings.js find about:
httpStatic: '/home/pi/.node-red/images/',

note: here i play/learn on a cleaned up flows.json
not build into the project, as the whole topic structure is to be checked:
like can i subscribe to "KLL/*/getvals" aka from all devices and save to database

again for SQLite the install:
cd .node-red
sudo apt install -y sqlite3
sudo apt install -y sqlitebrowser ( optional: desktop tool )
npm install node-red-node-sqlite ( takes very long )
and find in node--red under "storage" node "sqlite"
setup as
[deploy] debug: "failed to open ..."
now add a "inject node" ( remember sqlite-node and inject node both talk via msg.topic not msg.payload )
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,

+ + database file is created and table defined ( 16kB )

now add a second "inject node"
INSERT INTO contacts (first_name,last_name,email,phone) VALUES ("me","kll","","1 234 567891");

and a debug to see the SQL commands send

use the desktop tool to verify

now you can delete the database file and try the 2 manual injections again
also try delete table ( DROP ) or empty table ( DELETE FROM )

today testing about node-red variables...
there is a
var myvar = context.get("varname");
and a
each can be done using
context.set(); //___________________ work for this node only ( like for incrementing a counter )
flow.set(); //______________________ work between nodes inside same flow
global.set(); //____________________ work between flows

if you have problems about timing, can use
if (context.get("counter") === undefined) {
context.set("counter", 0)

like in the function ON START TAB

on my learning system RPI2 i reinstalled node-red ( 3.0.2 )
and run the
node-red admin init
this makes a new /home/pi/.node-red/settings.js
and i selected both:
** the login and
** the project option

i choose to not encrypt that so in the settings.js can edit
"permissions" : "*"
"permissions" : "read"

now, would you dare to use it later in on your working system?
or better make a complete new installation and try to save the backup flow.json from the working system
into the new 'project' folders
** production
** play-learn

oh, here a good question:
i want a login system for the dashboard too:
** view and operate
** view
not find a way, in settings.js:
httpNodeAuth: {user:"user",pass:"$2b$08$nf78svfpL9MrdkZKCrdcNOGDdRPJlZEhyC3XAWZHw7ZophSJHzZmi>
httpStaticAuth: {user:"user",pass:"$2b$08$nf78svfpL9MrdkZKCrdcNOGDdRPJlZEhyC3XAWZHw7ZophSJHzZ>

gives you login: user pw: user
but no separation view / operate.

a little big step in using node-red is about editor / code...
-a- with click / select several nodes / context menu: EXPORT
get the code JSON to see / file / forum

-b- with [ctrl][i] can import code ( also like from forum.. or other node-red system )

-c- in case of mqtt-in dynamic subscription
you can feed parameters like

MQTT little steps

above i started with using a cloud MQTT broker ( play HIVEMQ and Adafruit )
so assuming a node red client ( and the there local database storing ) is in an other location as the data source ( MQTT client / smart sensor )

but what if we are on a remote island?
so we can use a local LAN for all,
means we need a MQTT broker in the RPI.
sudo apt update sudo apt upgrade
sudo apt install mosquitto mosquitto-clients
sudo systemctl status mosquitto

sudo nano /etc/mosquitto/mosquitto.conf add
allow_anonymous false

password_file /etc/mosquitto/pwfile

listener 1883

sudo mosquitto_passwd -c /etc/mosquitto/pwfile YOUR-NEW-MQTT-USERNAME

edit python credentials
make a python client ( local hardcoded IP in credentials, 1883, NO / uncomment TLS )
run RPI local broker on RPI3
run python MQTT client "sending" also RPI3 ( for a good test should be a 3rd device )
node-red server on RPI2

when i used the database tools just from node-red and also test a manual INSERT inject node
i see that that should not be used on a running database ( well the time stamp is old and creates problems in the graph.. )
but the main thing is the database structure itself, very questionable..
- - timestamp epoch and timestamp readable is redundant... can create the readable timestamp for the printed table while reading back.
- - save the mqtt topic is needed... like when you want save data from several sites in one db
but readback must do the "where topic is selected_topic" from that tested dropdown menu
- - save the mqtt payload aka data what is a JSON construct STRING
is needed, very flexible, devices can send what they want...
- - the from there extracted volt amp watt store again is redundant and creates problems for variable data from devices / sites.

so i need make a new structure for the database:
timestamp epoch , mqtt.topic, mqtt.payload
and for the 'show' like readback 30 records from one site ( or device ) the lists like time,volt, amp, watt
must be created ( as arrays )and used for html-table print, and hist graphs...
summary: need a new slim database and more JS tools in node-red.

but that is only one part: mqtt to db..
also already established that topic == site / so different devices ( like from one site ) must have its device name?identifier ?
in the payload JSON structure..
again for using the data need a second dropdown selector and searching for that specific device in the payload hist records

for the MQTT topic
structure now KLL/RPI2/getvars ( setvars )
so yes 3 sections is ok,
-a- unique system identifier KLL/
-b- site / remote project ID / here mqttclient / can have several feeds with different datastructure /ESP7/
-c- change to /get /set for data or commands

the payload is JSON with
optional counter ( see there was a reboot or packets missing )
optional datetime if RTC available
mandatory device ID
optional data packs "power":{...}, "status":"good"

until now ( 2 days later ) i not get that "improvements" running!
i think i miss some basics..
but storing JSON in SQLite TEXT column did not work out.
so needed long JS functions for storing and retrieving

add i needed to flatten out / rearrange the data for a simple list ( printed in HTML table )

all looks good ( like in the first version ) just no data on the web page
( and no idea how to debug that part )

but i do think i got the problem solved about checking if data are in a JSON before storing to variable and running into problems )
use .hasOwnProperty('var_name')
for .power.Volt structure it is even more complicated to check.
( but must, as i say that data are optional )
only after that works i can try that ( dropdown) selector on "dev":"Powersupply7inthegarage"
also better for all this to have new database tables for projects / sites ( selector ) / devices ( selector ).

but good news: today my internet was down ( for 8 hours? or so )
but sure LAN still works. So i used the new RPI local mosquitto broker
made it in the python simulation select-able by command line '-b'
but in node-red i would not know how to do, so i copy the mqtt-in block
and edit broker and port "" to use RPI3 mqtt broker
! optional different user / password needed.

but coding without googling tutorial / forums ... lucky mobile was still online

use ESP for MQTT client

now i wanted to go into ESP ( old ESP8266 ) via Arduino IDE for MQTT
here to my Sonoff

now there is a general ?problem? like
ESP8266 can't do TLS, that makes it incompatible to many good online MQTT systems also like rainmaker
but here i see a network idea what uses ESP32 as a ?gateway?
advanced MQTT networking