how to integrate MQTT-client and database SQlite with nodejs

PietH

The Things Network User

Posted on 29-11-2020

Requirements:
Sensor: in Example with Temperature and Humdity
Sensor registered and connected via TTN-Gateway with TTN
TTN Application ID, TTN ACCESS KEY for App
NodeJS
SQLite npm install sqlite3
MQTT npm install mqtt --save

// SQLite3 
const sqlite3 = require('sqlite3')
let sql_create = "CREATE TABLE IF NOT EXISTS data001 (\n"
        +"id    INTEGER,\n"
        +"sensor_device_id  TEXT,\n"
        +"sensor_timestamp  INTEGER,\n"
        +"sensor_payload_bytes  BLOB,\n"
        +"sensor_battery    INTEGER,\n"
        +"sensor_temperature    INTEGER,\n"
        +"sensor_humidity   INTEGER,\n"
        +"PRIMARY KEY(id AUTOINCREMENT))";
// MQTT
var mqtt=require('mqtt');

var options={
    username:"YOUR-ID", //TTN Application ID
    password:"ttn-account-YOUR-KEY", // TTN ACCESS KEY
    };

var client  = mqtt.connect("mqtt://eu.thethings.network",options); // Server location EU 

//connect
client.on("connect",function(){ 
    var topic="+/devices/+/up";
    client.subscribe(topic); //single topic
    console.log("connected +subscribed");
    });
//handle errors
client.on("error",function(error){
    console.log("Can't connect" + error);
    process.exit(1)});
//handle incoming messages
client.on("message",function(topic, message, packet){
    console.log("message is "+ message);
    console.log("topic is "+ topic);

    let obj_message=JSON.parse(message);

      let rec_dev_id=obj_message.dev_id;
      let rec_timestamp=obj_message.metadata.time;
      let rec_payload_bytes=obj_message.payload_fields.bytes;
      let rec_battery=obj_message.payload_fields.battery;
      let rec_temperature=obj_message.payload_fields.temperature;
      let rec_humidity=obj_message.payload_fields.humidity; 

      //open Database
    let db = new sqlite3.Database('./data/sensor.db', (err) => {  //3. sqlite3.OPEN_CREATE: open the database, if the database does not exist, create a new database.
      if (err) {
        console.error(err.message);
      }
      console.log('Connected to database.');
    });
    //create table if not exist
      db.all(sql_create, [], (err, rows) => {
        if (err) {
          throw err;
          console.log("Tabelle nicht angelegt");
        }
      });
    // insert one row
      db.run(`INSERT INTO data001(sensor_device_id,sensor_timestamp,sensor_payload_bytes,sensor_battery,sensor_temperature,sensor_humidity  ) VALUES(?,?,?,?,?,?)`, [rec_dev_id,rec_timestamp,rec_payload_bytes,rec_battery,rec_temperature,rec_humidity], function(err) {
        if (err) {
          return; //console.log(err.message);
        }
        // get the last insert id
        console.log(`A row has been inserted with rowid ${this.lastID}`);
      });

    //close db
    db.close((err) => {
      if (err) {
        //console.error(err.message);
      }
      //console.log('Close the database connection.');

    });