Dhairya continues his article series on how to build your own IoT platform. Here, in Part 3, he adds some much-needed functionality to your platform, including REST API support and the ability to send emails and SMS texts.
Last month, in Part 2 (Circuit Cellar 372, July 2021) [1], we built our very first project using the IoT platform in our backend and creating a simple NodeMCU-based project to view sensor readings, while at the same time controlling relays from our Node-RED dashboard.
That was a fairly simple and straightforward project. To create complex projects and connections with our IoT platform, we need a lot of additional features. In this article, we will add some much-needed functionality to our platform—features that are basic requirements for any IoT platform. In this article, we will add the following features to our platform:
1) An SQL database through phpMyAdmin
2) Support for REST API development and its applications
3) Email capability for alerts and notifications
4) SMS capability for alerts and notifications
Beyond those, there are some very basic features that are available in most commercial IoT platforms—features like Thingspeak, Ubidots and so forth. We will create much simpler and unsecured versions of those features. These will include the following capabilities that make for a more finished and professional project:
1) Ability to store sensor values in an SQL database
2) Ability to fetch past data using a simple GET or POST request (HTTP API)
3) Send SMS and email alerts
Please keep in mind that whatever we create here is just for personal use and should not be used on a professional level because its security level is very limited. This article’s aim is to inform you on how these IoT platforms actually work in backend. So, let’s get started.
— ADVERTISMENT—
—Advertise Here—
DATABASE SUPPORT
Database support is a very important component of any IoT platform because it enables you to fetch past data for analysis and other applications, and at the same time log the traffic of the platform. For our platform, we’ll be using SQL for database storage. Just as we did when setting up our platform, we will use phpMyAdmin as the UI for our SQL database. We will go through the steps to be performed once you have installed phpMyAdmin and SQL and set yourself up as a user in phpMyAdmin with all privileges. Our database schema (for storing past data) will look something like what’s shown in Table 1.
The database we’ll use for storing past data will have a schema that looks something like this.
Here, the Topic and Payload elements will be the most important and we will use the ID as a primary Key to select and manipulate with the data. The Timestamp is just an additional element that provides info about the time when the data was logged into the database. Timestamp is useful when we want to fetch the data with respect to time—between two given dates, for instance. Now that we’ve discussed the importance of a database, we can move on to creating the actual database on our platform.
First, open the phpMyAdmin interface for your platform. Access the MySQL database with phpMyAdmin by navigating to http://<INSTANCE_IP>/phpmyadmin. That will open a login window. Type in the credentials created during the setup process from the Part 1 article, and login to phpMyAdmin. Now, we’ll create a new database for our project. Just follow Figure 1 to do this. You can give the database any name you like but, for this article, I gave it the name “Article3DB.”
This shows you how to create a new SQL database using the phpMyAdmin console. Follow the steps shown in the diagram.
This article assumes that you’re signed in as a user and have root privileges that let you access and modify every database. The next step is to create a new table in this database. The table stores the data traffic that flows through our platform. In this case, we will be storing the temperature and humidity data we obtained from our NodeMCU-based device.
With that in mind, we’ll name our table “SensorData.” Follow the steps shown in Figure 2 to create the table and define the table schema for this project. After this, we’re ready for the phpMyAdmin part. Our next task is to implement some additions and tweaks to our Node-RED to add this functionality.
This image describes how to create a new table in your database and then what schema to define for that table (for the purpose of this article).
NODE-RED SETUP
With the required database and data structure ready in MySQL, now it’s time to enable Node-RED with the required additional nodes, and then configure it to couple with the database. The default installation of Node-RED doesn’t have a node to access MySQL. Therefore, we will add this node using Node-RED’s palette manager. Open the Node-RED interface in your browser, and then select the Manage Palette option from the menu in the top right. This opens a new window, and you can type “sql” to list the available nodes. Select and install node-red-node-mysql by following the subsequent prompts. This will add the mysql node in the storage section of the available nodes.
Having this node available means that now we can access our MySQL database from Node-RED. At this stage, the database and data table are ready to accept and store input values. The Node-RED functional instance is ready for configuration and programming, and our project is already up and running.
We will create a new sub flow that will enable us to store the temperature and humidity values to our SensorData table. This flow will have three main components: a MQTT listener, which will keep track of topics of interest (the temperature and humidity data topics in our case), a Function node to assign the received values to variables and create a SQL query to add the received data into our database, and finally a mysql node that enables us to access our database—it has all the necessary credentials to do so.
Note that the mysql node requires some additional steps like configuration of your SQL database. Refer to (Figure 3) for a detailed explanation of each node’s configuration. The JSON file for the sub flow can be found in the GitHub repository [2] created for this article. The link is provided on in RESOURCES at the end of this article. The code for the Function node is shown in Listing 1.
— ADVERTISMENT—
—Advertise Here—
Listing 1
Code for the Function node
// Store the received topic and data in variables
var topic = msg.topic;
var payload = msg.payload;
// Obtain the current Timestamp
var time = Math.floor(Date.now() / 1000);
// Creating the SQL query
var query = “INSERT INTO SensorData (Topic,Payload,Timestamp) VALUES (‘” + escape(topic) + “’,’” + escape(payload.trim()) + “’,’” + escape(time) + “’)”;
msg.topic = query;
return msg;This completes the Node-RED setup. Deploy this flow by pressing the Deploy button on the top right and now database functionality has been successfully added in your project. To test if everything is working as expected, just turn on your NodeMCU device for 30 seconds and then turn it off. Now, open the SensorData table and you can see all the received values stored in the table as shown in Figure 4.
Database functionality flow is explained here. This shows you how the flow components should be set up step by step.
Once the database storage function has been added, this shows how the data will be stored in our table. Each row includes the MQTT topic, payload value and the timestamp at which that value arrived.
REST API FOR DATA FETCHING
Because we’ve added database functionality to our platform, we now have access to past data. But, in order to use it in other applications or systems, we need some method to fetch this data when required. This is where REST APIs come into play. We can fetch the data using simple HTTP requests. In order to do so, we need to create a REST API for this task. In this article, we’ll be creating a simple API. It lets you fetch the N latest temperature or humidity values. For this article, we will be using the POST method for all the APIs, and we will be testing the working of the APIs using a software tool called Postman.
To create any REST API in Node-RED, we need the following blocks: A HTTP In node, a Function node (one or more) and a HTTP Response node. Moreover, because we need to fetch the data from an SQL database, we also need the same SQL node we used to add database functionality. The HTTP In node specifies the address we need to send a request on in order to call the API we created. In this case we’ll give it the name /getsensordata. The Function node helps us get the parameters values of the API assigned to variables and also to create an SQL query to fetch the desired values from the database. Next, we process the query using an SQL block and whatever results come in are forwarded to the HTTP Response node. That’s the node through which the user or program that has called the API finally gets the results—the results being the N latest temperature or humidity values in our case.
For a better understanding, refer to Figure 5, which graphically explains how the API flow is created and how it operates. We will create two such APIs, and all the code and flow export JSON files are available in the GitHub repository [2] for this article. The Function node code for this API is shown in Listing 2.
REST API Flow explained! This image shows you how to create a simple REST API for our platform that will let us fetch past sensor values from our database table.
Listing 2
The function node code for our REST API
// Fetch all the arguments of the api
var which_data = msg.payload.type;
var N = msg.payload.N;
var query = “”;
// Logic to select what type of sensor data do we want to fetch.
if(which_data == ‘temperature’)
{
query = “SELECT Payload FROM SensorData WHERE Topic = ‘monitor/temperature’ ORDER BY Timestamp DESC LIMIT “ + escape(N) + “”;
}
else if(which_data == ‘humidity’)
{
query = “SELECT Payload FROM SensorData WHERE Topic = ‘monitor/humidity’ ORDER BY Timestamp DESC LIMIT “ + escape(N) + “”;
}
// msg.topic variable set to the SQL query.
msg.topic = query;
return msg;Our next step is to test the working of our API using the popular Postman software. For this article, the purpose of this tool is for testing our REST APIs. Download the Postman software from www.postman.com (there is a free version). Once you’ve downloaded the software for your specific operating system, run the installer if required (or unzip all the necessary files) and then open the application.
A new application window will appear. Next, we need to create a New Request. For this, press the New button located on the top left side of the window that will open a new dialog box of options. Select the first one saying Request, give it a name and press Save. It will add a new “Request” tab. We will create a simple POST API that takes the following arguments:
Type—The type of sensor data (possible values in our case: temperature or humidity)
N—How many latest values do we want.
We provide these arguments as a JSON Body and not as general parameters. For this, you need to select Body instead of Params and select RAW JSON file format. You can now type in the REST API address, the type of API call and any parameters we need to pass. Refer to Figure 6, which shows all these things along with the expected response. Note: You will only get a response if you have some values stored in your database table using the flow defined earlier. If these steps are still difficult to understand, check out the tutorial video at [3] that explains how to test your API using Postman.
This image shows how to test our REST API using Postman and what kind of response we receive.
We now have the capability to create HTTP REST APIs for different tasks on our IoT platform. This can be scaled to something big—for example, an API that creates a new table in our database for a specific task. Next, we will add email and SMS alert capabilities to our platform.
EMAIL FUNCTIONALITY
To add the email feature, we will be using Node-RED’s email package. For this article, we’ll generate some data using the Inject node. We will be creating an HTTP request in which we will take the email address and the message as queries and complete the task accordingly. Now let’s get to the steps.
First, open the Node-RED server on port 1880 of our server. After that, we need to install a node package for adding the email feature. Go to the Manage Palette from the options menu by clicking on the top right icon from the homepage. Search for the email package just be typing “email” in the search box and install the package that is first on the list. Now you can see the email blocks in the social section on the left side where all the blocks are available. Now, we’re ready to make our flow, which will help us test the email facility of our server. For that, create a simple flow that monitors all the temperature values and, when it goes above a specified threshold value, an email alert is sent.
We will see how that flow is created exactly by going through each component of this flow. First, have a look at the MQTT in node. This node will collect and pass each temperature value through this flow. Next, we see the Logic function node. The main aim of this function is to set this threshold value and define the subject and content of the mail alert. In this case, we have set this value to 20 and the other things can be seen in the function code shown in Listing 3. The function node will create a message and pass it to the email node for sending it to the specified email address. Note that this service uses an SMTP server by default, so if you are using Gmail, you need a less secure account (two step verification turned off) or you need an application password that can be generated in your Gmail account settings. A blog is available at [4] that shows you a smooth and easy way to do that password setup process. The code for the Logic function node is shown in Listing 3.
Listing 3
Code for the Logic function node
// Condition for Temperature value (greater than 20 degrees for this case)
if(msg.payload > 20)
{
// Define the mail subject and content
// 1. Subject
msg.topic = “High Temperature Alert!”
// 2. Content
msg.payload = “The temperature of the room is above 20 degrees.”
}
return msg;
Now, the only setup remaining is the email out node. When you open the node for the first time, you will see that it uses Gmail by default. The setup for this node will require the following things:
1) The email address you want to send the alert to
2) The email address through which the alert will be sent
3) The app password for the email address from 2 above
— ADVERTISMENT—
—Advertise Here—
In the node, we just need to add the password in the password field because the emails have already been set up in the Function node. The detailed set up process of this flow is shown in the Figure 7. Now click Deploy and you can test this new functionality by just turning on your NodeMCU device. When a temperature value greater than 20°C is recorded, you will see the mail alert as shown in Figure 8 on the email address you specified in the Email node. Next, we will add a similar alert feature—this time sending SMS text messages to specified numbers instead of email addresses.
This Email Alert Flow sends an email alert to a pre-defined email address when the temperature goes above 20°C.
This Email Alert Flow sends an email alert to a pre-defined email address when the temperature goes above 20°C.
SMS TEXT FEATURE
To add the SMS feature, we’ll be using the Twilio package from Node-RED. For this article, we will generate some data using the Inject node. Similar to the email feature discussed earlier, we will be creating an HTTP request in which we will take the number and the message as queries and complete the task accordingly.
Our first step is to open the Node-RED server on the port 1880 of our server. After that, we need to install a node package for adding the SMS feature. Go to the Manage Palette from the options. Search for the SMS package just be typing “twilio” in the search box and install the package that’s first on the list.
The setup of the flow will be very similar to the email functionality flow. The temperature data will come in through the MQTT in node as before and there will be a little change in the function node. The code for the SMS function node is shown in Listing 4.
Listing 4
Code for the SMS function node
// Condition for Temperature value (greater than 20 degrees for this case)
if(msg.payload > 20)
{
// Define the SMS Content
msg.payload = “The temperature of the room is above 20 degrees.”
}
return msg;Now, the only setup remaining is the SMS out node. To complete its setup, we first need to open the node. After that, we need to provide the Twilio API credentials. For that, go to the Twilio website (www.twilio.com) and create a free account. You will be able to access the trial version by default. You’ll be provided with a trial number and you can send SMS texts only to the verified numbers. Add your number to the verified numbers by OTP verification process. Now you can use that number to test the SMS feature. To set up the Twilio API on Node-RED, we need the following from the site: account SID, your trial number and the token provided.
After adding those to your API credentials, name this set of values and press OK. I named mine “Twilio API”. After this, we need to configure the remaining settings and we are good to go. This completes the setup of our flow, which is shown in Figure 9. Now, Deploy the flow and, to test this, just turn on the NodeMCU device again. You will see a text message on your mobile device that looks as shown in Figure 10.
This SMS Alert Flow sends an email alert to a pre-defined email address when the temperature goes above 20°C. Set up the Twilio API before proceeding.
This shows what a sample SMS text alert to the verified phone number looks like. Note that this is using the Twilio trial membership.
This wraps up the article and, in turn, the article series. There are a lot more features and functionalities that could be added to this IoT platform. For instance, we could add a user interface—basically a front end—for our platform. We can add user account access and switch our primary coding language to JavaScript or Python for better scalability. These are just some ideas that come to mind—the possibilities are endless. If you happen to implement this project and require any assistance or would like to provide me with some additional advice, feel free to email me at dhairyaparikh1998@gmail.com
RESOURCES
References:
[1] “Build Your Own IoT Platform: Part 2” Circuit Cellar 372, July 2021
[2] GitHub Repo: https://github.com/Dhairya1007/IoT-Platform-Article-3
[3] Postman Tutorial Video: https://youtu.be/cR_FqveTewo
[4] Application Password setup (for Gmail): https://support.google.com/accounts/answer/185833?hl=en
Node-RED | www.nodered.org
phpMyAdmin | www.phpmyadmin.net
Postman | www.postman.com
Twilio | www.twilio.com
PUBLISHED IN CIRCUIT CELLAR MAGAZINE • AUGUST 2021 #373 – Get a PDF of the issue
Sponsor this Article
Dhairya Parikh is an Electronics Engineer and an avid project developer. Dhairya makes projects that can bring a positive impact in a person’s life by making it easier. He is currently working as an IoT engineer. His projects are mainly related to IoT and machine learning. Dhairya can be contacted on dhairyaparikh1998@gmail.com
