CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Julius Guevarra
Julius Guevarra

Reputation: 888

Sequelize: How to upsert with specified column duplicates

I wanted to create new record if the the record with specified column name does not match or exist else update it.

I have a Model named AssignedDownloadRoute and defined it like this

AssignedDownloadRoute.init(
  {
    assigned_download_route_id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      allowNull: false,
    },
    route_code: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    assigned_user_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
    },
    assigner_user_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
    },
    reading_period: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    created_modified: {
      type: DataTypes.DATE,
      allowNull: false,
      defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
    }
  },
  {
    sequelize: DBInstance,
    modelName: 'AssignedDownloadRoute',
    tableName: 'assigned_download_route',
    timestamps: false,
  }
);

Assuming I have this data that I want to be inserted but once I already have it inserted it should not create a new record but instead, updates/overwrites it.

const bulkData = [
  {
    route_code: '123',
    assigned_user_id: 3,
    assigner_user_id: 26,
    reading_period: '202105',
    created_modified: '2021-06-11 06:36:20'
  },
  {
    route_code: '456',
    assigned_user_id: 4,
    assigner_user_id: 32,
    reading_period: '202105',
    created_modified: '2021-06-11 06:36:20'
  }
]

When I try to run bulkCreate with updateOnDuplicate option, it does not update the existing record, it creates new one. I want the existing data to only update the assigned_user_id and assigner_user_id if the column name route_code matched/already exists.

AssignedDownloadRoute.bulkCreate(bulkData, {
   updateOnDuplicate: ['assigned_user_id', 'assigner_user_id'],
});

Upvotes: 0

Views: 1464

Answers (1)

dusthaines
dusthaines

Reputation: 1350

It appears that only one field in your model would qualify as unique (assigned_download_route_id) since it is defined as the primary key. Since that value is not included in your payload, there is nothing unique about each payload. If you would like route_code to be a unique value, you must define it accordingly in your model with the unique: true option.

AssignedDownloadRoute.init(
  {
    assigned_download_route_id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      allowNull: false,
    },
    route_code: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
    },
    assigned_user_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
    },
    assigner_user_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
    },
    reading_period: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    created_modified: {
      type: DataTypes.DATE,
      allowNull: false,
      defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
    }
  },
  {
    sequelize: DBInstance,
    modelName: 'AssignedDownloadRoute',
    tableName: 'assigned_download_route',
    timestamps: false,
  }
);

More details on the options of Models along with more complex unique key definitions (such as composite keys) can be read about here: https://sequelize.org/master/manual/model-basics.html

Upvotes: 1

Related Questions