Add transaction support

[fsateler]

This needs issue #65 to be fixed first.

For this to happen we need a client pool that allows clients to be marked as non-reclaimable (node-postgres can do this), and "lock" the client for the duration of the transaction block. This would probably require a way to pass a client instance (which was generated at init-transaction time) to the query functions, and a callback to mark the transaction as done.

[nl0]

+1

[mikermcneil]

+1 Sequelize just did this. I don't have time to knock this out myself right now, but I'm happy to help

[jamesholcomb]

+1

[softminer]

+1

[anatoliychakkaev]

Any ideas on how to do develop it in current API? We should take in account that we could have more than one transaction at the moment. Right now i only see one possiblity and don't like it: create some transaction object and pass it to every operation. Each db call should be extended with one optional argument: transaction.

Any other suggestions?

[erin-noe-payne]

I've been thinking about this because I'd really like to have transaction support in my projects (using postgres). The approach I would propose:

1. Create a "Query" object that represents any generic query. Queries can have chainable methods that basically look like what you support for filtering on models now: where, include and so on. . A query instance then has an .exec method that actually makes the call and returns data. Mongoose has a very robust query object, for reference [http://mongoosejs.com/docs/api.html#query-js]. The mechanics of what a query does are implemented by the adapters.

2. Any of the current calls to juggling db models or instances are creating a new query object under the hood. If a callback is provided then that query object is executed immediately. If no callback is provided the query object is returned. This will probably allow you to decouple models from queries, with each adapter managing the building and execution of queries, and models staying generic and using queries for their functionality.

// these are the same thing
var query1 = c.all();
query1.where({name: 'bob'});
query1.exec(function(err, bobs){//...});

MyModel.all({where: {name: 'bob'} }, function(err, bobs){//...});

3. You add a transaction object, probably to schema. A transaction takes a list of query objects to create a queue. Then on commit it runs through all of the queries in a transaction block, and returns to the callback with either an error, or with the return values for each individual query. You could get cute or make an argument for the best syntax, but something like...

//var schema is my previously defined schema
var tx = new schema.Transaction([
    model1.destroy(),
    model2.destroy(),
    MyModel.create({name: 'ted'}),
    MyModel.all()
]);

//you could optionally have an add function to add to the queue of a transaction
tx.add(model3.updateAttr('name', 'jill'));

/*
the callback from commit could have the return values individually, or come back as an array of values[]
*/
tx.commit(function(err, task1return, task2return, task3return, task4return, task5return){

});

That's it. It's worth noting that transactions aren't supported in mongo I know, and I think in many other nosql databases. So this may mostly be a sql concern. The original poster is probably correct that the postgres adapter will need connection pooling support, but that's an adapter specific concern.

I also think that breaking out a query object will probably be good for maintainability going forward.

I would be interested in implementing or helping to implement this, if it would be supported. It would probably create significant overhaul for the other adapters.

[fsateler]

Hi,

I am not working with nodejs now so I cannot work on this, but I have some issues with @autoric's proposal. In particular, transactional support should allow doing different things depending on the results of early queries. For example, an accounting system might want to check the current balance on an account, and depending on the current balance the system might decide to deny a purchase. AFAIK this cannot be done with the proposed API without resorting to stored procedures or check constraints.

I had some thoughts about this earlier, last year, and I concluded that the only option was adding an optional parameter to all of jugglingdb's queries with the transaction object. The transaction object is needed because otherwise jugglingdb should be fetching a connection from the pool, and transactions work per-connection, of course.

[erin-noe-payne]

Ok that's a good point. My concern when I was thinking about this was rolling back state in memory.

So if I go through each step in my transaction and handle the results individually but then rollback half way through, what happens to my models?

[erin-noe-payne]

Ok, so I think this is actually a simple solution. The original proposal stands. But to a transaction object you can add an .execute() method. This will execute and clear the query queue without committing, so that you can make a logical branching point. You can add more tasks to the query queue, and nothing is done until you .commit or .rollback the transaction.

[bioform]

May be it will be better to use https://github.com/laverdet/node-fibers ?
Just make transaction as a Fiber and wrap all queries to it.
So transaction will be available via "Fiber.current"

[ivantodorovich]

Any updates on this issue?

I'm looking for a ORM for my project, but transactions is a must.

I actually like @autoric purposal but with a few changes:

• Forget about the queueing-style API. Use async for that. Transactions should internally queue queries, though, since we are dealing with a single connection.
• Query.exec looks for a free db connection on some pool, creates one or gets queued (depending on settings)
• Query should have a tx property optionally set on construction. Also Query.exec could accept this parameter. This should be used in Query.exec instead of looking for a free db connection on the pool.
• I would also add the optional tx parameter to every Model method, which should be passed to Query, in case you want to auto-execute queries with a single line of code.

var tx = new schema.Transaction();

// this
var q = MyModel.create({name: 'bob'});
q.exec(cb, tx);

// equals to
MyModel.create({name: 'bob'}, cb, tx);

[1602]

Currently I propose this API:

var tx = schema.transaction();

tx.Model.create(data, function(err, obj) {
    if (err) tx.rollback(); else
    obj.relation.create(function(err, rel) {
        if (err) tx.rollback(); else tx.commit();
    });
});

If we all agree with this API, we could proceed to the next step - support transactions on specific adapter level.