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:

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 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.

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.


Learn how to program and build projects with the ESP32 and ESP8266 using MicroPython firmware DOWNLOAD »

Learn how to program and build projects with the ESP32 and ESP8266 using MicroPython firmware DOWNLOAD »


Enjoyed this project? Stay updated by subscribing our weekly newsletter!

4 thoughts on “SQLite with Node-RED and Raspberry Pi”

  1. 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.

    Reply
  2. 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!

    Reply

Leave a Reply to Russ Cancel reply

Download our Free eBooks and Resources

Get instant access to our FREE eBooks, Resources, and Exclusive Electronics Projects by entering your email address below.