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.

You have to place `res.render('home', {data:queryRows});` **withing** then callback of `connection.query`. `getConnection` and `connection.query` are async, so at the time your `res.render('home', {data:queryRows});` is executed the `getConnection` and `connection.query` callback have not been called. Or use await and async if the use api supports promises. — t.niese, Aug 13 '17 at 07:23
@t.niese Yes, I could place the render inside query; But I wanted to run the whole code using await and async; Because, unlike the example code, I have other render functions and want to place just one render in the end. I was thinking of getting a solution in terms of [this solution](https://stackoverflow.com/a/44004731/7028505) — Abhijit S, Aug 13 '17 at 07:31

robertklep · Accepted Answer · 2017-08-13T08:39:33.393

2

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)

edited Aug 13 '17 at 08:39

answered Aug 13 '17 at 07:37

robertklep

198,204
35
394
381

I tried out mysql2 and tried the promise wrapper; but how exactly can I call that main function? from the [link you suggested](https://github.com/sidorares/node-mysql2#using-promise-wrapper) – Abhijit S Aug 13 '17 at 08:01
@AbhijitS see edit for some untested example code to create a (shared) database connection and how to perform a query in your routes. – robertklep Aug 13 '17 at 08:34
Yes; this works perfectly fine. But could I createPool in my router.post function. Because calling it in app.js, will just make unnecessary connection to the db (As only one of my page needs a connection to db). Kudos to your solution thought. – Abhijit S Aug 13 '17 at 15:27
@AbhijitS it doesn't really matter if only one page uses the connection. With this setup, there is only one connection/pool created during the lifetime of the application (so not a new connection/pool for each _request_). – robertklep Aug 13 '17 at 15:29

Asynchronous call of mySql on Node.JS

1 Answers1