Using for loops and pg-bluebird

Question

How do you incorporate for loops using pg-bluebird (https://github.com/aphel-bilisim-hizmetleri/pg-bluebird)?

I want to first get the id, latitude, and longitude for each of my locations (from the locations table). Then for each location I need to do an http request (this is where I want the for loop). Then after I have done the request the data received should be inserted in the table Aeris.

I'm new to node.js and just learning about promises. Am I using pg-bluebird as it is intended?

Here's what I have so far:

var request = require("request");
var Pgb = require("pg-bluebird");

exports.retrieve = function(){

  var pgb = new Pgb();

  var cnn;

  pgb.connect(DB_URL)
  .then(function (connection) {

    cnn = connection;
    var queryString = 'SELECT id, latitude, longitude FROM locations';

    return cnn.client.query(queryString);
 })
 .then(function (result) {

    console.log(result.rows);

    //this needs to run for each location 
    //this needs to access result.rows[0].id then 
    //result.rows[1].id and so on
    location_id = result.rows.id; 

    lat = result.rows.latitude;
    lng = result.rows.longitude;
    console.log("Lat=" + lat + " Lng=" + lng);
    var aerisUrl = AERIS_URL+
        "?p="+lat+","+lng+
        "&radius=10miles"+
        "&client_id="+CLIENT_ID+
        "&client_secret="+CLIENT_SECRET;

    request.get(aerisUrl,function(err,response,body){

        //do something with request data
    });

    return cnn.client.query();

})
.then(function (result, aerisResults) {
        //insert data in aeris table
        return cnn.client.query(queryString);
})
.then(function (result){

    console.log(result.rows);

    cnn.done();
})
.catch(function (error) {

      console.log(error);
});

Answer 1

First of all, use pg-promise for communication with the database. Second, you need to separate your http request logic into a function that would return a promise, so you can stack up all such requests into an array and then execute via promise.all. All of that is shown in the code below.

var request = require("request");
var promise = require("bluebird");
var pgp = require("pg-promise")({promiseLib: promise});

var db = pgp(DB_URL);

function createRequest(r) {

    var aerisUrl = AERIS_URL +
        "?p=" + r.latitude + "," + r.longitude +
        "&radius=10miles" +
        "&client_id=" + CLIENT_ID +
        "&client_secret=" + CLIENT_SECRET;

    return new promise.Promise(function (resolve, reject) {
        request.get(aerisUrl, function (err, response, body) {
            // do something with the request data and
            // then call either resolve(), if successful,
            // or reject(), if failed;
        });
    });
}

db.query("SELECT id, latitude, longitude FROM locations")
    .then(function (rows) {
        var req = rows.map(function (r) {
            return createRequest(r);
        });
        return promise.all(req);
    })
    .then(function (data) {
        // all http requests resolved successfully,
        // time to insert the data into table Aeris:
        return db.query("insert into Aeris(fields) values($1, $2,...)", [/*values*/]);
    })
    .then(function () {
        // successfully inserted data into table Aeris;

        // all done!
    })
    .catch(function (error) {
        // Ops, something failed;
        console.log("ERROR:", error);
    });