DatabaseManager class that handles multiple database connections using Knex.js.

DatabaseManager.ts

/**
 * DatabaseManager class that handles multiple database connections using Knex.js.
 *
 * @class     DatabaseManager
 * @author    Mohamed Riyad <m@ryad.me>
 * @link      https://RyadPasha.com
 * @copyright Copyright (C) 2023 RyadPasha. All rights reserved.
 * @license   MIT
 * @version   1.0.0-2023.07.16
 * @see       {@link https://knexjs.org} for more information on Knex.js
 * @see       {@link https://gist.github.com/RyadPasha/64c6c8f2113dbe6db66be9307fcd98c1} for updates
 */

import knex, { Knex } from 'knex'
import Config from '../config/config'

interface DatabaseConfigs {
    [name: string]: Knex.Config
}

class DatabaseManager {
    private static instance: DatabaseManager
    private pools: { [name: string]: Knex } = {}

    /**
     * Private constructor to initialize the DatabaseManager.
     *
     * @param {DatabaseConfigs} databaseConfigs - Object containing database configurations.
     */
    private constructor(databaseConfigs: DatabaseConfigs) {
        for (const [name, config] of Object.entries(databaseConfigs)) {
            /**
             * @type {Knex}
             */
            this.pools[name] = knex(config)
        }
    }

    /**
     * Get the instance of the DatabaseManager (singleton).
     *
     * @param   {DatabaseConfigs} databaseConfigs - Object containing database configurations.
     * @returns {DatabaseManager}                 The DatabaseManager instance.
     */
    public static getInstance(databaseConfigs: DatabaseConfigs): DatabaseManager {
        if (!DatabaseManager.instance) {
            DatabaseManager.instance = new DatabaseManager(databaseConfigs)
        }

        return DatabaseManager.instance
    }

    /**
     * Get the Knex connection pool for the specified database name.
     *
     * @param     {string} [name='main']  - The name of the database pool to retrieve.
     * @returns   {Knex}                  The Knex connection pool.
     * @throws    {Error}                 If no pool is found with the given name.
     */
    public getPool(name: string = 'main'): Knex {
        console.log('getPool')
        if (!this.pools[name]) {
            throw new Error(`No pool found with the name '${name}'`)
        }

        return this.pools[name]
    }

    /**
     * Execute a raw SQL query on the specified database pool.
     *
     * @param   {string}      poolName      - The name of the database pool to use.
     * @param   {string}      query         - The raw SQL query string.
     * @param   {any[]}       [bindings=[]] - Array of query parameter bindings.
     * @returns {Promise<any>}              A promise that resolves with the query result.
     * @throws  {Error}                     If an error occurs while executing the query.
     */
    public async executeRawQuery(poolName: string = 'main', query: string, bindings: any[] = []): Promise<any> {
        const pool = this.getPool(poolName)

        try {
            const result = await pool.raw(query, bindings)

            return result[0]
        } catch (error) {
            throw new Error(`Error executing raw query in pool '${poolName}': ${error.message}`)
        }
    }

    /**
     * Close all the database connection pools.
     *
     * @returns {Promise<void>} A promise that resolves when all pools are closed.
     * @throws {Error} If an error occurs while closing the pools.
     */
    public async closePools(): Promise<void> {
        try {
            for (const pool of Object.values(this.pools)) {
                await pool.destroy()
            }
        } catch (error) {
            throw new Error(`Error closing database pools: ${error.message}`)
        }
    }
}

// Database configurations
const databaseConfigs: DatabaseConfigs = {
    main: {
        client: 'mysql2',
        connection: {
            host: Config.DB_HOST,
            user: Config.DB_USER,
            password: Config.DB_PASS,
            database: Config.DB_NAME,
            port: Config.DB_PORT || 3306,
            dateStrings: true,
            charset: 'utf8mb4',
            ...(Config.DB_SSL && {
                ssl: { ca: Config.DB_SSL }
            })
        },
        pool: {
            min: Config.DB_POOL_MIN || 0,
            max: Config.DB_POOL_MAX || 10
        }
    }
}

const manager = DatabaseManager.getInstance(databaseConfigs)
const pool = manager.getPool()

/**
 * Export the Knex connection pool for the main database.
 * @type {Knex}
 */
export { pool as database }

/**
 * Export the DatabaseManager instance (singleton).
 * @type {DatabaseManager}
 */
export default manager