Node.js: for each line by line and update asynchronously?

Question

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

This is my sample code where the intention is to iterate over each line 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 result of this:

 { 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 updated line 4 four times instead of four lines once. While I'm new, the requests will be non-blocking, I thought that the values ​​will change for everyone.

I know that I can change my code to use rows.forEach(function(){...}) , and then perform each UPDATE one by one, and that will be fine. But to help my understanding, I would like to know how I can correctly perform updates asynchronously.

Answer 1

Your row variable is a closure in the callback function. The callback function is not called until you loop through the entire list of results. The sql queries are correct, but listing the row.id value in each callback simply gives you the last iteration of the for loop each time, because this is the closing state for each callback.

You can avoid this by using the underscore module. It can also help simplify your logic.

 npm install underscore 

Then your code will 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); }); }); }); });