Store Messages From Mosquitto MQTT Broker Into SQL Database

I need a server which is able to setup the Mosquitto MQTT Broker, this server have to be handle MQTT connections with clients and capable to save MQTT data (payload) into database.

The server also act as a File Server and Web Server where I can upload photos via HTTP Post. After reading some posts and research, it looks like the Synology NAS having all the features for me to setup the said server. Therefore I decided to choose the Synology DS216j NAS for this project.

Step 1: Synology DS216j Product Specification

Processor: Dual Core 1.0GHz
Memory: 512MB DDR3 RAM
Storage: Up to 2x 10TBUSB: 2x USB3.0
Network: 1x Gb port
OS: DiskStation Manager (DSM6.1)

Step 2: Setup the Synology NAS

Upon sucessful installing DiskStation Manager (web-based operating system) to my DS216j, I can access the DS216j immediately via http, configure settings, setup users and managing permissions for shared folders.

Next step is to launch the Package Center in DSM in order to installing additional packages (software) to the NAS, the software can be found and done in Package Center with just a few clicks.

From the Package Center in DSM, I choose to install phpMyAdmin which also install the MariaDB, PHP 5.6 & Web Station automatically. Please follow this video guide on how to set up the Web Server.

The Mosquitto MQTT broker can be found in the Package Center but require more steps to complete the installation, it also require some settings modification before it can run on the NAS. Please follow this video guide on how to set up the Mosquitto MQTT broker.

Step 3: Test the Mosquitto MQTT Broker

It is time to test the Mosquitto MQTT broker with the MQTT client tool, I'm using MQTTLens (Google Chrome Add-on) to test the Mosquitto MQTT broker, it is very simple and easy to setup but the screen layout is fairly ugly. Alternately you can use MQTTBox which is a cross-platform supported application. As shown in figure is the MQTTBox client settings used to test my Mosquitto MQTT Broker.

Step 4: Few Ways to Store MQTT Data (payload) Into Database

