2

I need to delete multiple rows from a table based on 3 fields. These fields are not the key columns. POST request sends all the rows. Sequelize does not support bulk delete based on columns. So I am running a forEach loop to delete and then want to insert using bulkCreate.

But ".then()" does not exist for the custom function I created. Can anybody suggest a better way of doing this. Thank you!

create: (req, res) => {
let deleteRows = (rows) => {
  rows.forEach((row) => {
    console.log("=============");
    console.log(row);
    console.log("=============");
    models.TABLE.destroy({ where: {CNTRY_CD: row['CNTRY_CD'], AREA_ID: row['AREA_ID'], BUS_DT: row['BUS_DT']}})
    .then((region, err) => {
      if(err) {
        console.log("I am here");
        return res.status(500).json({ error: 'Internal Server Error' });
      }
      //res.status(200).json(region);
    });
      });
};

deleteRows(req.body.rows)
  .then((err) => {
    if(err) {
      console.log("Instead I am here");
      return res.status(500).json({ err: 'Internal Server Error' });
    } else {
      models.TABLE.bulkCreate(req.body.rows)
        .then((rows, err) => {
          if(err) {
            return res.status(500).json({ err: 'Internal Server Error' });
          } else {
            return res.status(201).json(`${req.body.rows.length} rows successfully inserted!`);
          }
        })
        .catch((err) => {
          res.status(500).json(err);
        });
    }
  });
}

I want to update the record if it exists and insert if it does not exist. Below is the code I have: 

create: (req, res) => {
  req.body.rows.forEach((row) => {
    models.AFT_DC_AREA_DAY_AOP.findOne({
        where: { CNTRY_CD: row['CNTRY_CD'], AREA_ORG_LVL_ID: row['AREA_ORG_LVL_ID'], BUS_DT: row['BUS_DT'] },
        individualHooks: true,
        defaults: {
          CNTRY_CD: row['CNTRY_CD'],
          FSCL_YR_NUM: row['FSCL_YR_NUM'],
          FSCL_PER_SID: row['FSCL_PER_SID'],
          FSCL_WK_BEG_DT: row['FSCL_WK_BEG_DT'],
          BUS_DT: row['BUS_DT'],
          AREA_ORG_LVL_ID: row['AREA_ORG_LVL_ID'],
          AREA_AOP_SALES: row['AREA_AOP_SALES']
        }}).then((data, err) => {
            if(err) {
                return res.status(500).json({ error: 'Internal Server Error' });
            }
            if(data) {
                models.AFT_DC_AREA_DAY_AOP.update(
                {
                  CNTRY_CD: row['CNTRY_CD'],
                  FSCL_YR_NUM: row['FSCL_YR_NUM'],
                  FSCL_PER_SID: row['FSCL_PER_SID'],
                  FSCL_WK_BEG_DT: row['FSCL_WK_BEG_DT'],
                  BUS_DT: row['BUS_DT'],
                  AREA_ORG_LVL_ID: row['AREA_ORG_LVL_ID'],
                  AREA_AOP_SALES: row['AREA_AOP_SALES']
                }, {
                    where: {
                        id: data.id
                    }
                }).then((updateData, err) => {
                    if(err) {
                        return res.status(500).json({ error: 'Internal Server Error' });
                    }
                    res.status(200).json(updateData);
                });
            } else {
                models.AFT_DC_AREA_DAY_AOP.create(
                {
                  CNTRY_CD: row['CNTRY_CD'],
                  FSCL_YR_NUM: row['FSCL_YR_NUM'],
                  FSCL_PER_SID: row['FSCL_PER_SID'],
                  FSCL_WK_BEG_DT: row['FSCL_WK_BEG_DT'],
                  BUS_DT: row['BUS_DT'],
                  AREA_ORG_LVL_ID: row['AREA_ORG_LVL_ID'],
                  AREA_AOP_SALES: row['AREA_AOP_SALES']
                }).then((createData, err) => {
                    if(err) {
                        return res.status(500).json({ error: 'Internal Server Error' });
                    }
                    res.status(200).json(createData);
                });
            }

        });
  });

}

I get following error "Unhandled rejection Error: Can't set headers after they are sent." which makes sense, because the response has been sent. Not sure how to perform the actions for all records and then send the response.

