Proposal: ignore_conflict

[bunnylushington]

Hi -- I use the following code to add an on conflict (col) do nothing clause to the SQL statement Moebius generates and wonder if (a) this would be a generally useful addition to the library and (b) how detailed an implementation would be required to be general enough. (on conflict can be arbitrarily complicated; personally I'd opt for a function for anything more involved than this simple statement but I know that might be an extreme position.)

  @spec ignore_conflict(%Moebius.QueryCommand{}, String.t) ::
      %Moebius.QueryCommand{}
  @doc "Do nothing when an insert conflict is detected on COL."
  def ignore_conflict(%Moebius.QueryCommand{} = cmd, col) do
    conflict = "on conflict (#{col}) do nothing"
    new = Regex.replace(~r/returning.+$/, cmd.sql, "#{conflict} \\0")
    %{cmd | sql: new}
  end

If there is interest, I'd be happy to supply a PR (with any guidance incorporated). Thanks!

[cpursley]

Hi @bunnylushington - could provide some hypothetical scenario? How are you using this?

[bunnylushington]

Sure. In a couple of instances in my code I'm receiving bursts of data where checking for the presence of an existing record would cause more DB traffic than is ideal and ignoring dups is sufficient. I am, however, interested in capturing any errors where other constraints are not fulfilled. In my code (minus any error handling) that's something like:

    {:ok, _} = Query.db(:messages)
    |> Query.insert(message)
    |> ignore_conflict(:message_id)
    |> Moebius.Db.run()

where message_id is my PK. I do something functionally similar with (reasonably small) lookup tables where new entries may be created but existing rows are immutable. There are more defensive ways of coding for that but this happy path approach where everything is inserted (or ignored) each time it's refreshed is easy to maintain and reason about.

To be clear: it's not a problem if this is inappropriate for inclusion! I use this pattern a lot but understand that I might be so very alone. 😄

[cpursley]

That sounds useful, I'm open to a PR as long as it's well tested :)