Problem when executing a query in a repeat loop on the Mssql Node

Question

So,

I am using Node and mssql package (sql-server) to perform queries to my database.

however I am facing a difficulty to return queries that need to be executed within a repeat loop.

Maybe it’s something to do with the way I’m performing the callback. In other queries where I don’t need to perform a repeat loop, it usually works.

My controller:

api.contabilizacaoItems =  function (req, res, next) {
    itensParaEnvio = [];
    var dados = req.body;
    cont = 1
    console.log(dados.items.length);
    dados.items.forEach(function (item, key){ 
        item.aprovacao = dados.aprovacao
        solicitacaoSqlDAO.contabilizacaoItem(item, function (erro, recordset) {
           item.contItem= recordset.recordset;
           solicitacaoSqlDAO.aenItem(item, function (erro, recordset) {
               item.aenItens= recordset.recordset;
               itensParaEnvio[key]= item;
               if(cont == dados.items.length) res.status(200).json(itensParaEnvio);
               else res.status(404).json("erro");
           })
       })
    });  
};

return api;

My DAO

solicitacaoDAO.prototype.contabilizacaoItem = function (item, callback) {
  console.log("# PARA CONSULTA contabilizacaoItem# ");
  mssql.close();
  mssql.connect(this._connection, function (err) {
    if (err) {
      console.log("# ERRO AO REALIZAR CONEXAO PARA CONSULTA contabilizacaoItem# "+ err);
    }
    var request = new mssql.Request();
    query = "select * from alguma coisa ";
    // console.log(query);
    request.query(query, function (err, recordset) {
      if (err) {
        console.log(query);
        callback(err,recordset);
      } else {
        callback(err,recordset);
      }   
    });
  });
}

solicitacaoDAO.prototype.aenItem = function (item, callback) {
  console.log("# PARA CONSULTA aenItem# ");

  mssql.close();
  mssql.connect(this._connection, function (err) {
    if (err) {
      console.log("# ERRO AO REALIZAR CONEXAO PARA CONSULTA aenItem# "+ err);
    }

    var request = new mssql.Request();
    query = "select * from alguma coisa"
    // console.log(query);
    request.query(query, function (err, recordset) {
      if (err) {
        console.log(query);
        callback(err,recordset);
      } else {
        callback(err,recordset);
      }   
    });
  });
}

Answer 1

Use promises to resolve only when all callbacks are finalized:

api.contabilizacaoItems = function(req, res, next) {
  var itensParaEnvio = [];
  var dados          = req.body;
  var promessas      = [];

  console.log(dados.items.length);

  dados.items.forEach(function(item, key) {
    promessas.push(new Promise(function(resolver, rejeitar) {
      item.aprovacao = dados.aprovacao;

      solicitacaoSqlDAO.contabilizacaoItem(item, function(erro, recordset) {
        item.contItem = recordset.recordset;

        solicitacaoSqlDAO.aenItem(item, function(erro, recordset) {
          item.aenItens       = recordset.recordset;
          itensParaEnvio[key] = item;

          resolver();
        })
      });
    }));
  });

  Promise.all([true, promessas]).then(function(values) {
    res.status(200).json(itensParaEnvio);
  }).catch(function() {
    res.status(404).json("erro");
  });
};