How to ignore DUPLICATE ENTRY error when updating multiple records at once using TypeORM

Question

I am trying to update hundreds of database records using the TypeORM library. Problem is that sometimes DUPLICATE ERR is returned from SQL when the bulk upload is performed and stops the whole operation. Is possible to set up TypeORM in a way so duplicate entries are ignored and the insert is performed? The table is using two primary keys:

This is my insert command (TypeORM + Nestjs):

  public async saveBulk(historicalPrices: IHistoricalPrice[]) {
    if (!historicalPrices.length) {
      return;
    }
    const repoPrices = historicalPrices.map((p) => this.historicalPricesRepository.create(p));
    await this.historicalPricesRepository.save(repoPrices, { chunk: 200 });
  }

Thanks in advance

I've never worked with typeorm, but it looks like they've had support for onIgnore since Q4 2018, at least based on this pull request. That will treat duplicate inserts as warnings instead of causing error. — Paul T.
– Paul T., Commented Oct 21, 2022 at 1:19

Rahul Sharma · Accepted Answer · 2022-10-21 06:58:08Z

4

You will have to use InsertQueryBuilder to save the entities instead of repository.save method. InsertQueryBuilder will allow you to call an additional method orIgnore() which will add IGNORE literal into your mysql INSERT statement. From mysql official doc:

When INSERT IGNORE is used, the insert operation fails silently for rows containing the unmatched value, but inserts rows that are matched.

One demerit is obviously that you'll have to now chunk the rows on your own. InsertQueryBuilder doesn't provide any options to chunk the entities. Your code should look like this:

for (let i = 0; i < historicalPrices.length; i += 200) {
  const chunk = historicalPrices.slice(i, i + 200);
  const targetEntity = this.historicalPricesRepository.target;
  await this.historicalPricesRepository
    .createQueryBuilder()
    .insert()
    .into(targetEntity)
    .values(chunk)
    .orIgnore()
    .execute();
}

answered Oct 21, 2022 at 6:58

Rahul Sharma

6,0555 gold badges32 silver badges54 bronze badges

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

1 Comment

Juraj Zovinec Over a year ago

Thank you for the explanation and solution. Chunking to code on my own is completely fine as it removes unnecessary layer of abstraction

Collectives™ on Stack Overflow

How to ignore DUPLICATE ENTRY error when updating multiple records at once using TypeORM

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related