Node-RED SQL Database Log – Modbus Logging

Node-RED can easily log data to a database. SQL (Structured Query Language) can be used to communicate to a SQL database. Databases separate analysis from the data unlike spreadsheets like Excel. The database is designed to hold more information than spreadsheets and allow multiple users to access the information.
Node-RED SQL Database Log - Modbus Logging
We will be creating an SQLite SQL database. SQLite Studio will be used to design the structure of the database. (Tables) Installing the node-red-sqlite-node palette will allow us to log the information from our Solo process temperature controller, Click PLC, and dashboard variables. Let’s get started.

In this series we started using Node-RED in some of the following ways:
Installing the Windows SoftwareVideo
Modbus CommunicationVideo
User Interface – DashboardsVideo
We will continue with the following:
Log information into a database
View information in the database through spreadsheets such as Excel.

Previously we installed Node-RED Modbus Palette on our windows 10 computer and communicated Modbus RTU (serial) and Modbus TCP (Ethernet) to a Solo Process Temperature Controller and a Click PLC. We then implemented the dashboard user interface on any web browser. (Windows / Android Phone) I would recommend that you review the following Node-RED Essentials Videos (Basics of the Editor) before continuing. This will allow you to become familiar with the editor.
Watch the video below to see the logging of data into a SQL database using Node-RED.

Download/Install SQLite

The first thing that we need to do is download and install SQLite on our Windows 10 computer. The files can be obtained on the SQLite website.
Select the download page on SQLite.
Node-RED SQL Database Log - Modbus Logging
Scroll down until you see the files for windows.
Node-RED SQL Database Log - Modbus Logging
Select to download the SQLite tools windows 32. This downloaded file is a zip file containing the program. It does not need to be installed.
Just create a folder like D:\sqlite and place the unzipped files into the directory.
Node-RED SQL Database Log - Modbus Logging
Here are the three files that you will see in the directory. Your installation of the SQLite is complete.
The following is a tutorial on SQLite that will get you started using the command prompt to set your database. We will be using SQLite Studio to get us going faster in creating our database. This is especially true if you are not familiar with using the command prompt.

Download/Install SQLite Studio

SQLite Studio can be used instead of SQLite directly. I prefer to use a GUI (graphic user interface) instead of the command prompts. SQLite Studio can be downloaded from the sqlitestudio.pl website. This will download the sqlitestudio-3.3.2.zip file to your computer. Just like SQLite, this program does not have to be installed. You just unzip the file and run the program.
Node-RED SQL Database Log - Modbus Logging
I have placed my unzipped file under D:\sqlite\.
Open the SQLiteStudio folder.
Node-RED SQL Database Log - Modbus Logging
Start the studio by double-clicking on the SQLiteStudio.exe file.
Node-RED SQL Database Log - Modbus Logging
W3Schools.com has a good tutorial on SQL.

SQLite Studio Create Database

When SQLite Studio starts for the first time you will have a blank SQLiteStudio (3.3.2) window. The first thing we need to do is create a database.
creating sqlite database studio
Select Database | Add a database from the main menu. You can also use Ctrl+O or right-click under database and select Database |Add a database.
creating sqlite database studio
The database type defaults to SQLite 3 in the database window. Select the plus icon in the file to create a new SQL database.
creating sqlite database studio
We will call our database ACCautomation. The extension can be any of the ones listed under the “Save as type”. We will use the “db” extension for our database. This name will contain all of the tables and information that we will collect and share. SQLite utilizes this single file name and can have a size greater than 140 terabytes. In other words, we can store a lot of information. Select save.
creating sqlite database studio
The database window will now show our newly created file. Select the test connection button and you should see the checkmark appear. This indicates that the database has been saved correctly. Select OK.
creating sqlite database studio
We now have our ACCautomation database appearing under our Databases title. Double click on this database.
creating sqlite database studio
You will now notice that we have connected to the database and it is showing tables and views under the name. We will now create a table in our database to save our Solo values.
creating sqlite database studio
Select structure | create a table from the main menu. You can also right-click on the database and select create a table.
creating sqlite database studio
The table structure will now appear on the right-hand side of the screen. We will use Solo for the table name.
creating sqlite database studio
Double click on the table to add columns to the table.
creating sqlite database studio
Our first column name will be ‘ID’. The data type will be set for Integer. This will be a primary key for the table.
creating sqlite database studio
Select Configure to the right-hand side of the primary key.
creating sqlite database studio
Select Auto increment for the primary key. This will assign an integer value to every row in our database table. This is one way to ensure the integrity of your data table. Select Apply and then OK on the column window.
creating sqlite database studio
Add a date column with the data type of DATETIME. PV and SV columns which will be data type double. This allows decimal numbers. A switch column will also be added with the data type Boolean.
creating sqlite database studio
Here are the completed columns. Select the green checkmark (Commit Structure Changes) to execute our SQL commands to add this table.
creating sqlite database studio
The SQL query will now be displayed showing the creation of the table. Select OK.
creating sqlite database studio
We can now see the Solo table under the tables heading for our SQL database. The status window will appear indicating that the changes were successful. Our database is now ready to accept data from Node-RED.

