Node Sequelize Postgres - BulkCreate ignore duplicate values for custom fields

Question

Goal: I have a list of objects from another service that I'd like to persist in my own Postgres datastore. The data coming from this other service returns JSON, but doesn't include any ids.

Current Outcome:

When I first run bulkCreate, it syncs the data to my database successfully (see example code below)
When I get a fresh batch from the other service (i.e. checking for updates) and call bulkCreate again, new topics are inserted into the database for each row, even if the title already exists

Expected Outcome

When I first run bulkCreate, it syncs the data to my database successfully (see example code below)
When I get a fresh batch from the other service (i.e. checking for updates) and call bulkCreate again, only topics with titles not found in the db are inserted, the rest have their 'count' property updated.

Topic.js

const database = require('../../shared/database'); // shared db connection. This works
const {DataTypes, Model} = require('sequelize');

class Topic extends Model { }

Topic.init({
  topicId: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
    defaultValue: undefined,
  },
  title: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  count: {
    type: DataTypes.INTEGER,
    allowNull: true,
  },
}, {
  sequelize: database, 
});

module.exports = Topic;

SyncAPI.js (1st run) - works as expected

const url = 'https://....'; // the remote service
const topics = await this.http.get(url); // simple example of getting the new data
// [{ 'title': 'Leadership', 'count': 214 }, 
// { 'title': 'Management', 'count': 51 }]

await Topic.bulkCreate(topics);
// [{ 'topicId': 1, 'title': 'Leadership', 'count': 214 }, 
// { 'topicId': 2, 'title': 'Management', 'count': 51 }]

SyncAPI.js (2nd run) - creates duplicates

const url = 'https://....'; // the remote service
const topics = await this.http.get(url); // note how the title is the same with updated counts
// [{ 'title': 'Leadership', 'count': 226 }, 
// { 'title': 'Management', 'count': 54 }]

await Topic.bulkCreate(topics); // the old, inserted topics remaining, with new entries appended
// [{ 'topicId': 1, 'title': 'Leadership', 'count': 214 }, 
// { 'topicId': 2, 'title': 'Management', 'count': 51 },
// [{ 'topicId': 3, 'title': 'Leadership', 'count': 226 }, 
// { 'topicId': 4, 'title': 'Management', 'count': 54 }

I see the sequelize documentation here (https://sequelize.org/master/class/lib/model.js~Model.html#static-method-bulkCreate) which says I can specify a 'ignoreDuplicates' option, but it only works by comparing the primary keys ("Ignore duplicate values for primary keys).

I'm looking for some way in the bulkCreate() method to specify 'ignoreDuplicates' with my custom key 'title' and then use 'updateOnDuplicate' to update the count.

E_net4 · Accepted Answer · 2022-08-31 13:39:47Z

6

You can define your own "unique" index (could be even composite) on the Model level and that will be used to check the uniqueness.

const database = require('../../shared/database'); // shared db connection. This works
const {DataTypes, Model} = require('sequelize');

class Topic extends Model { }

Topic.init({
  topicId: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
    defaultValue: undefined,
  },
  title: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  count: {
    type: DataTypes.INTEGER,
    allowNull: true,
  },
}, {
  sequelize: database, 
  indexes: [{
             unique: true,
             fields: ['title'] // you can use multiple columns as well here
           }]
});

module.exports = Topic;

And now when you run this command, it will not create any duplicates and simply ignore them.

await Topic.bulkCreate(topics, {ignoreDuplicates: true);

edited Aug 31, 2022 at 13:39

E_net4

30.5k13 gold badges118 silver badges155 bronze badges

answered Sep 29, 2021 at 15:25

Mr.P

1,2674 gold badges26 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

AreUMinee Over a year ago

Very good answer, thank you! What if I don't want to add a Unique Key, and my Primary Key is UUID?

Mr.P Over a year ago

@AreUMinee check this out :) stackoverflow.com/questions/50414899/…

AreUMinee Over a year ago

I don't even remember what I was looking for but thanks! :)

ebandersen · Accepted Answer · 2020-10-09 16:11:26Z

0

My final solution was to actually take the title and convert it into a hash, and then store that as a 'topic_uid' in my database as as UUID.

answered Oct 9, 2020 at 16:11

ebandersen

2,3521 gold badge27 silver badges25 bronze badges

Collectives™ on Stack Overflow

Node Sequelize Postgres - BulkCreate ignore duplicate values for custom fields

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related