How do I send information from a MQTT node into a MYSQL database?

Question

I'm trying to recieve data from a MQTT node which I then want to proceed with putting into a MYSQL database. From what I've understood I need to use Javascript to do this, I however can't find any examples of this which will work. Is there anyone who have done this before who could help out? This specifically is about how to make a script in Javascript to send the information from the MQTT broker to a MYSQL database in node red. The question that was suggested as an answer is not specifically for Node Red nor does it offer any answers to my question about using Javascript as a way to achieve what I'm trying to do. The answer to that question was to use Node red but it was to no help with how you should use it.

Possible duplicate of [Is there a way to store Mosquitto payload into an MySQL database for history purpose?](https://stackoverflow.com/questions/31584613/is-there-a-way-to-store-mosquitto-payload-into-an-mysql-database-for-history-pur) — hardillb, Jun 20 '18 at 10:44

karan sharma · Answer 1 · 2018-06-22T05:13:29.370

yeah, you can use any language for sending payload from MQTT to MYSQL. basically what you can do is set a small node which will subscribe to all the incoming payload and dump it in your MYSQL Db

here is the JS script:-

var mqtt = require('mqtt'); //https://www.npmjs.com/package/mqtt
var Topic = '#'; //subscribe to all topics
var Broker_URL = 'mqtt://MQTT_BROKER_URL';
var Database_URL = 'Database_URL';



var options = {
    clientId: 'MyMQTT',
    port: 1883, 
    keepalive : 60
};

var client  = mqtt.connect(Broker_URL, options);
client.on('connect', mqtt_connect);
client.on('reconnect', mqtt_reconnect);
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 after_publish() {
    //do nothing
};

//receive a message from MQTT broker
function mqtt_messsageReceived(topic, message, packet) {
    var message_str = message.toString(); //convert byte array to string
    console.log("message to string", message_str);
    message_str = message_str.replace(/\n$/, ''); //remove new line
    //message_str = message_str.toString().split("|");
    console.log("message to params array",message_str);
    //payload syntax: clientID,topic,message
    if (message_str.length == 0) {
        console.log("Invalid payload");
        } else {    
        insert_message(topic, message_str, packet);
        //console.log(message_arr);
    }
};

function mqtt_close() {
    //console.log("Close MQTT");
};

////////////////////////////////////////////////////
///////////////////// MYSQL ////////////////////////
////////////////////////////////////////////////////
var mysql = require('mysql'); //https://www.npmjs.com/package/mysql
//Create Connection
var connection = mysql.createConnection({
    host: Database_URL,
    user: "newuser",   //DB Username
    password: "mypassword",  //DB Password
    database: "mydb"         //DB Name
});

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_str, packet) {
    var message_arr = extract_string(message_str); //split a string into an array
    var clientID= message_arr[0];
    var message = message_arr[1];
    var date= new Date();
    var sql = "INSERT INTO ?? (??,??,??,??) VALUES (?,?,?,?)";
    var params = ['tbl_messages', 'clientID', 'topic', 'message','date', clientID, topic, message, date];
    sql = mysql.format(sql, params);    

    connection.query(sql, function (error, results) {
        if (error) throw error;
        console.log("Message added: " + message_str);
    }); 
};  

//split a string into an array of substrings
function extract_string(message_str) {
    var message_arr = message_str.split(","); //convert to array    
    return message_arr;
};  

//count number of delimiters in a string
var delimiter = ",";
function countInstances(message_str) {
    var substrings = message_str.split(delimiter);
    return substrings.length - 1;
};

Reference:- https://github.com/karan6190/MQTT-DB-plugin/blob/master/mqttTOmysql.js

This is basically a link only answer, which will become useless once the link breaks. Please edit to include the useful part of the code in the answer. — hardillb, Jun 21 '18 at 15:04

score -1 · Answer 2 · answered Jun 20 '18 at 11:52

You can use any language to send messages from MQTT to MySQL database(or any other).

For example, you can create a separate python service which uses Paho MQTT client and subscribes to all the topics and adds that data to a database when the message is received.

Here is how the code will look like in Python:

def on_message(client, userdata, msg):
    topic = msg.topic
    payload = msg.payload
    # run mysql query using library like MySQLdb 
    # https://www.tutorialspoint.com/python/python_database_access.htm

topic = '#" #subscribe to all topics
client = mqtt.Client()
client.on_connect = on_connect
client.on_message = on_message
client.subscribe(topic)
client.connect(mqttserver)
client.loop_forever()

The OP specifically asked for NodeJS/Node-RED not Python – hardillb Jun 20 '18 at 12:21 — hardillb, Jun 20 '18 at 12:21

How do I send information from a MQTT node into a MYSQL database?

2 Answers2