How to get value from db.any() promise in pg-promise?

Question

I use pg-promise and i tried to make a request to database in user model to get an array of all users like this:

 exports.getAllUsers = function () {
      let users = db.any("SELECT * FROM users;").then(function (data) {
        return data; // in debug mode data is my expected array 
      });
      return users;
    };

But when i make get request from controller, it returns promise Promise { <pending> }, not an array of json objects that I expected. There is a controller:

exports.getUsers = function (req, res) {
   let users = userModel.getAllUsers();
   console.log(users); // here it outputs Promise { <pending> }
   res.json(users);
 };

How to get array instead of promise?

Answer 1

Let's break down your code. You are exporting a function of your design, getUsers, which is supposed to return a sequence of user objects. But it does not -- db.any(...) returns a Promise. How else do you think you can call then on the returned object? then is a member function of the Promise class, and you pass another function to then to work with the actual data (passed as parameter to the function you pass to then). Meaning that indeed data, as passed to the then call invoked on the return value of db.any(...) call, is the sequence of users you're after.

The mistake you are making is to assume that if you return data from the callback passed to then, it will become the return value of the then(...) call. It will not. then always returns a Promise -- whatever you return from the callback to then will become the resolved value of the returned promise, but it is the promise that is returned and that is why your users variable is a Promise.

You need to read more about promises, and how they are resolved asynchronously (between running your scripts), and how to synchronise your code on them resolving.

Hint: Use the await keyword to wait for and use the value given a promise, like the value you return from the callback that you pass to your then(...) call, or accept the design of your getUsers function returning a promise and adapt the rest of your code to work with that, without use of await.

Your getAllUsers function can be reduced to:

exports.getAllUsers = function () {
    return db.any("SELECT * FROM users;");
}

...and with await you can then use it like:

let users = await getUsers();

The statement above has to be part of a function that is tagged as async though -- ECMAScript requires marking these explicitly in order to allow use of await expressions:

async function whatever() {
    let users = await getUsers();
    /// ...
}

Execution of a script that invokes an async function such as one containing the statement above, will be interrupted by the JavaScript interpreter at each await expression, and resumed when the promise expressed after the await keyword, resolves to a value, with the value being assigned to the users variable.

Using async and await does require a version of Node.js that can support them, but current release does (as of the time of writing this).

Otherwise you can still keep the getUsers function, but you need to use it differently since you can't use await -- having to use promises as one did prior to introduction of await and async, like:

getUsers().then(users => {
    /// Do something with the `users` array.
});

At any rate, it looks like you have some holes in your understanding of how promises work, and I recommend you fill these holes by reading about them this time and not just going straight through to the pg-promise API, which builds on them.

Answer 2

Like you already found out userModel.getAllUsers() will return a promise and not an array. You need to wait for this promise to be resolved. This can be done using the an async function.

exports.getUsers = async function (req, res) {
   let users = await userModel.getAllUsers();
   console.log(users);
   res.json(users);
};

Note that an async function will always return a promise.

Or you can make use of then method of the returned promise.

exports.getUsers = function (req, res) {
   userModel.getAllUsers().then(users => {
     console.log(users);
     res.json(users);
   });
};