Promises inside for loops / promise.all, using psql (pg-promise) in node

Question

Hello I am new to Promises and am stuck on how to wait for all promises within a for loop to resolve before going to the next then(). I have seen a couple of promise.all examples, but it wasn't clear to me how to adapt them for my the following code. It currently goes to the next then() after the for loop and resolves before the for loop is complete. Any help is appreciated!

I am using pg-promise (psql with promises).

Original code:

function getTeamMembers(aTeam) {
    let promise = new Promise(function(resolve, reject) {
      db.getTeamMembers(aTeam.tid) //return sql results rows
        .then(function(rows){
          for(let i=0; i<rows.length; ++i) { //loop through each result row
            getUserByUsername(rows[i].username)
              .then(function(cfUser) { //add user from row to aTeam object                    
                aTeam.addMember(cfUser);
              })
              .catch(function(e) {
                reject(e);
              });
          }
        })
        .then(function(){
          console.log(aTeam); //confirm added properly
          resolve(aTeam); //resolve object
        })
        .catch(function(e) {
          console.log('addMemberToTeamByUsername: '+e.stack);
          reject(e);
        });
    });
    return promise;
  }

Answer 1

I am the author of pg-promise.

Below is a few considerations about invalid use of Promise.all in this context within an answer that's now removed.

---

When using a promise-based interface that represents physical resources, it is important to understand the physical context that’s used. Without it you are risking to run into bottlenecks from the simple fact that physical resources do not scale like your generic promise solutions.

In case of pg-promise your physical context is made up by two things:

• Query strings to be piped through Node.js IO
• Connection context provided by the connection pool

Each query request acquires and releases a connection from the connection pool, which is a very limited physical resource. Your default size of the pool is 10, as set by the underlying driver node-postgres. And although you can increase it to up to 100, doing so will start creating an overload on the connections management, so it is not that scalable. A typical increase is to be set to 20, which is about the average.

So, if you use Promise.all on your array of queries, your app will deplete the pool almost instantly, and any next request into your service will just sit there waiting for available connections.

Such solution cannot scale at all, and it is listed as an anti-pattern for query execution here: Tasks versus root/direct queries.

Basically, what it explains there is you must execute multiple queries through tasks:

• Method task, if you are not changing data
• Method tx (transaction), if you are changing the data

This way you can pipe all your queries through a single connection, which is essential to allow scalability of your service.

---

There's plenty of examples within Learn By Example tutorial for both Tasks and Transactions.

---

And considering that you are trying to get mutiple parent rows, and then multiple child rows, you should look at this question: get JOIN table as array of results with PostgreSQL/NodeJS.

I would also suggest reading the Performance Boost article, to better understand the physical limitations of executing mutiple queries, and how to solve them.

Example

function getTeamMembers(aTeam) {
    return db.task(t=> {
        return t.map('SELECT * FROM team_members WHERE id=$1', aTeam.id, tm=> {
            return t.any('SELECT * FROM users WHERE name=$1', tm.username)
                .then(users=> {
                    tm.users = users;
                    return tm;
                });
        }).then(t.batch);
    });
}

// usage example:

getTeamMembers({id: 123})
    .then(members=> {
        // members = array of member objects
    })
    .catch(error=> {
        // error
    });

This is not the only way to do it, but it is the shortest ;)

This approach is given a better consideration in the following question: get JOIN table as array of results with PostgreSQL/NodeJS.