SQLite with Node-RED and Raspberry Pi

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

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 npm@2.x
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:

1. CREATE
2. INSERT
3. SELECT
4. INSERT
5. SELECT
6. DELETE
7. 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.

Thanks for reading.