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
package.json
{
"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.
app.js
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.
/models/db.js
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();