Rollback many-to-many (association table) not working with SailsJs 0.12.11

[sam9291]

I've tested a simple scenario with two models Dog and Bone (many-to-many association) and I can't get it to rollback.

Here are my two models:

///////////////////
// Dog.js
///////////////////
module.exports = {

  attributes: {

    name: { type: 'string', unique: true },

    age: { type: 'integer' },

    bones: {
      collection: 'bone',
      via: 'dogs'
    }

  }
};

///////////////////
// Bone.js
///////////////////
module.exports = {

    attributes: {

        size: { type: 'string' },
        dogs: {
            collection: 'dog',
            via: 'bones'
        }

    }
};

And here is my test using the framework to try to rollback a modification to the association:

it('should rollback transaction when updating the row with an association table', done => {

            // sails-mysql-transactions
            const Transaction = require('sails-mysql-transactions').Transaction;

            // method to remove all bone associations
            const updateDogBones = (transaction, id) => Dog.transact(transaction).update({ id: id }, { bones: [] });

            // create a dog with 2 bones
            Dog.create({ name: 'fido', age: 1, bones: [{ size: 'small' }, { size: 'large' }] })
                .then(dog => {
                    // start the transaction
                    Transaction.start((err, transaction) => {
                        // remove the bones associations
                        updateDogBones(transaction, dog.id)
                            // fetch back the dog with the transaction
                            .then(() => Dog.transact(transaction).findOne({ id: dog.id }).populate('bones'))
                            .then(dog => {
                                // should have no bones
                                dog.bones.length.should.be.equal(0); // <-- this works
                            })
                            // rollback the transaction
                            .then(() => transaction.rollback(err => {
                                if (err) {
                                    done(err);
                                    return;
                                }
                                // fetch back the dog
                                Dog.findOne({ name: 'fido' })
                                    // populate the dog's bones
                                    .populate('bones')
                                    .then(dog => {
                                        // we should have two here since we rolled back
                                        dog.bones.length.should.be.equal(2); // <-- Error here! we still have 0 after the rollback
                                    })
                                    .then(() => done())
                                    .catch(done);
                            }));
                    });
                });
        });

I've tested both with the Promise and the exec syntax and both are having the same issue.

Here is the output result:

