How to check update and insert while using bulkCreate in node.js

Question

I am using node.js to upload an excel file into the database, in my service i am using bulkCreate to upload the data into the mysql db.Let me post the table structure

  table name : customer_details
  columns:
  customer_org_id INT,
  customer_name VARCHAR,
  customer_type char,
  active boolean,
  customer_slot VARCHAR,
  service_start_time DATE,
  service_end_time DATE

I have one additional requirement that is, while i will upload the excel and try to push into the db then it must check in the database that customer_org_id &customer_name exists in the database or not.If the combination exists then the existing record will be updated and active column will be false.And a new row will be inserted with customer_org_id & customer_name and the active will be set to true.I am able to do the individual operations like create , update ,delete etc but i don't understand where to put these operations together while doing a bulkCreate. I am posting my code

const upload = async(req,res) => {
 try{
      if(req.file == undefined){
        return res.status(400).send("Please upload an excel file");
     }
  let path=
    __basedir + "/resources/static/assets/uploads/" + req.file.filename;

  readXlsxFile(path).then((rows)=>{
      rows.shift();
      let custdetail = [];
      
      row.forEach((row)=>{
        let custdetails ={
          customer_org_id: row[0],
          customer_name :row[1],
          customer_type :row[2],
          active :row[3],
          customer_slot: row[4],                           
        }; 
       custdetails.push(custdetail);
       });

     CustomerDetails.bulkCreate(custdetails)
       .then(()=>{
       res.status(200).send({
       message: "Uploaded the file successfully :" + req.file.originalname,
       });
       })
       .catch((error) =>{
        res.status(500).send({
        message : "Fail to import data into DB",
        error : error.message,
        });
      });
     });
    }catch(error){
    console.log(error);
    res.status(500).send({
    message : "Could not upload the file :" +req.file.originalname,
   });
   }
  }

Can anyone let me know how i can do the operations before adding data to the Db ? I am new to node js

Answer 1

If I understood it correctly, it seems that bulkCreate is not best solution for your problem, because, you will need to do a validation and create/update for each line of your array.

I didn't understand all your requirements, but the code would be something close to this:

const upload = async (rows) => {
  const promises = rows.map(async (sindleRow) => {
    const customer = await CustomerDetails.find({ where: { customer_org_id: row[0], customer_name: row[1] }})

    if (customer !== undefined) { // CUSTOMER FOUND
      await CustomerDetails.update({ where: { id: customer.id }, data: { active: false }})
      await CustomerDetails.create({ data: { customer_org_id: row[0], customer_name: row[1], active: false }})
    }
    
    return;
  });

  return await Promise.all(promises);
}

Important: This code is only an example.

Your scenario does not seem to benefit from the use of a bulkCreate, because the data needs to be individually verified.