Contents hide 1) Build Up The Node App Structure 2) Dependencies 3) The Main App File 4) Where : 5) The Database Connection Build Up The Node App Structure Following the node express folder architecture to build the application. Connecting Node to MySQL database needs node dependencies. Check out the related post on Node Express App Structure. Node Express App Structure 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(); Related Tags: JavaScript, MySql, nodejs