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 :

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:

Used for simple queries such as simple SELECT queries where opening a transaction for multiple table inserts/updates is not required. E.g.:
`typescript
const sqlString = 'SELECT * FROM users_table where user_id = ?';
const dbResults = sql.executeQuery(sqlString, [userId]);
`


---

- public getConnectionFromPool(): Promise:

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.
`typescript
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();
});
});
});
});
``