Run multiple MSSQL queries in node.js

Question

I am trying to run multiple SQL queries.

The problem is that variables keep going out of scope due to fire-and-forget node.js uses. I am trying to work out how to do something similar to the await keyword in C#.

Here is my broken code, as is; which should make it clearer as to what I am trying to achieve:

function getTopicsForMessages(request, results)
{
    //Get topics for this message
    var queryString = "SELECT ripple_messenger.TopicTypes.name, ripple_messenger.TopicTypes.imageUri";
    queryString += " FROM ripple_messenger.MessageTopics JOIN ripple_messenger.TopicTypes";
    queryString += " ON (ripple_messenger.MessageTopics.topicId = ripple_messenger.TopicTypes.id)";
    queryString += " WHERE messageId = ?";

    for (var i = 0; i < results.length; i++)
    {
        mssql.query(queryString, [results[i].id],
        {
            success: function (topicsResults)
            {
                results[i].topics = topicsResults;
            },
            error: function (err) {
                console.error("SQL Read error: " + err);
                request.respond(statusCodes.INTERNAL_SERVER_ERROR, "SQL Query failed on read");
            }
        });
    } 
    //Continue... once all queries have executed successfully.
}

I think this is just a problem of not quite knowing the node way of doing things.

I've read about a few packages / options for Node.js which allow things similar to the await keyword, but I'm not sure if I would be able to use them seens as I am using Azure Mobile Services.

I'm also sure I could somehow do this query better in SQL. But again, I'm not quite sure how.

Many thanks in advance,

Danny

you want use use the promise pattern: http://stackoverflow.com/questions/17351494/promises-pattern-in-node-js-libraries-to-use — Populus, Jul 24 '14 at 21:52

score 0 · Answer 1 · answered Jul 25 '14 at 21:45

The code below shows one possible implementation. Since all DB calls in node.js are asynchronous, if you run them into a "regular" for loop you'll be sending all the calls to the DB at the same time, and that may trigger some quota. The code below sends the queries one at a time (by waiting until one call is completed to call the next one). You can also send some of the queries in parallel (using something similar to the batching technique shown in this post).

function getTopicsForMessages(request, results)
{
    //Get topics for this message
    var queryString = "SELECT ripple_messenger.TopicTypes.name, ripple_messenger.TopicTypes.imageUri";
    queryString += " FROM ripple_messenger.MessageTopics JOIN ripple_messenger.TopicTypes";
    queryString += " ON (ripple_messenger.MessageTopics.topicId = ripple_messenger.TopicTypes.id)";
    queryString += " WHERE messageId = ?";

    var queryNextItem = function(i) {
        if (i >= results.length) {
            // All done
            continuation();
        } else {
            mssql.query(queryString, [results[i].id],
            {
                success: function (topicsResults)
                {
                    results[i].topics = topicsResults;
                    queryNextItem(i + 1);
                },
                error: function (err) {
                    console.error("SQL Read error: " + err);
                    request.respond(statusCodes.INTERNAL_SERVER_ERROR, "SQL Query failed on read");
                }
            });
        }
    }

    queryNextItem(0);

    function continuation() {
        //Continue... once all queries have executed successfully.
    }
}

Run multiple MSSQL queries in node.js

1 Answers1