Elixir Repo.insert_all/2 and unique constraint

Question

I've got a rather interesting problem today. I'm trying to implement a bulk insert from CSV file into my database using the Ecto function Repo.insert_all/2 however, one thing bothers me.

The problem is the following code in my context:

defmodule AppName.Roles do
  def bulk_insert(array_of_maps) do
    try do 
      Repo.insert_all(Role, array_of_maps)
    rescue
      exception in Postgrex.Error ->
        _handle_exception(exception) # or whatever
    end
  end
end 

For now though, this seems like a hack. Since I know there's a built-in mechanism of changesets which do handle unique constraints, but I have no idea how to include that part of the system with Repo.insert_all/3

But since the insert_all function doesn't care about changesets it makes this even harder.

( I'm of course referring to unique_constraint/2 )

I do know I can:

• Use Multi to perform this, but this creates separate queries on the backend instead of doing it as a one big query

• Keep the code using a try rescue block, but I'd like to see if there's a more Elixir-y way to solve this due to the philosophy of pattern matching and let it crash terminology.

Answer 1

Ecto.Repo.insert_all/3 accepts a list of options, one of which is

• :on_conflict — It may be one of :raise (the default), :nothing, :replace_all, :replace_all_except_primary_key, {:replace, fields}, a keyword list of update instructions or an Ecto.Query query for updates.

Usually whether one wants to handle conflicts, they do set that option to somewhat helpful instead of raising an exception.

Also :conflict_target option might be used to provide unsafe fragments.