Synchronous mysql in Node.js

Question

I know that node.js is event driven and I should do this async but i can't find in my mind a way to do it.

so, i have this

var querystr = "SELECT * FROM groups";
    var result = "";
    dbClient.query(querystr, function (err, res, fields) {
        if (err) {
            console.log(err);
            return;
        }
        for(var i in res) {
            result = result + "@" +res[i].name + " =";
            for (var j in res[i].members.split(",")) {
                var memberquery;
                if (j.substr(0,1) == "@") {
                    memberquery = "SELECT name FROM groups WHERE id = "+j.substr(1, j.length-1);
                } else {
                    memberquery = "SELECT username FROM users WHERE id= "+j;
                }
                dbClient.query(memberquery, function(err, memres, fields) {
                    var membername = "";
                    if (typeof memres[0].username == "undefined") {
                        membername = "@"+memres[0].name;
                    } else {
                        membername = memres[0].username;
                    }
                    result = result + " " + membername; 
                });

            }
            result = result + "\n"; 
        }
    });

The issue that makes it sync is the for inside. basically i'm generating a document in the result variable where i check the groups and tell the members so the expected output is

Group1 = member, member
Group2 = member, member

Couple of quick asides: If you did a first pass of your first query results, and loop into the members on a second pass, you could avoid the SELECT name FROM groups queries. Additionally, if your DB was normalized properly, you could imagine a single query doing all that table-matching work for you. — Metal
– Metal, Commented Aug 9, 2011 at 2:51

Geoff Chappell · Accepted Answer · 2011-07-10 12:01:59Z

3

I usually use a pattern like the one below for this type of problem. In a nutshell: get a list of things then call a function to handle the list; that function calls itself until the list is completed; gather up the results as you go in an accumulator; when the list is empty, return whatever you've accumulated through the callback. It's just another way of accomplishing what @Andrey Sidorov demonstrated in his response.

//cb is (err, res) 
function getData(cb){
  var querystr = "SELECT * FROM groups";
  var result = "";

  dbClient.query(querystr, function (err, res, fields) {
      if (err)
          cb(err);
      else {
        var groups = [];
        for (var ndx in res)
          groups = groups.concat(res[ndx].members.split(","));

        getMembers(groups, [], cb);
      }
  });
}

function getMembers(members, sofar, cb){
  var member = members.shift();

  if (!member)
    cb(null, sofar);
  else {
    var memberquery;
    var params;

    if (member.substr(0,1) == "@") {
        memberquery = "SELECT name FROM groups WHERE id = ?";
        params = [member.substr(1, member.length-1)];
    } else {
        memberquery = "SELECT username FROM users WHERE id = ?";
        params = [member];
    }

    dbClient.query(memberquery, params, function(err, res) {
      if (err)
        cb(err);
      else {
        var membername = "";
        if (typeof res[0].username == "undefined") {
            membername = "@" + res[0].name;
        } else {
            membername = res[0].username;
        }

        sofar.push(membername);

        getMembers(members, sofar, cb);
      }
    });
  }
}

answered Jul 10, 2011 at 12:01

Geoff Chappell

2,4421 gold badge16 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Geoff Chappell Over a year ago

@Andrey true, though node-mysql queues them anyway and only issues the next when the previous has completed -- so I don't think there's really much difference in that respect. But, you're right, that if you used some pool and could issue the queries simultaneously to mysql, you'd get some parallelization.

Diego Torres Over a year ago

thanks @Andrey and @Geoff, I thought I deleted the question since I came with a solution thanks to async.js (well, portion of it). I'm using an approach like Geoff (in fact now i have a function called getMembers) but i'll take a look to the other one since I'm not expecting a response to output, this can be perfectly async

Andrey Sidorov Over a year ago

if you are using one connection the queries are serialized even if you expect no output. The only way to execute queries in parallel is to execute them in separate connections (probably using connections pool)

Andrey Sidorov · Accepted Answer · 2011-07-10 06:18:03Z

function do_queries( resultCallback )
{
    var querystr = "SELECT * FROM groups";
    var result = "";
    var num_queries_left = 0;
    dbClient.query(querystr, function (err, res, fields) {
        if (err) {
            console.log(err);
            return;
        }

        // calculate number of invocations of second sub-query
        for(var i in res)
            num_queries_left += res[i].members.split(",").length;

        for(var i in res) {
            result = result + "@" +res[i].name + " =";                 
            for (var j in res[i].members.split(",")) {
                var memberquery;
                if (j.substr(0,1) == "@") {
                    memberquery = "SELECT name FROM groups WHERE id = "+j.substr(1, j.length-1);
                } else {
                    memberquery = "SELECT username FROM users WHERE id= "+j;
                }
                dbClient.query(memberquery, function(err, memres, fields) {
                    var membername = "";
                    if (typeof memres[0].username == "undefined") {
                        membername = "@"+memres[0].name;
                    } else {
                        membername = memres[0].username;
                    }
                    result = result + " " + membername;
                    num_queries_left--;
                    if (num_queries_left == 0)
                    {
                        resultCallback(result);
                        return;
                    } 
                });

            }
            result = result + "\n"; 
        }
    });
}


do_queries( function(result) {
    console.log(result);
});

Collectives™ on Stack Overflow

Synchronous mysql in Node.js

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related