Read and Store Sensor Data into Database with Node Red

 Here it is illustrated how to store sensor from Arduino serial port into MySQL database using Node Red. The sensor data is the humidity and temperature data from DHT11 module.This sensor data read and sent by Arduino to PC via serial port. On the PC, XAMPP is used to create new MySQL database. Then using node red sensor sensor data is read from the serial port with serial in node. Using a function node, SQL query is performed that inserts data into the MySQL database.

Node red is open source platform for building Internet of Things application. Often we need to store and record sensor data in database. These recorded sensor data can then be retrieved and displayed on the web. So this tutorial shows how one can achieve that.

Arduino DHT11

 The Arduino nano is used here to read DHT11 humidity and temperature data. The interfacing of Arduino and DHT11 is shown below.

The actual implementation of the above diagram is shown below.

The Arduino code to read and send the humidity and temperature data serially over the USB is below.

// Read DHT11 sensor and send serially to PC

#include <DHT.h>         // Include Adafruit DHT11 Sensors Library

#define DHTPIN 7          // DHT11 Output Pin connection
#define DHTTYPE DHT11     // DHT Type is DHT11

DHT dht(DHTPIN, DHTTYPE);   // Initialize DHT sensor

void setup () {
  dht.begin();
  Serial.begin(9600);         // To see data on serial monitor
}

void loop (){

    float H = dht.readHumidity();     //Read Humidity
    float T = dht.readTemperature();    // Read temperature as Celsius

    // Check if any reads failed and if exit
    if (isnan(H) || isnan(T)){
      Serial.println("Failed to read from DHT sensor!");
      return;
    }
 
    // Combine Humidity and Temperature into single string
    String dhtData = String(H) + "," + String(T);
    Serial.println(dhtData);
    delay(10000);   // Wait ten seconds between measurements
}

If you need in details how the Arduino and DHT11 sensor works see the tutorial DHT11 Humidity and Temperature Sensor with Arduino.

One can check on the serial monitor to see the humidity and temperature sent by Arduino.

 MySQL Database

To save the serial data into database with node red we will use XAMPP. If you don't know XAMPP and how to install it see the tutorial how to host website from home. Once installed open the XAMPP control panel and click on Admin to open the MySQL database user interface.

In the MySQL database create a new database with name DHT11.

Then create a new table called DHT11Table for the database called DHT11 with number of columns of 4.

Create the following fields in the table.

The database will be created and is shown below.

Node Red

In node red we need the serial in node, function node and the mysql node. The serial in node and the mysql node are not installed in new installation so you have to install it. In the previous tutorial   .

If you don't have serial in node see the tutorial Node Red with Arduino Simple Example to learn how to install it. Double click the serial in node to open its configuration dialog window. There select the com port to which the Arduino serial port is connected such as COM17 in this example. Set the baud rate to 9600.

 Next double click the function node to open its configuration dialog window. There type in some name such as SQL Query used here for the function node. In this function node we will write the SQL query code that inserts our sensor data into the MySQL database DHT11 created earlier. The following is the code that goes into the function node.

m = msg.payload.split(',');
temperature =m[0]
humidity = m[1]
msg.topic = "INSERT INTO DHT11Table (Humidity,Temperature) VALUES ("+temperature+","+humidity+");"
return msg;

Next we have to configure the database on the mysql node. If you don't have already installed the mysql node then see the tutorial Node-RED MYSQL How to? which explains how to install the node-red-node-mysql library and also explains how to perform basic insert and retrieve data from the database in node-red. In this example, double click on the mysql node to bring up its configuration window. Here we have to provide the database name which is DHT11 in this example, the user name and password for the database which you should know. Then we can optionally name the mysql node. Here we have named the mysql node DHT11Database.

 The following shows the completed node red flow diagram to retrieve data from the serial port and store the humidity and temperature data into the MySQL database. 

Once the flow is ready click the Deploy button and then the Start button. 

After this the flow will be executed and node red will the serial data coming from the serial port and will insert them into the database. Now open the MySQL database, open the DHT11 database then the DHT11Database table. You should see the recorded data in the database as illustrated below.

So in this way we can read sensor data from the serial port and store them into a database using Red-Node as IoT framework. We can also display the sensor data on the web which is explained in details in the Node Red IoT with Arduino DHT11.