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: stackoverflow.com/questions/25399725/… and stackoverflow.com/questions/14220321/… — Paul
– Paul, Commented Jul 16, 2018 at 22:16

Paul · Accepted Answer · 2018-07-16 22:50:45Z

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.

Sanju Varghese · Accepted Answer · 2018-07-16 21:07:35Z

1

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

answered Jul 16, 2018 at 21:07

Sanju Varghese

5252 silver badges4 bronze badges

Comments

3ocene · Accepted Answer · 2018-07-16 21:17:23Z

1

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.

answered Jul 16, 2018 at 21:17

3ocene

2,2311 gold badge19 silver badges31 bronze badges

2 Comments

nadhirxz Over a year ago

Isn't there a way for me to console.log the data ?

3ocene Over a year ago

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.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related