Transactional SQL with Sails.js

Question

So I have been playing with NodeJS/Express for a little with now and I would really like to try to rewrite a relatively large side project using a full JavaScript stack just to see how it will work. Sails.js seems to be a pretty good choice for a NodeJS backend for a REST API with support for web sockets which is exactly what I am looking for however is one more issue I am looking to resolve and that is transactional SQL within NodeJS.

Most data layer/orms I have seen on the NodeJS side of things don't seem to support transactions when dealing with MySQL. The ORM provided with Sails.js (Waterline) also does not seem to support transactions which is weird because I have seen places where is mentioned it did though those comments are quite old. Knex.js has support for transactions so I was wondering if it is easy to replace the ORM is Sails.js with this (or if Sails.js assumes Waterline in the core framework).

I was also wondering if there is an ORM built on top of Knex.js besides Bookshelf as I am not a fan of Backbones Model/Collection system?

Curious what you think could be improved in the Model/Collection system or what you don't like about it. — tgriesser, Aug 09 '13 at 22:19

score 17 · Answer 1 · answered Jan 15 '14 at 14:20

You can still write SQL queries directly using Model.query(). Since this is an asynchronous function, you'll have to use promises or async to reserialize it. For instance, using the MySQL adapter, async, and a model called User:

async.auto({
  transaction: function(next){
    User.query('BEGIN', next);
  },
  user: ['transaction', function(next) {
    User.findOne(req.param('id')).exec(next);
  }],
  // other queries in the transaction
  // ...
}, function(err, results) {
  if (err) {
    User.query('ROLLBACK', next);
    return next(err);
  }
  User.query('COMMIT', next);
  // final tasks
  res.json(results.serialize);
});

This might not work when connection pooling is enabled. You should not run BEGIN, ROLLBACK and COMMIT on separate connections. — Russell Santos, Jan 06 '16 at 08:11

score 6 · Answer 2 · answered Jul 30 '13 at 20:02

6

We're working on native support for transactions at the ORM level: https://github.com/balderdashy/waterline/issues/62

Associations will likely come first, but transactions are next. We just finished GROUP BY and aggregations (SUM, AVG, etc.)

answered Jul 30 '13 at 20:02

mikermcneil

11,141
5
43
70

1

what is the state of this... I'm just interested in single adapter transactions over multiple table... I see documentation saying waterline has transactions but no elaboration on that statement.. or documentation of how to use it. – jonasfj Nov 23 '13 at 20:37
How do you actually perform the groupBy? Ctrl+f on both waterline documentation and sails documentation shows zero results. A quick example would be awesome. – Kory Nov 27 '13 at 23:02

score 1 · Answer 3 · answered May 03 '15 at 19:49

Transactions in SailsJS turned out to be much trickier than anticipated. The goal is to let the ORM adapter know that two very disparate controller actions on models are to be sent through a single MySQL connection.

The natural way to do it is two write a new adapter that accepts an additional info to indicate that a query belongs to a transaction call. Doing that requires a change in waterline (sails ORM abstraction module) itself.

Checkout if this helps - https://www.npmjs.com/package/sails-mysql-transactions

Transactional SQL with Sails.js

3 Answers3

Linked