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'