MySQL Pool not creating connection in helper function (node.js/express)

Question

I have the following setup for an express app. I am trying to check if a token provided in the route is legitimate using a helper function, then if it is decide which res to send to the requester. No matter what though, Invalid Token: -1 is returned whether or not a legitimate token is provided.

var pool = sql.createPool({
  connectionLimit: 50,
  host : "localhost",
  port : 3306,
  user : "bla",
  password : "bla",
  database : "bla",
  multipleStatements: true
});

...

function authorize(token) {

  var check = "select * from User where Token=?";
  var status = -1;

  pool.getConnection(function (err, connection) {
    connection.query(check, [token], function (err, rows) {
        if (err) {
          status = err;
        };
        status = rows.length;
    });
    connection.release();
  });

  return status;

}

app.get('/user/token/:Token', function(req, res) {

  var token = req.params.Token;
  var date = currentDate();
  var endpoint = req.route;
  var stmt = "insert into APIAccess (Token, Endpoint, Date) values (?, ?, ?);select * from User where Token=?";
  var auth = authorize(token);

  pool.getConnection(function (err, connection) {
      if (auth == 1) {
        connection.query(stmt, [token, endpoint, date, token], function (err, rows) {
            if (err) throw err;
            res.send(JSON.stringify(rows[1]));
        });
        connection.release();
      } else {
        res.send("Invalid token: " + auth);
      }
  });

});

What is going on here? I know the two statements in the multipleStatements set worked fine before I tried implementing this authorization bit, and that they aren't even being reached here, and that this code is syntactically correct...

Bergur · Accepted Answer · 2021-02-16 00:57:50Z

That's because you're dealing with async code. It doesn't get executed in the same order you write it.

That's because it takes time to connect to the database and get the data from it, meanwhile your code is still running.

What you think is happening is

Set default value of status to -1
Override that value with what comes from my database
Return the status

But the getConneciton and query functions are async and have callback. That means you're saying

getConnection AND whenever that is done do whatever inside that function

query AND whenever that is done then set the status to the row length.

So what is happening is

Set the default value of status to -1
Connect to the database (async code with callback)
Wait for the connection
Return the status (We went here because the code is still running while we wait for the connection)
I have my connection, lets query
Query that database
Wait for query to finish
status = row.length

You can check it by doing console.log

function authorize(token) {
  console.log('starting function')

  var check = "select * from User where Token=?";
  var status = -1;
  console.log('default value set')

  pool.getConnection(function (err, connection) {
    console.log('got connection')
    connection.query(check, [token], function (err, rows) {
        if (err) {
          status = err;
        };
        console.log('query finished')
        status = rows.length;
    });
    connection.release();
    console.log('connection released')

  });
  console.log('returning status', status)
  return status;
}

To fix this you need to make a callback, to return you the value. You also don't need to create a connection and release it after use. Just use the pool.

function authorize(token, callback) {
  console.log('starting function')

  var check = "select * from User where Token=?";
  var status = -1;
  console.log('default value set')

  pool.query(check, [token], function (err, rows) {
        if (err) {
          status = err;
        };       
        status = rows.length;    
        callback(status)
    });
  });
}

And then in your code

authorize(token, function(status) { console.log('i have my status', status) if (auth == 1) { ... } })

To avoid callback inside callback, you should check out promises, promise chains and async/await to get clearer and more readable code.

Collectives™ on Stack Overflow

MySQL Pool not creating connection in helper function (node.js/express)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related