sykopomp/storychat — pull request #5 by sykopomp (2014-03-01 17:29:25+00:00)
Title: Transaction support for db.js

The shared `db.js` library needs SQL transaction support. Using the [examples from the postgres docs](http://www.postgresql.org/docs/current/static/tutorial-transactions.html), I propose an API like this:

```sql
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;
```
would turn into:
```js
var result = db.transaction(function(txn) {
// BEGIN is implicit with db.transaction()
// txn is identical to the global db, but it uses its own connection and
// transaction context
var res = txn.query("UPDATE accounts SET balance = balance - 100.00"+
" WHERE name = :name", {name:"Alice"});
res = res.then(function() { return txn.savepoint(); });
res = res.then(function() { return txn.query("UPDATE...", {name: "Bob"}); });
res = res.then(function() { return txn.rollback("my_savepoint"); });
res = res.then(function() { reutnr txn.query("UPDATE...", {name: "Wally"}); });
// This final promise should have a .then() handler attached to it. If the promise
// succeeds, execute COMMIT, otherwise execute ROLLBACK on the transaction.
// You may need an internal, name-munged savepoint to do this.
return res;
});
```