Storing your TTN node data in a noSQL database

This blog post is all about building a Node.js server to subscribe to the TTN MQTT(S) broker and store the data in a Mongo noSQL database. I am assuming that you have built a Node.js app before. There is also a suggestion of an application that will show your data.

The Things Network (TTN) is an open-source, secure and scalable solution that has around 5,000 gateways and 50,000 members around the world. It started in August 2015 and just keeps on growing.

TTN manages every aspect of creating and managing a LoRaWAN network except storing your data (note that TTN does offer a storage solution, but provides storage for 7 days only). There are several integrations available, including EVRYTHNG, AWS IoT, Cayenne and Tago. This example is useful if you want to keep your data in-house.

Just FYI, my example code, shown below, is running on a VPS with an instance of ubuntu xenial server, hosted by https://scaleway.com for around €3/month.

First, install the following components

Mosquitto – lightweight broker/client for MQTT
Node.js – javascript run-time environment – I used V8.x LTS
MongoDB – noSQL database, ideal for json-formatted data

Then create a Node.js application, which typically consists of

package.json – describes app, dependencies, licence, etc.
config.js – store for all configuration data – URL, username, etc
(Note: contains your db password & MQTT access key in plain text!)
server.js – the Node.js app
mqtt-ca.pem – enables use of TLS encrypted MQTT traffic (available from your TTN console)

package.json typical contents:

{
    "name": "ttn-mqtt-database",
    "description": "Listens to MQTT messages and stores in MongoDB",
    "version": "1.0.0",
    "dependencies": {
	"mongodb": "^3.0.3",
	"mqtt": "2.15.1",
	"ttn": "^2.3.1"
    },
    "repository": {
    	"type": "git",
      "url": "https://github.com/[your github repository].git"
    }
} 

config.js contains the configuration information to subscribe to your specific MQTT channel and the connection credentials and path for your mongo database

var config = {};

config.debug = process.env.DEBUG || true;

config.mqtt  = {};
config.mqtt.namespace = process.env.MQTT_NAMESPACE || '+/devices/+/up';
config.mqtt.protocol  = process.env.MQTT_PROTOCOL  || 'mqtts://';
config.mqtt.hostname  = process.env.MQTT_HOSTNAME  || 'eu.thethings.network';
config.mqtt.port      = process.env.MQTT_PORT      || 8883;
config.mqtt.user      = process.env.MQTT_USER      || '[name of your TTN application]';
config.mqtt.password  = process.env.MQTT_PASSWORD  || 'ttn-account-v2.[your TTN key]';
config.mqtt.cafile    = process.env.MQTT_CAFILE    || 'mqtt-ca.pem';

config.mongodb = {};
config.mongodb.user       = process.env.MONGODB_USER       || '[user]';
config.mongodb.password   = process.env.MONGODB_PASSWORD   || ‘[your ultra-secret password]';
config.mongodb.hostname   = process.env.MONGODB_HOSTNAME   || '[your server DNS]';
config.mongodb.port       = process.env.MONGODB_PORT       || 27017;
config.mongodb.database   = process.env.MONGODB_DATABASE   || 'mqtt';
config.mongodb.collection = process.env.MONGODB_COLLECTION || 'nodedata';

module.exports = config;

The code for your Node.js server should look something similar to this:

server.js

var mongodb  = require('mongodb');
var mqtt     = require('mqtt');

var fs       = require('fs');
var config   = require('./config');

var client = mqtt.connect(config.mqtt.hostname, {
            ca: [fs.readFileSync(config.mqtt.cafile)],
          username: config.mqtt.user,
          password: config.mqtt.password,
              port: config.mqtt.port
         });

client.on('connect', function () {
    client.subscribe(config.mqtt.namespace);
});

var mongoUri = 'mongodb://' + config.mongodb.user + ':' + config.mongodb.password + '@' + config.mongodb.hostname + ':' + config.mongodb.port + '/' + config.mongodb.database;

console.log(mongoUri);
mongodb.MongoClient.connect(mongoUri, function(err, client) {
    if(err != null) {
        console.log(mongoUri);
        throw error;
    }
    else {
        var mydb = client.db('mqtt');
        var collection = mydb.collection(config.mongodb.collection);
        collection.createIndex({ "topic" : 1 });
        client.on('message', function (topic, message) {
        var messageObject = {
            topic: topic,
            message: message.toString()
        };
        console.log(message.toString());
        collection.insert(messageObject, function(err, result) {
            if(err != null) {
                console.log("ERROR: " + err);
            }
        });
    });
});

To see the data stored in your mongo database, either use the mongo client:

cli access via mongoDB client
user@demo:~/ttn-demo# mongo
> use mqtt
> db.message.find();

Or use a GUI app, like NoSQLBooster for MongoDB

https://nosqlbooster.com/downloads

A useful noSQL query would be similar to this, which shows the last 20 messages from a specific device called pycomr001:

db.nodedata.where({'device': 'pycomr001'}).sort({ "natural": -1}).limit(20);

So, in summary:
TTN provides an excellent platform to move your node data to the cloud
TTN enables access via a secure MQTT broker
MongoDB is a noSQL db, good for JSON data
Node.js is a simple way to build a headless server app
NoSQLBooster for MongoDB is a good GUI to access your db
systemctl is a sensible method to manage your app

Now, it’s down to you to do something useful with your data!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.