Thursday, 29 June 2017 21:00

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.

Synology DS216j product specification:

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

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.

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 below is the MQTTBox client settings used to test my Mosquitto MQTT Broker.

MQTTBox client settings


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
  • Perl
  • PHP
  • 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.

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.

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.

  1. Login to Synology NAS via PuTTY
  2. 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 figure below.

mySql driver

Installing client library for MQTT

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

  1. Login to Synology NAS via PuTTY
  2. Enter the following commands in the PuTTY windows to start the installation

    cd /volume1/web
    npm install mqtt --save

mqtt client library

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.

  1. Login to phpMyAdmin as root
  2. Execute the following command in the SQL tab

    CREATE USER 'newuser'@'%' IDENTIFIED BY 'mypassword';
    GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%';

Create a new user for MariaDB database

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

Create a new database and table

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

  1. Login to phpMyAdmin
  2. Execute the following command in the SQL tab

    USE mydb;

    CREATE TABLE tbl_messages (
       clientID VARCHAR(20) NOT NULL,
       topic VARCHAR(50) NOT NULL,
       message VARCHAR(100) NOT NULL,
       Enable BOOLEAN DEFAULT 1,
       PRIMARY KEY (messageID)

Insert a message to a table

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

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.

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).

Store MQTT messages into Database with correct clientID

This 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:

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

protocol websockets

Where 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.

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.

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

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


Disable console.log for production

Finally, add the following code to your application for production, this will redefine the console.log function in your script.

console.log = function() {}

Read 85360 times Last modified on Friday, 08 June 2018 12:16
Back to Top