Ecto.Multi: Atomic Database Workflows with Named Failure Reporting

The Problem: Coordinating Multiple Database Changes Safely

When building Elixir applications with Ecto, you often need to execute several database operations that must either all succeed or all fail together. Consider a user registration flow that creates a user record, generates a profile, and sends a welcome email. If any step fails, you need to ensure partial data doesn't remain in the database.

Ecto.Multi: The Solution

Ecto.Multi is a data structure that describes a sequence of named database operations executed within a single transaction. It returns {:ok, changes} on success or {:error, failed_name, failed_value, changes_so_far} on failure, giving you structured error handling with clear identification of which operation failed.

How It Works: A Practical Example

Here’s a complete example showing how to use Ecto.Multi for a registration workflow:

def register_user(user_params, profile_attrs) do
  Ecto.Multi.new()
  |> Ecto.Multi.insert(:user, User.changeset(%User{}, user_params))
  |> Ecto.Multi.insert(:profile, fn %{user: user} ->
      Profile.changeset(%Profile{}, Map.put(profile_attrs, :user_id, user.id))
    end)
  |> Repo.transaction()
end

Each step receives a changes map containing results from previous successful steps. The second insert accesses the newly created user via %{user: user}, enabling clean dependency chaining without nested callbacks.

Understanding the Return Values

On success, you receive:

{:ok, %{user: %User{}, profile: %Profile{}}}

If the profile insertion fails due to a validation error:

{:error, :profile, %Ecto.Changeset{}, %{user: %User{}}}

This tells you exactly which step failed, what value caused the failure, and what succeeded before it.

Available Operations

Ecto.Multi supports various database operations:

• Multi.insert/3 - Insert a new record
• Multi.update/3 - Update existing records
• Multi.delete/3 - Delete records
• Multi.insert_all/3 - Bulk insert multiple rows
• Multi.run/3 - Execute arbitrary functions
• Multi.one/3 and Multi.all/3 - Read operations
• Multi.merge/3 - Compose smaller multis together

Common Mistakes and Gotchas

External Side Effects Are Not Rolled Back

Operations like sending emails, making HTTP requests, or writing files inside Multi.run/3 will execute regardless of later failures. These effects cannot be undone by the database transaction:

# WRONG: Email sent even if later steps fail
Multi.run(:send_email, fn _changes ->
  Email.deliver(user.email)  # This sends immediately
  {:ok, %{}}
end)

# CORRECT: Send after transaction succeeds
case Repo.transaction(multi) do
  {:ok, changes} ->
    Email.deliver(changes.user.email)  # Only sends on full success
    {:ok, changes}
  {:error, _} ->
    {:error, :transaction_failed}
end

Incorrect Error Return from Multi.run

To abort a transaction, Multi.run must return {:error, reason}. Returning nested error tuples will be treated as success:

# WRONG: This commits the transaction
Multi.run(:validate, fn _ ->
  {:ok, {:error, "invalid"}}
end)

# CORRECT: This aborts the transaction
Multi.run(:validate, fn _ ->
  {:error, "invalid"}
end)

Relying on Step Order Alone

While steps execute in the order added, only explicit dependencies in the changes map guarantee correct sequencing. Refactoring may break code that assumes incidental ordering:

# FRAGILE: Relies on order, not explicit dependency
Multi.new()
|> Multi.insert(:a, changeset_a)
|> Multi.insert(:b, fn _ -> build_b_from_a() end)  # Assumes 'a' exists

# ROBUST: Explicit dependency
Multi.insert(:b, fn %{a: a} -> build_b_from_a(a) end)

When to Use Multi vs Plain Transactions

Use Ecto.Multi when:

• You have 3+ operations that must be atomic
• You need structured error reporting identifying the failed step
• You want readable, testable workflows with clear dependencies

Use a plain Repo.transaction/1 with an anonymous function when:

• You have simple, linear operations
• You prefer direct pattern matching on results

Testing Your Multi Workflows

Verify behavior with Ecto’s SQL sandbox:

test "registration rolls back on profile failure" do
  user_params = valid_user_params()
  profile_attrs = invalid_profile_attrs()  # Triggers validation error

  assert {:error, :profile, %Ecto.Changeset{}, %{user: user}} =
         register_user(user_params, profile_attrs)

  # Verify user was rolled back
  assert nil == Repo.get(User, user.id)
end

Key Takeaways

• Ecto.Multi provides atomic transaction semantics with named operation tracking
• Only database operations are rolled back; external effects persist
• Use Multi.merge/3 to compose complex workflows from smaller units
• Always check the exact return tuple shape for your Ecto version