Pick up connection if there is a disconnect

Question

I make use of this specific version: https://github.com/patriksimek/node-mssql/tree/v3.3.0#multiple-connections of the SQL Server npm package.

I have been looking through the documentation of tedious (the underlying lib) and Microsofts documentation (see the github link above).

I couldn't find anything that does something simple like getCurrentConnection, or getConnectionStatus or anything similar.

I had two ways to solve this problem but I'm not happy with both of them so that's why I'm asking here.

My first approach was to set a timeout and let the connect function call itself on each catch(err).

The second one was to handle this in the middleware but then if all is working fine it will make a connection to SQL on every request and closing that connection again.

My middleware function:

api.use(function(err, req, res, next){
   sql.close();
   sql.connect(config.database).then(() => {
     next();
   }).catch(function(err) {
     sql.close();
     server.main();
   });
});

I want to, if possible pick up the connection instead of closing and starting a new one with regards to when the server or the database crashes I still have some data from the existing function.

Answer 1

By the help of Arnold I got to understand the mssql package and it's inner workings a lot better.

Therefor I came up with the following solution to my problem.

let intervalFunction;
const INTERVAL_DURATION = 4000;

if (require.main === module){
    console.log("Listening on http://localhost:" + config.port + " ...");
    app.listen(config.port);
    // try to connect to db and fire main on succes. 
    intervalFunction = setInterval(()=> getConnection(), INTERVAL_DURATION);
}

function getConnection() {
  sql.close();
  sql.connect(config.database).then(() => {
    sql.close();
    clearInterval(intervalFunction);
    main();
}).catch(function(err) {
    console.error(err);
    console.log(`DB connection will be tried again in ${INTERVAL_DURATION}ms`)
    sql.close();
  });
}

Once the initial connection has been made but it got lost in the meantime the pool will pick up the connection automatically and handle your connections