Upsert not working on bulk_insert if the upsert_identity attribute is not accepted by the action

I was trying to upsert rows from a database export that I processed with Python and exported back to CSV to be loaded back from Elixir + Ash.

Instead of fetching all rows, updating the resources in memory, and using a bulk_update (not sure about that as I’ve never tried to use it for now. EDIT: welp, this is definitely not how bulk_update works :smiling_face_with_tear:), I figured it would be easier to just use bulk_insert and use my id attribute as the upsert_identity:

%{records: inserted_skills, error_count: skills_error_count, errors: skills_errors} =
  Ash.bulk_create(unique_skills_data, Skill, :create,
    return_records?: true,
    upsert?: true,
    upsert_identity: :id,
    upsert_fields: [:slug, :name, :is_product, :acronym, :acronym_of],
    return_errors?: true,
    select: [:id, :slug, :name]
    # skip_unknown_inputs: :*
  )

As you can see, I initially tried using skip_unknown_inputs because I was getting an UnknownInput error on the id attribute. Since my id isn’t writable and should not be accepted by actions, I didn’t see another workaround that wouldn’t risk data consistency.

After that, I couldn’t understand why the upsert wasn’t working (it just inserted new rows with new IDs), and I thought my code was at fault (whether Python notebook exporting the CSV or the Elixir import script).

It turns out that if I just make my id attribute accepted by the create action (and writable, as it’s a requirement to make it accepted by actions), the issue doesn’t arise anymore.

I’m not sure whether you’d consider that a bug or a feature. On my side, I’d consider it somewhat of a bug, especially since I’m not trying to write to it (it’s not in my upsert_fields list, which then would make sense to error).

I think for now I’ll consider updating in memory and using bulk_update EDIT: [make my id attribute writable & accepted] (unless there’s a better way I’m not aware of), but figured out it would be interesting to raise a discussion on the topic!

All the best :grin:

So, the reason it has to be writable is because, if it actually does turn out to be a create, you will be creating a record with the value provided. There isn’t really any way to get around that.

1 Like

Ah, thanks Zach, that makes sense!

I guess I’ll just leave it as writable and create a special action for this script, which will be the only one accepting it.

Hope you’re doing great!

Doing well, thanks! Busy busy busy as always :laughing:

1 Like