Sails.js + Waterline + MySQL => Transactions?

Sails.js

This is the road...

There is no need for special introduction of Sails.js for anyone toying around with node.js lately. For those who have been visiting outskirts of known universe until recently, it is a MVC framework, simple and efficient, does what it says.

Then there's Waterline, an integral part of Sails, a nice ORM to provide models for your controllers and talk to those ugly things from the past - the databases. Maybe not so much in the past, there are adapters for just MongoDB and other hip things available. But for the problem we were trying to solve, a standard, non high performance relational database was wanted, so MySQL seemed good enough for the job.

And it was. Models, views, controllers, everything clicked together like a set of Legos, right until the moment where we needed transactions. The application in question is supposed to keep track of products in store. When a customer buys a product, it should check the counts and print the invoice. If it finds that there isn't enough of a product it should reject all the entries and force the clerk to adjust the values.

... to nowhere

This is where you think of transactions. Right, no problem, MySQL has had those for years. But you're not talking to MySQL, you're talking to Waterline who, in turn, talks to the sails-mysql adapter, who then talks to the DB. And, as it turns out, Waterline does not talk transactions yet. At first I wanted to dump the implementation, fire up Visual Studio and do it right. But that had a bitter sense of failure to it. Then there was an option of using a different ORM, one that knows stuff that a decent ORM should know. There is no lack of those. Bookshelf.js, Sequelize, node-orm2, each with it's gaps and some awesome features. But integrating that into sails means some hard work much below the level that was intended at the beginning. So if we could just pull the transactions through somehow...

... but maybe?

Searching through Waterline documentation, a method I previously had no use for caught my attention. Model.query() takes as a parameter a SQL string of your choice. So, why not look up the syntax and just start the transaction manually, do your bidding and finish it off in the same way?

complete: function (req, res) {
    var invoiceId = req.params.all().id;


    InvoiceEntries.find({racun: invoiceId})
    .populate('product')
    .then( function(entries) {
        ProductStore.query("START TRANSACTION;");
        return entries;
    })
    .then(function(entries) {
        var m = Promise.map(entries, function(entry) {
         return process(entry);
         }, {concurrency: 1})
            .catch(function(x){throw x;})

        return Promise.all(m).catch(function(e){throw e;})
    })
    .then(function () {
        //... set the status of the invoice here

        return Invoice.update({id: invoiceId}, update);
    })
    .then(function (invoice) {
        return ProductStore.query("COMMIT;");
    })
    .then(function () {
        res.json(200);
    })
    .catch(function(e){
            ProductStore.query("ROLLBACK;", function(err){
            if(err){ console.log(err)}
        })
        return res.serverError(e.message);
    });
} 

process = function(entry) {
    var before = 0, after = 0;
    return ProductSupply.findOne({product: entry.product.id})
    .then(function (supply) {
        before = supply.count;
        after = supply.count - entry.quantity;
        if (supply.count < entry.quantity) {
            throw new Error('You are selling ' + entry.quantity + ' of ' + entry.product.name + ', but you have only ' + supply.count + 'units available');
        }
        return ProductSupply.update({product: entry.product.id}, {count: supply.count - entry.quantity})
        })
        .catch(function(e){
            console.log('rethrow ' + e);
            throw e;
        })
    }

When executed, this code does not work. It seems to handle the offending product correctly, and it does not decrease it's count, but the changes to other products that might have been on the invoice, remain. Where did that come from?

Hey, this is node!

In my noob attempt of handling the problem I forgot that, in Node.js, things go paralell when they can. And the connections in the mysql adapter are poooled, meaning that whenever your app needs to talk to DB, it calls a function, which checks if there are free connections in the pool. If so, it returns one, but if not, it just opens another one and returns that. What happened above was that at first we got a connection, then opened the transaction and then proceeded to update the model. Here the adapter was asked for a connection, and since the first one was locked up by our transaction, it opened anothe one, with no transaction. With no means of referencing the transaction in our updates, the only solution was to do everything manually. That means fetching the connection, initializing the transaction (mind the syntax START TRANSACTION, not BEGIN TRANSACTION), updating manually (with SQL) and closing the transaction accordingly.

complete: function (req, res) {
    var invoiceId = req.params.all().id;

    var mySqlPath = process.env.PWD + '/node_modules/sails-mysql/node_modules/mysql';
    var mysql = require(mySqlPath);

    var sailsMySqlConfig = sails.config.connections.yourConnectionName;
    var connection = mysql.createConnection({
        host: sailsMySqlConfig.host,
        user: sailsMySqlConfig.user,
        password: sailsMySqlConfig.password,
        database: sailsMySqlConfig.database,
        multipleStatements: true,
    });

    InvoiceEntries.find({racun: invoiceId})
    .populate('product')
    .then( function(entries) {
        connection.query("START TRANSACTION;");
        return entries;
    })
    .then(function(entries) {
        var m = Promise.map(entries, function(entry) {
            return process(entry, conn);
        }, {concurrency: 1})
        .catch(function(x){throw x;})

        return Promise.all(m).catch(function(e){throw e;})
    })
    .then(function () {
        //... set the status of the invoice here

        return Invoice.update({id: invoiceId}, update);
    })
    .then(function (invoice) {
        connection.query("COMMIT;", function(err){
            if(err){ console.log(err)}
            connection.end(function(err) {
                if (err) {
                    console.log(err)
                }
            });
        });
    })
    .then(function () {
        res.json(200);
    })
    .catch(function(e){
        connection.query("ROLLBACK;", function(err){
            if(err){ console.log(err)}
            connection.end(function(err) {
                if (err) {
                    console.log(err)
                }
            });
        });
        return res.serverError(e.message);
    });
} 

process = function(entry, conn) {
    var before = 0, after = 0;
    return ProductSupply.findOne({product: entry.product.id})
    .then(function (supply) {
        before = supply.count;
        after = supply.count - entry.quantity;
        if (supply.count < entry.quantity) {
            throw new Error('You are selling ' + entry.quantity + ' of ' + entry.product.name + ', but you have only ' + supply.count + 'units available');
        }
        return conn.query("UPDATE PRODUCTSUPPLY SET COUNT = COUNT - " + entry.quantity + " WHERE PRODUCT = " + entry.product.id);
        })
        .catch(function(e){
            console.log('rethrow ' + e);
            throw e;
        })
    }

Notice that the selects are still done using pooled connections. The only trade-off performance-wise is that you never get to use a pooled connection for your updates. If you select a lot and update a little, that's not going to hurt you too much.

Luckily, Waterline is supposed to get real transaction support in the next release. Until then, we're stuck to doing things old school. But use this to show those youngsters that databases do matter and knowing the mechanics behind the scene does help...