MikroORM: How to ignore duplicate entries on flush()

Is there a way to ignore duplicate entries (typically a 1062 SQL error on MySQL/MariaDB), when calling flush()?

If the entry exists, is there a way to get the EntityManager to use the existing row to override the Entity with the new reference and continue with the Unit Of Work?

If not, would the best solution be to write my own function to findOne() and then add to the entity graph?

Thanks for you help!

Edit 1: For example is there any downfall to simply not persisting if found?Maybe a function something like this...

  /**
   * 
   * @param em an EntityManager
   * @param ent an Entity that is already populated and ready to be persisted.
   * @param collision field and value for potential duplicate.
   * @returns the referenced entity to be used in an entity graph if necessary
   */
  async persistOrIgnore<T extends AnyEntity<T>>(em: EntityManager, ent: Loaded<T>, collision: ObjectQuery<T>): Promise<Loaded<T>> {
    const entName = ent.__meta?.className;
    const found = await em.findOne(entName, collision);
    if (!found) em.persist(ent);
    return found || ent;
  }

Then use like this:

const order = new Order(); //parent to persist

let contents = new OrderContents(); // new entity
contents.checksum = 'xxx' // unique field that will produce 1062 SQL error
// [...add other properties you would like to persist.]
  
contents = await persistOrIgnore(em, contents, { checksum: contents.checksum });

order.contentsId = contents;
em.persist(order);

// [...do more work, e.g. add more to the parent order entity]

await em.flush();

Edit 2: There is now upsert functionality in v5.5.0:

https://mikro-orm.io/docs/entity-manager#upsert

Solution

You can't, it is your responsibility to have a valid state in the context (identity map). You should use the QB for upserts, there is a support for on conflict queries.

const qb = em.createQueryBuilder(User)
  .insert({
    email: 'ignore@example.com',
    name: 'John Doe',
  })
  .onConflict('email').ignore();

See more examples in the QB tests:

https://github.com/mikro-orm/mikro-orm/blob/master/tests/QueryBuilder.test.ts#L1327

Or check the knex documentation on this, as the API is designed based on it (and just wraps it basically):

https://knexjs.org/guide/query-builder.html#onconflict

2024 update: nowadays there is em.upsert() and em.upsertMany().

https://mikro-orm.io/docs/entity-manager#upsert