Ecto Insert vs. Update Best Practice

[wi...@samadhi.tech]

Hi all,

I am loading data from an external vendor API and mapping it into an internal schema. The external data has the ability to change without notifying me, so I must constantly check incoming data versus my internal schema to check whether it is new entirely, unchanged or needs updating. This has the potential for a lot of database reads, so I'm wondering what the best way of dealing with this in in Ecto. I have a Postgresql database. 

There are two routes I see, but I'm not sure which is more expensive (or whether there is a third, better option).

1. Load the existing values into a variable, then enumerate the incoming external data versus the variable to identify the correct action (insert, update, nothing).

2. Enumerate the incoming external data calling the database with each record to determine the correct action (insert, update, nothing).

I sense #1 is more efficient, but I'm new to Elixir so I'm hoping for another opinion.

Thanks!

Will

[José Valim]

The #1 option will be more efficient unless you have so much data you can't load it all into memory.

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/d6c90569-c036-4fc2-8a46-0f4d377f8de5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[wi...@samadhi.tech]

Great, thanks!

On Monday, August 8, 2016 at 4:21:17 PM UTC-7, José Valim wrote:

The #1 option will be more efficient unless you have so much data you can't load it all into memory.

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

On Mon, Aug 8, 2016 at 6:34 PM, <wi...@samadhi.tech> wrote:

Hi all,

I am loading data from an external vendor API and mapping it into an internal schema. The external data has the ability to change without notifying me, so I must constantly check incoming data versus my internal schema to check whether it is new entirely, unchanged or needs updating. This has the potential for a lot of database reads, so I'm wondering what the best way of dealing with this in in Ecto. I have a Postgresql database. 

There are two routes I see, but I'm not sure which is more expensive (or whether there is a third, better option).

1. Load the existing values into a variable, then enumerate the incoming external data versus the variable to identify the correct action (insert, update, nothing).

2. Enumerate the incoming external data calling the database with each record to determine the correct action (insert, update, nothing).

I sense #1 is more efficient, but I'm new to Elixir so I'm hoping for another opinion.

Thanks!

Will

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.

To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.

[wi...@samadhi.tech]

I have a follow-up question. For the sake of discussion, I am working with the second scenario. For each record of the external data I'd like to insert or update, I prepare a changeset to validate the data with my internal schema. I thought I could easy alter the changeset for an update action. However I get an error when trying to do this.

  def insert_else_update(changeset) do

    # Check to see if account already exists.

    account = Repo.get_by(Account, ext_id: changeset.changes.ext_id)

    if account == nil do

      Repo.insert!(changeset)

    else

      %{changeset | action: :update}

      |> put_change(:id, account.id)

      |> Repo.update!

    end

  end

The error is (Ecto.NoPrimaryKeyValueError). When I inspect the changeset before the Repo.update! statement, all the data is available. Is my approach totally off here?

Thanks again!

[wi...@samadhi.tech]

To close the loop on this, I figured it out. I was using an incorrect approach. Behind the scenes, Repo.update! is loading the existing record into the :data element of the changeset; you cannot simply update a changeset with the primary key of the record you want to update. The below modification to my function solved my problem.

  defp insert_else_update(changeset) do

    # Check to see if account already exists.

    account = Repo.get_by(Account, ext_id: changeset.changes.ext_id)

    if account == nil do

      Repo.insert!(changeset)

    else

      account = change(account, changeset.changes)

      |> Repo.update!

    end

  end