Sails.js best practice in using transactions with promises (Postgres)

Question

I'm using sails 0.9.16 with Postgres and my question is: what is the best way to execute transaction using current API with promises? May be there is something better than:

    Model.query('BEGIN TRANSACTION', function (err) {
      if (err) {
        next(err);
      } else {
        Model
          .create(...)
          .(function (value) {
            return [value, RelatedModel.create(...).then(...)];
          })
          .fail(function (err) {
            Model.query('ROLLBACK');
            next(err);
          })
          .spread(function (...) {
            Model.query('COMMIT')
            next(...);
          })
      }
    })

Thanks for help!

Hey how did you solve the problem or which was the best way for doing it for you? — alexventuraio, Dec 08 '15 at 03:47
Since Sails postgres adapter pulls/creates new connection for every single query I've end up with adapter modification: https://gist.github.com/zuker/62cd680d58a8f218e87d — Zuker, Dec 09 '15 at 08:39

aclave1 · Accepted Answer · 2015-04-14T22:34:20.267

I'm currently using this exact workflow. For executing one query with promises do this:

Model
 .query(params)
 .then(function(result){
 //act on result
 })
 .catch(function(error){
 //handle error
 })
 .done(function(){
 //clean up
 });

To execute multiple queries in parallel, do this:

var Promise = require('q');

Promise.all([

    User.findOne(),
    AnotherModel.findOne(),
    AnotherModel2.find()

])
.spread(function(user,anotherModel,anotherModel2){
    //use the results
})
.catch(function(){
    //handle errors
})
.done(function(){
    //clean up
});

If you're trying to avoid nesting in your code:

Model
.query(params)
.then(function(result){//after query #1
    //since you're returning a promise here, you can use .then after this
    return Model.query();
})
.then(function(results){//after query#2
    if(!results){
        throw new Error("No results found in query #2");
    }else{
        return Model.differentQuery(results);
    }

})
.then(function(results){
//do something with the results
})
.catch(function(err){
    console.log(err);
})
.done(function(){
    //cleanup
});

Note: currently, waterline uses Q for promises. There is a pull request to switch waterline from Q to bluebird here: waterline/bluebird

When I answered this question, I'd yet to take the database class in college, so I didn't know what a transaction was. I did some digging, and bluebird allows you to do transactions with promises. The only problem is, this isn't exactly built into sails since it's some what of a special use case. Here's the code bluebird provides for this situation.

var pg = require('pg');
var Promise = require('bluebird');
Promise.promisifyAll(pg);

function getTransaction(connectionString) {
    var close;
    return pg.connectAsync(connectionString).spread(function(client, done) {
        close = done;
        return client.queryAsync('BEGIN').then(function () {
            return client;
        });
    }).disposer(function(client, promise) {
        if (promise.isFulfilled()) {
            return client.queryAsync('COMMIT').then(closeClient);
        } else {
            return client.queryAsync('ROLLBACK').then(closeClient);
        }
        function closeClient() {
            if (close) close(client);
        }
    });
}

exports.getTransaction = getTransaction;

can you give bluebird equivalent, since waterline has shifted to bluebird — Luja Shrestha, Nov 04 '14 at 11:44
@lujaw the code for the bluebird equivalent should be exactly the same as they both adhere to the A+ promise spec. — aclave1, Dec 15 '14 at 17:28
@aclave1 In the third example how can I return two objects or variables in the second `then` to the third `then`. I mean the result from the firs query and another resulto made on the second query and receive those in the third `then`? I hope to explain a little bit. Thanks in advance! — alexventuraio, Jan 06 '16 at 19:36
@Lexynux You'll want to look at Bluebird's Promise.Join(result1,result2).spread(function(result1,result2){ }) or at Promise.bind() depending on your use case — aclave1, Jan 06 '16 at 20:02
Do you have any example? My code looks like this and it doesn't work! — alexventuraio, Jan 06 '16 at 20:05
Can you please post a question and post a link in the comments for me? — aclave1, Jan 06 '16 at 20:05
@aclave1 Here it is the question: http://stackoverflow.com/questions/34641985/how-to-return-two-objects-in-a-promise-using-sails — alexventuraio, Jan 06 '16 at 20:19

vitaly-t · Answer 2 · 2017-04-02T22:12:13.047

0

The best way to deal with transactions is when they are wrapped properly by a promise library, because transaction logic maps perfectly into the promise event chain, one doesn't have to worry about when to do COMMIT or ROLLBACK, as it happens automatically.

Here's a complete example of how it works with pg-promise library:

var pgp = require('pg-promise')(/*options*/);

var cn = "postgres://username:password@host:port/database";
var db = pgp(cn); // database instance;

db.tx(t => {
    // BEGIN has been executed
    return t.batch([
        t.one("insert into users(name) values($1) returning id", 'John'),
        t.one("insert into users(name) values($1) returning id", 'Mike')
    ]);
})
    .then(data => {
        // COMMIT has been executed
        console.log(data[0].id); // print id assigned to John;
        console.log(data[1].id); // print id assigned to Mike;
    })
    .catch(error => {
        // ROLLBACK has been executed
        console.log(error); // print why failed;
    });

edited Apr 02 '17 at 22:12

answered Apr 04 '15 at 12:48

vitaly-t

24,279
15
116
138

This looks like awesome logic to work with in SailsJS but how do I know when to use promises and when use simple code? – alexventuraio Dec 08 '15 at 03:46
By simple code - do you mean callbacks? The asynchronous nature of your code and libraries dictates what should be used. – vitaly-t Dec 08 '15 at 08:44
But I have read that promises are used most we you need to have to many nested callbacks in order to have clean code. Is it true? – alexventuraio Dec 10 '15 at 03:50
@AlexVentura no, promises is the new standard for asynchronous programming in Node.js. And new things like ES6 generators and ES7 async/await automatically work with the promise standard. See [examples](https://github.com/vitaly-t/pg-promise/blob/master/examples/select-insert.md). – vitaly-t Apr 02 '17 at 22:14
Alright, so it better to use than callbacks in any case? – alexventuraio Apr 04 '17 at 16:06
@AlexVentura that's right. And when you start chaning results from many methods you will see the benefit, as with callbacks it can quickly turn into an unreadable nested nightmare. – vitaly-t Apr 04 '17 at 16:27

Sails.js best practice in using transactions with promises (Postgres)

2 Answers2

Linked