How to put the results of MySQL database in a variable in Node.js?

Question

I have a problem in my node.js application. I'm connecting to the database and getting the data

let users = {};
let currentUser = "example"; // this variable changes every time a user connects
connection.query('SELECT * FROM users WHERE name="'+currentUser+'"', function (err, result) {
  if (err) console.log(err);
  users[currentUser] = result[0];
});
console.log(users[currentUser]);

When I try to console.log the result[0] from inside the function, it returns this:

RowDataPacket {
n: 11,
id: 'VKhDKmXF1s',
name: 'user3',
status: 'online',
socketID: 'JbZLNjKQK15ZkzTXAAAB',
level: 0,
xp: 0,
reg_date: 2018-07-16T20:37:45.000Z }

I want to put that result from MySQL into users.example or something like that, but when I try the code it returns undefined. So I tried console.log(users[currentUser].id) as an example and it shows an error

TypeError: Cannot read property 'id' of undefined

So how do I put the data from the result inside my variable users[currentUser]?

Similar issue, different code: https://stackoverflow.com/questions/25399725/nodejs-get-async-return-value-callback and https://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call — Paul, Jul 16 '18 at 22:16

Paul · Accepted Answer · 2018-07-16T22:50:45.383

So how do I put the data from the result inside my variable users[currentUser]?

That's happening. The problem is how it is being tested.

The correct test is with the console.log inside the callback function(err, result)

function (err, result) {
  if (err) console.log(err);
  users[currentUser] = result[0];
  console.log(users[currentUser]);
});

Issue: users[currentUser] is still undefined outside that function

Well, yes and no. It is undefined in code that executes before the callback is fired.

And how do I fix that?

Anything that needs the result of the query must be executed from within the callback function, because that's the only code location where you know for certain that the data exists.

Well, I need that data outside in a global variable.

You can stick the query data in a global, but that doesn't solve the timing issue of only accessing that global when it is defined and contains current data. That will cause lots of frustration.

If you don't want to call one or more specific functions to process the query data within the callback, an alternative is to use a nodejs EventEmitter to coordinate the data production and data consumption.

Yet another alternative is to not use a callback function, and use Promise and/or async/await, both of which are supported by modern nodejs. This alternative doesn't involve global variables, but provides different ways to code the fact that some operations need to wait for the results of others.

score 1 · Answer 2 · answered Jul 16 '18 at 21:07

1

connection.query is an async call. The console.log is called before the query fetches the data from db.

answered Jul 16 '18 at 21:07

Sanju Varghese

487
2
4

score 1 · Answer 3 · answered Jul 16 '18 at 21:17

Because you're using a callback, your console.log is happening before the result comes back from the database. Additionally, you have a type in your code user -> users.

let users = {};
let currentUser = "example"; // this variable changes every time a user connects
connection.query('SELECT * FROM users WHERE name="'+currentUser+'"', function (err, result) {
  if (err) console.log(err);
  users[currentUser] = result[0];
  console.log(users[currentUser]);
});

Side note: research "SQL Injection" before you use this code. The way you're building your query opens you up for anyone to access your database. Using a library like squel.js or knex.js will help you avoid this.

For an explanation of why things happen in the order they do, take a look at the JavaScript event loop.

Yes. Do it within the callback. What happens is node executes all of the top level code and then puts the callback in a "I should call this later" queue so when it executes the console.log, the callback hasn't been executed yet. — 3ocene, Jul 16 '18 at 22:17

How to put the results of MySQL database in a variable in Node.js?

3 Answers3