How to retry database connection in Node.js when first connect is failed?

Question

I am using SQL Server with Node.js. When the connection fails in first attempt the Node.js does not reattempt to connect. I am using setTimeout() to keep trying periodically until it connects.

const poolPromise = new sql.ConnectionPool(config.db);
poolPromise
  .connect()
  .then(pool => {
    console.log('Connected to MSSQL');
    return pool;
  })
  .catch(err => {
    if (err && err.message.match(/Failed to connect to /)) {
      console.log(new Date(), String(err));

      // Wait for a bit, then try to connect again
      setTimeout(function() {
        console.log('Retrying first connect...');
        poolPromise.connect().catch(() => {});
      }, 5000);
    } else {
      console.error(new Date(), String(err.message));
    }
  });

The above code attempt to connect, fails and try for second time but does not continue for third, fourth and so on.

Why would you want to try and reconnect instead of checking if something in the configuration is wrong? What problem could a reconnect solve? — DaCurse, Jul 03 '19 at 18:37
@DaCurse When the server restarts, the node instance is run first, and it tries and fails because SQL Server is not ready. After few seconds the SQL Server is ready. — Maihan Nijat, Jul 03 '19 at 18:39
See https://stackoverflow.com/questions/26694467/promises-repeat-operation-until-it-succeeds — danh, Jul 03 '19 at 18:51
Yes, and if you're running via docker-compose, this is ideal. — John, Jul 03 '20 at 06:49

score 3 · Accepted Answer · answered Jul 03 '19 at 19:08

I wrote this small snippet that works. I wrapped connection part into a function and then invoke it using a recursive function.

In this example you'll see an infinity.

function sql() {
    this.connect = function() {
        return new Promise((resolve, reject) => reject("error connecting"));
    }
}


function connect() {
    return new Promise((resolve, reject) => {
        // const poolPromise = new sql.ConnectionPool("config.db");
        const poolPromise = new sql();
        poolPromise
            .connect()
            .then(pool => {
                console.log("connected");
                resolve(pool);
            })
            .catch(err => {
                console.error(err);
                reject(err);
            });
    });
}

function establishConnection() {
     var a = connect();
     a.then(a => console.log("success"))
    .catch(err => {
        console.error("Retrying");
        // I suggest using some variable to avoid the infinite loop.
        setTimeout(establishConnection, 2000);
    });
};

establishConnection();

score 0 · Answer 2 · answered Jul 03 '20 at 08:57

After checking out the answers here I agree that callbacks are the way to go. I wrote the follow script to attempt to connect to MySQL until connection is established, and then to occasionally check that the connection is still valid, and if not, attempt connection again. I placed console.log's in a few places so that as things run you can see and understand what's happening.

var mysql = require('mysql');
var env = require('dotenv').config()

// ENVIRONMENT LOADS
var env = process.env.NODE_ENV.trim();
var host = process.env.MYSQL_HOST.trim();
var user = process.env.MYSQL_USER.trim();
var password = process.env.MYSQL_ROOT_PASSWORD.trim();
var database = process.env.MYSQL_DB.trim();
var port = process.env.MYSQL_PORT.trim();

console.log('\n\n********\n\nMySQL Credentials\n\n********\n\n');
if (env != 'production') {
  console.log("Host: ", host, ":", port);
  console.log("User: ", user);
  console.log("Database: ", database);
  console.log("Password: ", password);
}else{
  console.log('Using Production Credentials');  
}
console.log('\n\n************************\n\n');

let mysqlDB = null; // db handler
let connected = null; // default null / boolean
let connectFreq = 1000; // When database is disconnected, how often to attempt reconnect? Miliseconds
let testFreq = 5000; // After database is connected, how often to test connection is still good? Miliseconds

function attemptMySQLConnection(callback) {
  console.log('attemptMySQLConnection')
  if (host && user && database) {

    mysqlDB = mysql.createPool({
      host: host,
      port: port, // Modified for Dev env
      user: user,
      password: password,
      database: database,
      connectionLimit: 300,
      waitForConnections: true, // Default value.
      queueLimit: 300, // Unlimited
      acquireTimeout: 60000,
      timeout: 60000,
      debug: false
    });

    testConnection((result) => {
      callback(result)
    })

  } else {
    console.error('Check env variables: MYSQL_HOST, MYSQL_USER & MYSQL_DB')
    callback(false)
  }
}

function testConnection(cb) {
  console.log('testConnection')
  mysqlDB.query('SELECT 1 + 1 AS solution', (error, results, fields) => {
    try {
      if (error) {
        throw new Error('No DB Connection');
      } else {
        if (results[0].solution) {
          cb(true)
        } else {
          cb(false)
        }
      }
    } catch (e) {
      // console.error(e.name + ': ' + e.message);
      cb(false)
    }
  });
}

function callbackCheckLogic(res) {
  if (res) {
    console.log('Connect was good. Scheduling next test for ', testFreq, 'ms')
    setTimeout(testConnectionCB, testFreq);
  } else {
    console.log('Connection was bad. Scheduling connection attempt for ', connectFreq, 'ms')
    setTimeout(connectMySQL, connectFreq);
  }
}

function testConnectionCB() {
  testConnection((result) => {
    callbackCheckLogic(result);
  })
}

function connectMySQL() {
  attemptMySQLConnection(result => {
    callbackCheckLogic(result);
  });
}

connectMySQL(); // Start the process by calling this once

module.exports = mysqlDB;

How to retry database connection in Node.js when first connect is failed?

2 Answers2