The required packages for this example are express, jsonschema, body-parser, promise-mysql. I analyze their usage in my previous blog posts. To install them run the following:

npm install express jsonschema body-parser promise-mysql

We will modify our previously exported module, to be able to share the requested connection from the MySQL pool. To do that, we need to pass the connection to an object of higher scope level. Since our middleware receives as arguments the request, response and next objects, we will use the request object to pass the connection to the next function.

var mysql = require('promise-mysql');

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

module.exports={
 getConnection: function(req, res, next){
  return new Promise(function(resolve,reject){
   connectionPool.getConnection().then(function(connection){
    req.connection = connection;
    next();
   }).catch(function(error){
    res.status(500).end("Error getting connection: " + error);
   });
  });
 }
}

When a Promise fails it throws an error that can be caught and managed by our try/catch blocks. But, in the future versions of Node.js, Unhandled promise rejections will terminate our application. Managing these is fairly simple:

process.on('uncaughtException', error => console.error('Uncaught exception: ', error));
process.on('unhandledRejection', error => console.error('Unhandled rejection: ', error));

In order to use the async/await model in a REST endpoint, we must consider that an "await for a promise" command requires an async function to call them. A fairly simple way of doing it is having the async keyword on our last RESTful endpoint's function. Considering that we use our imported middleware for JSON input Validation and Requesting MySQL connection, we have:

app.post('/add/product', validate.JsonValidation, mysqlConnection.getConnection, async function(req,res){
 var connection = req.connection;
 try {
  item.seller = await findOrCreateUser(user,connection);
  var user_id = await addItem(item,connection);
  var items = await selectUserItems(user_id, connection);
  connection.connection.release();
  res.status(200).json(result);
 } catch(error) {
  res.status(500).end(error);
 }
});

The complete code of our example is shown below:

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){
 console.log("Adding Item");
 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){
 console.log("Finding User");
 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);
   })
 })
}

const selectUserItems = function(userID,connection){
 console.log("Selecting Items " + userID);
 return new Promise(function(resolve,reject){
  connection.query("SELECT * FROM product WHERE seller = " + connection.escape(userID))
   .then(function(results){
    resolve(results);
   }).catch(function(error){
    reject(error);return;
   });
  })
}

app.post('/add/product', validate.JsonValidation, mysqlConnection.getConnection, async function(req,res){
 var connection = req.connection;
 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
 };

 try {
  item.seller = await findOrCreateUser(user,connection);
  var user_id = await addItem(item,connection);
  var items = await selectUserItems(user_id, connection);
  connection.connection.release();
  res.status(200).json(result);
 } catch(error) {
  res.status(500).end(error);
 }
});

process.on('uncaughtException', error => console.error('Uncaught exception: ', error));
process.on('unhandledRejection', error => {console.error('Unhandled rejection: ', error));

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