MySQL Wrapper for NodeJS

mysql.service.ts

import { Connection, createPool, createConnection, Pool } from 'mysql';
import { MYSQL_DATABASE, MYSQL_HOST, MYSQL_PASSWORD, MYSQL_PORT, MYSQL_USER } from './config.service';
import { InternalServerError } from 'http-errors';
import { LoggerService } from './logger.service';
import { isEmpty } from '../lib/utilities';

const logger = LoggerService.getInstance();

export class MySQLService {

    private static pool: Pool;

    /**
     * Execute a one off query to the DB, connecting without specifying the database
     * @param database database name
     */
    public static async queryNoDB<T>(text: string, params?: any[]): Promise<T> {
        const conn = createConnection({
            host: MYSQL_HOST,
            password: MYSQL_PASSWORD,
            port: MYSQL_PORT,
            user: MYSQL_USER,
            charset: 'utf8mb4',
        });

        return new Promise((resolve, reject) => {
            conn.query(text, params, (error, results, fields) => {
                conn.destroy();
                if (error) { reject(error); }
                return resolve(results);
            });
        });
    }

    /**
     * Returns instance of a connection pool
     */
    public static getPool(): Pool {
        if (this.pool) {
            return this.pool;
        }

        this.pool = createPool({
            database: MYSQL_DATABASE,
            host: MYSQL_HOST,
            password: MYSQL_PASSWORD,
            port: MYSQL_PORT,
            user: MYSQL_USER,
            charset: 'utf8mb4',
            typeCast(field, next) {
                // Cast TINYINT(1) to boolean
                if (field.type === 'TINY' && field.length === 1) { return (field.string() === '1'); }
                return next();
            },
        });

        this.pool.on('acquire', (connection) => {
            logger.trace(`MYSQL ACQUIRE ThreadId: ${connection.threadId}`);
        });

        this.pool.on('connection', (connection) => {
            logger.trace(`MYSQL CONNECTION ThreadId: ${connection.threadId}`);
        });

        this.pool.on('enqueue', () => {
            logger.trace('MYSQL ENQUEUE');
        });

        this.pool.on('release', (connection) => {
            logger.trace(`MYSQL RELEASE ThreadId: ${connection.threadId}`);
        });

        return this.pool;
    }

    /**
     * Get a connection from the pool
     */
    public static getConnection(): Promise<Connection> {
        const pool = this.getPool();

        return new Promise((resolve, reject) => {
            pool.getConnection((error, connection) => {
                (error) ? reject(error) : resolve(connection);
            });
        });
    }

    /**
     * End the pool
     */
    public static end() {
        const pool = this.getPool();
        return pool.end();
    }

    /**
     * Make a SQL query
     * @param text
     * @param params
     * @param connection
     */
    public static async query<T>(text: string, params?: any[], connection?: Connection): Promise<T> {

        // Get a connection from the pool if not provided
        const conn = connection || await this.getConnection();

        return new Promise((resolve, reject) => {
            conn.query(text, params, (error, results, fields) => {

                // Don't close the connection if it was passed in as an argument
                if (!connection) { conn.destroy(); }

                if (error) { reject(error); }

                logger.debug({ text: text.replace(/[\s\t\n\r]+/g, ' '), params, connnectionId: conn.threadId }, 'MYSQL QUERY');
                return resolve(results);
            });
        });
    }

    /**
     * Make DB query, returning first result or false
     */
    public static async queryOne<T>(text: string, params?: any[], connection?: Connection): Promise<false | T> {
        const result = await this.query<T[]>(text, params, connection);
        if (result.length === 0) { return false; }
        return result[0];
    }

    /**
     * Execute two queries in a transaction
     * Utility function for INSERT/SELECT and UPDATE/SELECT transactions
     * @param text1
     * @param params1
     * @param text2
     * @param params2
     */
    public static async twinQuery<T>(text1: string, params1: any[], text2: string, params2: any[]): Promise<T> {
        // Get a DB connection
        const connection = await this.getConnection();

        try {
            // Begin transaction
            await this.query('START TRANSACTION', [], connection);

            // Execute first query
            await this.query(text1, params1, connection);

            // Execute second query
            const result = await this.query<T>(text2, params2, connection);

            // Commit transaction
            await this.query('COMMIT', [], connection);

            // Destroy connection
            connection.destroy();

            // Return result of second query
            return result;

        } catch (err) {
            // Rollback the transaction if any errors occur
            await this.query('ROLLBACK', [], connection);
            connection.destroy();
            throw (err);
        }
    }

