Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL Wrapper for NodeJS
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];
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment