CODEWITHSUNDEEP
mysqlelixirphoenix-frameworkecto
Teo Choong Ping
Teo Choong Ping

Reputation: 12808

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

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?

Upvotes: 16

Views: 11318

Answers (3)

Alex Troush
Alex Troush

Reputation: 677

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

Upvotes: 23

Klesun
Klesun

Reputation: 13683

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
)

Upvotes: 7

codeanpeace
codeanpeace

Reputation: 324

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!

Upvotes: 4

Related Questions