Now we want to store the MQTT data (payload) into database. Mosquitto don’t provide any built-in mechanism to save MQTT data into Database. To overcome this problem, I have to write a MQTT client with Wildcard Subscription and based on the message topic insert the values into a SQL Database (I'm using MariaDB since it is supported by Synology).

There are ways to develop MQTT client and make it running on desktop computer, however it is a bit difficult to make it running on Synology NAS and the programming language is limited.

Below is a list of software where you can download from Package Center and use them to run your MQTT client on Synology NAS:

Node.js
Java
Per
lPHP
Python

After a few days to play around with the Synology NAS, I found that only the Node.js application (written in Javascript) is capable to connecting the MariaDB with the MQTT client subscript to QOS 0, 1 & 2.

Step 5: Installing Node.js on Synology

Node.js is an open-source, cross-platform JavaScript run-time environment for executing JavaScript code server-side. The Node.js running on Synology NAS is rather an old version (Version 4), but at least it works. Here is a video which shows how to run a Node.js application on Synology DS216j.

Step 6: Installing Node.js Driver for MySQL

We need a driver for connecting to the MariaDB (or MySQL) database, this is a node.js driver for mysql written in JavaScript, does not require compiling.

Login to Synology NAS via PuTTY
Enter the following commands in the PuTTY windows to start the installation
cd /volume1/web
npm install mysql

This will create a node_modules directory in the /volume1/web directory as shown in the figure.

Step 7: Installing Client Library for MQTT

MQTT.js is a client library for the MQTT protocol, written in JavaScript for node.js

Login to Synology NAS via PuTTY
Enter the following commands in the PuTTY windows to start the installation
cd /volume1/web
npm install mqtt --save

Step 8: Create a New User for MariaDB Database

Before we can access to the MariaDB, we need to create a new user and grant permissions in MariaDB.

Login to phpMyAdmin as root
Execute the following command in the SQL tab
CREATE USER 'newuser'@'%' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%';
FLUSH PRIVILEGES;

The new user name will be newuser with a password mypassword.

Step 9: Create a New Database and Table

The following query will create a new database (mydb) and a new table (tbl_messages) in the MariaDB.

Login to phpMyAdmin
Execute the following command in the SQL tab
CREATE DATABASE mydb;
USE mydb;
CREATE TABLE tbl_messages (
messageID INT NOT NULL AUTO_INCREMENT,
clientID VARCHAR(20) NOT NULL,
topic VARCHAR(50) NOT NULL,
message VARCHAR(100) NOT NULL,
Enable BOOLEAN DEFAULT 1,
DateTime_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (messageID)
);

Step 10: Insert a Message to a Table

The following Node.js application will insert a message to the tbl_message table in the mydb database

https://gist.github.com/smching/c3755ff035ca6fbf17...

var mysql = require('mysql');
//Create Connection
var connection = mysql.createConnection({
host: "192.168.1.123",
user: "newuser",
password: "mypassword",
database: "mydb"
});

connection.connect(function(err) {
if (err) throw err;
console.log("Database Connected!");
});

//insert a row into the tbl_messages table
connection.connect(function(err) {
var clientID= "client001";
var topic = "myhome/kitchen";
var message = "dev01,on";
var sql = "INSERT INTO ?? (??,??,??) VALUES (?,?,?)";
var params = ['tbl_messages', 'clientID', 'topic', 'message', clientID, topic, message];
sql = mysql.format(sql, params);
connection.query(sql, function (error, results) {
if (error) throw error;
console.log("1 record inserted"); });
});

Step 11: MQTT Client Subscribe to All Topics

This is a MQTT client for Node.js with Wildcard Subscription, it can receive messages from all MQTT clients (devices) with only one generic subscription.

https://gist.github.com/smching/3582d2fbae417fc919...

var mqtt = require('mqtt');
var Topic = '#'; //subscribe to all topics
var Broker_URL = 'mqtt://192.168.1.123';

var options = {
clientId: 'MyMQTT',
port: 1883,
keepalive : 60
};

var client = mqtt.connect(Broker_URL, options);
client.on('connect', mqtt_connect);
client.on('reconnect', mqtt_reconnect);
client.on('error', mqtt_error);
client.on('message', mqtt_messsageReceived);
client.on('close', mqtt_close);

function mqtt_connect(){
console.log("Connecting MQTT");
client.subscribe(Topic, mqtt_subscribe);
}

function mqtt_subscribe(err, granted){
console.log("Subscribed to " + Topic);
if (err) {console.log(err);}
}

function mqtt_reconnect(err){
console.log("Reconnect MQTT");
if (err) {console.log(err);}
client = mqtt.connect(Broker_URL, options);
}

function mqtt_error(err){
console.log("Error!");
if (err) {console.log(err);}
}

function after_publish(){
//do nothing
}

function mqtt_messsageReceived(topic, message, packet){
console.log('Topic=' + topic + ' Message=' + message);
}

function mqtt_close(){
console.log("Close MQTT");
}

Step 12: Store MQTT Messages Into SQL Database

This is a MQTT client for Node.js with Wildcard Subscription, it can store MQTT messages into database upon receiving a message from the MQTT broker. Mosquitto MQTT broker does not support for client identifiers, and hence it is unable to store the correct clientID into the table. To fix this problem, I have to put the clientID in the published message (see next example).

https://gist.github.com/smching/e202f8c8bf91849b94...

Step 13: Store MQTT Messages Into Database With Correct ClientID

his is the complete code for Node.js MQTT client with Wildcard Subscription. The clientID is published with the message data and the payload syntax is as follow:

clientID,message

https://gist.github.com/smching/ff414e868e80a6ee2f...

Step 14: Enable WebSockets on Mosquitto MQTT Broker

Websockets allows you to receive MQTT data directly into a web browser in real time. Adding the following lines to the Mosquitto configuration file (/volume1/@appstore/mosquitto/var/mosquitto.conf) will enable Mosquitto to listen on Websocket port 1884.

port 1883
listener 1884 192.168.1.123
protocol websockets

Where 192.168.1.123 is the IP address of NAS, please change it to your own IP address.

Here is a simple MQTT client demo written in Javascript that uses webSockets to connect to an MQTT Broker.

Step 15: Mosquitto Username and Password Authentication

An MQTT broker can be configured to require client authentication using a valid username and password before a connection is permitted. You can enable a basic authentication by adding two lines of code to the Mosquitto configuration (/volume1/@appstore/mosquitto/var/mosquitto.conf) file.

allow_anonymous false
password_file /volume1/@appstore/mosquitto/var/passwd

By default, Mosquitto set allow_anonymous to true to allow clients to connect without providing authentication information. Set allow_anonymous to false to force clients to provide authentication information.

The password_file command is use to set the path to a password file.

Step 16: Create a Mosquitto Password File

The contents of the password file are used to control client access to the broker which can be created using the mosquitto_passwd utility. Please read this page on how to create a password file.

Example of creating a new user: mosquitto_passwd -c passwordfile mqtt_user

After creating the password file (name it as passwd) from your desktop computer, copy it to the /volume1/@appstore/mosquitto/var/ directory.on NAS

Step 17: Advanced Process Manager for Production Node.js Applications

PM2 is a production process manager for Node.js applications with a built-in load balancer, it can handle unexpected error Node.js application to avoid downtime, allowing you to keep your applications alive forever. Please watch this video on how to installing and running Node.js application on Synology NAS.

Starting an Node.js application in production mode: pm2 start your_application.js