Transactions with async/await?

[rob-johansen]

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?

[rob-johansen]

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();
            });
    });