1 failing
  1) SqlTransaction :: beginTransaction :: should rollback transaction when updating the row with an association table:
      AssertionError: expected 0 to equal 2
      + expected - actual
      -0
      +2
      
      at Dog.create.then.Transaction.start.updateDogBones.exec.Dog.transact.findOne.populate.then.then.transaction.rollback.Dog.findOne.populate.then.dog (test\transactions.spec.js:492:72)
      at tryCatcher (node_modules\waterline\node_modules\bluebird\js\release\util.js:11:23)
      at Promise.module.exports.Promise._settlePromiseFromHandler (node_modules\waterline\node_modules\bluebird\js\release\promise.js:491:31)
      at Promise.module.exports.Promise._settlePromise (node_modules\waterline\node_modules\bluebird\js\release\promise.js:548:18)
      at Promise.module.exports.Promise._settlePromise0 (node_modules\waterline\node_modules\bluebird\js\release\promise.js:593:10)
      at Promise.module.exports.Promise._settlePromises (node_modules\waterline\node_modules\bluebird\js\release\promise.js:676:18)
      at Promise.module.exports.Promise._fulfill (node_modules\waterline\node_modules\bluebird\js\release\promise.js:617:18)
      at module.exports (node_modules\waterline\node_modules\bluebird\js\release\nodeback.js:42:21)
      at wrapper (node_modules\waterline\node_modules\lodash\index.js:3592:19)
      at applyInOriginalCtx (node_modules\waterline\lib\waterline\utils\normalize.js:421:80)
      at wrappedCallback (node_modules\waterline\lib\waterline\utils\normalize.js:324:18)
      at success (node_modules\waterline\node_modules\switchback\lib\normalize.js:33:31)
      at _switch (node_modules\waterline\node_modules\switchback\lib\factory.js:58:28)
      at returnResults (node_modules\waterline\lib\waterline\query\finders\basic.js:180:9)
      at node_modules\waterline\lib\waterline\query\finders\basic.js:91:16
      at node_modules\waterline\lib\waterline\query\finders\operations.js:78:46
      at wrapper (node_modules\waterline\node_modules\lodash\index.js:3592:19)
      at applyInOriginalCtx (node_modules\waterline\lib\waterline\utils\normalize.js:421:80)
      at wrappedCallback (node_modules\waterline\lib\waterline\utils\normalize.js:324:18)
      at success (node_modules\waterline\node_modules\switchback\lib\normalize.js:33:31)
      at _switch (node_modules\waterline\node_modules\switchback\lib\factory.js:58:28)
      at node_modules\sails-mysql\lib\connections\spawn.js:114:16
      at Object.poolfully [as releaseConnection] (node_modules\sails-mysql\lib\connections\release.js:28:12)
      at node_modules\sails-mysql\lib\connections\spawn.js:99:35
      at Cursor.attachRecords (node_modules\waterline-cursor\cursor\cursor.js:117:10)
      at afterPopulateBuffers (node_modules\waterline-cursor\cursor\cursor.js:55:10)
      at node_modules\sails-mysql\lib\adapter.js:786:15
      at node_modules\sails-mysql\node_modules\async\lib\async.js:451:17
      at node_modules\sails-mysql\node_modules\async\lib\async.js:441:17
      at _each (node_modules\sails-mysql\node_modules\async\lib\async.js:46:13)
      at Immediate.taskComplete (node_modules\sails-mysql\node_modules\async\lib\async.js:440:13)

UPDATE:
I tested in this repo, I can reproduce the issue with the TeamController.add_member and TeamController.remove_member when changing the commit for a rollback after updating sails from 0.11.5 to 0.12.11

UPDATE 2:

After some research testing with sails 0.12.11, I found out that the issue is not when doing the rollback, but when doing the update using the transaction in my test. The association gets removed right away before commit or rollback. After some testing, I found out that the location of waterline used by Sails has moved from sails' dependencies in node_modules/sails/node_modules/waterline to node_modules/sails-hook-orm/node_modules/waterline so I tried changing the postinstall script to inject waterline at the right place. After doing that, the update/rollback is now working, but I am having a new issue when using the promise syntax or exec syntax vs inline function on the create:

This works just fine:

it('should rollback create', done => {
            const Transaction = require('sails-mysql-transactions').Transaction;
            
            Transaction.start((err, transaction) => {
                Dog.transact(transaction)
                .create({name:'fido'}, (err, dog) => {
                    transaction.rollback(err => {
                        Dog.count({})
                        .then(count => {
                            count.should.be.equal(0);
                            done();
                        });
                    });
                });
            });
        });

This syntax does not not work:

it('should rollback create', done => {
            const Transaction = require('sails-mysql-transactions').Transaction;
            
            Transaction.start((err, transaction) => {
                Dog.transact(transaction)
                .create({name:'fido'})
                .exec((err, dog) => { // <-- This .exec function never gets called
                    transaction.rollback(err => {
                        Dog.count({})
                        .then(count => {
                            count.should.be.equal(0);
                            done();
                        });
                    });
                });
            });
        });

Same thing for this syntax, it doesn't work:

it('should rollback create', done => {
            const Transaction = require('sails-mysql-transactions').Transaction;
            
            Transaction.start((err, transaction) => {
                Dog.transact(transaction)
                .create({name:'fido'})
                .then(dog => { // <-- This .then function never gets called
                    transaction.rollback(err => {
                        Dog.count({})
                        .then(count => {
                            count.should.be.equal(0);
                            done();
                        });
                    });
                });
            });
        });