How to finish the loop to insert data into an array before populating the JSON in nodeJs

Question

I like to fill all the data from my loop into my array but the problem is the loop is executed lastly. I try to print the data but the array is always empty.

var data = new Array();
        for (let station of table) {
            request.query('SELECT TOP (1) CONVERT(varchar, Date_Time, 120) Date_Time FROM '+station+' ORDER BY Date_Time DESC', function (err, req) {
                if (err) throw err;
                date_time = req.recordset.map(el=>el.Date_Time)[0];
                var i = data.push({"date_time":date_time,"station_name":name[table.indexOf(station)]});
                console.log(i);
            });
        }
        let json_data = JSON.stringify({"data":data});
        res.writeHead(200, {'Content-Type': 'text/html'});
        res.write(json_data);
        res.end();

Alex · Accepted Answer · 2020-11-04T07:39:08.617

request.query('your query....', callback(err, res))

Your SQL queries are running asynchronously. Because SQL queries are async, they return immediately before finishing, causing your loop to finish almost instantly without waiting for any queries to finish. Then, you're sending out the data before it's been changed because none of the completion callbacks have executed yet.

You'll need a way to force your code to wait until all callbacks have completed before you send off the results.

I found a similar question with some really good solutions, particularly the ES6 Standard Promises section in the first answer here: How can I wait for set of asynchronous callback functions?

I've used that page as a basis to rewrite your code as an example, because it can get pretty confusing:

var data = new Array();
var promises = []; // this will be filled up with Promises, allowing us to wait for them all to complete

// define a function to turn your callback style async requests into awaitable Promises
function doQuery(yourQuery, station) {
    return new Promise(function(resolve, reject) {
        request.query(yourQuery, function (err, res) {
            if (err) reject(err);
            date_time = res.recordset.map(el=>el.Date_Time)[0];
            var i = data.push({"date_time":date_time,"station_name":name[table.indexOf(station)]});
            console.log(i);
            resolve(res);
        });
    });
}

// run all of your queries
for (let station of table) {
// this will run a query and add it to the Promise array so we can keep track of it and wait for it
    promises.push(doQuery('SELECT TOP (1) CONVERT(varchar, Date_Time, 120) Date_Time FROM '+station+' ORDER BY Date_Time DESC', station));
}

// Promise.all will pause execution until every Promise in our array is done, then run a callback with the results of everything
Promise.all(promises).then(function() {
        // complete
        json_data = JSON.stringify({"data":data});
        res.writeHead(200, {'Content-Type': 'text/html'});
        res.write(json_data);
        res.end();
    }, function(err) {
        // error occurred...
        console.log(err);
    }
);

score 0 · Answer 2 · edited Nov 04 '20 at 07:22

Because of asynchronous nature of SQL queries, you have to collect all the query promises first and pass into Promise.all().

The Promise.all() method takes an iterable of promises as an input, and returns a single Promise that resolves to an array of the results of the input promises.

I'm pasting the sample version of your code below using async/await.

  const data = new Array();
  const queriesArray = []
  for (let station of table) {
    const result = await request.query('SELECT TOP (1) CONVERT(varchar, Date_Time, 120) Date_Time FROM ' + station + ' ORDER BY Date_Time DESC')
    queriesArray.push(result)
  }
  Promise.all(queriesArray).then((details) => {
    details.map(detail => {
      date_time = detail.recordset.map(el => el.Date_Time)[0];
      var i = data.push({ "date_time": date_time, "station_name": name[table.indexOf(station)] });
      console.log(i);
    })
    let json_data = JSON.stringify({ "data": data });
    res.writeHead(200, { 'Content-Type': 'text/html' });
    res.write(json_data);
    res.end();
  })

How to finish the loop to insert data into an array before populating the JSON in nodeJs

2 Answers2