How do i format mysql query to utilize async/promsies?

Question

I'm trying to make a simple query on my database with 2 parameters, and I'm having trouble trying to reformat this code using Promises/Async to avoid callback hell. Here is the code:

module.exports = {
  getDailyRaw: function(school, meal, callback) {
     var sql = "SELECT * FROM daily WHERE school = ? AND meal = ?";
     pool.getConnection(function(err, conn) {
         if(err) { console.log(err); callback(true); return; }
         conn.query(sql, [school, meal], function(err, results) {
             conn.release();
             if(err) { console.log(err); callback(true); return; }
             callback(false, school, results);
         })
     })
  }, ....

I've been constantly trying to look up tutorials on fixing this issue, but haven't been able to implement/understand them properly.

EDIT: I am now using the 'promise-mysql' library to get the job done, but I am still having trouble with chaining together multiple queries. How would I pass over the already established connection into another then statement so that I can call for a query again? Updated code:

getDaily: function(school, meal, callback) {
    pool.getConnection().then(function(conn) {
        var sql = "SELECT * FROM daily WHERE school = ? AND meal = ?";
        return conn.query(sql, [school,meal]);
    }).then(function(rows) {
        var qMarks = "";
        var foodNames = [school];
        rows.forEach(function(item) {
            foodNames.push(item.name);
            qMarks += "?,";
        });
        qMarks = qMarks.substring(0, qMarks.length - 1);
        var sql = "SELECT * FROM foods WHERE school = ? AND name IN (" + qMarks + ")";
        return conn.query(sql, foodNames);
    }).then(function(finalData) {
        callback(finalData);
    }).catch(function(err) {
        callback(null);
        console.log(err);
    })
},

Which exact mysql module are you using with node.js? Is it node-mysql? — jfriend00, Jun 10 '17 at 23:39
The package name just looks to be 'mysql', but I'm not sure if that's the same as node-mysql. — Kevin Lee, Jun 10 '17 at 23:40
Perhaps you can use promise-mysql: https://www.npmjs.com/package/promise-mysql — jfriend00, Jun 10 '17 at 23:40
Thanks for the idea! See updated code however for a new issue that came up. — Kevin Lee, Jun 11 '17 at 00:22

score 0 · Accepted Answer · answered Jun 11 '17 at 01:16

There are a variety of ways to make variables from one chained .then() available to someplace later in the chain. You can see a summary of those options here in How to Chain and Share Prior Results with Promises.

For this particular case, I'd suggest one level of nesting like this:

getDaily: function(school, meal, callback) {
    pool.getConnection().then(function(conn) {
        var sql = "SELECT * FROM daily WHERE school = ? AND meal = ?";
        return conn.query(sql, [school,meal]).then(function(rows) {
            var qMarks = "";
            var foodNames = [school];
            rows.forEach(function(item) {
                foodNames.push(item.name);
                qMarks += "?,";
            });
            qMarks = qMarks.substring(0, qMarks.length - 1);
            var sql = "SELECT * FROM foods WHERE school = ? AND name IN (" + qMarks + ")";
            return conn.query(sql, foodNames);
        }).then(function(finalData) {
            callback(finalData);
        });
    }).catch(function(err) {
            callback(null);
            console.log(err);
    });
},

And, it's really better to just get rid of callbacks all together and just return the promise and let the caller use the promise. Then, if the caller wants to synchronize this async operation with others, it's already using a promise for them to make that easier:

getDaily: function(school, meal) {
    return pool.getConnection().then(function(conn) {
        var sql = "SELECT * FROM daily WHERE school = ? AND meal = ?";
        return conn.query(sql, [school,meal]).then(function(rows) {
            var qMarks = "";
            var foodNames = [school];
            rows.forEach(function(item) {
                foodNames.push(item.name);
                qMarks += "?,";
            });
            qMarks = qMarks.substring(0, qMarks.length - 1);
            var sql = "SELECT * FROM foods WHERE school = ? AND name IN (" + qMarks + ")";
            return conn.query(sql, foodNames);
        });
    }).catch(function(err) {
        console.log(err);
        throw err;
    });
},

Note: If you are returning the promise and want to log the error in a .catch(), you have to rethrow the error in order to make sure the returned promise is still rejected (just like with try/catch).

Thank you so much! This looks great. However, when using this promise in my routing file for index.js, do I append a .then to the function call getDaily to continue the chain? When i try that, it says that cannot read property 'then' of undefined, not letting me able to chain it. How would I fix this? — Kevin Lee, Jun 11 '17 at 01:31
Nevermind, I forgot to put the return up at the very top so that it would return the promise itself. Thanks a bunch! — Kevin Lee, Jun 11 '17 at 01:34

score 0 · Answer 2 · answered Jun 11 '17 at 02:23

You can write and execute your logic in sequential manner via synchronous executor nsynjs. It runs code step-by-step, automatically pauses on promises and waits for their resolution before going to next step. The code will look like this:

function synchronousCode(pool, school, meal){
    var conn = pool.getConnection().data;
    var sql = "SELECT * FROM daily WHERE school = ? AND meal = ?";
    var rows = conn.query(sql, [school,meal]).data;
    // rows from 'daily' table are ready at this point
    var qMarks = "";
    var foodNames = [school];
    for(var i=0; i<rows.length; i++) {
        foodNames.push(rows[i].name);
        qMarks += "?,";
    };
    qMarks = qMarks.substring(0, qMarks.length - 1);
    sql = "SELECT * FROM foods WHERE school = ? AND name IN (" + qMarks + ")";
    var foods = conn.query(sql, foodNames).data;
    // rows from 'foods' table are ready at this point
    ... process food, or ...
    return foods;
};

var nsynjs = require('nsynjs');
var school =..., meal = ..., pool = ...;
nsynjs.run(synchronousCode,{},pool, school, meal,function(foods){
    console.log('synchronousCodeis done',foods);
});

How do i format mysql query to utilize async/promsies?

2 Answers2