I updated the code to return a promise for each row, collecting the promise and returning them. This code works fine.

 create: (req, res) => {
let UpdateOrCreate= function(rows){
    var promises = [];
    rows.forEach((row) => {
       promises.push(insert(row));
    });
    return Promise.all(promises);
}
let insert = function(row){
  return new Promise((resolve,reject)=>{
    var newPromise = models.TABLE.findOne({
        where: { CNTRY_CD: row['CNTRY_CD'], AREA_ORG_LVL_ID: row['AREA_ORG_LVL_ID'], BUS_DT: row['BUS_DT'] },
        individualHooks: true,
        defaults: {
          CNTRY_CD: row['CNTRY_CD'],
          FSCL_YR_NUM: row['FSCL_YR_NUM'],
          FSCL_PER_SID: row['FSCL_PER_SID'],
          FSCL_WK_BEG_DT: row['FSCL_WK_BEG_DT'],
          BUS_DT: row['BUS_DT'],
          AREA_ORG_LVL_ID: row['AREA_ORG_LVL_ID'],
          AREA_AOP_SALES: row['AREA_AOP_SALES']
        }
      }).then((data, err) => {
            if(err) {
              reject('Internal Server Error');
            }
            if(data) {
              models.TABLE.update(
                {
                  CNTRY_CD: row['CNTRY_CD'],
                  FSCL_YR_NUM: row['FSCL_YR_NUM'],
                  FSCL_PER_SID: row['FSCL_PER_SID'],
                  FSCL_WK_BEG_DT: row['FSCL_WK_BEG_DT'],
                  BUS_DT: row['BUS_DT'],
                  AREA_ORG_LVL_ID: row['AREA_ORG_LVL_ID'],
                  AREA_AOP_SALES: row['AREA_AOP_SALES']
                }, {
                    where: {
                      id:data['dataValues']['id']
                    }
                }).then((updateData, err) => {
                    if(err) {
                      reject('Internal Server Error');
                    }
                    resolve(updateData);
                });
            } else {
                models.TABLE.create(
                {
                  CNTRY_CD: row['CNTRY_CD'],
                  FSCL_YR_NUM: row['FSCL_YR_NUM'],
                  FSCL_PER_SID: row['FSCL_PER_SID'],
                  FSCL_WK_BEG_DT: row['FSCL_WK_BEG_DT'],
                  BUS_DT: row['BUS_DT'],
                  AREA_ORG_LVL_ID: row['AREA_ORG_LVL_ID'],
                  AREA_AOP_SALES: row['AREA_AOP_SALES']
                }).then((createData, err) => {
                    if(err) {
                      reject('Internal Server Error');
                    }
                    resolve(createData);
                });
            }

        });
  })
}
UpdateOrCreate(req.body.rows).then(function (result) {
  console.log("%%%%%%%%%%%%%%%%%%%%%%%%");
  console.log(result);
  console.log("%%%%%%%%%%%%%%%%%%%%%%%%");

    res.status(200).json(`${req.body.rows.length} rows successfully inserted!`);
}).catch(function (err) {
    return res.status(500).json({ error: 'Internal Server Error' });
});

}

But, I want to ensure errors were handled properly. I disconnected the DB while the inserting was in progress. It throws following errors for each failed record in the loop:

Unhandled rejection TimeoutError: ResourceRequest timed out Unhandled rejection SequelizeHostNotFoundError: Failed to connect to host

Isn't the resolve, reject supposed to handle this error. Am I missing something here?

pooja kosala
  • 29
  • 3
  • 8

2 Answers2

2

For deleting the record using Sequelize, what you have used is right. It deletes the record based on the condition that you were providing like below. Sequelize documentation

Model.destroy({
    where: {
        // criteria
    }
})

So you are using for loop to iterate over the list and delete it. But your custom function (deleteRows) is not supporting .then, so you are running in to problem of deleting the record.

I suggest you to make your function like below, using promise. it will return either resolve or reject. Promise Documentation

deleteRows: function(rows){
  return new Promise(function(resolve, reject){
    rows.forEach((row) => {
      models.TABLE.destroy({ where: {CNTRY_CD: row['CNTRY_CD'], AREA_ID: row['AREA_ID'], BUS_DT: row['BUS_DT']}})
      .then((region, err) => {
        if(err) {
          console.log("I am here");
          reject('Internal Server Error');
        }

        resolve(region);
      });
    });
  });
}

By using the promise you can able to use .then for you function. Which is as follows

deleteRows(req.body.rows).then(function(result){
  // your logic
})

Similar you can use promise for adding the records too.

Second approach: You can use async await also for performing the same operation async await documentation

deleteRows: async function(rows) {

    var result = await rows.forEach((row) => {
        models.TABLE.destroy({
                where: {
                    CNTRY_CD: row['CNTRY_CD'],
                    AREA_ID: row['AREA_ID'],
                    BUS_DT: row['BUS_DT']
                }
            })
            .then((region, err) => {
                return region
            });
    });

}
Jeeva
  • 1,550
  • 12
  • 15
  • Jeeva, your suggestion worked. But I updated the problem statement. I want to search each record, update if it exists or insert. The second snippet has the code and the issue I am facing. Could you suggest an approach? Thanks – pooja kosala Jun 08 '18 at 17:43
  • Sure, you can try by adding the counter.. initially get the total count of the rows as total count. ie. var total count = req.body.rows ,then var processed count = 0. Inside foreach loop you are trying to find existence of the record and inserting it, if it wasn't there. Otherwise you were updating it right. So increase the processedCount by 1 on each record, whether it may be an insert or update block. Then release the response one time when the totalCount and processedCount matches. By this way you can send the response only once. Try and let me know if any, hope it will work – Jeeva Jun 09 '18 at 10:05
  • Jeeva, I added the code i wrote for performing multiple inserts/updates. It works well. Error handling is not working. I updated the post. Could you please suggest an approach? Thanks – pooja kosala Jun 12 '18 at 00:18
  • Hi, The Promise.all will resolve all the promises inside it, have resolved, but it rejects with the reason of first promise that rejects. So you may use the custom promise all function. which will be easier and also see the workaround for promise.all in the following link. I think this might help you fix your issue. https://stackoverflow.com/questions/30362733/handling-errors-in-promise-all – Jeeva Jun 12 '18 at 07:27
0

For the error:

Unhandled rejection TimeoutError: ResourceRequest timed out Unhandled rejection SequelizeHostNotFoundError: Failed to connect to host

This might help you to catch the errors in Promise.all function. You can use like below.

const a = Promise.resolve(1);
const b = Promise.reject(new Error(2));
const c = Promise.resolve(3);

Promise.all([a, b, c].map(p => p.catch(e => e)))
  .then(results => console.log(results)) // 1,Error: 2,3
  .catch(e => console.log(e));
Jeeva
  • 1,550
  • 12
  • 15