Node-Red – Storing IOT Data in a SQL Database

IOT data from sensors and other devices usually comes as either a text string usually with key value pairs or more commonly as JSON encoded data.

Databases accept a variety of data formats the most common are INT and TEXT.

Incoming text data can be stored directly in the database.

JSON encoded data is also a text string and so can also be stored directly in the database, but more commonly you will want to extract parts of the data to use as database keys (column names).

The easiest way to see this is by doing an example. Let’s assume our incoming data payload looked like this:

sensor1,temperature,20,humidity,71

So what we have is a device called sensor1 and two key/value pairs:

temperature=20 and humidity=71

To store this data in a data base we first need a database and a table.

So lets create a database called test.db and a table called sensor_data.

Now a table consists of rows and columns. To create a row we need to add elements to the columns. To do that we need to assign names to the columns.

In our example we would need three columns called:

• sensor
• temperature
• humidity

We also need to assign a data type to the values, in our case sensor would be TEXT and temperature and humidity would be Integers (INT)

When we add our data to the table the table would look like this:

Sensor	Temperature	Humidity
sensor1	20	71

Note: Column names are not case sensitive.

Adding Data To a Table

To add data to a table we need to use SQL commands. The command to do this has the form:

Insert Into Tablename (columns)(values)

where columns are out column names and values are the values we are assigning to these columns.

In our simple example we have:

Insert Into sensor_data(Sensor,Temperature,Humidity)("sensor1",20,71);

In our node-red flow we will usually need to construct this command in JavaScript using a function node.

The process is:

• Extract data elements from incoming data
• Create SQL command
• Pass SQL command to the database connector

The following code snippet shows how we do this:

var data="sensor1,temperature,20,humidity,71";
//extract elements from data
var sensor=data[0];
var temperature=data[3];
var humidity=data[5];

//create SQL command
msg="(sensor,temperature,humidity) values("+",\'"+sensor +"\'," + temperature + "," + humidity +")";
var topic="INSERT INTO DATA " +msg;
var msg1={}; //create new msg object
//The SQlite node expects the command in the topic field
msg1.topic=topic;
return msg1;

The interesting and most important point when constructing the command is that text values need to be in quotes, and because we are constructing a string we need to escape them. So we have:

SQL Database Nodes

There are node-red nodes available for a variety of databases in the video I use the SQlite node and a SQLite database.

SQLite is a simple lightweight database and ideal for using on devices like the Raspberry PI.

In contrast to other SQL databases it isn’t run as a service and so is much easier to setup and use.

However the code shown here and used in the videos should work with any SQL database.

Note: When installing the SQLite node on raspberry Pi it appears to have installed but hasn’t.

This is because it usually needs to do a compile which takes time so you need to start the install and wait 5-10 minutes for it to install.

If you look at the console you should see the confirmation when the node has finished installing.

There are two things that we need to configure in the SQLite node

• The database name:
• The SQL Query

SQL Query

You have several choices here the default is via msg topic

In most of my earlier flows I used the msg topic but have since moved to prepared statement as it is supposed to be more secure but I found it much easier to use.

All of the videos on this page use the msg.topic

Note: It isn’t necessary to create a database in advance as if you create a table using the node then it automatically creates a database if it doesn’t already exist.

SQLite Node Input

The database command is passed into the node using the topic property, the msg.payload isn’t important.

The node will output an empty array in the payload on success.

However if it is unsuccessful there is no output from the SQlite node.

The error message shown below is captured using a catch node elsewhere on the flow.

SQL Commands Notes:

Function nodes are necessary for inserting data as the data is dynamic and the command has to be built with each insert.

However creating and dropping tables and other tasks can be done using inject nodes with the command hard coded.

 

Video-Node-Red Storing IOT data In a SQL Database

Part 2 Storing IOT data In a SQL Database- JSON Data

Part 3 Reading Data from a SQL Database

Update- January 2023

Previously I have always used the msg.topic method of doing a database query but have discovered another method called the prepared statement which I was told about but never got around to testing until now. So here are my notes on using it and would recommend you consider switching to it. See Node-Red SQlite- Using a Prepared Statement

Resources :

flow-video1,2 and 3

Note: if you have any problem importing the flows please let me know.

• SQLite Website
• SQLite Tutorial

Common Questions and Answers

Q- Can I store time series data in SQLite?

A- Yes. Video 3 illustrates reading data from a SQlite database by time.

Q- Should I use Influxdb for my Time series data and not SQLite?

A- It all depends on your application and expertise. Influxdb is far more complex than SQLite and requires more resources.

Q- Can I graph Data from a SQLite database?

A- Yes you just pass the result of the query to a graph node.

Related Tutorials:

• Working with JSON Data And JavaScript Objects in Node-Red
• Storing and Retrieving JSON Data In SQLite
• Using the Node Red Status Node
• Node-Red MQTT Data and Topic Logger
• Understanding and Using The Node-Red Message Object
• Using Node-Red with Influxdb