CODEWITHSUNDEEP
mysqlnode.js
E.Ak
E.Ak

Reputation: 67

Node.js MySQL connection with timer and error handling

I have a basic node.js app running as a service on RaspPi. There should be two important issues:

  1. Check MySQL database every 3 secs and get Status
  2. Never throw even if internet connection is lost or MySQL server stops etc.

This code runs well but app closes and throw an error if MySQL server stops. It needs to be run even if there is an any kind of error. (No need to log and get error type) And also; is this every 3 secs connection algorithm correct for performance?

var mysql = require('mysql');
var con = mysql.createConnection({
host: "192.168.1.100",
user: "test",
password: "test",
database: "test"
});

var Gpio = require('onoff').Gpio;
var LED = new Gpio(4, 'out'); //use GPIO pin 4
var getStatus = setInterval(checkStatus, 3000);

function checkStatus() {
 try
 {
  con.connect(function(err) {
  //if (err) throw err;
  con.query("SELECT device_status FROM Device_Status WHERE 
  device_id='device01'", function (err, result, fields) {
  if (err)
    {
        //console.log(err);
    }
  else
    {
        //console.log(result[0].device_status);
        if(result[0].device_status == "1")
        {
            LED.writeSync(1);
        }
        else
        {
            LED.writeSync(0);
        }
    }
 });
 });

 }
 catch(err){ //console.log(err);
 }
}

Upvotes: 1

Views: 493

Answers (2)

Mathew John
Mathew John

Reputation: 579

Can you check this code from my repo https://github.com/MathewJohn1414/node-mysql-quickstart . It is uses the MySQL connection pool and the connection errors are also handled.

Upvotes: 0

Matt Kuhns
Matt Kuhns

Reputation: 1368

Have you thought about pooling connections? Then you would connect on every query.

    connect() {
        return new Promise((resolve, reject) => {
            pool = mysql.createPool({
                connectionLimit: 10,
                host     : this.host,
                user     : this.user,
                password : this.password,
                database : this.database
            });
            resolve(pool);
        });
    }

Then you would search like this:

    search(collection, searchStr) {
        return new Promise((resolve, reject) => {
            pool.getConnection((err, connection) => {
                if (err) {
                    resolve(err);
                } else {
                    var sql = "SELECT * FROM " + collection
                    if (searchStr)
                        sql += " WHERE " + searchStr;
                    connection.query(sql, (err, result) => {
                        if (err) reject(err);
                        resolve(result);
                        connection.release();
                    });
                }
            });
        });
    }

Upvotes: 3

Related Questions