NodeJS MySQL How to get the result outside of the query function

Question

I can't seem to figure out how the get the result outside of a NodeJS MySQL Pool query. Here is some sample code to better explain what I mean.

var result = 'Hello world!';

var mysql = require('mysql');
var pool = mysql.createPool({
    connectionLimit : 100,
    host            : process.env.DB_HOST,
    user            : process.env.DB_USERNAME,
    password        : process.env.DB_PASSWORD,
    database        : process.env.DB_DATABASE
});

pool.query('SELECT * from user LIMIT 10', function (err, rows) {
    result = rows;
});

res.send(result);

The above will return 'Hello world!' instead of the query.

If I console.log(result) inside the pool.query function it returns the rows from the query but I can't seem to get the data outside of the function. I've logged the function and checked all the associated functions and I think I'm just missing something basic.

Answer 1

You're sending the results back before the query finishes (and the callback is called). Sending the results in your callback will fix the problem:

pool.query('SELECT * from user LIMIT 10', function (err, rows) {
    result = rows;
    res.send(result);
});

As Aaron pointed out, this is a common problem. A more thorough answer can be found here.

Answer 2

The pool query function is asynchronous. It means that your code won't execute in the same sequence you declared the statements.

You start a query and you specify a callback function which should run when the query async operation completes.

The res.send will run immediately after the query and callback declaration and the callback will run much later. By the time you set the result you already send it.

Try to move your res.send inside the callback.

pool.query('SELECT * from user LIMIT 10', function (err, rows) {
    res.send(rows);
});

Answer 3

Sorry if my English is bad,

I have facing the same problem with you and I found a solution where you can extract the result of your query function by mixing the async-await function with the promise

Look at my solution code here :

async () => {
  const sqlQuery = `select * from contact;`
  const promise = await new Promise((resolve, reject) => {
    pool.query(sqlQuery, (err, result) => {
      resolve(result)
    })
  })
  console.log(promise)
})

With this code, your console definitely show the result of sqlQuery. How's this can be happen? The promise that you make will always has a pending status if the resolve/reject aren't being executed. With the mixing of asynchronus function and await in the promise that you make, your promise code has to be in resolve/reject status, if it's in pending status, they will not execute the next code, and always waiting until the resolve/reject has been executed. As you know, the result of your query statement shows in the callback function in pool.query. So why we don't execute the resolve/reject in the callback function of pool.query.

You can pass the result of query into the variable of your promise by adding it to the parameter of resolve function.