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.');
});