Asynchronous call of mySql on Node.JS [duplicate]

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
– t.niese, Commented Aug 13, 2017 at 7: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 — Abhijit S
– Abhijit S, Commented Aug 13, 2017 at 7:31

robertklep · Accepted Answer · 2017-08-13 08:39:33Z

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, 2017 at 8:39

answered Aug 13, 2017 at 7:37

robertklep

205k38 gold badges421 silver badges413 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Abhijit S Over a year ago

I tried out mysql2 and tried the promise wrapper; but how exactly can I call that main function? from the link you suggested

robertklep Over a year ago

@AbhijitS see edit for some untested example code to create a (shared) database connection and how to perform a query in your routes.

Abhijit S Over a year ago

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.

robertklep Over a year ago

@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).

Collectives™ on Stack Overflow

Asynchronous call of mySql on Node.JS [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related