current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question
    
Why would you want to? What possible benefit would there be? –  Richard Huxton Dec 16 '13 at 15:34
    
I would be able to execute "insert" query for several times synchronously –  Igal Dec 17 '13 at 8:37
    
Yes, but why? Why use multiple connections for multiple inserts? Do you think it will be faster or something? –  Richard Huxton Dec 17 '13 at 9:13
    
Oh, now I got your question :)... I've tried to take the client creation out of the each scope, but then I'm getting memory leak exception. –  Igal Dec 17 '13 at 9:21

2 Answers 2

up vote -2 down vote

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.

share|improve this answer
    
First thanks for the answer ! In pg-node, client query is the query execution –  Igal Dec 17 '13 at 11:23
up vote 0 down vote accepted

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();
        }
share|improve this answer
    
You are creating a new connection for every insert statement. That is insane. –  Richard Huxton Dec 18 '13 at 16:03
    
I'm closing it just after the callback from query execution is returned... –  Igal Dec 19 '13 at 13:06
    
Yes, but you are having to set up and close down a connection for every insert –  Richard Huxton Dec 19 '13 at 14:15
    
So ? stateless connection to DB... I can also implement it by using a connection pool, and then I would need to get a Done in connection callback, and then just to use it instead calling client.end() –  Igal Dec 19 '13 at 14:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.