    /**
     * Utility function to insert and select records in a transaction
     * @param insertText
     * @param insertParams
     * @param selectText
     * @param selectParams
     */
    public static async insertSelect<T>(insertText: string, insertParams: any[], selectText: string, selectParams: any[]): Promise<T[]> {
        return await this.twinQuery<T[]>(insertText, insertParams, selectText, selectParams);
    }

    /**
     * Utility function to insert and select a single record in a transaction
     * @param insertText
     * @param insertParams
     * @param selectText
     * @param selectParams
     */
    public static async insertSelectOne<T>(insertText: string, insertParams: any[], selectText: string, selectParams: any[]): Promise<T> {
        return await this.twinQuery<T[]>(insertText, insertParams, selectText, selectParams).then((result) => result[0]);
    }

    /**
     * Utility function to update and select records in a transaction
     * @param updateText
     * @param updateParams
     * @param selectText
     * @param selectParams
     */
    public static async updateSelect<T>(updateText: string, updateParams: any[], selectText: string, selectParams: any[]): Promise<T[]> {
        return await this.twinQuery<T[]>(updateText, updateParams, selectText, selectParams);
    }

    /**
     * Utility function to update and select a single record in a transaction
     * @param updateText
     * @param updateParams
     * @param selectText
     * @param selectParams
     */
    public static async updateSelectOne<T>(updateText: string, updateParams: any[], selectText: string, selectParams: any[]): Promise<T> {
        return await this.twinQuery<T[]>(updateText, updateParams, selectText, selectParams).then(((result) => result[0]));
    }

    /**
     * Parse response rows into child objects
     * The query must be carefully designed with the following constraints
     *  - Rows must be ordered by parent record ID
     *  - Field aliases deonte the heirarchy of relationships
     *  - Parent fields must be aliased like 'field'
     *  - Child fields must be aliased like 'table.field'
     *  - Children are assumed to be one-to-one relationship
     *  - Children specified in 'oneToManyTables' array are returned one-to-many
     *  - One-to-many children must include an ID field
     *  - Parent-child relationships can only go one layer deep. e.g. 'table.table.value' is not supported
     * @param rows
     * @param oneToManyTables
     */
    public static buildChildren<T>(rows: any[], oneToManyTables: string[] = []): T[] {
        // The array of response row objects
        const responseRows = [];

        // The current row object being built
        let currentRow: any = {};

        for (const row of rows) {

            // Dictionary of one-to-many child objects being built for the current row
            const OTMObjects: any = {};

            // Populate with empty objects
            oneToManyTables.map(f => OTMObjects[f] = {});

            // Push current row to array if this record ID is different from the last
            // Otherwise continue building current row
            if (currentRow.id && currentRow.id !== row.id) {
                responseRows.push(currentRow);
                currentRow = {};
            }

            // Build record with child objects
            for (const key in row) {
                if (row.hasOwnProperty(key)) {
                    const value = row[key];
                    const keys = key.split('.');

                    if (keys.length === 1) {
                        // Insert parent fields
                        currentRow[key] = value;
                    } else {
                        if (oneToManyTables.indexOf(keys[0]) >= 0) {
                            // Insert field into one-to-many object
                            OTMObjects[keys[0]][keys[1]] = value;
                        } else {
                            // Insert field into one-to-one object
                            currentRow[keys[0]] = currentRow[keys[0]] || {};
                            currentRow[keys[0]][keys[1]] = value;
                        }
                    }
                }
            }

            // Push one-to-many child objects to parent object array
            oneToManyTables.map(f => {
                currentRow[f] = currentRow[f] || [];

                // Insert one-to-many object if it has non-null values
                if (OTMObjects[f].id) {
                    currentRow[f].push(OTMObjects[f]);
                }
            });
        }

        // Push final object to array
        responseRows.push(currentRow);

        // Sanity check for empty objects
        return responseRows.filter(obj => !isEmpty(obj))
    }

    /**
     * Same as buildChildren, but returns a single record
     * @param rows
     * @param oneToManyTables
     */
    public static buildChildrenOne<T>(rows: any[], oneToManyTables: string[] = []): T {
        const response = this.buildChildren<T>(rows, oneToManyTables);
        if (response.length > 1) {
            throw new InternalServerError(`Expected single record in DB response, but several retuned ${response}`);
        }
        return response[0];
    }
}