# how to integrate MQTT-client and database SQlite with nodejs

> Requirements: Sensor: in Example with Temperature and Humdity Sensor registered and connected via TTN-Gateway with TTN TTN Application ID, TTN ACCESS KEY for Ap

- Type: Community update
- Community: [The Things Network Neuss](https://www.thethingsnetwork.org/community/neuss/)
- Author: PietH
- Published: 2020-11-29
- Web page: https://www.thethingsnetwork.org/community/neuss/post/how-to-integrate-mqtt-client-and-database-sqlite-with-nodejs

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

    });
```