Sequelize CreateOrUpdate Function

Question

I am using node version v10.15.3 and "sequelize": "^4.22.8". When using bulkCreate I am getting double values in my db:

My model looks like the following:

module.exports = (sequelize, DataTypes) => {
  const Company = sequelize.define('Company', {
    name: DataTypes.STRING,
    url: DataTypes.STRING,
    symbol: DataTypes.STRING,
    description: DataTypes.STRING,
  }, {})
  Company.associate = function(models) {
    Company.hasMany(models.Rating)
  };
  return Company
};

I have created a custom createOrUpdateCompany() function. Find below my minimum executable example:

const models = require('./src/models');
const Company = require('./src/models').Company;

async function getAllCompanies() {
    try {
        let res = await Company.findAll()
        return res;
    } catch (error) {
        console.log(error)
    }
}

async function createOrUpdateCompany(dataArray) {

    if (dataArray === undefined || dataArray.length === 0) return ''

    let iss = []

    const allCompanies = await getAllCompanies()

    // flatten array
    dataArray = [].concat.apply([], dataArray)

    if (allCompanies !== undefined) {
        // 1. remove exact dedupes from dataArray
        dataArray = [...new Map(dataArray.map(obj => [JSON.stringify(obj), obj])).values()]

        // 2. compare dataArray to allCompanies and remove difference
        // dataArray = dataArray.filter(cv => !allCompanies.find(e => e.symbol === cv.symbol))
        dataArray = dataArray.filter(cv => !allCompanies.find(e => e.symbol === cv.symbol))

        // 3. Remove null values for link and "" values for name
        dataArray = dataArray.filter(cv => !(cv.name === '' || cv.url === null))
    }

    try {
        iss = await Company.bulkCreate(dataArray, {
            fields: [
                'name',
                'url',
                'symbol',
                'description',
            ]
        })
    } catch (error) {
        console.log(error)
    }
    return iss
}

let data = [{
    "date": "9/14/2019",
    "issuer": "Issuer6",
    "name": "Name1",
    "symbol": "Symbol2",
    "url": "www.url.com"
}, {
    "date": "9/11/2029",
    "issuer": "Issuer3",
    "name": "Name1",
    "symbol": "Symbol1",
    "url": "www.url.com"
}, {
    "date": "8/13/2019",
    "issuer": "Issuer1",
    "name": "Name1",
    "symbol": "Symbol1",
    "url": "www.url.com"
}]

async function main() {
    // setup db
    await models.sequelize.sync({
        force: true
    })

    await createOrUpdateCompany(data)
    await createOrUpdateCompany(data)
    await createOrUpdateCompany(data)
    await createOrUpdateCompany(data)

    console.log("##################### DONE #####################");
}

main()

When executing the above example I get the following in my db:

However, I would like to get only the following as result:

As you can see I only get two entries based on the unique result.

Any suggestions why my createOrUpdateCompany() is wrong?

I appreciate your replies!

There are 2 different dates with the same symbol ( ` "symbol": "Symbol1" `) — Neel Rathod, Sep 17 '19 at 18:16
@NeelRathod Thx for your reply! Yes I was thinking that the problem is coming from the array. However, why aren`t the duplicates not removed OR is there any easier way how to `bulkCreateAndUpdate()` data to the db... — Carol.Kar, Sep 17 '19 at 18:41

Neel Rathod · Accepted Answer · 2019-09-18T19:09:02.833

0

There are 2 ways to achieve uniqueness in your result

Filter your array data before execution (Javascript stuff) Unique elements from array

Make specific fields unique in the DB (composite unique) (Database stuff)Add Composite unique key in MySQL

edited Sep 18 '19 at 19:09

answered Sep 18 '19 at 03:31

Neel Rathod

2,013
12
28

Thx for your reply! How could I improve the above example? – Carol.Kar Sep 18 '19 at 16:44

Sequelize CreateOrUpdate Function

1 Answers1