How to do an Ecto upsert query in Ash?

How do I run the Ecto upsert bellow using Ash?

      Repo.insert!(
        %DocumentoFiscalSerie{codigo: codigo, contador: 2},
        on_conflict: [inc: [contador: 1]],
        conflict_target: [:codigo],
        returning: [:contador]
      )

At the moment we do not have atomic upserts. I am planning to have them available this week or next. You will need to use ecto + a manual action to approximate it for now.

1 Like

For context, you could do an atomic update like this:

update :increment_contador do
  change atomic_update(:contractor, expr(contador + 1))
end

The syntax for an upsert will look very similar

create :create_or_increment_counter do
  upsert? true
  upsert_identity :identity_name
  upsert_fields []
  change atomic_update(:contractor, expr(contador + 1))
end

@zachdaniel, it’s not a problem for me if I have to use ecto + a manual action until it’s available on Ash, but I don’t know how to use (pure) Ecto statements/actions. Could you give me an example?

Thanks.

Sure, an example using a manual create might look like this (haven’t run this code, may be issues with it):

create :create_or_increment_counter do
  accept []
  argument :codigo, :whatever_type_this_is, allow_nil?: false

  manual fn changeset, _ -> 
       result = Repo.insert!(
        # we use `struct` because using %__MODULE__{} in this context will cause a compile error
        struct(__MODULE__, codigo: changeset.arguments.codigo, contador: 2),
        on_conflict: [inc: [contador: 1]],
        conflict_target: [:codigo],
        returning: true
      )
     {:ok, result}
  end
end

Alternatively you could use a generic action if you want a customized return.

action :create_or_increment_counter, :whatever_type_this_is do
  argument :codigo, :whatever_type_this_is, allow_nil?: false

  run fn input, _ ->
       result = Repo.insert!(
        # we use `struct` because using %__MODULE__{} in this context will cause a compile error
        struct(__MODULE__, codigo: changeset.arguments.codigo, contador: 2),
        on_conflict: [inc: [contador: 1]],
        conflict_target: [:codigo],
        returning: [:codigo]
      )

     {:ok, result.codigo}
  end
end
1 Like

How do I set id and timestamps?

iex(6)> Garagem.Operacional.DocumentoFiscalSerie.contador(“A”)
[debug] QUERY ERROR db=0.9ms idle=1841.1ms
INSERT INTO “documentos_fiscais_series” AS d0 (“codigo”,“contador”) VALUES ($1,$2) ON CONFLICT (“codigo”) DO UPDATE SET “contador” = d0.“contador” + $3 RETURNING “codigo” [“A”, 2, 1]
↳ Garagem.Operacional.DocumentoFiscalSerie.run_0_generated_7D9DC3760FCF8DAEA60EDEC12C8F51E3/2, at: lib/garagem/operacional/resources/documento_fiscal_serie.ex:41
** (Postgrex.Error) ERROR 23502 (not_null_violation) null value in column “id” of relation “documentos_fiscais_series” violates not-null constraint

table: documentos_fiscais_series
column: id

Failing row contains (null, null, null, A, 2).
(ecto_sql 3.10.2) lib/ecto/adapters/sql.ex:1047: Ecto.Adapters.SQL.raise_sql_call_error/1
(ecto 3.10.3) lib/ecto/repo/schema.ex:764: Ecto.Repo.Schema.apply/4
(ecto 3.10.3) lib/ecto/repo/schema.ex:377: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
(ecto 3.10.3) lib/ecto/repo/schema.ex:273: Ecto.Repo.Schema.insert!/4
(garagem 0.1.0) lib/garagem/repo.ex:2: Garagem.Repo.insert!/2
(garagem 0.1.0) lib/garagem/operacional/resources/documento_fiscal_serie.ex:41: Garagem.Operacional.DocumentoFiscalSerie.run_0_generated_7D9DC3760FCF8DAEA60EDEC12C8F51E3/2
(ash 2.15.8) lib/ash/actions/action.ex:121: Ash.Actions.Action.run/3

Ah, right you’ll probably want to do it this way:

create :create_or_increment_counter do
  accept []
  argument :codigo, :whatever_type_this_is, allow_nil?: false

  manual fn changeset, _ ->
     {:ok, record} = Ash.Changeset.apply_attributes(changeset, force?: true)
       result = Repo.insert!(
        # we use `struct` because using %__MODULE__{} in this context will cause a compile error
       %{record | contador: 2},
        on_conflict: [inc: [contador: 1]],
        conflict_target: [:codigo],
        returning: true
      )
     {:ok, result}
  end
end
1 Like

Ash.Changeset.apply_attributes/2 lets you take the changes done so far in the changeset and get a struct with those values, which should include the generated fields.

1 Like

Thanks, @zachdaniel. We are almost there. How do I add the argument contador to %{record | contador: 2}?

Replace 1 with changeset.arguments.contador

1 Like

And replace 2 with the same, I assume.

Sorry, I wasn’t clear. The argument is :codigo, which is another attribute. Actually, :contador is the conflict_target. I have to add it to the struct for the insert.

I gotta here. Thanks, @zachdaniel. Case closed.

2 Likes