How to make Ecto.Multi link multiple inserts where subsequent inserts require the primary key of the first operation?

[fireproofsocks]

A fairly simple use-case: a user has one or more email addresses, stored in the database in 2 separate tables (users, emails) such that the emails table has a foreign key for user_id. (In reality, my use case is more complex, but this illustrates the problem).

Ecto.Multi lets us create a single transaction for adding both the user record and an email record, and we can rollback if either one of those operations fail. I’m looking at the docs for the run/3 and run/5 functions https://hexdocs.pm/ecto/Ecto.Multi.html#run/3 but I can’t figure out how these are supposed to be used. From the docs, I’m not at all clear on what value the run function is supposed to return or how that gets ingested by the next operation.

I know I could (sometimes) do this with a single insert operation by leveraging the relationships defined in the schema, but I’m more interested in the principle of the thing.

Thanks as always!

[abitdodgy]

Edit: I’d use @idi527’s example as it’s much more idiomatic.

---

Here’s a contrived example that should give you the general idea.

Ecto.Multi.new
|> Ecto.Multi.run(:user, fn _ ->
  create_user(user_params)
end)
|> Ecto.Multi.run(:email, fn %{user: user} ->
  create_email(user, email_params)
end)
|> Repo.transaction()

When you use Multi.run, you tag the operation using the second argument. For example, the |> Ecto.Multi.run(:user ... makes the result of create_user available under the :user key in the next the function.

[idi527]

create_user!(user_params) and create_email!(user, email_params) would need to return {:ok, resource} or {:error, reason}, so these are unlikely to be “bang” operations.

And the last |> Repo.insert() would need to be replaced with Repo.transaction().

---

@fireproofsocks note that Multi.runs are not automatically rolled back on errors, I’d probably use inserts instead. Modifying @abitdodgy’s example:

alias Ecto.Multi

Multi.new()
|> Multi.insert(:user, User.changeset(%User{}, user_params) end)
|> Multi.insert(:email, fn %{user: %User{id: user_id}} ->
  Email.changeset(%Email{author_id: user_id}, email_params)
end)
|> Repo.transaction()

[abitdodgy]

Good catch. I just cooked that example up!

[LostKobrakai]

note that Multi.run s are not automatically rolled back on errors, I’d probably use inserts instead.

Are you sure? A Multi is executed in a transaction and for as long as the command in a Multi.run does only cause side effects to the db and nothing else it should just be rolled back like any other db operation, which happened in that transaction. I‘ve even some places in my projects, where Multis are effectively nested, as the inner multis are executed in named functions which a outer multi composes.

[OvermindDL1]

Are you sure? A Multi is executed in a transaction and for as long as the command in a Multi.run does only cause side effects to the db and nothing else it should just be rolled back like any other db operation, which happened in that transaction. I‘ve even some places in my projects, where Multis are effectively nested, as the inner multis are executed in named functions which a outer multi composes.

Precisely, DB actions are rolled back because of a transaction, but run/3,5 is often used to perform non-db actions that need to be run only if the DB succeeds, however there is not version of run/? that allows you to pass both a ‘do’ and an ‘undo’ function, which would be a huge help personally. as right now you have to check it ‘after’ running Repo.transaction, thus meaning the actions of the multi have to ‘leak’ out of where they are defined. We could really use a run/4;8 as well to support both a do and undo…

[LostKobrakai]

How would you ensure that the cleanup doesn’t fail? For the database part it’s a guarantee of the database, but for other side effects ecto cannot do that. Ecto.Multi.run exist because before ecto 3 you could only pass a callback to Ecto.Multi.run and it’s the only way to compose function which do their own database operations.

[OvermindDL1]

How would you ensure that the cleanup doesn’t fail? For the database part it’s a guarantee of the database, but for other side effects ecto cannot do that. Ecto.Multi.run exist because before ecto 3 you could only pass a callback to Ecto.Multi.run and it’s the only way to compose function which do their own database operations.

That would be on the onus of the one writing their own cleanup function. Some things are just not undoable of course, thus keep using the currently existing functions for those.

[LostKobrakai]

That’s exactly why Ecto.Multi is not the tool to use imo. You’re no longer orchestrating database operations within a transaction, but arbitrary operations. This is a different requirement, which should be handled by a more appropriate tool like sage.

[OvermindDL1]

That’s exactly why Ecto.Multi is not the tool to use imo. You’re no longer orchestrating database operations within a transaction, but arbitrary operations. This is a different requirement, which should be handled by a more appropriate tool like sage.

Well in my case I’m orchestrating things that need to stay in sync between 2 different databases and an LDAP system, where if any of them fail I can and do need to roll them all back. It’s still a database transaction, just spread across 3 databases (one of which is not transactional, LDAP, so have to roll it back manually)… ^.^;

What is this sage thing and most curiously how would it handle this?

[LostKobrakai]

Nebo15/sage

[OvermindDL1]

@LostKobrakai Awesome, thanks for that! It seems the readme makes clear everything I’d need to do and how it works except for one big thing, the multiple database calls across the multiple databases do they run in a singular DB transaction each across them all or are they on a call-by-call basis only (I.E. no DB transactions across the entire Sage call)? I.E. does it start up an Ecto.Repo.transaction for all calls to the given database (thus those calls need to be handled inside that transaction) on the first call of a given repo’s transaction or…)? How does it handle the transactions failing in any order and handling the rollback then?

[LostKobrakai]

I’ve never used it by myself, so I’m not sure how integrated it is with ecto. But maybe the possibility to arbitrarily nest transactions in ecto can be of aid for that.

[OvermindDL1]

But maybe the possibility to arbitrarily nest transactions in ecto can be of aid for that.

That depends on the database itself, but that’s not the issue here, the issue is keeping two transactions active and in sync across two different databases, I.E. two different Repo modules.

[LostKobrakai]

I was thinking about something like this, but as I said I’ve no clue if it would really work:

Repo1.transaction(fn -> 
  Repo2.transaction(fn -> 
    Sage.execute(…)
  end)
end)

Maybe there are also better ways to do that.

[TKOxff]

Recently I’m faced the similar problem and I solved like the sample code below.
To get a ID from previous insert operation through Multi.run/3 and use Enum.map for several insert operations.
In my case It was simpler not using insert_all.

def create_user_and_email(params) {
email_params = params[“user_emails”]

Multi.new()
|> Multi.insert(:user, User.changeset(%User{}, params)
|> Multi.run(:emails, fn repo, %{user: user} ->
results =
Enum.map(email_params, fn email ->
# you can use the user.id into email here.
repo.insert(Email.changeset(%Email{}, email))
end)
{:ok, results}
end)
}
|> Repo.transaction( )