In this project you’ll install an SQLite package for Node-RED and learn how to write simple SQL queries to interact with your SQLite database.
Recommended resources:
- You need a Raspberry Pi board – read Best Raspberry Pi Starter Kits
- Getting Started with Node-RED on Raspberry Pi
- SQLite Database on a Raspberry Pi
- How to install phpLiteAdmin Database in a Raspberry Pi
- ESP8266 Publishing DHT22 Readings to SQLite Database
If you like home automation and you want to build a complete home automation system, I recommend downloading my home automation course.
Basic Raspberry Pi setup
Before you continue reading this project, please make sure you have Raspbian Operating System installed in your Raspberry Pi.
You can read my Getting Started with the Raspberry Pi Guide to install Raspbian and complete the basic setup.
Installing SQLite
Follow this next tutorial to learn how to Install SQLite database on a Raspberry Pi . Having SQLite installed in your Raspberry Pi.
Installing Node-RED SQLite
Run the list of commands below to install node-red-node-sqlite in your Node-RED user directory. This packages gives you basic access to an SQLite database.
pi@raspberry:~ $ sudo apt-get install npm pi@raspberry:~ $ sudo npm install -g [email protected] pi@raspberry:~ $ hash -r pi@raspberry:~ $ cd ~/.node-red pi@raspberry:~/.node-red $ npm install node-red-node-sqlite
Restart your Node-RED software with the next commands for the changes to take effect:
pi@raspberrypi:~/.node-red $ cd pi@raspberrypi:~ $ node-red-stop pi@raspberrypi:~ $ node-red-start
Checking the installation
When your Node-RED software is back on, you can open it entering the RPi IP address in a web browser followed by :1880 as follows:
http://YOUR_RPi_IP_ADDRESS:1880
In my case is:
http://192.168.1.98:1880
A new node called sqlite should appear on the left under the storage tab:
Creating the flow
In this flow, you’re going to send 5 SQL queries (CREATE, INSERT, SELECT, DELETE and DROP) to your SQLite database. Follow these next 10 steps to create your flow:
1) Drag 5 inject nodes, 1 sqlite node and 1 debug node
2) Press the Add new sqlitedb button
3) Type /tmp/sqlite in the Database field
4) Configure your CREATE inject node as follows
CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currenttime TIME, device TEXT)
5) Configure your INSERT inject node
INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(22.4, 48, date('now'), time('now'), "manual")
6) Configure your SELECT inject node with
SELECT * FROM dhtreadings
7) Configure your DELETE inject node as follows
DELETE from dhtreadings
8) Configure your DROP inject node
DROP TABLE dhtreadings
9) Connect all your nodes
10) To save your application, you need to click the deploy button on the top right corner
Your application is saved and ready.
Testing the flow
Let’s test the simple flow. Open the debug window and press the first inject node to trigger the CREATE SQL query. Then, follow this procedure:
- CREATE
- INSERT
- SELECT
- INSERT
- SELECT
- DELETE
- DROP
As you can see, the database schema was created, data was inserted, you can select the data from the dhtreadings table, delete it and drop the table.
This is a very basic example that can easily be extended for your own projects.
Wrapping up
This post is a quick guide to send simple SQL queries to an SQLite database with Node-RED. You can apply this concept to create a complex database that actually stores useful readings.
Like home automation? Learn more about Node-RED, Raspberry Pi, ESP8266 and Arduino with my course: Build a Home Automation System for $100.
Do you have any questions? Leave a comment down below!
Thanks for reading. If you like this post probably you might like my next ones, so please support me by subscribing my blog.
Hi Dear Rui,
Thanks For powerful articles, Good job…
Nice job, I’ve been trying to figure this out for a few months now.
He Rui,
Thanks for the nice examples about MQTT, SQLite and Node-RED.
I was a bit confused about the lack of a t in currenttime but am able to store some values in a db.
I only face one or two problems with Node-RED that I can’t solve.
First I push the insert inject node and get:
21-9-2018 11:54:39node: 6c67fca9.7eeec4INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(19.6, 53.8, date(‘now’), time(‘now’), “manual”) : msg.payload : array[0]
[ empty ]
If I push select (the inject node) the debug window shows:
21-9-2018 11:55:08node: 6c67fca9.7eeec4SELECT * FROM dhtreadings : msg.payload : array[61]
[ object, object, object, object, object, object, object, object, object, object … ]
But if I look with DB Browser for SQLite (on a windows 7 machine) I can see that data is added.
“59” “59” “19.6” “53.8” “2018-09-21” “09:49:29” “manual” (with 2 hours difference!)
Probably a very simple issue.. but where to look. Maybe you have an idea?
Also: I have a sensor that send different MQTT topics:
home/BME01/temperature
home/BME01/humidity
home/BME01/pressure
I can use 3 different MQTT out nodes to see this working in the debug window.
But how do I make it so that I can put these different values in the table? (after add a pressure record to the table of-course)
Any pointers on how to do this?
Thanks,
Ray.
Hi Rui,
Your tutorials are always awesome. I followed this one and it works perfectly to insert data from my NodeMCU modules but how can see the data from the command line? I installed sqlite3 but when I start it with sqlite3 I don’t see the node-red data. Thanks!
press insert :
INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(22.4, 48, date(‘now’), time(‘now’), “manual”)
8/2/2020, 7:00:05 PMnode: 331e0e9f.21ff5a
INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(22.4, 48, date(‘now’), time(‘now’), “manual”) : msg.payload : array[0]
[ empty ]
8/2/2020, 7:11:05 PMnode: 331e0e9f.21ff5a
SELECT * FROM dhtreadings : msg.payload : array[8]
[ object, object, object, object, object, object, object, object ]
click on any of the objects in the debug window.
the expand
1: object
id: 2
temperature: 22
humidity: 48
2: object
id: 3
temperature: 22
humidity: 48
3: object
need a follow-up to show how to put the DHT data into the file
and a second follow-up to show how to display the sqlite file as a graph/chart