node-postgres : query not executing in sequence

Question

i have kept insert & update code in 2 different files and based on condition always insert should execute first and then update.but somehow update executes first then insert

test.js : simplified code

i am using these packages :pg , uuid

var pg = require('pg');
var uuid = require('node-uuid').v4;
var id = uuid().toString();
var conString = 'postgres://postgres:[email protected]:5432/testdb';

// ------INSERT
pg.connect(conString, function(err, client, done) {

    console.log('Executing Insert query');

    client.query('insert into testdb (id,data,iscancelled) values ($1,$2,$3)',[id,'hello','no'],  function(err, result) {

        done();

        if(err) { return console.error('error running query', err);  }

        console.log('finished executing Insert query');

    });
});

// ------UPDATE
pg.connect(conString, function(err, client, done) {

    console.log('Executing update query');

    client.query("update testdb set iscancelled = 'yes' where id = $1",[id],  function(err, result) {

        done();

        if(err) { return console.error('error running query', err);  }

        console.log('finished executing Update query');

    });
});

output

tom@tom:~$node test.js
Executing Insert query
Executing update query
finished executing Update query //WHY UPDATE FINISHES FIRST
finished executing Insert query

Note :

this problem can be easily solved by using async.But my insert code and update code are in different files and depending on some situation update code might execute.so don't want to use async

Problem

Even though Insert query goes to execute first why does update finishes first in output

am i missing any thing ..?

Answer 1

Lets solve this question step by step

you "stated so don't want to use async" libraries

solution 1 :

if PostgreSQL make update faster, update will return result before insert. If you want start executing update query only after finishing insert then

you should set connection pool capacity to 1.

pg.defaults.poolSize = 1

but you should do this before any pg.connect()

The connect method retrieves a Client from the client pool, or if all pooled clients are busy and the pool is not full, the connect method will create a new client passing its first argument directly to the Client constructor. In either case, your supplied callback will only be called when the Client is ready to issue queries or an error is encountered. The callback will be called once and only once for each invocation of connect.

Conclusion : your queries will execute in sequence.BUT BUT BUT this solution is BAD for scaling app as there will be always only one connection serving all users .So till one connection is serving one user , other users will have to wait for response.

Solution 2 :

you also stated "i have kept insert & update code in 2 different files"

it looks like you need to designed your code in such a way that it you are able to use asynchronus libraries , that solves this problem

Answer 2

As I already mentioned, the only way to ensure that update function will be fired only after insert function is done, is to call it inside of insert function callback. That are the basics of asynchronous programming.

pg.connect(conString, function(err, client, done) {

    console.log('Executing Insert query');

    client.query('insert into testdb (id,data,iscancelled) values ($1,$2,$3)',[id,'hello','no'],  function(err, result) {

        done();

        if(err) { return console.error('error running query', err);  }

        console.log('finished executing Insert query');

        // ------UPDATE
        pg.connect(conString, function(err, client, done) {

            console.log('Executing update query');

            client.query("update testdb set iscancelled = 'yes' where id = $1",[id],  function(err, result) {

            done();

            if(err) { return console.error('error running query', err);  }

            console.log('finished executing Update query');

        });

    });
});

Answer 3

You are missing the asynchronous nature of the pg.connect and also client.query. The call to these return a callback which passes the control to next expression before the completion of execution and hence non-blocking nature of nodejs. If you want to assure the correct flow, either call the successive ones inside the callback success

var pg = require('pg');
var uuid = require('node-uuid').v4;
var id = uuid().toString();

// ------INSERT
return pg.connect;

// ------UPDATE
return pg.connect;

// your calling file
var insert = require('/path/to/insertfile');
var conString = 'postgres://postgres:[email protected]:5432/testdb';
var update = require('/path/to/updatefile');

insert(conString, function (err, client, done) {
    console.log('Executing Insert query');
    client.query('insert into testdb (id,data,iscancelled) values ($1,$2,$3)',[id,'hello','no'],  function (err, result) {
        if (err) { 
           return console.error('error running query', err);  
        }
        console.log('finished executing Insert query');

        update(conString, function (error, client, done) {
           console.log('Executing update query');

           client.query("update testdb set iscancelled = 'yes' where id = $1",[id],  function (err, result) {

              if (err) { 
                  return console.error('error running query', err);  
              }
              console.log('finished executing Update query');
              done();
          });
        });
        done();
    });    

});

But this is very prone to callback hell. So consider making all async call return a promise. Take a look at bluebird. If you want an ORM that has built in promise based call, you can take a look at sequelize. It might be handy for you.

It has syntax as easy as:

var Model1 = require('/path/to/model1');
var Model2 = require('/path/to/model2');

var insertObj = {
    "someKey": "value"
};

Model1.create(insertObj)
     .then( function (createdObj1) {
         return Model2.findOne({
             where: {
                "filter": "filterValue"
             }
         });
     })
     .then( function (documentToUpdate) {
         return documentToUpdate.update({
             "fieldToUpdate": "value"
         });  
     })
     .then( null, function (err) {
         console.log(err);
     });