Asynchronous call of mySql on Node.JS

Question

So, I'm trying to make the getConnection and querying asynchronously in my node.js project as I want to render my response only after my query. This is the code,

router.post('/', function(req, res, next) {

  var queryRows;

  con.getConnection(function(error, connection) {
    if(error) {
    } else {
      connection.query('SELECT * FROM Tablee', function(error, rows, fields){
        queryRows = rows;
      });
    }
  });

  res.render('home', {data:queryRows});
}

I want to run the getConnection() and code inside first; and then render.

I followed the exact solution given in this Question, But in vain. the connection itself is undefined; so query returns error.

I'm using Node version 8 to support async and await; But I'm not able to get the result.

Answer 1

mysql doesn't support promises, which are a requirement of being able to use async/await.

Instead of wrapping it using util.promisify or something similar, you could consider migrating your code to mysql2, which supports promises out of the box: https://github.com/sidorares/node-mysql2#using-promise-wrapper

Since mysql2 tries to offer the same API as mysql, code changes should be minimal.

EDIT: some (untested) example code:

// Install an Express middleware that will set up the database connection, if required.
// Call this somewhere "high up" in your Express app, before route declarations.
app.use(async (req, res, next) => {
  if (! app.db) {
    const mysql = require('mysql2/promise');
    app.db = await mysql.createConnection({ ... });
    // instead of .createConnection, you can also use .createPool
  }
  req.db = app.db;
  return next();
});

Then in your route:

router.post('/', async function(req, res) {
  let [ queryRows, queryFields ] = await req.db.query('SELECT * FROM Tablee');
  res.render('home', { data : queryRows });
}

(I left out all error handling for brevity, make sure you add it, though)