used in a project where every user had a seperate DB....

dynamicPoolCreator.js

const mysql = require('mysql2/promise');
const Logger = require('./SqlLogger.service');
const dbPool = require('./authDb.service')

const _DB_POOL_LIST = {}

async function _createPool({ db_id, db_host, db_name, db_user, db_password }) {
    try {
        let pool = _DB_POOL_LIST[db_id]

        if (!pool) {
            pool = await mysql.createPool({
                host: db_host,
                user: db_user,
                password: db_password,
                database: db_name,
                charset: 'utf8_general_ci',
            });
            _DB_POOL_LIST[db_id] = pool
        }

        pool.on('acquire', function (connection) {
            console.log('Connection %d acquired', connection.threadId);
        });

        pool.on('connection', function (connection) {
            console.log('connection made', connection.threadId);
        });

        pool.on('enqueue', function () {
            console.log('Waiting for available connection slot');
        });

        pool.on('release', function (connection) {
            console.log('Connection %d released', connection.threadId);
        });

        return pool
    } catch (err) {
        Logger.error(err);
        return null
    }
};

async function getPoolForUserDB({ database_id }) {
    try {
        let authDbPoolConn;
        try {
            authDbPoolConn = await dbPool.getConnection()
            const sql = authDbPoolConn.format(`SELECT * FROM db_names WHERE db_id = ?`, [database_id]);
            var [rows] = await authDbPoolConn.query(sql)
            const user = rows[0];
            const pool = _createPool(user)
            return pool
        } catch (error) {
            Logger.error("🚀 ~ file: db.service.js ~ line 35 ~ getPoolForUserDB ~ error", error)
            return null
        } finally {
            authDbPoolConn?.release()
        }
    } catch (error) {
        console.log("🚀 ~ file: db.service.js ~ line 49 ~ getUserDbConnection ~ error", error)
        return [null, error]
    }
}

module.exports = { getPoolForUserDB };