Node Mysql async multiple queries

Question

I was wondering what's the best way to handle nested mysql-queries is nodejs.

So something like this:

connection.query("SELECT * FROM blogs ORDER BY time DESC", function(err, blogs, fields) {

   for (blog in blogs) {

        connection.query("SELECT * FROM tags WHERE blog_id='blog.id' ", function(err, tags, fields) {

        blog.tags = tags

     });

   }

   res.send(blogs)

});

This obviously doesn't work, because of the async nature. The result already gets returned before the tags are fetched.

I've been reading up on node and callbacks and promises seems to be the way to go. But I'm unable to see how I would best use them in this small example.

Thx!

Answer 1

So you have to wait for all callbacks to return before you send the response. If we ignore error handling and empty results for simplicity this can be done similar to:

var callback = function(blogs) {
    res.send(blogs);
}

connection.query("SELECT * FROM blogs ORDER BY time DESC", function(err, blogs, fields) {
    var pending = blogs.length;

   for (blog in blogs) {

        connection.query("SELECT * FROM tags WHERE blog_id='blog.id' ", function(err, tags, fields) {
        blog.tags = tags;

        if (0 === --pending) {
            callback(blogs);
        }
     });
   }
});

With promises, look into Promise.all function which returns a new promise. This promises is resolved when all promises passed to it in the array are resolved. With the Q library it should be something like:

var getTags = function(blog) {
    var deferred = Q.defer();
    connection.query("SELECT * FROM tags WHERE blog_id='blog.id' ", function(err, tags, fields) {
        blog.tags = tags;
        deferred.resolve();
    });
    return deferred.promise;
}

var promises = blogs.map(getTags(blog));

Q.all(promises).then(res.send(blogs));

Answer 2

You can use async.series module(to iterate over the queries`)

async.eachSeries(blogs,
                function (query, callback) {

                    connection.query(blogs.blog, "SELECT * FROM tags WHERE blog_id='blog.id'" , function (err, result) {
                        if (err) {

                            //throw err;
                            callback(err, null);
                            return;

                        } else {
                            console.log('Query executed successfully');
                            blogs.blog.tags = tags;
                        }
                        callback(null, blogs.blog);

                    });

                },

                function finalCallback(err, results) {

                            return callback(null, results);
                        });
                    }
                });

        });



    });`

You can enhance this with and query.on feature (to process each row)

Answer 3

You can try something like this;

connection.query("SELECT * FROM blogs ORDER BY time DESC", fetchedBlogs);

function fetchedBlogs(err, blogs, fields) {

    if (err || !blogs || !blogs.length) {
        // an error occurred or no blogs available
        // handle error or send empty blog array
        return res.send();
    }

    var count = blogs.length;

    (function iterate(i) {
            if (i === count) {
                // all blogs processed!
                return res.send(blogs);
            }

            var blog = blogs[i];

            connection.query("SELECT * FROM tags WHERE blog_id='" + blog.id +
                "'", fetchedTags);

            function fetchedTags(err, tags, fields) {
                blog.tags = tags;
                iterate(i+1);
            });
    })(0);
}

Hope this helps.