Node + Mysql: How to execute more queries based on another query

Question

I'm pretty new to the node world and trying to migrate our php application to node. To be able to return all article data several different queries have to be done depending on the results of the first query. Currently my data object is empty as it's returned before the two queries run. How can I "chain" these queries using a promised based approach.

I found a library https://github.com/lukeb-uk/node-promise-mysql which I think could help but I have no idea how to implement it with my code.

exports.getArticleData = function(req, done) {
  pool.getConnection(function(error, connection) {
    if (error) throw error;

    var data = {
        article: {},
        listicles: []
    };

    // Inital query
    connection.query(
        `SELECT article_id, title, is_listicle_article, FROM com_magazine_articles AS article WHERE article_id = ${req
            .params.articleId}`,
        function(error, results) {
            data.article = results;
        }
    );

    // This query should only be excuted if is_listicle_article = true
    if (data.article.is_listicle_article) {
        connection.query(
            `SELECT * FROM com_magazine_article_listicles WHERE article_id = ${req.params
                .articleId}`,
            function(error, results) {
                data.listicle = results;
            }
        );
    }

    // More queries depending on the result of the first one
    // ....
    // ....

    // Callback with the data object
    done(data);

    connection.release();
  });
};

What would be the best approach to execute queries based on other queries results? Any help is really appreciated.

Answer 1

The functionality you are looking for is Promise chaining, it allows you to construct a sequence of promises, each depending on the result of the previous value. Applying this to your code, you would get something like this:

exports.getArticleData = function(req, done) {
  pool.getConnection(function(error, connection) {
    if (error) throw error;

    // Inital query
    return connection.query(
        `SELECT article_id, title, is_listicle_article, FROM com_magazine_articles AS article WHERE article_id = ${req
            .params.articleId}`
    ).then((rows) => {
    
       return Promise.all(rows.map((article) => {
          if (article.is_listicle_article) {
            return connection.query(
                `SELECT * FROM com_magazine_article_listicles WHERE article_id = ${req.params
                    .articleId}`
             );
          } else {
             return Promise.resolve(null);
          }
       }));
    }).then((res) => {
     connection.release();
     done(res.filter(function(i){ return i != null; }));
    })

    // This query should only be excuted if is_listicle_article = true
    

    // More queries depending on the result of the first one
    // ....
    // ....

    // Callback with the data object
    connection.release();
  });
};

Obviously since I don't have all of your code, I couldn't verify this example, but this should be roughly the functionality you are looking for. That said, I think there were a couple of mistakes you should watch out for in your example code:

• connection.query() returns a promise (aka doesn't need a callback function). Use this functionality to your advantage- it will make your code prettier.
• connection.query() returns an array of rows, not a single value. You seemed to ignore this in your example code.
• Try not to save things into a variable when using promises, it isn't necessary. To remedy this, read more into the Promise API (Promise.resolve(), Promise.reject(), Promise.any(), Promise.catch(), Promise.all()) etc.
• It seems like these SQL queries could easily be combined into a single query. This will be way more efficient that performing two operations. Not sure if this is the case with the remaining queries you wish to use, but definitely something to look out for.