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 39639 times Last modified on Friday, 08 June 2018 12:16


  • Comment Link JamesQuelf Thursday, 20 February 2020 07:53 posted by JamesQuelf

    naked and sexy black girl pictures otatovodin tumblr retro ebony dark pussy.
    surprise black stranger fucks milf in kitchen japan milf outdoor sex.
    online sex cams free oltoravimap asian sex cams free.
    wife black cock porn erualinordis black floppy tits selfie nude.
    light skin black porn wifes pussy gets overfilled by big black cock.

  • Comment Link JamesQuelf Thursday, 20 February 2020 06:40 posted by JamesQuelf

    plentyoffish login sign in watch the hunting ground online free reviews.
    japanese teen fucking old man asian teen nude pics.
    skinny ass milf anal milf fucks her handyman. legit robambbete best sites for hooking up.
    private sex web cams ovouscritthege sex cams free live.

  • Comment Link JamesQuelf Thursday, 20 February 2020 05:24 posted by JamesQuelf

    big tits mature asian outininva huge mature boobs tumblr.
    dating site codes affair sites sign up websites.
    pussy milf mature fingers solo pictures varsitotar big tit milf cum.
    lissaray chaturbate gracefulness on chaturbate.
    ebony teen anal tube enpaucotem mature granny skinny ebony women porn.

  • Comment Link Cameronpooca Wednesday, 05 February 2020 02:31 posted by Cameronpooca

    sexy cam naked couple having sex on webcam anal chat rooms.

  • Comment Link Cameronpooca Tuesday, 04 February 2020 09:32 posted by Cameronpooca

    amateur cameltoe pics free xxx webcam.

  • Comment Link Issacmek Saturday, 01 February 2020 16:39 posted by Issacmek

    all sex cams cams tits candid teen upskirt christian teen chat room online.

  • Comment Link Anthonyfoopy Friday, 31 January 2020 14:15 posted by Anthonyfoopy

    huge cytheria squirt hits cameraman s face bytes teen nudist camp video.

  • Comment Link Henrypaf Thursday, 30 January 2020 12:22 posted by Henrypaf

    free adult sex chat views pinkkpurplepeople teen titans chatroom amateur webcam couple

  • Comment Link Lizzie Refshauge Monday, 03 December 2018 01:30 posted by Lizzie Refshauge

    Good Afternoon

    I am writing to introduce our brand new USA and UK Yellow Pages Extractor (Ivy\'s B2B Leads Miner).

    With our software, you can extract unlimited business contact details from USA Yellow Pages ( and UK Yellow Pages (

    You can learn more about our software at

    The software is presently on sale which will be ending in one week.

    Have a good day!

  • Comment Link admin Saturday, 16 June 2018 17:33 posted by admin

    Please refer to PM2 Quick Start

  • Comment Link Wojtalisk Friday, 15 June 2018 03:23 posted by Wojtalisk

    How did you manage to install PM2 on synology? My attempts after npm install -g pm2 produce PM2 in Node.js install folder in @appstore folder on Synology (DS218). Trying to PM2 from terminal (SSH) produce no result. Running PM2 from install location works fine. Any suggestions?

  • Comment Link admin Sunday, 05 November 2017 19:54 posted by admin

    The clientID is published together with the payload. It can be a string.

    e.g. if your clientID is ID1 and messesage is Hello, then your payload should be ID1,Hello.

  • Comment Link svr Sunday, 05 November 2017 19:04 posted by svr

    Where is the clientTD attached to the message resulting in clientID, message string?

  • Comment Link admin Thursday, 07 September 2017 21:58 posted by admin

    Are you using a real Synology or just a Virtual Machine? Please check your MYSQL/MariaDB port (3306 by default). Additionally check your database settings matching with your node.js application.

    host: "",
    user: "newuser",
    password: "mypassword",
    database: "mydb"

  • Comment Link Karl Thursday, 07 September 2017 14:43 posted by Karl

    hi, yes I can connect to my sql.
    still receive the following errors
    if (err) throw err;

    Error: connect ETIMEDOUT
    at Connection._handleConnectTimeout (/volume1/web/node_modules/mysql/lib/Connection.js:419:13)
    at Socket.g (events.js:260:16)
    at emitNone (events.js:67:13)
    at Socket.emit (events.js:166:7)
    at Socket._onTimeout (net.js:336:8)
    at _runOnTimeout (timers.js:537:11)
    at _makeTimerTimeout (timers.js:528:3)
    at Timer.unrefTimeout (timers.js:597:5)
    at Protocol._enqueue (/volume1/web/node_modules/mysql/lib/protocol/Protocol.js:145:48)
    at Protocol.handshake (/volume1/web/node_modules/mysql/lib/protocol/Protocol.js:52:23)
    at Connection.connect (/volume1/web/node_modules/mysql/lib/Connection.js:130:18)
    at Object. (/volume1/web/app_mysql.js:10:12)
    at Module._compile (module.js:409:26)
    at Object.Module._extensions..js (module.js:416:10)
    at Module.load (module.js:343:32)
    at Function.Module._load (module.js:300:12)
    at Function.Module.runMain (module.js:441:10)
    at startup (node.js:140:18)

  • Comment Link admin Thursday, 07 September 2017 09:38 posted by admin

    Run the following script in phpMyAdmin to create a new user for your database & use this user name and password in your node.js application.

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

    Make sure you can login to the phpMyAdmin with this new user name before you test the node.js application.

  • Comment Link Karl Wednesday, 06 September 2017 22:24 posted by Karl

    HI, the simple node.js app is working, I think it has to do with the connection to the mysql. maybe you can help here?

  • Comment Link admin Wednesday, 06 September 2017 21:02 posted by admin

    Make sure you can run a simple node.js application before you insert a message to the tbl_message table. Please follow this video:

  • Comment Link Karl Tuesday, 05 September 2017 18:59 posted by Karl

    I meanwhile I got this:
    [email protected]:/volume1/web$ node app_mysql.js
    return, stringToFlags(flags), mode);

    Error: EACCES: permission denied, open '/volume1/web/app_mysql.js'
    at Error (native)
    at Object.fs.openSync (fs.js:549:18)
    at Object.fs.readFileSync (fs.js:397:15)
    at Object.Module._extensions..js (module.js:415:20)
    at Module.load (module.js:343:32)
    at Function.Module._load (module.js:300:12)
    at Function.Module.runMain (module.js:441:10)
    at startup (node.js:140:18)
    at node.js:1043:3

  • Comment Link Karl Tuesday, 05 September 2017 13:51 posted by Karl

    Hi, very good instructions until "Insert a message to a table
    The following Node.js application will insert a message to the tbl_message table in the mydb database"
    I don´t know how to to that in mysql? maybe you can do a step by step instruction from that point.
    Thx. Karl

Leave a comment

Back to Top