I am trying to create a sequence of 'INSERT INTO' queries, based on the response from an API call to a 3rd aprty.

the API response is broken up into different pages and I am cycling through the data, then cycling through the pages trying to update the string for each piece of data.

All pages of the response are being reached and the data is being added to the total string but what is happening is that the return is happening too early - that is, all the data is being returned EXCEPT for the last page. I can't figure out why the return object bulkInsertString is being returned one page too early. The function of concern is the getBulkInsert function... I think it has something to do with the conditions on the while loop to stop running once it reaches the last page. The data on the last page page is definitely being reached as it is being logged in the console.log('string added',forename,surname)... but this is being done too late. I am wondering if I am missing an 'await' somewhere. Can anyone see how I would have to adapt my code to make sure I return all the data including the last page? I thought that since the return statement was after the while loop, it would have returned once the while loop had completed?

When the server begins running, 'syncStaffRoll' function is called:

const axios = require("axios");
const generateString = require("./queryBuilders/CreateInsertInto");
const sql = require("mssql");

const getBulkInsert = async (schoolId, myDate) => {
  var queryStart = "";
  var bulkInsertString = "";
  var lastPage = false;

  var endPoint = `***`;
  var i = 1;
  while (!lastPage) {
    console.log(`currently on element ${i} of response`);

const config = {
  method: "get",
  url: endPoint,
  headers: {
    Authorization: "Bearer "   process.env.TOKEN,
  },
};

await axios(config).then((response) => {
  const staffArray = response.data.data;
  console.log(
    `${staffArray.length} staff objects received in this API call`
  );
  lastPage = !response.data.meta.pagination.more;
  endPoint = response.data.meta.pagination.next;

  if (lastPage) {
    console.log("this is the final page");
  }

  staffArray.forEach(async (staffMember) => {
    try {
      const { id, surname, forename, contact_details } = staffMember;

      const { data } = contact_details;
      const { emails } = data;
      const { primary, work } = emails;
      const getEmail = () => {
        if (work) {
          return work;
        } else {
          return primary;
        }
      };

      var nextString = await generateString("SyncStaff", "UpdatedAt", {
        StaffId: id,
        Surname: surname.replace(/'/g, '"'),
        FirstName: forename.replace(/'/g, '"'),
        StaffEmail: getEmail(),
        StaffRole: "Staff",
        UpdatedAt: myDate,
      });
      bulkInsertString = bulkInsertString   nextString;
      console.log("string added", forename, surname);
      // console.log(bulkInsertString);
    } catch (err) {
      console.log(`error in staffMember ${staffMember.id}`, err);
      // console.log(err);
    }
  });
});
i  = 1;


}
  return bulkInsertString;
};

const getQuery = async (schoolId, myDate) => {
  const queryStart = `
  BEGIN TRANSACTION [Tran1]\n
    BEGIN TRY\n
  
  `;
  const queryEnd = `
      COMMIT TRANSACTION [Tran1]\n
    END TRY\n
    BEGIN CATCH \n
      ROLLBACK TRANSACTION [Tran1]\n
    END CATCH
  `;
  const bulkInsertString = await getBulkInsert(schoolId, myDate);

  const removalString = `DELETE FROM SyncStaff where UpdatedAt != '${myDate}'`;

  const queryString = queryStart   bulkInsertString   removalString   queryEnd;

  return queryString;
};

const performQuery = async (query) => {
  console.log("trying to perform query", query);
  const sqlConfig = {
    user: process.env.DB_USER,
    password: process.env.DB_PWD,
    database: process.env.DB_NAME,
    server: process.env.DB_SERVER,
    pool: {
      max: 10,
      min: 0,
      idleTimeoutMillis: 30000,
    },
    options: {
      encrypt: true, // for azure
      trustServerCertificate: false, // change to true for local dev / self-signed certs
    },
  };

  const runQuery = async () => {
    try {
      // make sure that any items are correctly URL encoded in the connection string
      await sql.connect(sqlConfig);
      const result = await sql.query(query);
      return "Query Completed";
    } catch (err) {
      console.log(err);
      return err;
    }
  };

  const result = await runQuery();
  return result;
};

const syncStaffRoll = async (schoolId) => {
  const d = new Date();
  const myDate = d.toString("en-AU");

  //generates the query to be used
  const query = await getQuery(schoolId, myDate);

  console.log(query);

  // const queryResult = await performQuery(query);

  // console.log(queryResult);
};

module.exports = syncStaffRoll;

CodePudding user response：

forEach is a little bit buggy when it comes to async...await. I've encountered this issue a few times as well. You should create that function given to forEach into a separate named async function and use it inside a for...of loop instead.

CodePudding user response：

Your forEach is synchronous, so it is not going to wait for your promises to resolve.

This line:

staffArray.forEach(async (staffMember) => {

Should be using Promise.all, and map instead of forEach so that you have an array of promises to work with.

await Promise.all(staffArray.map(async (staffMember) => { 
  // your async code
})