Nodejs + mssql how to do a search sequence

Question

I’m starting with nodejs and I came across a problem that I’ve been a few days resisting not posting here, but it seems I don’t have enough knowledge and I can’t find material when searching for mssql and Nodejs, so I came to ask for help here.

I need to do a sequence of queries in the database, so I created the following code:

router.post('/importbom', (req, res) => {

    const { listaBom } = req.body;
    let filter = "";

    listaBom.forEach((item, i) => {

        sql.close();
        if (item) {
            const { mpn, descricao } = item;

            filter = "CompName LIKE '0%' " + `AND CompID = '${mpn}' `;
            if (descricao !== null) {
                filter += "OR CompName LIKE '0%' ";
                let arrayWord = descricao.split(" ");
                arrayWord.forEach((word, i) => {
                    if (!i) filter += `AND Remark LIKE '%${word.replace('%', '[%]')}%' `;
                    if (i) filter += `AND Remark LIKE '% ${word.replace('%', '[%]')}%' `;
                });
            }

            sql.connect(vmanagerConfig).then(pool => {
                return pool.request()
                    .query(`SELECT 
                    LTRIM(RTRIM(CompName)) AS ipnProd, 
                    LTRIM(RTRIM(Remark)) AS descricao, 
                    LTRIM(RTRIM(CompID)) AS CompID,
                    LTRIM(RTRIM(McID)) AS ID,
                    LTRIM(RTRIM(Amount)) AS amt,
                    ipnCad = 0,
                    mnfpartno as mpn
                    FROM ComponentTrace
                    WHERE ${filter} and McID <> 9999`).then(result => {
                        console.log(result.recordset);

                    });
            }).catch(err => {
                sql.close();
                console.log("--> Erro na linha ..., index.js ", err);
            });

            sql.on('error', err => {
                sql.close();
                console.log("--> Erro na linha ..., index.js: ", err);
            });

        }


    });


    res.send([]); //por hora não estou usando

})

it receives a list and each row of that list should be a query, only it passes n times by for and returns me only one result.

Answer 1

The .then in the sql.connect and pool.request().query indicate that your returns are promises. So your code runs asynchronously. To run them in an organized way you can use the async/await as follows:

const mountFilter = ({ mpn, descricao }) => {
  let filter = `CompName LIKE '0%' AND CompID = '${mpn}' `;

  if (descricao !== null) {
    filter += "OR CompName LIKE '0%' ";
    let arrayWord = descricao.split(" ");
    arrayWord.forEach((word, i) => {
      if (!i) filter += `AND Remark LIKE '%${word.replace('%', '[%]')}%' `;
      if (i) filter += `AND Remark LIKE '% ${word.replace('%', '[%]')}%' `;
    });
  }
  
  return filter;
};

const post = async (req, res) => {
  const { listBom } = req.body;
  const results = [];

  for (let i = 0, tamanho = listaBom.length; i < tamanho; i++) {
    const pool = await (new sql.ConnectionPool(/** configurações da conexão **/)).connect();
    const item = listaBom[id];

    if (item) {
      const filter = mountFilter(item);
      const { recordset: result } = await pool
        .request()
        .query(`SELECT LTRIM(RTRIM(CompName)) AS ipnProd, 
                       LTRIM(RTRIM(Remark)) AS descricao, 
                       LTRIM(RTRIM(CompID)) AS CompID,
                       LTRIM(RTRIM(McID)) AS ID,
                       LTRIM(RTRIM(Amount)) AS amt,
                       ipnCad = 0,
                       mnfpartno as mpn
                  FROM ComponentTrace
                 WHERE ${filter} and McID <> 9999`);

      results.push(result);
    }
  };
  
  res.send(results);
};

router.post('/importbom', post);

Observing: I wouldn’t use the query the way you used it. It may be possible to improve the filter scheme.

---

Asynchronous functions

The statement async Function defines an asynchronous function, which returns an object Asyncfunction.

You can also define asynchronous functions using a async expression Function.

When an asynchronous function is called, it returns a Promise. When the asynchronous function returns a value, a Promise will be solved with the value returned. When the asynchronous function throws an exception or some value, the Promise will be rejected with the value launched.

An asynchronous function may contain an expression await, which pauses the execution of the asynchronous function and waits for the resolution of the Promise passed, and then resumes the execution of the asynchronous function and returns the solved value.