node js: check mysql connection before a query
I solved this problem like this:
let connection = mysql.createConnection(DB_CONFIG);
function runDBQuery() {
const disconnected = await new Promise(resolve => {
connection.ping(err => {
resolve(err);
});
});
if (disconnected) {
connection = mysql.createConnection(DB_CONFIG);
}
... use actual connection
}
I know this is an old question but I have found connection.ping( (err) => {...}) to be very useful for health-checks made from load balancers and whatnot.
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 ..
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:
- disconnected (when due to DB server down or wrong config use for DB connection is wrong)
- authenticated (when DB connection is successfully created to DB server).
So either check state == 'disconnected' or state == 'authenticated'