NodeJs function inside forEach loop need to complete before go to next item

Question

I have a Nodejs script, where it's detail look like below :

1) it requests to API to get a list of city, it will get JSON array. using this array, I do looping using forEach.

2) at each iteration (2nd loop), I do request again to API to get details (about 100 rows) and insert it into mysql database.

my question, how to make the function inside the first loop (where to get the list of city) wait to complete before going to next item (city). I want to make a loop sequential with delay.

my source code :

const request = require('request');
var moment = require('moment');
var mysql = require('mysql');

var a = moment('2019-04-01');
var b = moment('2019-04-06');
const sleep = (waitTimeInMs) => new Promise(resolve => setTimeout(resolve, waitTimeInMs));

function timer(ms) {
 return new Promise(res => setTimeout(res, ms));
}

var connection = mysql.createConnection({
    host : 'localhost',
    user : 'user1',
    password : 'password',
    database : 'local'
});

async function getURL(id_city,dates) {

    var url = 'https://localhost/api/format/json/schedule/city/'+id_city+'/date/'+dates;    
    request(url, { json: true }, (err, res, body) => {
      if (err) { return console.log(err); }
      // console.log(body.status);
      var item1 = body.schedule.data.item1;
      var item2 = body.schedule.data.item2;

      connection.connect();
      connection.query('INSERT INTO schedule (city,item1,item2) values ("'+id_city+'","'+task1+'", "'+task2+'")', function (error, results, fields) {
         if (error) throw error;
      });
      // connection.end();    
    });
}


async function getDate(id_city)
{
    var end;
    for (var m = moment(a); m.isBefore(b); m.add(1, 'days')) {
        getURL(id_city,m.format('YYYY-MM-DD'));
        await timer(1000); //making delay           
    }
}

async function main () {    
    var url = 'https://localhost/api/format/json/list_city';
    connection.connect();
    request(url, { json: true }, (err, res, body) => {
      if (err) { return console.log(err); }

          var list_city = body.city; //this is an array
          var counter = 0;
          list_city.forEach(function(city){
              getDate(city.id, function(){

              });//i need this to complete before go to next city

          });
    });//end request url
}

main();

my expectation (sequential) :

city1
insert item a done...
insert item b done...
city2
insert item a done...
insert item b done...
insert item c done...
city3
...

Answer 1

For both request and mysql you can use the Promise supported package namely: request-promise and mysql2. To guarantee sequential execution, you can then do:

const rp = require('request-promise');
const mysql = require('mysql2/promise');

// then in your getURL function
async function getURL(id_city,dates) {

  var url = 'https://localhost/api/format/json/schedule/city/'+id_city+'/date/'+dates;
  const body = await rp(url, { json: true })
  const item1 = body.schedule.data.item1;
  const item2 = body.schedule.data.item2;

  const connection = await mysql.createConnection({host:'localhost', user: 'root', database: 'test'});
  const [rows, fields] = await connection.execute('INSERT INTO schedule (city,item1,item2) values ("'+id_city+'","'+task1+'", "'+task2+'")');
}

// One await in getDate should do
async function getDate(id_city) {
  var end;
  for (var m = moment(a); m.isBefore(b); m.add(1, 'days')) {
    await getURL(id_city,m.format('YYYY-MM-DD'));
  }
}

For handling error with async/await:

try {
  const body = await rp(url, { json: true })
} catch (e) {
  // handle erorr
  console.error(e);
  // or rethrow error: throw e
}

For efficiency you could use mysql connection pool like:

// myPool.js
const mysql = require('mysql2');

// create pool
const pool = mysql.createPool({
  host:'localhost',
  user: 'root',
  database: 'test',
  connectionLimit: 10,
  queueLimit: 0
});
// now get a Promise wrapped instance of that pool
const promisePool = pool.promise();

module.exports = () => promisePool; 

// Then in your getURL
const getPool = require('./myPool');
async function getURL(id_city,dates) {
  ...

  const pool = await getPool();
  const [rows, fields] = await pool.execute('INSERT INTO schedule (city,item1,item2) values ("'+id_city+'","'+task1+'", "'+task2+'")');
  ...

Also consider using prepared statement.

connection.execute('SELECT * FROM `table` WHERE `name` = ? AND `age` > ?', ['Morty', 14]);

Answer 2

Use a for loop instead of forEach, and on each iteration, await the call of getDate, so that one getDate call always finishes before it gets called again:

for (let i = 0; i < list_city.length; i++) {
  await getDate(city[i]);
  await timer(100); // this will put a delay of at least 100ms between each call
}

Make sure to make the containing function async for this to work.

Note that since getDate returns a Promise, it probably shouldn't accept a callback - either chain awaits or thens on to the end instead.