Node.js MySQL query can't assign MySQL results to a variable

Question

I am trying to assign MySQL query results in to a variable and then use it multiple times in script. I want to do this because I have PHP background and I like to work in this way. I know Node.js works asynchronously so this is my problem.

In the code below I want to assign MySQL results in to sql_results variable but connection.query function works asynchronously and code works in different order. In this case at the end of my code when I console.log(sql_results) It returns me undefined. This is normal because when I check console outputs I clearly see console.log(sql_results) worked before console.log(results).

var mysql = require('mysql');

var connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "1234",
  database: "db_progress"
});

var sql_query = "SELECT * FROM db_progress.tblresults res WHERE res.id < 3";
var sql_results; // This is my variable

connection.connect();

connection.query(sql_query, function (error, results, fields) {
  if (error) throw error;
  console.log(results); // This is mysql results
  sql_results = results; // I am sure this works but I can't see
});

connection.end();

console.log(sql_results); // This returns undefined because works before query

How can I assign MySQL results in a variable, or If there is a different method for this purpose. Please explain in details.

Thanks.

As you already pointed out the database query is asynchronous. Therfor you cannot use the variable in the way you are trying to here. Everything you want to do with it, has to happen after the callback function of `connection.query`. You might want to read about [Promises](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise) and [async/await](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/async_function) — Danmoreng, Aug 21 '19 at 13:35
Yes you are right. I have read about ```Promises``` and ```asycn/await``` topics. First of all, some people are not suggesting ```asycn/await``` they say It's not the way how Node.js works. For ```Promises``` actually I couldn't really understand working structure in Node.js — X999, Aug 21 '19 at 13:51
Possible duplicate of [How do I return the response from an asynchronous call?](https://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call) — Evert, Aug 21 '19 at 14:10

score 0 · Answer 1 · answered Aug 21 '19 at 13:46

Node js function works asynchronously so it's my suggestion to use your query with promise.

class Database {
            constructor( config ) {
                this.connection = mysql.createConnection( config );
            }
            query( sql, args ) {
                return new Promise( ( resolve, reject ) => {
                    this.connection.query( sql, args, ( err, rows ) => {
                        if ( err )
                            return reject( err );
                        resolve( rows );
                    } );
                } );
            }
            close() {
                return new Promise( ( resolve, reject ) => {
                    this.connection.end( err => {
                        if ( err )
                            return reject( err );
                        resolve();
                    } );
                } );
            }
        }

I saw this example in this [link](https://codeburst.io/node-js-mysql-and-promises-4c3be599909b) and it doesn't work either. — X999, Aug 21 '19 at 13:58

score 0 · Answer 2 · answered Aug 21 '19 at 13:47

0

Might try replacing this code,

connection.query(sql_query, function (error, results, fields) {
  if (error) throw error;
  console.log(results); // This is mysql results
  sql_results = results; // I am sure this works but I can't see
});

connection.end();

with this,

connection.query(sql_query)
    .then( results=> {
        sql_results = results;
        return connection.close();
    } );

Danmoreng has already mentioned the articles about Promises and async/await. They will help you understand how the above code works.

answered Aug 21 '19 at 13:47

Huzaifa

121
11

Hi, when I use this way I get this error. ```TypeError: connection.query(...).then is not a function``` – X999 Aug 21 '19 at 13:55
If nothing worked, try following [this](https://www.npmjs.com/package/mysql-query-promise)? – Huzaifa Aug 21 '19 at 15:28

score -1 · Answer 3 · answered Aug 21 '19 at 13:37

-1

The results of your query are returned in a callback function, which executes at a later time than the calling code. You are logging the results in the context of the calling code, before sql_results has been set.

Try this:

var mysql = require('mysql');

var connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "1234",
  database: "db_progress"
});

var sql_query = "SELECT * FROM db_progress.tblresults res WHERE res.id < 3";
var sql_results; // This is my variable

connection.connect();

connection.query(sql_query, function (error, results, fields) {
  if (error) throw error;
  console.log(results); // This is mysql results
  sql_results = results; // I am sure this works but I can't see
  console.log(sql_results);
});

connection.end();

answered Aug 21 '19 at 13:37

Victor P

1,496
17
29

Well, yes I can do by this way but it doesn't solve my problem because I want to use sql_results variable separate from the query function. If want to use some other functions after ```connection.end()``` line I will still have same asynchronous problem. – X999 Aug 21 '19 at 13:43
@Qencezero, I suggest you re-read my answer as it explains how callbacks work. You can't do what you are trying to do. – Victor P Aug 22 '19 at 01:48
@Danmoreng the question was edited after I posted my answer. – Victor P Aug 22 '19 at 01:53

Node.js MySQL query can't assign MySQL results to a variable

3 Answers3