Control the column on upsert

[bakura10]

Hi !

I have an entity where I generate the UUID on application side (can't use database generation), where the email is the unique field. I try to do the upsert like this:

const customer = entityManager.create(Customer, request.body);
const managedCustomer = await entityManager.upsert(customer);

However MikroORM generate this SQL:

insert into customers (email, first_name, id) values ('test@test.com', 'foo', '71b804dc-7c92-4287-aff8-6e2c536832be') on conflict (id) do update set email = excluded.email, first_name = excluded.first_name returning last_name;

However, I would like MikroORM to check the conflict on the email, not the ID, so generate this query:

insert into customers (email, first_name, id) values ('test@test.com', 'foo', '71b804dc-7c92-4287-aff8-6e2c536832be') on conflict (email) do update set email = excluded.email, first_name = excluded.first_name returning last_name;

Is there a way to override the column that MikroORM is using? Maybe somehing like that:

await entityManager.upsert(customer, {conflictColumn: 'email'});

Thanks!

[B4nan]

What you can do now is to pass in DTO without the id and let the database generate it (you will need a default for the id on db level).

const customer = await em.upsert(Customer, { email: '...' });

Note that in your code, customer === managedCustomer, you won't get a new entity instance from that call.

[bakura10]

I would like to generate uuidv7, which is not yet supported natively (I generate it server side).

[B4nan]

I will add 4 new options:

• onConflictFields?: (keyof T)[]; (to control the conflict clause)
• onConflictAction?: 'ignore' | 'merge'; (used ignore and merge as that is how the QB methods are called)
• onConflictMergeFields?: (keyof T)[]; (to control the merge clause)
• onConflictExcludeFields?: (keyof T)[]; (to omit fields from the merge clause)

Which should make the upsert queries completely configurable. Already have a WIP that handles the queries correctly, the tricky part will be handling the entity mapping (both to existing entity instances and to the POJO signatures) and reloading the missing data (for mysql that does not have a returning clause).

This should cover also #4325, which is about being able to omit a field from the merge data.

[bakura10]

Thanks a lot, this looks like a perfect solution to this problem! Great job!