CODEWITHSUNDEEP
node.jspostgresqltransactions
Dercni
Dercni

Reputation: 1224

nodejs pg transactions without nesting

I would like to know if it's possible to run a series of SQL statements and have them all committed in a single transaction.

The scenario I am looking at is where an array has a series of values that I wish to insert into a table, not individually but as a unit.

I was looking at the following item which provides a framework for transactions in node using pg. The individual transactions appear to be nested within one another so I am unsure of how this would work with an array containing a variable number of elements.

https://github.com/brianc/node-postgres/wiki/Transactions

var pg = require('pg');
var rollback = function(client, done) {
  client.query('ROLLBACK', function(err) {
    //if there was a problem rolling back the query
    //something is seriously messed up.  Return the error
    //to the done function to close & remove this client from
    //the pool.  If you leave a client in the pool with an unaborted
    //transaction weird, hard to diagnose problems might happen.
    return done(err);
  });
};
pg.connect(function(err, client, done) {
  if(err) throw err;
  client.query('BEGIN', function(err) {
    if(err) return rollback(client, done);
    //as long as we do not call the `done` callback we can do 
    //whatever we want...the client is ours until we call `done`
    //on the flip side, if you do call `done` before either COMMIT or ROLLBACK
    //what you are doing is returning a client back to the pool while it 
    //is in the middle of a transaction.  
    //Returning a client while its in the middle of a transaction
    //will lead to weird & hard to diagnose errors.
    process.nextTick(function() {
      var text = 'INSERT INTO account(money) VALUES($1) WHERE id = $2';
      client.query(text, [100, 1], function(err) {
        if(err) return rollback(client, done);
        client.query(text, [-100, 2], function(err) {
          if(err) return rollback(client, done);
          client.query('COMMIT', done);
        });
      });
    });
  });
});

My array logic is:

banking.forEach(function(batch){
  client.query(text, [batch.amount, batch.id], function(err, result);
}

Upvotes: 4

Views: 13339

Answers (2)

Alex Herman
Alex Herman

Reputation: 2838

Here's a simple TypeScript solution to avoid pg-promise

import { PoolClient } from "pg"
import { pool } from "../database"

const tx = async (callback: (client: PoolClient) => void) => {
  const client = await pool.connect();

  try {
    await client.query('BEGIN')
    try {
      await callback(client)
      await client.query('COMMIT')
    } catch (e) {
      await client.query('ROLLBACK')
    }
  } finally {
    client.release()
  }
}

export { tx }

Usage:

...

let result;

await tx(async client => {
  const { rows } = await client.query<{ cnt: string }>('SELECT COUNT(*) AS cnt FROM users WHERE username = $1', [username]);
  result = parseInt(rows[0].cnt) > 0;
});

return result;

Upvotes: 4

vitaly-t
vitaly-t

Reputation: 25880

pg-promise offers a very flexible support for transactions. See Transactions.

It also supports partial nested transactions, aka savepoints.

The library implements transactions automatically, which is what should be used these days, because too many things can go wrong, if you try organizing a transaction manually as you do in your example.

See a related question: Optional INSERT statement in a transaction

Upvotes: 3

Related Questions