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
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 costumer = 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.