Sequelize CreateOrUpdate Function

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:

enter image description here

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

enter image description here

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!

Answer

There are 2 ways to achieve uniqueness in your result

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *