Transactions

Prego allows automated transactions for both models and plain connection objects. Transaction object provides connection object, that should be used to perform operations. Prego duplicates connection object specified on creation, and performs 'BEGIN' on it:

# Start transaction for default/any connection
prego.transaction prego.db, (err, transaction) ->
    tr.connection.execute "UPDATE counters SET counter=counter+1 WHERE owner_id=$1", [someId], tr.check()
    tr.connection.execute "UPDATE watchers SET watch=1 WHERE owner_id = $1", [someId], tr.check()
    tr.connection.executeRow "SELECT sum(counter) FROM counters WHERE owner_id=$1", [someId], tr.check (err, val) ->
        tr.rollback() if err || val.sum > 10
        tr.close() 

    # Or you can wait for it:
    tr.close ->
         console.log 'Transaction failed?', tr.failed

In this case, tr.check would provide callbacks that will wait for all checked operations be performed. tr.close will wait when all checks will be called and will commit or rollback transaction if at least one operation will fail. Or tr.rollback() would be called.

Note that in the executeRow we chain our own callback to check some condition and rollback transaction if it fails. tr.close() works fairly well

Models transactions

Are much easier:

user = User.findById 1
prego.transaction user, (err, tr) ->
    orders = user.orders.each tr.check (err,order) ->
        tr.attach order
        if !order.completed
            tr.rollback()
        else
            order.processed = true
            order.save()
    user.noWaitingOrders = true
    user.save()

    tr.close ->
         # check tr.failed if need
         # if !tr.failed, all orders are processed and saved, and user is saved too

All you need is to eqither create a transaction with a table, or attach a table to existing transaction. Prego will keep track of attached transaction in model.save so you can concentrate on something more interesting.

Transaction object

Transaction object has two states: open and closed. Initially it has the open state (BEGIN SQL statement is issued). It switches to the closed state when commit(), rollback() or close() methods are executed, after all pending operations are done.

tr.check [userCallback]

Returns a callback that should be called to let transaction end. The callback should be called with non-null first argument to cause rollback. If userCallback is specified, it will be called with the same arguments.

Note that neither commit(), rollback() or close() won't execute until there is at least one check() callback pending.

tr.commit [callback]

Waits for all check()'ed operations to complete and then executes commit no matter what were err arguments passed to check()s. If a callback is provided, it will be executed after 'COMMIT' will be executed on the connection. Closes the transaction.

tr.rollback [callback]

Exactly same as commit() but executes 'ROLLBACK' on connectino and sets transaction.failed to true.

tr.close [callback]

Same as above. Issues 'COMMIT' and sets failed to false if no one check() callback was called with non-empty first argument. Otherwise issues ROLLBACK and sets failed to false. Closes the transaction.

Caveats

There is a potential problem with tables and transactions if, being inside the transaction, you'll create/load new model instances, as currently Table.findBy... Table.allFromSql and like use Table's connection, not the transaction connection, so changes made in the transaction will not be reflected until you commit it.

If this is a problem. pls create an issue, and I'll fix it.

Manual transactions using Connection object

You can make whatever transaction you want executing SQL statements using connectinon.execute and other methods, but keep in mind that you must lock() client before start and unlock() it after the end (in any case), because you need the same client for the whole transaction lifetime (otherwise prego might free it after the statement is completed, for example):

connection = prego.db.clone()
connection.lock()
connection.execute 'BEGIN', (err) ->
  if err
    connection.unlock()
    assert.ok err, null
  else
    # Do something with connection
    connection.execute 'COMMIT', (err) ->
      connection.unlock()
      assert.equal err, null

As you can see, using preso.transaction mechanics is much easier. Still, you can do it manually this way.