NodeJS + MySql : Is this code correct for multiple select queries since they are asyncronous

Question

I was wondering if I'm doing this right inside my route page when I need to make multiples SELECT queries.

Since I need to render my page with data, I have to wait for all queries to be finished, otherwise it renders the page without waiting the queries to be finished (since they are async)..

pool.getConnection(function(err, conn) {
    var array = [];
    var IDS = [];
    var array2 = [];
    var array3 = [];

    //query 1
    query = conn.query('SELECT * FROM table1 LIMIT 0, 5');
    query.on('error', function(err){
        throw err;
    });
    query.on('result', function(row){
        array.push(row);
        IDS.push(row.id);
    });
    query.on('end', function(result){
        //query 2 with results from query 1
        query = conn.query('SELECT * FROM table2 WHERE table1_id IN (?)', [IDS]);
        query.on('error', function(err){
            throw err;
        });
        query.on('result', function(row){
            array2.push(row);
        });
        query.on('end', function(result){
            //query 3
            query = conn.query('SELECT * FROM table3');
            query.on('error', function(err){
                throw err;
            });
            query.on('result', function(row){
                array3.push(row);
            });
            query.on('end', function(result){
                res.render('index', {data1: array, data2: array2, data3: array3});
            });
        });
    });
    conn.release();

});

As you can see, I put my second query inside the END stream of my first query and so on. When I have to make more queries it begins to be a nightmare.

And I'm not talking of every if/else statement + duplicate res.render I have to use when I'm using data from a previous query to make a new one (When the first query has no result I need to avoid making a second one with 0 result).

To finish, I would also like to know if the conn.release() code is put at the right place or should it be placed right after res.render ?

Thank you for helping

Answer 1

So I found out the async.js module which allows to run functions in parralel or in series.

This is my first attempt with the module (Please correct me if I'm doing it wrong) and it seems to work fine.

Don't look at my SQL queries since I truncated some for better view. If you're wondering what my code is for: I use this to show feeds (posts) with their comments and a like system (like a feed, like a comment)

In the code, I use :

• 2 queries in series (get the 5 last feeds, then I get the comments for theses 5 feeds).
• 1 more task in series which does 2 parralel task (Get the likes for the feeds and comments)
• Render my template with the global array db_result

Maybe I'm doing wrong because I don't really use any callback from the async module, all I do is popolating my global arrays...

var db_feedIDS = [];
var db_feedIndex = [];
var indexFeed = -1;

var db_commentsIDS = [];
var db_commentsIndex = [];
var indexComments = -1;
var lastComment = [];

var db_result = [];

async.series({
    Feeds: function(callback){
        pool.getConnection(function(err, conn) {
            query = conn.query('SELECT * FROM feed LIMIT 0, 5');
            query.on('error', function(err){
                callback(err);
            });
            query.on('result', function(row){
                indexFeed++;
                db_feedIndex[row.id] = indexFeed;
                db_feedIDS.push(row.id);
                db_result.push({"id": row.id, "date": row.date, "content": row.content, "comments": [], "like":[]});
            });
            query.on('end', function(result){
                conn.release();
                callback(null, db_feedIDS.length);
            });
        });
    },
    Comments: function(callback){
        if(db_feedIDS.length == 0){
            callback(null, 0);
        }else{
            pool.getConnection(function(err, conn) {
                query = conn.query('SELECT * FROM feed_comments, users WHERE feed_comments.feedid IN (?)', [db_feedIDS]);
                query.on('error', function(err){
                    callback(err);
                });
                query.on('result', function(row){
                    if(!lastComment[row.id_comments]){
                        lastComment[row.id_comments] = [];
                        indexComments = -1;
                    }
                    indexComments++;
                    db_commentsIDS.push(row.id_comments);
                    db_commentsIndex[row.id_comments] = indexComments;
                    var a = {"id_comments": row.id_comments, "content_comments": row.content_comments, "date_comments": row.date_comments, "likeComment":[]};
                    db_result[db_feedIndex[row.feedid_comments]].comments.push(a);
                });
                query.on('end', function(result){
                    conn.release();
                    callback(null, db_commentsIDS.length);
                });
            });
        }
    },
    Likes: function(callback){
        async.parallel({
            likes_feed: function(callback){
                if(db_feedIDS.length == 0){
                    callback(null, 0);
                }else{
                    pool.getConnection(function(err, conn) {
                        query = conn.query('SELECT * FROM feed_like, users WHERE feed_like.feed_id IN (?) AND users.id = feed_like.userid ORDER BY feed_like.id ASC', [db_feedIDS]);
                        query.on('error', function(err){
                            callback(err);
                        });
                        query.on('result', function(row){
                            var b = {"id_like": row.id_like, "userid_like": row.userid_like, "username_like": row.username_like, "img_thumb_like": row.img_thumb_like};
                            db_result[db_feedIndex[row.feedid_like]].like.push(b);
                        });
                        query.on('end', function(result){
                            conn.release();
                            callback(null, "ok");
                        });
                    });                                
                }
            },
            likes_comment: function(callback){
                if(db_commentsIDS.length == 0){
                    callback(null, 0);
                }else{
                    pool.getConnection(function(err, conn) {
                        query = conn.query('SELECT * FROM feed_comments_like, users WHERE feed_comments_like.comment_id IN (?) AND users.id = feed_comments_like.userid ORDER BY feed_comments_like.id ASC', [db_commentsIDS]);
                        query.on('error', function(err){
                            throw err;
                        });
                        query.on('result', function(row){
                            var c = {"id_comments_like": row.id_comments_like, "feedid_comments_like": row.feedid_comments_like, "commentid_comments_like": row.commentid_comments_like, "userid_comments_like": row.userid_comments_like, "username_comments_like": row.username_comments_like, "img_thumb_comments_like": row.img_thumb_comments_like}
                            db_result[db_feedIndex[row.feedid_comments_like]].comments[db_commentsIndex[row.commentid_comments_like]].likeComment.push(c);
                        });
                        query.on('end', function(result){
                            conn.release();
                            callback(null, "ok");
                        });
                    });
                }
            }
        },
        function(err, results) {
            callback(null, results); 
        });
    }
},
function(err, results) {
    console.log(results);
    res.render('index', { title: "Blabla", db_feed: db_result });
});