Promised Connections Returning Nothing (JS)

Question

Problem with Promised Connections

I recently converted my Node app from running on my local machine to utilizing an Amazon EC2 for the Node app and a VPN for the file-serving and MySQL.

I learned just enough about Promises to write the following connection snippet (which runs 3 queries before responding to the client), utilizing Bluebird. The connections worked on my machine, but with the VPN hosted MySQL settings, the connections crashed every time, about 30 seconds after the app started, which I realized was probably because I'd forgotten to close them.

EDIT: Based on the comments, it appears the issue is not in the connection closures.

So I modified my script in the best way I knew to close the connections, but with Promises, this is confusing. This version of the connection doesn't work. It doesn't fail or cause any errors. It just returns no results on the server side. I think my problem is in the way I've closed the connections.

• What's causing the issue?

• Is it the connection closures?

• If so, how would I close them properly?

My (Simplified) MySQL Connection Attempt with Bluebird Promises

var mysql = require('mysql');
var Promise = require('bluebird');
var moment = require('moment');
function createConnection() {
    var connection = mysql.createConnection({
            dateStrings : true,
            host : 'hostname', 
            user : 'username',
            password : 'password', 
            database : 'database' 
        });
    connection = Promise.promisifyAll(connection);
    return connection;
}
function sendGame(req, res, sales, settings, categories, players) {
    var game = new Object();
    game.sales = sales;
    game.players = players;
    game.settings = settings;
    game.categories = categories;
    var JSONgame = JSON.stringify(game);
    console.log("Game: " + JSON.stringify(game, undefined, 4));
}
var retrieveSales = Promise.method(function (username, connection, timeFrame) {
        console.log('User ' + username + ' retrieving sales...');
        var q = 'select * from sales_entries where date BETWEEN ? AND ?';
        return connection.queryAsync(q, timeFrame).then(function (results) {
            return results[0];
        });
    });
var retrieveSettings = Promise.method(function (username, connection) {
        console.log('User ' + username + ' retrieving settings...');
        var q = 'select * from sales_settings';
        return connection.queryAsync(q).then(function (results) {
            return results[0];
        });
    });
var retrieveCategories = Promise.method(function (username, connection) {
        console.log('User ' + username + ' retrieving categories...');
        var q = 'select * from sales_categories';
        return connection.queryAsync(q).then(function (results) {
            return results[0];
        });
    });
var retrievePlayers = Promise.method(function (username, connection) {
        console.log('User ' + username + ' retrieving players...');
        var q = 'select * from users';
        return connection.queryAsync(q).then(function (results) {
            return results[0];
        });
    });
var gameSucceed = Promise.method(function gameSucceed(req, res) {
        var username = req.body.username;
        console.log('User ' + req.body.username + ' retrieving game...');
        var timeFrame = [moment().days(0).hour(0).minute(0).second(0).format("YYYY-MM-DD HH:mm:ss"), moment().days(6).hour(0).minute(0).second(0).format("YYYY-MM-DD HH:mm:ss")];
        //var connection = Promise.promisifyAll(createConnection());
        return connection.connectAsync().then(function () {
            console.log('Connection with the MySQL database openned for Game retrieval...');
            return Promise.all([retrieveSales(username, connection, timeFrame), retrieveSettings(username, connection), retrieveCategories(username, connection), retrievePlayers(username, connection)]);
        }).then(function () {
            connection.end(),
            console.log("...Connection with the MySQL database for Game retrieval ended")
        });
    });
function getGameData(req, res) {
    gameSucceed(req, res).spread(function (sales, settings, categories, players) {
        return sendGame(req, res, sales, settings, categories, players);
    });
};
var req = new Object();
var res = new Object();
req.body = {
    "username" : "user123",
    "password" : "password"
}
getGameData(req, res);

Console Result

User user123 retrieving game...  
Connection with the MySQL database openned for Game retrieval...
User user123 retrieving sales... 
User user123 retrieving settings... 
User user123 retrieving categories... 
User user123 retrieving players... 
...Connection with the MySQL database for Game retrieval ended 
Game: {}

Answer 1

var gameSucceed = function gameSucceed(req, res) {
    …
    var connection = createConnection());
    return connection.connectAsync().then(function () {
        return Promise.all([…]);
    }).then(function () {
        connection.end();
    });
};

The promise that is ultimately returned from this method does not have a resolution value. It is created by that then call from whose callback you do not return - which will lead to undefined. To fix this, just route the result through:

.then(function(results) {
    connection.end();
    return results;
});

However, if you do it like that the connection won't be closed in case of an error. The best solution is to use the finally() method, which just works like a finally clause in synchronous code. It's callback will be invoked both for resolutions and rejections, and the resulting promise will automatically carry on the value.

.finally(function() {
    connection.end();
})
// .then(function(results) { })

Answer 2

Your code has a particular resource management problem like Bergi put it. You have to keep remembering when to close the collection and when not to.

The optimal solution would be to use Promise.using however, that's only available in the v2 branch of Bluebird so you're going to have to wait a while.

Until then, you can create your own wrapper method that does more basic scoped resource management:

function connect(fn,timeout){
    timeout = (timeout === undefined) ? 8000 : timeout; // connection timeout
    return createConnection().then(function(connection){
        // run the function, when it resolves - close the connection
        // set a 7 second timeout on the connection
        return fn(connection).timeout(timeout).finally(function(){  
             connection.end();
        });
    });
}

Which would let you do:

connect(function(connection){
    return gameSucceed(req,resp,connection); // connection is injected to that fn now
}).then(function(val){
      // gameSucceed resolution value here
});

Now, when the gameSucceed is done, the connection will close itself automatically. This would make gameSucceed itself look like:

var gameSucceed = Promise.method(function gameSucceed(req, res,connection) {
    var username = req.body.username;
    console.log('User ' + req.body.username + ' retrieving game...');
    var timeFrame = [moment().days(0).hour(0).minute(0).second(0).format("YYYY-MM-DD HH:mm:ss"), moment().days(6).hour(0).minute(0).second(0).format("YYYY-MM-DD HH:mm:ss")];
    return connection.connectAsync().then(function () {
        console.log('Connection with the MySQL database openned for Game retrieval...');
        return Promise.all([retrieveSales(username, connection, timeFrame), retrieveSettings(username, connection), retrieveCategories(username, connection), retrievePlayers(username, connection)]);
    }); // no longer its responsibility to handle the connection
});

Generally, you might also want to consider a more OOPish style of coding for your code.

Good luck, and happy coding.