node js: check mysql connection before a query

Question

I use node js with mysql and want to avoid that the app crash on connection errors.At the moment i use this :

function mysql_handleDisconnect() {
  mysql_connection = mysql.createConnection(mysql_config_obj); // Recreate the connection, since
                                                  // the old one cannot be reused.

  mysql_connection.connect(function(err) {              // The server is either down
    if(err) {                                     // or restarting (takes a while sometimes).
      console.log('error when connecting to db:', err);
      mysql_handleDisconnect(); // We introduce a delay before attempting to reconnect,
    }                                     // to avoid a hot loop, and to allow our node script to
  });                                     // process asynchronous requests in the meantime.
                                          // If you're also serving http, display a 503 error.
  mysql_connection.on('error', function(err) {
    console.log('db error', err);
    if(err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection to the MySQL server is usually
      mysql_handleDisconnect();                         // lost due to either server restart, or a
    } else {                                      // connnection idle timeout (the wait_timeout
      throw err;                                  // server variable configures this)
    }
  });
}

 mysql_handleDisconnect(mysql_connection);

so this is blocking because it leads to a hot loop if the connection is closed.my problem is, if i add a setTimeout to reestablish connection just every 2 seconds i could get an fatal error when i do a query with "mysql_connection.query('SELECT ...')".in this case the app crashes.

So my question is,if there's a possibility to check the connection before i do a query?

Answer 1

Try using below code in every microservice before doing anything:

 if(connection.state === 'disconnected'){
     return respond(null, { status: 'fail', message: 'server down'});
   }

State of connection to DB could fall in 2 states:

1. disconnected (when due to DB server down or wrong config use for DB connection is wrong)
2. authenticated (when DB connection is successfully created to DB server).

So either check state == 'disconnected' or state == 'authenticated'

Answer 2

Every time, while I'm pushing my code in production, the mysql connection is lost. It is a very common problem in production, or local.
My solution is that At every query established the db connection and remove connection after completing the db query. My solution is to establish the db connection before every query, and then remove the connection after completing the db query.

Step1: Here is the code for dbConnection.js

//this code is for conenct to db
const mysql = require('mysql2');
require('dotenv').config();
module.exports.stablishedConnection = ()=>{
return new Promise((resolve,reject)=>{
  const con = mysql.createConnection( {
    host: process.env.DB_HOST||localhost,
    user: process.env.DB_USER_NAME||myUserName ,
    password: process.env.DB_PASSWORD||mypassword,
    database: process.env.DB_NAME||mydb
  });
  con.connect((err) => {
    if(err){
      reject(err);
    }
    resolve(con);
  });
  
})
}
module.exports.closeDbConnection =(con)=> {
  con.destroy();
}

Step2: For Router.js I am import the db connection and handle the promise

const router = require('express').Router();
const {stablishedConnection,closeDbConnection}  =require('../db/dbConnection');

router.get('/user/:sId/:userId',function(req,res){
  stablishedConnection()
  .then((db)=>{
    console.log("Db connection stablished");
    db.query(`select * from user WHERE  sent_id=${req.params.sId} AND user_id=${req.params.userId}`, null, function (err,data) { 
      if (!data) {
        res.status(200).json({sucess:false,err});
      }else{ 
        res.status(200).json({sucess:true,data});
        closeDbConnection(db);
         console.log("Db Connection close Successfully");
      }
  })                         
}).catch((error)=>{
  console.log("Db not connected successfully",error);
}); 
  
 
});
router.get('/sen/:userId',function(req,res){
  stablishedConnection()
  .then((db)=>{
    console.log("Db connection stablished");
    db.query(`select * from sen WHERE  user_id=${req.params.userId}`, null, function (err,data) { 
      if (!data) {
        res.status(200).json({sucess:false,err});
      }else{
        res.status(200).json({sucess:true,data});
        closeDbConnection(db);
        console.log("Db Connection close Successfully");
      }
  })                         
}).catch((error)=>{
  console.log("Db not connected successfully",error);
});   
});
router.get('/language',(req,res)=>{
  stablishedConnection()
  .then((db)=>{
    console.log("Db connection stablished");
    db.query("select * from language", null, function (err,data) { 
      if (!data) {
        res.status(200).json({sucess:false,err});
      }else{
        res.status(200).json({sucess:true,data});
        closeDbConnection(db);
         console.log("Db Connection close Successfully")
      }
  })                         
}).catch((error)=>{
  console.log("Db not connected successfully",error);
}); 
})

module.exports = router;

This is perfectly run If you want to create and close connection at every query ..