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.

possible duplicate of [Why is my variable unaltered after I modify it inside of a function? - Asynchronous code reference](http://stackoverflow.com/questions/23667086/why-is-my-variable-unaltered-after-i-modify-it-inside-of-a-function-asynchron) — Aaron Dufour, May 14 '15 at 16:29
Did you figure out how to do this? It seems that everyone here answered the question indirectly so far. Those links posted on answers and the comment above this - is honestly, although needed to know, TMI - at least for now because we can't directly experience a working solution immediately. I hope there would be a sample code at least with all the required elements to witness first hand if and how the solution is working or not. — jagc, Dec 03 '15 at 06:10
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise node version 8 up + async / await in the function callback — Fábio Zangirolami, May 30 '18 at 13:56

score 1 · Answer 1 · edited May 23 '17 at 12:22

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.

edited May 23 '17 at 12:22

Community

1
1

answered May 14 '15 at 16:45

Brennan

1,764
1
14
17

score 1 · Answer 2 · answered May 14 '15 at 16:54

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);
});

score 1 · Answer 3 · edited Sep 01 '22 at 10:54

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.

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

3 Answers3