In my previous post, We discussed the basics of Node red basic tutorials and installation. This post is about retrieved data from MySQL database in node red IOT applications. Usually, IOT application will interact with the database for storing and reading the data for dashboard related analytics. In this
As you know node red is a flow-based programming language. In this everything is based on nodes. To connect to the MYSQL database, First, install MySQL data node. this node is not available by default. Once you installed node red tool and started node server. Go to the dashboard page and URL is localhost:1880 There are two ways to install MySQL node.
Using Manage palette option - dashboard
Click Top Right button, Click on Manage Palette option. You will show UI like below screen, Install this node.
Using Npm Package
node-red-node-mysql library provides support for node red nodes for interaction with MySQL. This has npm library
Install using npm command
npm install node-red-node-mysql
Once MySQL node is installed successfully, restart node-red server via command line. On accessing admin dashboard, you are able to see MySQL node on left side navigation menu under the storage section as like below.
Here we will see the steps read from mysql
1. Drag new MySQL node into flow editor like below screenshot. Configure the below things. Local host, port, Username and password of a MySQL Database
2. Drag function node. Configure function node to write sql query
3. Drag inject input node and Debug output node into flow editor and connect nodes like the below
Configure inject node as a timer which is starting node initiates a request for every 1 seconds of interval automatically.
Debug output node is like a console log which prints the results to the console
Connect different nodes as shown in below screenshot.
Once you are done, You are ready to deploy changes. On clicking the Deploy button on the top right side will deploy changes. you can see the result records in debug tab as per below screenshot
First, initiate timer request to function node which sends SQL query to MySQL node, this node outputs a list of records in the form of an array, that goes as payload into debut node which prints the data to debug tab.
You can replace with debug node with your template where you need to define the data format like table