Random Nerd Tutorials
Shares

SQLite with Node-RED and Raspberry Pi

Shares

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.

Recommended Resources

Home Automation using ESP8266 »
Build IoT projects and home automation gadgets with the ESP8266 Wi-Fi module.

Build a Home Automation System »
Learn how to build a automation system using open-source hardware and software from scratch.

Arduino Step-by-Step Projects »
Build 25 cool Arduino projects with our couse even with no prior experience!

Leave a Comment:

Add Your Reply

⏰ Early Black Friday 2017 Special ⏰
Download all our courses up to 35% off