Node-RED SQLite Install
Installing node-red-node-sqlite Palette

We will be using our Flow that we created last time. This will retrieve data from the Solo and Click controllers and display it on the dashboard. The SV (set value) can either come from the dashboards or Click controller.
Node-RED SQL Database Log - Modbus Logging
On the main menu beside the deploy button, select the three lines (pancake icon). Select the Manage palette option.
Node-RED SQL Database Log - Modbus Logging
Select the install tab and search for ‘sqlite’. The palette node-red-node-sqlite will be displayed. Select the install if it is not already installed. Select close.
Node-RED SQL Database Log - Modbus Logging
The SQLite node will now appear in the group of storage nodes.

Node-RED SQL Database Log Program

We will be adding the following under our existing Node-RED program.
Node-RED SQL Database Log - Modbus Logging
The inject will allow us to specify a time frequency for your values to be stored. The function node will be used to set up the SQL statement to insert data into our table within the SQL database. SQLite node will then execute the SQL commands.
Inject Node
Node-RED SQL Database Log - Modbus Logging
This will contain a message payload with a JSON expression.
$now(‘[Y0001].[M01].[D01] [H01]:[m01]:[s01]’,’-0400′)
This expression will format the date and time to YYYY.MM.DD HH:MM:SS in the correct time zone. The inject node will have a repeat interval every 1 second.
Function Node
Node-RED SQL Database Log - Modbus Logging
The function node will set up the following SQL command.
“topic”:”INSERT INTO Solo(Date,PV,SV,Switch)values(“+”\'”+msg.payload+”\'”+”, “+ parseFloat(flow.get(“PV”)/10) +”, “+ parseFloat(flow.get(“SV”)/10) +”,\'”+ flow.get(“Switch”) +”\’)”
The message topic is used to hold the SQL command.
SQLite Node
Node-RED SQL Database Log - Modbus Logging
This node will execute our SQL command. The database path must point to the SQL database ACCautomation.db that we created above. The SQL Query will come in with the message topic.

Node-RED Database Program Run

We can now deploy the new program.
Node-RED SQL Database Log - Modbus Logging
Calling up the dashboard we can see the variables being retrieved.
Call up our SQLite Studio once again.
node-red sqlite modbus
Select the open SQL editor using the icon or Alt + E.
node-red sqlite modbus
We can now write SQL query commands to display the data that we are collecting.
node-red sqlite modbus
Type in the following query “Select * FROM Solo ORDER BY ID desc Limit 5;”
Select the play icon. (Arrowhead to the right)
node-red sqlite modbus
This SQL query will select all of the columns from the table Solo and order them by ID. This will be sorted in descending order and only 5 records will be returned.
node-red sqlite modbus
If we go back to the solo table itself we can quickly view all of the data in the table. Select the data tab and click on the refresh structure to display all of the records. F5 can also be used to refresh the structure. Clicking on the column heading will allow you to display the records ascending or descending in the table.
Node-RED SQL Database Log - Modbus Logging

Watch the video below to see the Node-RED SQLite SQL database log the Solo and Click controller data.

Download the Click Program, Flow, and database here.

Node-RED Links
Node-RED Organization Home Page
Getting Started – Run Locally
Node-RED running on Windows (Run at Startup)
Securing Node-RED
Node-RED Essentials Videos (Basics of the Editor)

Learn JavaScript Free
w3schools JavaScript Tutorial
learn-js.org
Node-Red JavaScript Primer

Modbus
Node-RED Modbus TCP and Serial

Dashboard – HMI
Node-RED Dashboard
Node-RED Dashboard extra nodes

SQL Database
Node-RED SQL Database
Node-RED SQL Plus – Execute queries and stored procedures

Modbus Learning Links:
Simply Modbus Frequently Asked Questions
Modbus TCP/IP Overview – Real-Time Automation
All You Need to Know About Modbus RTU – Video

Next time we will view the information in the SQL database through spreadsheets such as Excel.

Watch on YouTube: Node-RED SQL Database Log


If you have any questions or need further information please contact me.
Thank you,
Garry

If you’re like most of my readers, you’re committed to learning about technology. Numbering systems used in PLCs are not difficult to learn and understand. We will walk through the numbering systems used in PLCs. This includes Bits, Decimal, Hexadecimal, ASCII, and Floating Point. To get this free article, subscribe to my free email newsletter.

Use the information to inform other people how numbering systems work. Sign up now. Arduino Uno Super Starter Kit Buzzers The ‘Robust Data Logging for Free’ eBook is also available as a free download. The link is included when you subscribe to ACC Automation.

9 thoughts on “Node-RED SQL Database Log – Modbus Logging”

  1. Hi my name is Daniel and I’m with Bremenour Electric.
    I work with the Productivity series (1000 and 3000). I wanted to ask does the SQLite work with the productivity series? The company has been switching all of are new stuff over to Automation Direct Products. And I’ve been looking for a good Scada/Database to use with the productivity series.

    I’ve also heard of Ignition and their Scada/Database was wondering if you have any comments on it?

    I would Appreciate any help.

    Thank you,
    Daniel

    Reply
    • Hi Daniel,
      Yes, the Productivity Series supports Modbus TCP and RTU. This would then be possible for Node-RED to communicate and log to the SQLite database. Node-RED and SQLite are free applications.
      Ignition is a packaged software package that you must purchase.
      I prefer to create my own with the powerful tools that are free.
      Regards,
      Garry

      Reply
  2. Hello,

    I try to run Yours exampe, but I have a problem.

    Function node sending error on string:
    “topic”:”INSERT INTO Solo(Date,PV,SV,Switch)values(“+”\’”+msg.payload+”\’”+”, “+ parseFloat(flow.get(“PV”)/10) +”, “+ parseFloat(flow.get(“SV”)/10) +”,\’”+ flow.get(“Switch”) +”\’)”

    Could You help me?

    Reply
  3. I have followed the steps pretty religiously but I am not sure what here connects my solo to the sql database? I ask because when I click refresh on the SOL Studio, I get no data.

    Reply
  4. Here is my updated code:
    msg = {
    “topic”: “INSERT INTO TestTable (PV, SV) values (” + “\'” + parseFloat(flow.get(“PV”) / 10) + “\'” + parseFloat(flow.get(“SV”) / 10) + “\)”
    }
    return msg;

    However, I am now getting the following error:
    “Error: SQLITE_ERROR: near “20.3”: syntax error”, where 20.3 is the value for my SV

    Reply
    • Hi Keshav,
      Thank you for including the code with the error. The “\” “\” indicates a text variable. You also need to ensure that you have a “,” between the values you are storing.
      msg = {
      “topic”: “INSERT INTO TestTable (PV, SV) values (” + parseFloat(flow.get(“PV”) / 10) + “,” + parseFloat(flow.get(“SV”) / 10) + “)”
      }
      return msg;

      I hope this helps you out.
      Regards,
      Garry

      Reply

Leave a Comment