node.js : For each over rows and update asynchronously?

Question

I need to query rows from a database, process some information per row, and then update each row with the result.

This is my example code where the intention is to loop over each row and update the label:

var mysql = require('mysql');

var db = mysql.createConnection(config.database);

db.connect(function() {
    db.query('SELECT id FROM testTable', function (err, rows) {
        if (err) {
            console.log(err);
        } else {
            if (rows.length) {
                for (var i = 0, len = rows.length; i < len; i++) {
                    var row = rows[i];
                    console.log(row);

                    var label = "Label_"+row.id;

                    db.query('UPDATE testTable SET label = ? WHERE id = ?', [label, row.id], function(err, result) {
                        if (err) {
                            console.log(err);
                        } else {
                            console.log("Set label on row %s", row.id);
                        }
                    })
                }
            }
        }
    })
});

The output of this is:

{ id: 1 }
{ id: 2 }
{ id: 3 }
{ id: 4 }
Set label on row 4
Set label on row 4
Set label on row 4
Set label on row 4

So, as you can see, I've updated row 4 four times instead of four rows once each. Whilst I new the queries would be non-blocking, I thought the values would change for each one.

I know I can change my code to use rows.forEach(function(){...}) and that then executes each UPDATE one after the other and that would be ok. But to help my understanding I would like to know how I can correctly execute the updates asynchronously.

Answer 1

Your row variable is a closure in the callback function. The callback function doesn't get called until you've looped through all your results list. The sql queries are correct, but printing out the value of row.id in each callback just gives you the last iteration of the for loop each time because that is the state of the closure for every callback.

You can avoid this by using the underscore module. It can also help in making you logic simpler.

npm install underscore

Then your code would look like this:

var mysql = require('mysql');
var _ = require('underscore');

var db = mysql.createConnection(config.database);

db.connect(function() {
  db.query('SELECT id FROM testTable', function (err, rows) {
    if (err) { console.log(err); return; }
    _.each(rows, function(one) {
      console.log(one);
      var label = "Label_"+one.id;
      var sql = 'UPDATE testTable SET label = ? WHERE id = ?';
      db.query(sql, [label, one.id], function(err, result) {
        if(err) { console.log(err); return; }
        console.log("Set label on row %s", one.id);
      });
    });
  });
});