Node-postgres: Transactions with async/await?

[stenrap]

I'm running Node 7.5.0 with the --harmony flag, which enables support for async/await. I'm using the client pool with async/await, which works great and has a nice example here.

The example for transactions (here) uses callbacks instead of async/await, so I thought I'd try something like this as a quick test:

let client = null;

try {
    client = await this.pool.connect();
} catch (error) {
    console.log('A client pool error occurred:', error);
    return error;
}

try {
    await client.query('BEGIN');
    await client.query('UPDATE foo SET bar = 1');
    await client.query('UPDATE bar SET foo = 2');
    await client.query('COMMIT');
} catch (error) {
    try {
        await client.query('ROLLBACK');
    } catch (rollbackError) {
        console.log('A rollback error occurred:', rollbackError);
    }
    console.log('An error occurred:', error);
    return error;
} finally {
    client.release();
}

return 'Success!';

This seems to work just fine, but is this a bad idea? Is there some technical or other reason I should be using the callback approach with transactions?

[stenrap]

Ok. I've posted my question to Stack Overflow here.

[brianc]

Because doing transactions are usually pretty application specific and node-postgres aims to be a low layer driver doing the nitty gritty communication between your app & postgres over the wire, I've intentionally left any higher-level transaction handling code out of the library. It's 100% possible to do transactions with just node-postgres (this is what I do in my apps) but in my experience it always ends up looking custom to your application, particularly when inside the transaction the output of a query forms some of the input to a subsequent query.

That being said...your code looks similar to how I've done transactions within my own apps using async/await. You can even made a simple abstraction like this:

const tx = callback => {
  const client = await pool.connect()
  try {
  await client.query('BEGIN')
    try {
      await callback(client)
      client.query('COMMIT')
    } catch(e) {
      client.query('ROLLBACK')
    }
  } finally {
    client.release()
  }
}

This allows you, in your own app, to do things like:

const fromAccount = 100
const toAccount = 103
const transferAmount = 33
tx(async client => {
  const { rows } = await client.query('SELECT balance FROM account WHERE id = $1', [fromAccount])
  const balance = rows[0].balance
  if (balance > transferAmount) {
    await client.query('UPDATE account SET balance = $2 WHERE id = $1', [fromAccount, balance - transferAmount])
    await client.query('UPDATE account SET balance = $2 WHERE id = $1', [toAccount, balance + transferAmount])
  }
})

fwiw I avoid using callbacks directly in node whenever I can - I think async/await provides such a nicer abstraction it allows me to focus more on coding and less on juggling callback hell.

hope this helps 😄

[mathroc]

thx for the input @brianc

I think it would be nice to have this in the readme or somewhere in the wiki

[mathroc]

for the record, I had an issues because I was using pool.query instead of first getting a client and then calling query on this one client

[zerbfra]

Here a different version, based on @brianc's one!
check it out 😃

https://gist.github.com/zerbfra/70b155fa00b4e0d6fd1d4e090a039ad4

[talkingtab]

Thanks @zerbfra for the gist, I added a comment to the gist for the case where there are parameterized queries.

[indreek]

Hi @brianc,
I have question about your code:

const tx = callback => {
  const client = await pool.connect()
  try {
  await client.query('BEGIN')
    try {
      await callback(client) //What does this line do?
      client.query('COMMIT')
    } catch(e) {
      client.query('ROLLBACK')
    }
  } finally {
    client.release()
  }
}

[talkingtab]

taking a stab at this:

const tx = callback => {

says that tx is a function that takes callback as a parameter. The type of callback is an async function. The second section of code defines an anonymous async function that is passed to tx

tx(async client => {... the anonymous function... })

So this line in tx is where that anonymous function is called:

await callback(client) . //What does this line do?

The effect is that the queries inside the anonymous function are wrapped by
client.query('Begin');
client.query('COMMIT');
client.query('ROLLBACK');

[ZiaDev]

You can use the package https://www.npmjs.com/package/pg-essential, it applies a patch on top of pg, which gives you some helper function against the client object. In this case you can use the executeTransaction function and pass your function as callback.
``` return getPool().then((p) => { return p.getClient().then((connection) => { // Insert into test and test_history table as a single transaction await connection.client.executeTransaction(async client => { await client.execute( INSERT INTO test (id, name, created_at, updated_at) VALUES (:id, :name, jsNow(), jsNow())`, {
id: chance.guid(),
name: chance.word()
});

            await client.execute(
                `INSERT INTO test_history (id, name, created_at, updated_at) VALUES (:id, :name, jsNow(), jsNow())`, {
                    id: chance.guid(),
                    name: chance.word()
                });

                connection.done();
        }).catch(()=>{
            connection.done();
        });
});