Connecting Mysql Database Using Node Express

Build Up The Node App Structure

Following the node express folder architecture to build the application.
Connecting Node to MySQL database needs node dependencies.

Dependencies

Install the node express & Mysql dependencies.

$ npm install
$ npm install mysql

{
  "name": "expapp",
  "version": "1.0.0",
  "description": "",
  "main": "app.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "devDependencies": {
    "gulp": "^3.9.0",
    "gulp-develop-server": "^0.5.0",
    "gulp-jshint": "^1.12.0",
    "jsdoc": "^3.4.0",
    "mocha": "^2.3.4",
    "should": "^7.1.1",
    "supertest": "^1.1.0"
  },
  "dependencies": {
    "async": "^1.5.2",
    "body-parser": "^1.14.1",
    "connect-multiparty": "^2.0.0",
    "connect-redis": "^3.0.1",
    "express": "^4.13.3",
    "express-session": "^1.12.1",
    "fs": "0.0.2",
    "http-proxy": "^1.12.0",
    "morgan": "^1.6.1",
    "multer": "^1.3.0",
    "mysql": "^2.9.0",
    "node-mysql": "^0.4.2",
    "randomstring": "^1.1.5",
    "xls-to-json": "^0.3.2",
    "xlsx-to-json": "^0.2.4"
  }
}

If you want to use the same version of the packages of Node and MySql Remove " ^ ".
Every time you install npm(node package module) it install’s the latest version of it.

The Main App File

Declare the node express methods, create the connection pool service in the app.js that it connects to the database.

var express = require('express'); 
var app = express();
var bodyParser = require('body-parser'); 
var session = require('express-session');
var db = require('./models/db.js'); // MySql database connection path

app.use(bodyParser.json());  
app.use(bodyParser.urlencoded({
  extended: true
}));

if(GLOBAL.SQLpool === undefined){
	GLOBAL.SQLpool = db.createPool(); //create a global sql pool connection
}

app.use(session({
  secret: 'keyboard cat',
  resave: false,
  saveUninitialized: true
}));

morgan.token('res', function getId(res) {
	return res;
});

var accessLogStream = fs.createWriteStream(__dirname + '/logs/access.log', {flags: 'a'});

app.use(morgan(':req[body] :res[body]', {stream: accessLogStream}));

app.use(require('./controllers'));

app.use('/', express.static(__dirname + '/client'));
app.use('/bower_components', express.static(__dirname + '/bower_components'));

app.listen('3000', function(){
	console.log("Connected on port 3000.");
});

Where :

createPool() : Creates database globally.
controller : Contains The API connected to the database.

Create the database connection globally to use the following connection through out the application.

The Database Connection

• Define the database operations. Assign host, username, database name & password (if available).
• Then establish the MySQL connection to returns the connection object.
• After that establish the MySQL connection to begin transaction and returns the transaction connection object.

var mysql =	require("mysql");
/**
 * Defines database operations.
 * @class
 */
var DB = function(){};
DB.prototype.createPool = function(){
	return mysql.createPool({
			host     : 'localhost', // Hostname 'localhost' If locally connected
			user     : 'root',      // Username
			password : '',          // Password
			database: '',           // Database name
			connectionLimit : 100
		});
}
/**
 * Establishes mysql connection and returns the connection object.
 * @function
 * @param {object} pool - Mysql pool object.
 * @param {function} callback - Callback.
 */
DB.prototype.getConnection = function(pool,callback){
	var self = this;
	pool.getConnection(function(err, connection) {
		if(err) {
			//logging here
			console.log(err);
			callback(true);
			return;
		}
		connection.on('error', function(err) {
			if(err.code === "PROTOCOL_CONNECTION_LOST") {
				connection.destroy();				
			} else {
				connection.release();
			}
			console.log(err);
			callback(true);
			return;
		});
		callback(null,connection);
	});
}
/**
 * Establishes mysql connection, begins transaction and returns the transactio connection object.
 * @function
 * @param {object} pool - Mysql pool object.
 * @param {function} callback - Callback.
 */
DB.prototype.createTransaction = function(pool,callback) {
	var self = this;
	self.getConnection(pool,function(err,connection){
		if(err) {
			//logging here
			console.log(err);
			callback(true);
			return;
		}
		connection.beginTransaction(function(err) {
			if(err){
				console.log(err);
				callback(true);
				return;
			}
			callback(null,connection)
		});
	});
}
module.exports = new DB();