Skip to content

Instantly share code, notes, and snippets.

@smching
Last active August 30, 2022 01:28
Show Gist options
  • Save smching/e202f8c8bf91849b94e206e3733b5ea8 to your computer and use it in GitHub Desktop.
Save smching/e202f8c8bf91849b94e206e3733b5ea8 to your computer and use it in GitHub Desktop.
Node.js application: Store messages from Mosquitto MQTT broker into SQL Database
var mqtt = require('mqtt');
var Topic = '#'; //subscribe to all topics
var Broker_URL = 'mqtt://192.168.1.123';
var Database_URL = '192.168.1.123';
var options = {
clientId: 'MyMQTT',
port: 1883,
//username: 'mqtt_user',
//password: 'mqtt_password',
keepalive : 60
};
var client = mqtt.connect(Broker_URL, options);
client.on('connect', mqtt_connect);
client.on('reconnect', mqtt_reconnect);
client.on('error', mqtt_error);
client.on('message', mqtt_messsageReceived);
client.on('close', mqtt_close);
function mqtt_connect() {
//console.log("Connecting MQTT");
client.subscribe(Topic, mqtt_subscribe);
}
function mqtt_subscribe(err, granted) {
console.log("Subscribed to " + Topic);
if (err) {console.log(err);}
}
function mqtt_reconnect(err) {
//console.log("Reconnect MQTT");
//if (err) {console.log(err);}
client = mqtt.connect(Broker_URL, options);
}
function mqtt_error(err) {
//console.log("Error!");
//if (err) {console.log(err);}
}
function after_publish() {
//do nothing
}
function mqtt_messsageReceived(topic, message, packet) {
//console.log('Message received = ' + message);
insert_message(topic, message, packet);
}
function mqtt_close() {
//console.log("Close MQTT");
}
////////////////////////////////////////////////////
///////////////////// MYSQL ////////////////////////
////////////////////////////////////////////////////
var mysql = require('mysql');
//Create Connection
var connection = mysql.createConnection({
host: Database_URL,
user: "newuser",
password: "mypassword",
database: "mydb"
});
connection.connect(function(err) {
if (err) throw err;
//console.log("Database Connected!");
});
//insert a row into the tbl_messages table
function insert_message(topic, message, packet) {
var clientID= "client001";
var sql = "INSERT INTO ?? (??,??,??) VALUES (?,?,?)";
var params = ['tbl_messages', 'clientID', 'topic', 'message', clientID, topic, message];
sql = mysql.format(sql, params);
connection.query(sql, function (error, results) {
if (error) throw error;
console.log("1 record inserted");
});
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment