Sharing MySQL Pool connections between files with Node.js

Connection pooling is a requirement in large scale applications. In this blog post I demonstrate my approach on sharing a MySQL pool's connections using Promises while validating the input of my REST endpoint with JSON as middleware, extending my previous blog post.

Installing the modules

The required packages are express, jsonschema, body-parser, promise-mysql. Promise-mysql allows the usage of Promises and provides the utillities of the mysql module. To install them run: 

npm install express jsonschema body-parser promise-mysql

JSON Validation as middleware

By exporting our JSON Validation example as a module and calling it as a middleware, we check for a valid input while achieving cleaner code.

var jsonValidator = require('jsonschema').Validator;
var validator = new jsonValidator();
validator.addSchema(userSchema,"/User");

module.exports = {
JsonValidation: function(req,res,next){
 if(req.get("Content-Type")!="application/json"){
  res.status(401).send("Invalid header format");
  return;
 }

 try {
  validator.validate(req.body,itemSchema,{"throwError":true});
 } catch(error) {
  res.status(401).end("Invalid body format: " + error.message);
  return;
 }

 next();
}
};

Creating pool and sharing connection

In order to create the pool and make it shareable to other files, we export a function that requests a connection from the pool. We make sure that it uses Promises and ensures the synchronous functionality, when calling it from our endpoint.

var connectionPool = mysql.createPool({
 host: '127.0.0.1',
 user: '********',
 password: '*******',
 database: '********',
 port: '3306'
});

module.exports = {
 getConnection: function() {
   return new Promise(function(resolve, reject) {
     connectionPool
       .getConnection()
       .then(function(connection) {
         resolve(connection);
       })
       .catch(function(error) {
         reject(error);
       });
   });
 }
};

var connectionPool = mysql.createPool({
 host: '127.0.0.1',
 user: '********',
 password: '*******',
 database: '********',
 port: '3306'
});

module.exports = {
 getConnection: function() {
   return new Promise(function(resolve, reject) {
     connectionPool
       .getConnection()
       .then(function(connection) {
         resolve(connection);
       })
       .catch(function(error) {
         reject(error);
       });
   });
 }
};

Connecting the dots

Now that our two modules are created and imported to our file, we can validate our input and request a connection, as shown below. After receiving our connection, we proceed in making changes in our database. Notice that JSON validation is run as middleware, before our main logic. Make sure that you release the connection after getting your results. Note that the connection returned by "getConnection", in promise-mysql, is a field of the object that we use for queries.

app.post('/add/product', validate.JsonValidation, function(req, res) {
 mysqlConnection.getConnection().then(function(connection) {
   findOrCreateUser(user, connection)
     .then(function(result) {
       item.seller = result;
       addItem(item, connection)
         .then(function(results) {
           connection.connection.release();
           res.status(200).json(results);
         })
         .catch(function(error) {
           res.status(500).end('Error: ' + error);
         });
     })
     .catch(function(error) {
       res.status(500).end('Error: ' + error);
     });
 });
});

The code for the functionality of the endpoint is shown bellow 

var express = require('express');
var bodyParser = require('body-parser');
var app = express();

var validate = require('./validate');
var mysqlConnection = require('./connectionShare');

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

const addItem = function(item, connection) {
 return new Promise(function(resolve, reject) {
   connection
     .query('INSERT INTO product SET ?', item)
     .then(function(result) {
       resolve(item.seller);
     })
     .catch(function(error) {
       reject(error);
     });
 });
};

const findOrCreateUser = function(user, connection) {
 return new Promise(function(resolve, reject) {
   connection
     .query('SELECT * FROM user WHERE email=' + connection.escape(user.email))
     .then(function(results) {
       if (results.length == 1) {
         resolve(results[0].id);
       } else {
         connection
           .query('INSERT INTO user SET ?', user)
           .then(function(results) {
             resolve(results.insertId);
           });
       }
     })
     .catch(function(error) {
       reject(error);
     });
 });
};

app.post('/add/product', validate.JsonValidation, function(req, res) {
 var item = {
   name: req.body.name,
   price: req.body.price,
   width: req.body.width,
   height: req.body.height,
   added: req.body.added,
   image: req.body.image
 };
 var user = {
   username: req.body.seller.username,
   email: req.body.seller.email,
   votes: req.body.seller.votes
 };

 mysqlConnection.getConnection().then(function(connection) {
   findOrCreateUser(user, connection)
     .then(function(result) {
       item.seller = result;
       addItem(item, connection)
         .then(function(results) {
           connection.connection.release();
           res.status(200).json(results);
         })
         .catch(function(error) {
           res.status(500).end('Error: ' + error);
         });
     })
     .catch(function(error) {
       res.status(500).end('Error: ' + error);
     });
 });
});

app.listen(8000, function() {
 console.log('App listening on port 8000');
});