What is the simplest way to do upsert with Ecto (MySQL)

Question

Doing upsert is common in my app and I want to implement the cleanest and simple way to implement upsert.

1. Should I use fragments to implement native sql upsert?
2. Any idiomatic ecto way to do upsert?

Whirlwind

Answer 1

You can use Ecto.Repo.insert_or_update/2, please note that for this to work, you will have to load existing models from the database.

 model = %Post{id: 'existing_id', ...}
 MyRepo.insert_or_update changeset
 # => {:error, "id already exists"}

Example:

result =
  case MyRepo.get(Post, id) do
    nil  -> %Post{id: id} # Post not found, we build one
    post -> post          # Post exists, using it
  end
  |> Post.changeset(changes)
  |> MyRepo.insert_or_update

case result do
  {:ok, model}        -> # Inserted or updated with success
  {:error, changeset} -> # Something went wrong
end

Tipperary

Answer 2

In my case insert_or_update raised an error due to the unique index constraint 🤔

What did work for me was Postgres v9.5 upsert through on_conflict parameter:

(considering unique column is called user_id)

changeset
|> MyRepo.insert(
    on_conflict: :replace_all,
    conflict_target: :user_id
)

Rev

This approach won't work on MySQL 5..7. Just learned that :) – Jeggar

Answer 3

If you're looking to upsert by something other than id, you can swap in get_by for get like this:

model = %User{email: "[email protected]", name: "Cat", ...}

model |> User.upsert_by(:email)
# => {:found, %User{...}} || {:ok, %User{...}}

defmodule App.User do
  alias App.{Repo, User}

  def upsert_by(%User{} = record_struct, selector) do
    case User |> Repo.get_by({selector, record_struct |> Map.get(selector)}) do
      nil -> %User{} # build new user struct
      user -> user   # pass through existing user struct
    end
    |> User.changeset(record_struct |> Map.from_struct)
    |> Repo.insert_or_update
  end
end

On the off chance you're looking for a flexible approach that works across models and for multiple selectors (ie country + passport number), check out my hex package EctoConditionals!

Moskow

This answer doesn't necessarily work because you have to have an id to use with insert_or_update. – Vanthe

Hmm, thanks for bring that up! Though the docs appear to suggest that insert_or_update/1 needs an id key in the struct, it doesn't seem to actually require it. Feel free to see for yourself by running something to the effect of%User{} |> User.changeset(%{email: "[email protected]"}) |> Repo.insert_or_update – Moskow

You don't need an id for the insert, you do need one for the update. Repo.update always does the update based on the id of the record. So, if you don't have an id then you can't update. – Vanthe

In the update case, Repo.get_by/2 would return a User struct that has a valid id. But yes, you are right that an id is necessary to update. – Moskow