NodeJS Postgresql Bulk upsert.

excelFileRead.js

// import XlsxStreamReader - it is an npm package ==== https://www.npmjs.com/package/xlsx-stream-reader

function readXlsx(file, headers) {
  return new Promise((resolve, reject) => {
    let resp_row = [];
    var workBookReader = new XlsxStreamReader();
    workBookReader.on('error', function (error) {
      throw error;
    });
    workBookReader.on('sharedStrings', function () {
      // console.log(workBookReader.workBookSharedStrings);
    });
    workBookReader.on('styles', function () {
      // console.log(workBookReader.workBookStyles);
    });
    workBookReader.on('worksheet', function (workSheetReader) {
      if (workSheetReader.id > 1) {
        workSheetReader.skip();
        return;
      }
      let headerKeys = [];
      workSheetReader.on('row', row => {
        // console.log(headers);
        if (row.attributes.r == 1) {
          row.values.shift();
          headerKeys = row.values;
        } else {
          let item = {};
          // File parse operations here - works on each row basis
          item['_id'] = `'${uuid()}'`;
          for (let i = 0; i < headers.length; i++) {
            if (typeof (row.values[i]) === 'string') {
              item[headers[i][headerKeys[i]]] = `'${row.values[i]}'`;
            } else {
              item[headers[i][headerKeys[i]]] = row.values[i] ? row.values[i] : 'NULL';
            }
          }
          item['createdAt'] = `'${new Date().toUTCString()}'`;
          item['updatedAt'] = `'${new Date().toUTCString()}'`;
          resp_row.push(`(${Object.values(item).join(', ')})`);
        }
      });
      workSheetReader.on('end', function () {
        // console.log(workSheetReader.rowCount);
      });
      workSheetReader.process();
    });
    workBookReader.on('end', function () {
      resolve(resp_row);
    });

    fs.createReadStream(file).pipe(workBookReader);
  });

upsertData.js

exports.uploadPpeData = async (req, res, next) => {
  let fileURL1 = process.cwd() + '/fileName.xlsx';
  let keys1 = '("_id", "key_1", "key_2", "key_3", "createdAt", "updatedAt")';
  let ppeArr = await excelFileRead(fileURL1, headerKeysPPE);
  let queryStr = `CREATE OR REPLACE FUNCTION public.ppeUp(OUT response public."CommonAttributes", OUT sequelize_caught_exception text) RETURNS RECORD AS $func_83947583745$ 
BEGIN INSERT INTO public."CommonAttributes" as CommonAttribute ("_id", "key_1", "key_2", "key_3", "createdAt", "updatedAt") VALUES ${ppeArr.join(', ')} 
ON CONFLICT ("key_1") DO UPDATE SET 
"key_2"=excluded.key_2, 
"key_3"=excluded.key_3,
"key_4"=excluded.key_4,
"key_5"=excluded.key_5,
"key_6"=excluded.key_6;
END $func_83947583745$ LANGUAGE plpgsql;
select public.ppeUp();`
  db.sequelize.query(queryStr).then(resp => {
    debugger
    res.status(200).json({ msg: 'Successfully added', data: resp });
  }).catch(err => res.status(400).json({ msg: 'failed at ppe', err }));
}