Friday, July 27, 2018

How to read data from MySQL in Node Red example

nodered mysql tutorials.

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.

mysql node install node red dashboard

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.

mysql node storage
Add caption

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

mysql configuraiton node red

2.  Drag function node. Configure function node to write sql query
Function node - 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

Connect node mysql node-red

Example summary

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