Nodejs express and promises not doing what I expect

Issues with the code

Ok, there are a lot of issues here so first things first.

        connection.query('...', function (err, rows) {
            connection.release();
            if (!err) {
                return rows;
            } else {
                return false;
            }
        });

This will not work because you are returning data to the caller, which is the database query that calls your callback with err and rows and doesn’t care about the return value of your callback.

What you need to do is to call some other function or method when you have the rows or when you don’t.

You are calling:

var rows = loginM.findUser(req.body, res);

and you expect to get the rows there, but you won’t. What you’ll get is undefined and you’ll get it quicker than the database query is even started. It works like this:

me.findUser = function(params, res) {
    // (1) you save the username in a variable
    var username = params.username;

    // (2) you pass a function to getConnection method
    pool.getConnection(function (err, connection) {
        console.log("Connection ");

        if (err) {
            console.log("ERROR 1 ");
            res.send({"code": 100, "status": "Error in connection database"});
            return;
        }

        connection.query('select Id, Name, Password from Users ' +
            'where Users.Name = ?', [username], function (err, rows) {
            connection.release();
            if (!err) {
                return rows;
            } else {
                return false;
            }
        });

        //connection.on('error', function (err) {
        //    res.send({"code": 100, "status": "Error in connection database"});
        //    return;
        //});
    });

    // (3) you end a function and implicitly return undefined
}

The pool.getConnection method returns immediately after you pass a function, before the connection to the database is even made. Then, after some time, that function that you passed to that method may get called, but it will be long after you already returned undefined to the code that wanted a value in:

var rows = loginM.findUser(req.body, res);

Instead of returning values from callbacks you need to call some other functions or methods from them (like some callbacks that you need to call, or a method to resolve a promise).

Returning a value is a synchronous concept and will not work for asynchronous code.

---

How promises should be used

Now, if your function returned a promise:

me.findUser = function(params, res) {
    var username = params.username;

    return new Promise(function (res, rej) {

      pool.getConnection(function (err, connection) {
        console.log("Connection ");

        if (err) {
          rej('db error');
        } else {
          connection.query('...', [username], function (err, rows) {
            connection.release();
            if (!err) {
                res(rows);
            } else {
                rej('other error');
            }
        });
      });
    });
}

then you’ll be able to use it in some other part of your code in a way like this:

app.post('/login/', function(req, res, next) {

    var promise = new Promise(function (resolve, reject) {

        // rows is a promise now:
        var rows = loginM.findUser(req.body, res);

        rows.then(function (rowsValue) {
            console.log("Success");
            resolve(rowsValue);
        }).catch(function (err) {
            console.log("Failed");
            reject(err);
        });
    });
    // ...

Explanation

In summary, if you are running an asynchronous operation – like a database query – then you can’t have the value immediately like this:

var value = query();

because the server would need to block waiting for the database before it could execute the assignment – and this is what happens in every language with synchronous, blocking I/O (that’s why you need to have threads in those languages so that other things can be done while that thread is blocked).

In Node you can either use a callback function that you pass to the asynchronous function to get called when it has data:

query(function (error, data) {
  if (error) {
    // we have error
  } else {
    // we have data
  }
});
otherCode();

Or you can get a promise:

var promise = query();
promise.then(function (data) {
  // we have data
}).catch(function (error) {
  // we have error
});
otherCode();

But in both cases otherCode() will be run immediately after registering your callback or promise handlers, before the query has any data – that is no blocking has to be done.

Summary

The whole idea is that in an asynchronous, non-blocking, single-threaded environment like Node.JS you never do more than one thing at a time – but you can wait for a lot of things. But you don’t just wait for something and do nothing while you’re waiting, you schedule other things, wait for more things, and eventually you get called back when it’s ready.

Actually I wrote a short story on Medium to illustrate that concept: Nonblacking I/O on the planet Asynchronia256/16 – A short story loosely based on uncertain facts.