I am having a problem where a service needs to connect to three databases using MySQL pools, I only want to start the service once all the databases are successfully connected, I came up with an approach that works, but I'm quite sure that it could be done better as the current one I feel is a bit over complicated, so any input, or a pointer to a design pattern would be greatly appreciated.
here is the code in my database class:
module.exports = class DB {
// this is making the constructor async as I couldn't think of another way
// because we don't want to start the service until we make sure that we're connected to the
// database, the constructor then resolves to an instance of the class, so when initilizing a class
// we need to await to get back the instance.
constructor() {
return (async () => {
this.retries = 0;
await this.initialize();
return this;
})();
}
async initialize() {
logger.info("initialzing database connections");
this.db = mysql.createPool(config.get('db'));
this.db.asyncQuery = util.promisify(this.db.query);
this.statisticsDb = mysql.createPool(config.get('statisticsDb'));
this.provisioningDb = mysql.createPool(config.get('provisioningDb'));
const success = await this.testConnections();
// retry up to 4 times, if all of them fail we will shutdown the process.
while (this.retries <= 4) {
if (success) break;
if (this.retries > 3) {
Logger.error(`Error connecting to one of the databases pools, number of retries is ${this.retries}, will exit`);
process.exit(1);
}
logger.error(`Error connecting to one of the databases pools, number of retries is ${this.retries}, will retry in 2 seconds`)
this.retries++;
// this basically pauses excution for 2 seconds.
await new Promise(resolve => setTimeout(resolve, 2000));
await this.initialize();
}
}
/**
* Tries to get a connection from each pool, if one of the connections fail, it resolves to false
* otherwise it resolves to true
*/
async testConnections() {
const isMainConnected = await this.testDatabasePoolConnection(this.db, 'main');
const isStatisticsConnected = await this.testDatabasePoolConnection(this.statisticsDb, 'statistics');
const isprovisioningConnected = await this.testDatabasePoolConnection(this.provisioningDb, 'provisioning');
if (isMainConnected.connected && isStatisticsConnected.connected && isprovisioningConnected.connected) {
this.retries = 0;
logger.info('All databases successfully connected.')
return true;
}
return false;
}
/**
* Given a mysql pool, tests if the pool is connected successfully.
* @param {mysql.Pool} dbPoolInstance the mysql pool we want to test
* @param {string} dbName the name of the pool for logging purposes
* @returns resolves to an object, either with error property if there was an error or a connected property if pool is working.
*/
async testDatabasePoolConnection(dbPoolInstance, dbName) {
try {
dbPoolInstance.asyncQuery = util.promisify(dbPoolInstance.query);
let testQuery = 'SELECT id FROM test LIMIT 1';
await dbPoolInstance.asyncQuery(testQuery);
logger.info(`${dbName} database successfully connected`);
return { connected: true }
} catch (e) {
logger.error(`${dbName} database connection failed, Error: ${e}`);
return { error: e };
}
}
and here is the index.js file (simplified):
initalize();
async function initalize() {
try {
const db = await new DB()
await cleanUpSomeData(db);
new Server(db);
} catch (e) {
Logger.error(`Error while trying to intialize the service ${JSON.stringify(e)}, will shutdown the service`);
process.exit(1);
}
}
You could split retry logic from database logic. Create a generic async retry function which reruns a given function until it returns true OR n retries are reached. Example:
const asyncRetry = async (fn, retries) => {
// for logging..
let retriesLeft = retries
do {
console.log('retries left:', retriesLeft)
retriesLeft--
try {
let result = fn()
// wait for promises if it is one..
if(result && typeof result.then === 'function') {
if(await result) {
return true
}
} else if(result) {
return true
}
} catch(err) {
console.error(err)
}
} while(retriesLeft > 0)
console.log('Still Failed after ' + (retries+1) + ' attempts')
return false
}
With this helper you can simplify your DB class and apply retriying from the outside what it makes more clear in my opinion.
Could look as follows:
module.exports = class DB {
constructor() {
// constructor is empty. Call initialize() after from
// the outside and not in the constructor.
}
async initialize() {
logger.info("initialzing database connections");
this.db = mysql.createPool(config.get('db'));
this.db.asyncQuery = util.promisify(this.db.query);
this.statisticsDb = mysql.createPool(config.get('statisticsDb'));
this.provisioningDb = mysql.createPool(config.get('provisioningDb'));
// return result directly!
return await this.testConnections();
}
async testConnections() {
const isMainConnected = await this.testDatabasePoolConnection(this.db, 'main');
const isStatisticsConnected = await this.testDatabasePoolConnection(this.statisticsDb, 'statistics');
const isprovisioningConnected = await this.testDatabasePoolConnection(this.provisioningDb, 'provisioning');
if (isMainConnected.connected && isStatisticsConnected.connected && isprovisioningConnected.connected) {
logger.info('All databases successfully connected.')
return true;
}
return false;
}
/**
* Given a mysql pool, tests if the pool is connected successfully.
* @param {mysql.Pool} dbPoolInstance the mysql pool we want to test
* @param {string} dbName the name of the pool for logging purposes
* @returns resolves to an object, either with error property if there was an error or a connected property if pool is working.
*/
async testDatabasePoolConnection(dbPoolInstance, dbName) {
try {
dbPoolInstance.asyncQuery = util.promisify(dbPoolInstance.query);
let testQuery = 'SELECT id FROM test LIMIT 1';
await dbPoolInstance.asyncQuery(testQuery);
logger.info(`${dbName} database successfully connected`);
return { connected: true }
} catch (e) {
logger.error(`${dbName} database connection failed, Error: ${e}`);
return { error: e };
}
}
}
And your final call would look like this:
async function initalize(retries) {
await cleanUpSomeData(db);
const success = await asyncRetry(() => {
return db.testConnections()
}, retries)
if(success) {
Logger.log('database successfully connected')
} else {
Logger.error('failed to connect to database')
process.exit(1)
}
await cleanUpSomeData(db);
new Server(db);
} catch (e) {
Logger.error(`Error while trying to intialize the service ${JSON.stringify(e)}, will shutdown the service`);
process.exit(1);
}
// try connecting with a maximum of 5 retries (6 attempts at all)
initalize(5);
The timeout between connections can be implemented within asyncRetry() with another parameter timeout for example.
Let me know if something is unclear.