Async pg-node query execution on object's array

Question

I have an object's array which I need to iterate through, and insert each item into the DB (postgres). I'm using _.each in order to iterate through the array.

arr = [ 
      {name: 'Aaron',    description: 'First'},
      {name: 'Brian',    description: 'Second'},
      {name: 'Chris',    description: 'Third'}
]

    var i = 0;
    _.each(array, function(lt){     
        var client = new pg.Client(self.getConnString());
        client.connect(function(err) {
            if (err) {
                          //doSomething//
            }
            var sql = 'insert into load_test (name,description) values(\''+lt.name+'\', \''+lt.description+'\')';
            console.log(i + " <- query: " + lt.name + " desc: " + lt.description);
            query = client.query(sql);
            query.on('end', client.end.bind(client));
            i++;
        });
    });//each

How can I write this function (_each) in the way that it will be async for each query execution?

Thanks

Answer 1

Finally, the solution looks like this :

self.InsertLT = function(index, callback){
        var client = new pg.Client(self.getConnString());
        if (index < arr.length){
            //console.log('index = ' + index);
            var sql = 'insert into table (name,description) values(\''+arr[index].name+'\', \''+arr[index].description+'\')';
            //console.log(sql);
            client.connect(function(err) {
                if (err) { 
                    logger.error(self.ERR_CONNECT_TO_DB + ' --> ' + err);
                    callback(-1);    
                }   
                client.query(sql, function(err, result){    
                    if (err){    
                        logger.error(self.ERR_RUNNING_QUERY + ' --> ' + err);    
                        callback(-1);    
                    }    
                    else{
                        client.end();
                        self.InsertLT(++index,callback);
                    }
                });//query
            });
        }
        else{
            callback();
        }

Answer 2

OK. I think I understand where you're coming from now.

You want to make three changes I'd say.

1. Create the connection ("client") outside the loop
2. Use parameterised queries
3. Wrap the inserts in a transaction (so they all work or all fail together)

I don't do node, but reworking your code it would look something like:

arr = [ 
      {name: 'Aaron',    description: 'First'},
      {name: 'Brian',    description: 'Second'},
      {name: 'Chris',    description: 'Third'}
];

var i = 0;
var client = new pg.Client(self.getConnString());
client.connect(function(err) {
    if (err) {
        //doSomething//
    }
    // I don't know how node does parameterised queries, but it will look
    // something like this
    var sql   = 'insert into load_test (name,description) values(?,?)';
    var query = client.query(sql);
    _.each(arr, function(lt) {
        console.log(i + " <- query: " + lt.name + " desc: " + lt.description);
        // There might be separate bind + excecute calls
        query.execute(lt.name, lt.description);
        i++;
    });
// There might be a client.disconnect() needed here

You'll need to consult the documentation for the proper syntax.

Typically you'd connect at application startup and keep the database connection around until it quits. I'm guessing you've got references to the database connection still referenced in a closure somewhere. If your queries need a "completed()" or "discard()" method or similar, make sure you call that to release resources too.