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

Answer 1

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();
}