Node.js | insert into postgresql database results from api

Question

I am quite newbie with node.js. What i am trying to achieve is the following:

1. Connect to my postgresql database and get info of three places (id, coordinates). For each place obtained i need:
2. call a weather api and get the info of that spot using the coordinates obtained in the previous step.
3. Insert the returned json in the database. I get 8 hourly objects, with the weather info every 3 hours (0,3,6,9,12,15,18,21). I need to iterate through this objects and the store them in 8 records in the database.

I wrote the following code:

app.get('/getapi', function(req, res, next){  
//------------ BBDD CONNECTION----------------

  pg.connect(conString, function(err, client, done) {
    if(err) {
      // example how can you handle errors
      console.error('could not connect to postgres',err);
      return next(new Error('Database error'));
    }
    client.query('SELECT * from places where id>3274 and id<3278', function(err, result) {
      if(err) {
        console.error('error running query',err);
        done();
        return next(new Error('Database error'));
      }

      var first_callback = 0;
      for (var y=0;  y<result.rows.length; y++) {
          var coords = JSON.parse(result.rows[y].json).coordinates;
          var id = result.rows[y].id;
          var input = {
            query: coords[1] + ',' + coords[0] ,
            format: 'JSON',
            fx: ''
          };

          var url = _PremiumApiBaseURL + "marine.ashx?q=" + input.query + "&format=" + input.format + "&fx=" + input.fx + "&key=" + _PremiumApiKey;

          request(url, function(err, resp, body) {
            body = JSON.parse(body);
            if (!err && resp.statusCode == 200) {

              var date = body.data.weather[0].date;
              var callbacks = 0;

              for (var i=0; i < 8; i++) {


                var hourly = body.data.weather[0].hourly[i];

                client.query(
                  'INSERT into parte (id, date, time) VALUES($1, $2, $3)',
                  [id, date, hourly.time], 
                  function(err, result) {
                    if (err) {
                      console.log(err);
                    } else {
                      console.log('row inserted: ' + id + ' iteration ' + i);
                    }
                    callbacks++;
                    if (callbacks === 8) {
                      console.log('All callbacks done!from id '+id);
                      //done();    // done(); is rough equivalent of client.end();
                      //res.send("done");
                    }
                }); 

              } // FOR
            }
            else { // if the API http request throws an error
               console.error(err);
               done();    // done(); is rough equivalent of client.end(); 
               return next(new Error('Http API error'));
            } 
          }); // REQUEST API URL

      first_callback++;
      if (first_callback === result.rows.length-1) {
        console.log('All global callbacks done!');
        done();    // done(); is rough equivalent of client.end();
        res.send("done");
      }} 
    }); // SELECT from pg
  }); // CONNECT to pg
}); // app.get

I don't know why it tries to insert the id=3277 three times instead of inserting id=3275, id=3276 and then id=3277... what it does instead is: it inserts the first 8 records ok the first time (id=3277), but then it throws an error saying that the records are already inserted (primary key=id,date,time) with id 3277...

It seems that first does the 3 iterations of the first FOR and then does the 3 iteration of the second FOR but with the info of the last iteration(place). I can't understand it very well...

Answer 1

Thanks for your answer. I think i understood the problem more or less. I decided to use your last solution but after three hours fighting with the code i finally decided to write again.

How can i pass the id variable into the api resquests callback function as it only has three fixed arguments?

I mean i can't do this:

request(url, function(err, resp, body, id) {//using here the returned body and the id too..}

and i don't know how to solve this problem as it won't let me add another argument, i get errors...

Regards,

Answer 2

You're encountering some unique scoping issues that are common among new JavaScript developers. The first thing you have to realize is that JavaScript scope is bound to a function, particularly the function it was defined in. If you define a function inside of another function, then you effectively bind that function to the scope of the current function as well. This leads to weird behavior. For example:

var someMsg = "Hello, World"; // English

var showMsg = function() {
  console.log(someMsg);
};

setTimeout(showMsg, 2000); // Fire off after 2 seconds
someMsg = "Hola, Mundo"; // Spanish

What you'll see print is "Hola, Mundo" where you might (bear with the contrived example) have been expecting "Hello, World". There are three simple solutions - the first is pass the value as an argument to the function:

var showMsg = function(msg) {
  // ...
};

And the second is provide a wrapper to scope those variables. This wrapper can be an IIFE because you won't need to access it directly.

var someMsg = "Hello, World";

(function(someMsg) {
  setTimeout(function() {
    console.log(showMsg);
  }, 2000);
)(someMsg);

someMsg = "Hola, Mundo";

Now the output is exactly as we expect, "Hello, World" and we did this by shadowing (only because of the same variable name) the original value by an argument containing it's current value at the time the IIFE is executed. So when we change the value of the outer someMsg later it doesn't change the value of someMsg inside the IIFE. This is useful (and required) when looping over values in JavaScript due to nesting functions for complex calls like this.

Your example (heavily simplified) would be:

// Do some stuff
for (var y=0;  y<result.rows.length; y++) {
  // ...
  var id = result.rows[y].id;

  (function(recordId) {
    // The current body of this for loop that requires id
  })(id);
}
// More stuff

If you notice, in that last example I didn't use id as the parameter name and I tend to do that to prevent confusion or clarify further the purpose of the value for the inner function.

The final solution is to use functions to build functions with a specific scope. You could define your generator:

var generateShowFn = function(someMsg) {
  return function() {
    console.log(someMsg);
  };
};

var someMsg = "Hello, World";

setTimeout(generateShowFn(someMsg), 2000);

someMsg = "Hola, Mundo";

You still get the correct results. I prefer this method over the previous two because it allows me to write callback code inside of an object, as another member function or logical step in a process. This also cuts back on "nest hell" where you have one nested function inside of another and another and etc...

I hope this provides some information for you and gets you back on the right track. There are some other really interesting things you should learn about JavaScript before you invest in any large amount of work on Node.js (or, for that matter, any JavaScript heavy website).