Database

Database functions are located in db.js. This is also a good place to dump your queries until you decide it makes sense to begin factoring out similar queries into their own files.

Querying

db.js has a simple query function that returns the Result object from https://github.com/brianc/node-postgres.

exports.getLatestUsers = function*() {
  var sql = 'SELECT * FROM users ORDER BY created_at DESC LIMIT 10';
  var result = yield query(sql);
  return result.rows;
};

However, since most queries involve returning either result.rows or result.rows[0], db.js also provides queryMany and queryOne which do it for you:

// Returns array of User objects
exports.getLatestUsers = function*() {
  var sql = 'SELECT * FROM users ORDER BY created_at DESC LIMIT 10';
  return yield queryMany(sql);
};

// Returns one User object
exports.getLatestUser = function*() {
  var sql = 'SELECT * FROM users ORDER BY created_at DESC LIMIT 1';
  return yield queryOne(sql);
};

Transactions

The withTransaction function in db.js is used for transactions.

It will automatically BEGIN, yield the client into the generator function that you give it, COMMIT, and ROLLBACK if there was a problem. If ROLLBACK fails, then it will remove the botched connection from the pool.

Note: withTransaction will keep retrying the transaction upon deadlock.

The classic example of transferring money between two bank accounts could look something like this:

exports.transferFunds = function*(fromAccountId, toAccountId, amount) {
  assert(Number.isInteger(fromAccountId));
  assert(Number.isInteger(toAccountId));
  assert(Number.isInteger(amount));

  var updateSql = `
    UPDATE accounts
    SET balance = balance + $2
    WHERE id = $1
    RETURNING *
  `;

  return yield withTransaction(function*(client) {
    // Update the accounts. Note: These aren't actually run in parallel nor are they
    // pipelined (Postgres limitation).
    let results = yield {
      fromAccountResult: client.queryPromise(updateSql, [fromAccountId, -amount]),
      toAccountResult: client.queryPromise(updateSql, [toAccountId, amount])
    };

    let updatedFromAccount = results.fromAccountResult.rows[0];
    let updatedToAccount = results.toAccountResult.rows[0];

    // Return the updated account records
    return { fromAccount: updatedFromAccount, toAccount: updatedToAccount };
  });
};

Reminder: Ensure that all of your SQL within a transaction are executing against the same client (the client yielded to you by withTransaction).

Reusing your db.js functions inside transactions

If you have a db.js function insertUser(uname, password), you will likely want to be able to use it both from within a route (where insertUser will have to grab a client from the pool) and also from within a transaction (the transaction already has a client).

You basically want two function signatures for insertUser:

1. insertUser(uname, password) for use within routes
2. insertUser(client, uname, password) for use within transactions and any time you already have a database client open.

So far, I'm just doing this:

// File: db.js

// For use inside db.js (like within a transaction) where you have a
// db client
function insertUser(client, uname, password) {
  var sql = `
    INSERT INTO users (uname, digest)
    VALUES ($1, $2)
    RETURNING *
  `;
  var digest = yield bcrypt.hash(password);
  var result = yield client.queryPromise(sql, [uname, digest]);
  return result.rows[0];
}

// For use outside of db.js when you don't have a db client
exports.insertUser = function*(data) {
  return yield withClient(function*(client) {
    return yield insertUser(client, data);
  });
};

TODO: It'd be nicer if I had one insertUser function that executed against a database client if one was provided in the first argument or else execute against a fresh client from the pool.