Module caching variable for saving database hits

Since Elixir is not object oriented, there are no module level variables (“properties”) that you could set like this. A process can keep state which can contain that data, and that you could accomplish with Agent or GenServer, but a single process may (or may not) become a bottleneck for the queries. Another option is to put the data in ETS, perhaps using some caching wrapper. There is also persistent_term which are kind of globals, but writing to them is expensive.

Personally I would think if I really need to do this, as databases should be quite good at checking a unique index already. But if I did need it and was running on a single node, I would use ETS and get the ID from there.

Thank you - this is a one time setup, so I would assume ETS would work out. But, thinking about it, since the database level is good at checking unique indexes, and this is a one time setup, maybe I shouldn’t care too much about the caching (I suppose I was challenging myself). If I went the database route, do I pass the options of on_conflict: :nothing? I would still need to get the id, however. Perhaps I will look for a solution to do something like find_or_create (I’m assuming a check by Repo.one first, and if not, insert)…

There’s some material here: Constraints and Upserts — Ecto v3.14.0

Also check out the :returning option.

Correct. A minimal solution to this problem is to switch from Enum.map (useful when the transformation is independent of previous elements) to Enum.reduce which passes state to each iteration:

  |> Enum.reduce(%{}, fn row, artists_map ->
     [artist, _album, _type, _release_date, _catalog, _label, _main_release] = row

      check_or_insert_artist(artist, artists_map)
  end)

I would just put everything in a map/list

Ecto.insert_all(%Artist, artist_map, opts)

Tell in the opts to use insert_ignore.

This would speed up your execution enough with minimal effort and offloading some of the responsibility to db. If the record exists ignored if not entered in one batch transaction and network call.

Thank you all for your suggestions - I like the reduce option, however, I’ll be dealing with more than just artists, but other attributes.

At the end, I didn’t worry about the database hits to worry about ETS, or Agent (though, these I will look into for my own sake). From the suggested help, and posted links, I did this in the Artist model:

  def changeset(artist, params \\ %{}) do
    artist
    |> cast(params, [:artist])
    |> validate_required([:artist])
    |> unique_constraint(:artist)
  end

And then inserted within lib/populate_data.ex:

    "priv/data/artists_and_albums.csv"
    |> File.stream!(read_ahead: 100_000)
    |> CSV.parse_stream
    |> Enum.map(fn row ->
      [artist, _album, _type, _release_date, _catalog, _label, _main_release, _duration, _wiki_link] = row
      IO.inspect insert_artist(artist, artists_map)
    end)
  end

  def insert_artist(artist, artists_map) do
    case Repo.insert(Artist.changeset(%Artist{}, %{artist: artist})) do
      {:ok, struct} ->
        struct.artist_id
      {:error, _} ->
        Repo.get_by!(Artist, artist: artist).artist_id
    end
  end

I see errors whizz by but they are caught, and the output is:

[debug] QUERY OK source="artists" db=0.1ms idle=2.8ms
SELECT a0."artist_id", a0."artist" FROM "artists" AS a0 WHERE (a0."artist" = $1) ["Nine Inch Nails"]
[debug] QUERY ERROR db=0.2ms idle=2.8ms
INSERT INTO "artists" ("artist") VALUES ($1) RETURNING "artist_id" ["Nine Inch Nails"]
452

452 being the ID. I see it fails to insert, but then selects the entry again. I’m not worried about optimizing this as I learn, but maybe will revisit this with the other suggestions.

I was thinking maybe iterate through the CSV file, collect the artists then do a insert_all (as suggested above), and after, then fetch all the records into a Map so I could do artist_map[artist] and get the ID that way?

Awesome suggestions - thank you all!

Actually, maybe I spoke too soon - it works but I was thinking maybe a more elegant pattern would be:

lib/populate_data.ex:

"priv/data/artists_and_albums.csv"
    |> File.stream!(read_ahead: 100_000)
    |> CSV.parse_stream
    |> Enum.map(fn row ->
      [artist, _album, _type, _release_date, _catalog, _label, _main_release, _duration, _wiki_link] = row

      IO.inspect  artist_id = Artist.fetch_or_insert_artist(artist)

    end)
  end

And then in the Artist model:

defmodule MyApp.AlbumType do
  use Ecto.Schema
  import Ecto.Changeset

  alias MyApp.Repo

  @primary_key {:artist_id, :id, autogenerate: true}

  schema "artists" do
    field :artist, :string
  end

  def fetch_or_insert_artist(artist) do
    Repo.get_by(MyApp.Artist, artist: artist) ||
     insert_artist(artist)
  end

  def insert_artist(artist) do
    case Repo.insert(MyApp.Artist.changeset(%MyApp.Artist{}, %{artist: artist})) do
      {:ok, struct} ->
        struct
      {:error, _} ->
        Repo.get_by!(MyApp.Artist, artist: artist)
    end
  end

  def changeset(artist, params \\ %{}) do
    artist
    |> cast(params, [:artist])
    |> validate_required([:artist])
    |> unique_constraint(:artist)
  end
end

Is this a good pattern - to move this to the model? Also referencing MyApp.Artist within the model seems…redundant - is there a better way?

Thank you - I am really loving this!

No, you have meddled your model with business actions rather than just data and changes.

You should use a context to operate on your model and define business use cases / actions

Did you arrive at a solution you are happy with?

Yes, 100%. Thank you all!