Node.js Async | 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 a place (id, coordinates).
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){
var json_bbdd;  
 //------------ BBDD CONNECTION----------------
            var pg = require('pg'); 
            var conString = "postgres://postgres:postgres2@localhost/places";

            var client = new pg.Client(conString);
            client.connect(function(err) {
              if(err) {
                 console.log('could not connect to postgres');
              }
              client.query('SELECT * from places where id=3276', function(err, result) {
                if(err) {
                 console.log('error running query'); 
                }
                json_bbdd=result.rows[0];

                var coords = JSON.parse(json_bbdd.json).coordinates;
                var id = json_bbdd.id;
                var input = {
                    query: coords[1] + ',' + coords[0] ,
                    format: 'JSON',
                    fx: '',
                    callback: 'MarineWeatherCallback'
                };


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

                $.ajax({
                type: 'GET',
                url: url,
                async: false,
                contentType: "application/json",
                dataType: 'jsonp',
                success: function (json) {

                    var date= json.data.weather[0].date;

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

                          var hourly = json.data.weather[0].hourly[i];
                          var time= hourly.time;

                            client.query('INSERT into parte (id, date, time) VALUES($1, $2, $3)', [id, date, time], 
                            function(err, result) {
                                if (err) {
                                    console.log(err);
                                } else {
                                    console.log('row inserted: ' + id + ' ' + time);
                                }

                            }); 

                          } // FOR

                    },
                    error: function (e) {
                        console.log(e.message);
                    }
                });

                client.end();
              });
            });
});

The steps 1 and 2 are performed perfectly. The third step, on the other hand, does nothing and it doesn't even throw an error.

I read in this post: node-postgres will not insert data, but doesn't throw errors either that using async module could help but i have no idea how to rewrite the code. I need some help.

Regards, Aitor

Answer 1

Ok, now cam up with another problem...i was doubting of creating a new post but i think that maybe could have relation with the previous post.

The aim is to read from the database instead of one place 3 places and do the same process than before for each one. The code is as follows (with the changes proposed by ivoszz):

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 2

I didn't test your snippet, I can only help you with things which looks bad to my eyes.

1. It is better not to use jQuery on node server. There is excellent library called request to do remote http requests.
2. You should better handle database errors because in your example your code will continue after DB error.
3. You are calling client.end() too early and at the time when you try to insert data to the database a connection is already closed. You have to move client.end() at the end of success and error functions and wait to all callbacks are done.
4. I think it is also better to use connection pool instead of Client.
5. You can possibly use JSON type in PostgreSQL to avoid serializing/deserializing JSON data in your code.

Here is revised example(untested). I didn't replace jQuery here, some minor tweaking included.

var pg = require('pg'); 
var conString = "postgres://postgres:postgres2@localhost/places";

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

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

      json_bbdd = result.rows[0];

      var coords = JSON.parse(json_bbdd.json).coordinates;
      var id = json_bbdd.id;
      var input = {
        query: coords[1] + ',' + coords[0] ,
        format: 'JSON',
        fx: '',
        callback: 'MarineWeatherCallback'
      };


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

      $.ajax({
        type: 'GET',
        url: url,
        async: false,
        contentType: "application/json",
        dataType: 'jsonp',
        success: function (json) {

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

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

            var hourly = json.data.weather[0].hourly[i];
            var time= hourly.time;

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

          } // FOR
        },
        error: function (e) {
           console.error(e.message);
           done();    // done(); is rough equivalent of client.end(); 
           return next(new Error('Http error'));
        }
      });
    });
  });
});