General

This is a node module that provides database connectivity tools to be used with Typescript. Currently it provides an abstraction layer over the node-mysql module and allows for (yet) very basic queries. All of the public methods that are asynchronous, return Promises so that they can also used with the async/await functionality - thus avoiding the infamous 'callback hell' of nested callbacks. It is published on npm where it can be installed from. It is currently a public package accessible for everyone.

Usage

Setup a .env file

Firstly you will need to setup a .env file in your projects root directory containing the following properties:

ACTIVE_DB=DEV_LOCAL_DB_HOST

DEV_LOCAL_DB_HOST=[db-host]
DEV_LOCAL_DATABASE=[db-name]
DEV_LOCAL_DB_USER=[db-user]
DEV_LOCAL_DB_PASSWORD=[db-password]

DEV_AWS_DB_HOST=[db-host]
DEV_AWS_DATABASE=[db-name]
DEV_AWS_DB_USER=[db-user]
DEV_AWS_DB_PASSWORD=[db-password]

PROD_AWS_DB_HOST=[db-host]
PROD_AWS_DATABASE=[db-name]
PROD_AWS_DB_USER=[db-user]
PROD_AWS_DB_PASSWORD=[db-password]

The idea is to have multiple database credentials, one for each evironment that your code runs on. E.g. a localhost dev environment & a cloud production/dev environment. Simply change the ACTIVE_DB property to the DB_HOST you want to use - see example above.

Usage

First of all import the module via the command:

import { MySqlUtils } from 'db-util-redoleus';

The module contains the following public methods:

• public static getInstance(): Returns the singleton instance of the MySqlUtils class. E.g.: const sql = MySqlUtils.getInstance();

• public async testConnection(): Promise<boolean> : Returns a boolean value indicating whether a successful connection was made to the database server. Can be used for server health-checks from load-balancers, etc, E.g.:

const healthStatus = await sql.testConnection();
if (healthStatus) {
    // return a status 200 
} else {
    // throw an error or return a status 500 
}

• public executeQuery(queryString: string, queryParams?: any): Promise<any>: Used for simple queries such as simple SELECT queries where opening a transaction for multiple table inserts/updates is not required. E.g.:

const sqlString = 'SELECT * FROM users_table where user_id = ?';
const dbResults = sql.executeQuery(sqlString, [userId]);

• public getConnectionFromPool(): Promise<mysql.Connection>: Used for more complicated queries where opening a transaction is required in order to be able to rollback in case of a multi-part query failing halfway through. For more information see the mysql node module documentation.

const connection = sql.getConnectionFromPool();
connection.beginTransaction(function(err) {
  if (err) { throw err; }
  connection.query('INSERT INTO user_table SET name=?', "mrfksiv", function(err, result) {
    if (err) { 
      connection.rollback(function() {
        throw err;
      });
    }
    const log = result.insertId;
     
    connection.query('INSERT INTO log SET logid=?', log, function(err, result) {
      if (err) { 
        connection.rollback(function() {
          throw err;
        });
      }  
      connection.commit(function(err) {
        if (err) { 
          connection.rollback(function() {
            throw err;
          });
        }
        console.log('Transaction Completed successfully.');
        connection.end();
      });
    });
  });
});