How can I use PostgreSQL transactions in SailsJs?

Question

My problem is that i have a complex chain of queries and it make rollback if someone of this transactions fail. I've read about transactions in Sails and, for default, Sails don't support transactions because each transaction make a new connection with Postgres, so, one query have a new connection. So, how i can use transactions with Sails?

score 1 · Accepted Answer · edited May 23 '17 at 11:53

Currently transactions are not supported by waterline. You can join the discussion here https://github.com/balderdashy/waterline/issues/755.

Transactions are on the roadmap but are not expected in near future: https://github.com/balderdashy/waterline/blob/master/ROADMAP.md#pending-proposals

As a workaround you might try https://github.com/Shyp/pg-transactions however you will loose the flexibility of waterline because your code will not be database agnostic any more.

Also check Sails.js best practice in using transactions with promises (Postgres) and Sails.js + Postgres: issue with transactions

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

The easiest approach is through automatic transactions as supported by pg-promise:

db.tx(t=> {
    // - returning a value or a resolved promise will result in COMMIT
    // - returning a resolved promise or throwing an error will result in ROLLBACK
})
    .then(data=> {
        // COMMIT has been executed
        // data  = data returned from the callback
    })
    .catch(error=> {
        // ROLLBACK has been executed
        // error = rejection reason or error thrown
    });

But you would need to execute your queries directly, not through Waterline, and as @zabware said:

you will loose the flexibility of waterline because your code will not be database agnostic any more.

How can I use PostgreSQL transactions in SailsJs?

2 Answers2