Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
a transaction with node-postgres - this is how _I_ write code which uses node-postgres. YMMV.
//this is an example of how you might run a transaction
//in postgres with node. This example has as little
//abstraction as possible. In your app you'll
//likely want to build a layer on top to ease some of the
//callback management and reduce code duplication
var log = require('logged')(__filename);
var pg = require('pg');
//I like to set my connection parameters
//as environment variables. node-postgres
//and the psql program use the same variables
pg.connect(function(err, client, done) {
if(err) {
//hanlde error. In the case of a connection error
//client will be null and no client will be added to the pool
//so you don't have to call done here. note: you CAN call done, it's a no-op function
//in the event of a connection error
return log.error('Could not connect to PostgreSQL server', err);
}
client.query('BEGIN', function(err) {
if(err) {
//if there was an error issuing a BEGIN statement
//something is seriously wrong. Kill this client
//as it could be an indication there are deeper issues
//such as loss of backend connectivity or weird systems problems
log.error('Problem starting transaction', err);
return done(true); //pass non-falsy value to done to kill client & remove from pool
}
client.query('INSERT INTO something', ['bla', 'bla'], function(err, result) {
if(err) {
//if there is an error doing the insert it could potentially be
//data related (unique constraints, etc) so you need to ROLLBACK
//the transaction
log.error('unable to insert data, rolling back transaction', err);
return client.query('ROLLBACK', function(err) {
if(err) {
log.error('unable to rollback transaction, killing client', err);
}
//if there is an error issuing the ROLLBACK statement
//something is seriously wrong with the backend
//so best thing to do is kill this client; otherwise, you'll
//be leaving the client in an errored-transaction state
//
//if the error is null we can consider the transaction rolled back successfully
done(err);
});
} //end if(err)
client.query('COMMIT', function(err) {
//same thing here as with the ROLLBACK statement...call `done` either way
//but if there is an error, kill the client
if(err) {
log.error('unable to commit transaction, killing client', err);
}
done(err);
});
});
});
});
@razoulay

This comment has been minimized.

Copy link

@razoulay razoulay commented Dec 6, 2019

hi
thank you for sharing your code .
just wanted to ask please: I used your code and added one more query inside and then got an error when getting to the commit part.
How could i make two queries between the BEGIN and he COMMIT and make it working please?
Here is the code:

client
.connect()
.then(() => {
console.log('connected: ' + order.user_token);
const query = {
text: 'insert into orders(user_token, account, parseketable, isin, op_type, amount_ordered, limit_price, tif, instructions, ' +
'security_name, side, filled_name, working, amnt_left, pct_left, average_price, broker_name, status, portfolio_manager, ' +
'trader_name, order_date, order_creation, last_touched, ts_order_date, settle_date, security_id, order_number, ticket_number,
order_id) ' +
'values($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, ' +
'$11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29' +
') RETURNING *',
values: [order.user_token,
order.parseketable,
order.isin,
order.op_type,
order.limit_price,
order.tif,
order.instructions,
order.security_name,
order.side,
order.filled_name,
order.working,
order.amnt_left,
order.pct_left,
order.average_price,
order.broker_name,
order.status,
order.portfolio_manager,
order.trader_name,
order.order_date,
order.order_creation,
order.last_touched,
order.ts_order_date,
order.settle_date,
order.security_id,
order.order_number,
order.ticket_number,
order.order_id
]
};

            let order_number;
            client.query('BEGIN', function(err) {
                if(err) {
                  //if there was an error issuing a BEGIN statement
                  //something is seriously wrong.  Kill this client
                  //as it could be an indication there are deeper issues
                  //such as loss of backend connectivity or weird systems problems
                  log.error('Problem starting transaction', err);
                  return done(true); //pass non-falsy value to done to kill client & remove from pool
                }
                client.query(query, function(err, result) {
                  if(err) {
                    //if there is an error doing the insert it could potentially be 
                    //data related (unique constraints, etc) so you need to ROLLBACK
                    //the transaction
                    log.error('unable to insert data, rolling back transaction', err);
                    return client.query('ROLLBACK', function(err) {
                      if(err) {
                        log.error('unable to rollback transaction, killing client', err);
                      }
                      //if there is an error issuing the ROLLBACK statement
                      //something is seriously wrong with the backend
                      //so best thing to do is kill this client; otherwise, you'll
                      //be leaving the client in an errored-transaction state
                      //
                      //if the error is null we can consider the transaction rolled back successfully
                      done(err);
                    });
                  } //end if(err)
                  

                    const allocationQuery = {
                        text: 'insert into allocations(account, amount_ordered, order_number ' +
                        'values($1, $2, $3' +
                        ') RETURNING *',
                        values: [
                                order.account,
                            order.amount_ordered,
                            order_number
                        ]
                    };
                    client.query(allocationQuery, function(err, result) {
                        if(err) {
                          //if there is an error doing the insert it could potentially be 
                          //data related (unique constraints, etc) so you need to ROLLBACK
                          //the transaction
                          log.error('unable to insert data, rolling back transaction', err);
                          return client.query('ROLLBACK', function(err) {
                            if(err) {
                              log.error('unable to rollback transaction, killing client', err);
                            }
                            //if there is an error issuing the ROLLBACK statement
                            //something is seriously wrong with the backend
                            //so best thing to do is kill this client; otherwise, you'll
                            //be leaving the client in an errored-transaction state
                            //
                            //if the error is null we can consider the transaction rolled back successfully
                            done(err);
                          });
                        } //end if(err)
                  client.query('COMMIT', function(err) {
                      //same thing here as with the ROLLBACK statement...call `done` either way
                      //but if there is an error, kill the client
                      if(err) {
                        log.error('unable to commit transaction, killing client', err);
                      }
                      done(err);
                  });
                });});
            });  
            
        })
        .catch(err => {
            console.error('connection error', err.stack);
            client.end();
            resolve(self.errorResponse(err.message));
        }
        );
@razoulay

This comment has been minimized.

Copy link

@razoulay razoulay commented Dec 6, 2019

hi
thank you for sharing this example.
i tried to make two different queries between the BEGIN and commit but it didnt work.. Could you give an example of sending to or three queries to the server as one transaction please..
Also i get that function "done(err);" is not a function
i would appreciate your help very much!
Thank you very much

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment