Asynchronous Database Queries with PostgreSQL in N

Question

Using Node.js and the node-postgres module to communicate with a database, I'm attempting to write a function that accepts an array of queries and callbacks and executes them all asynchronously using the same database connection. The function accepts a two-dimensional array and calling it looks like this:

perform_queries_async([
  ['SELECT COUNT(id) as count FROM ideas', function(result) {
    console.log("FUNCTION 1");
  }],
  ["INSERT INTO ideas (name) VALUES ('test')", function(result) {
    console.log("FUNCTION 2");
  }]
]);

And the function iterates over the array, creating a query for each sub-array, like so:

function perform_queries_async(queries) {
  var client = new pg.Client(process.env.DATABASE_URL);

  for(var i=0; i<queries.length; i++) {
    var q = queries[i];

    client.query(q[0], function(err, result) {
      if(err) {
        console.log(err);
      } else {
        q[1](result);
      }
    });
  }

  client.on('drain', function() {
    console.log("drained");
    client.end();
  });

  client.connect();
}

When I ran the above code, I expected to see output like this:

FUNCTION 1
FUNCTION 2
drained

However, the output bizarrely appears like so:

FUNCTION 2
drained
FUNCTION 2

Not only is the second function getting called for both requests, it also seems as though the drain code is getting called before the client's queue of queries is finished running...yet the second query still runs perfectly fine even though the client.end() code ostensibly killed the client once the event is called.

I've been tearing my hair out about this for hours. I tried hardcoding in my sample array (thus removing the for loop), and my code worked as expected, which leads me to believe that there is some problem with my loop that I'm not seeing.

Any ideas on why this might be happening would be greatly appreciated.

Answer 1

A victim of the famous Javascript closure/loop gotcha. See my (and other) answers here:

I am trying to open 10 websocket connections with nodejs, but somehow my loop doesnt work

Basically, at the time your callback is executed, q is set to the last element of the input array. The way around it is to dynamically generate the closure.

Answer 2

It will be good to execute this using async module . It will help you to reuse the code also . and will make the code more readable . I just love the auto function provided by async module Ref: https://github.com/caolan/async

Answer 3

The simplest way to properly capture the value of the q variable in a closure in modern JavaScript is to use forEach:

queries.forEach(function(q) {
    client.query(q[0], function(err, result) {
      if(err) {
        console.log(err);
      } else {
        q[1](result);
      }
    });
 });

If you don't capture the value, your code reflects the last value that q had, as the callback function executed later, in the context of the containing function.

forEach, by using a callback function isolates and captures the value of q so it can be properly evaluated by the inner callback.