node-postgres connection and query with async/await

index.js

const pg = require('pg')

// create a config to configure both pooling behavior
// and client options
// note: all config is optional and the environment variables
// will be read if the config is not present
var config = {
  user: '', // env var: PGUSER
  database: '', // env var: PGDATABASE
  password: '', // env var: PGPASSWORD
  host: 'localhost', // Server hosting the postgres database
  port: 5432, // env var: PGPORT
  max: 10, // max number of clients in the pool
  idleTimeoutMillis: 30000 // how long a client is allowed to remain idle before being closed
}

const pool = new pg.Pool(config)

async function query (q) {
  const client = await pool.connect()
  let res
  try {
    await client.query('BEGIN')
    try {
      res = await client.query(q)
      await client.query('COMMIT')
    } catch (err) {
      await client.query('ROLLBACK')
      throw err
    }
  } finally {
    client.release()
  }
  return res
}

async function main () {
  try {
    const { rows } = await query('SELECT * FROM users')
    console.log(JSON.stringify(rows))
  } catch (err) {
    console.log('Database ' + err)
  }
}

main()

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?

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 smile