Ecto.Multi: Turn Nested Case Statements into a Flat, Inspectable Transaction Pipeline

Problem: Nested Case Statements for Multi‑Step Writes

When building a feature that writes to several tables—say, creating a user, a profile, and an audit record—developers often write something like:

case Repo.insert(user_changeset) do
  {:ok, user} ->
    case Repo.insert(profile_changeset(user)) do
      {:ok, profile} ->
        case Repo.insert(audit_changeset(user, profile)) do
          {:ok, audit} -> {:ok, %{user: user, profile: profile, audit: audit}}
          {:error, reason} -> {:error, reason}
        end
      {:error, reason} -> {:error, reason}
    end
  {:error, reason} -> {:error, reason}
end

Deep nesting makes the code hard to read, hard to test, and hard to see which step failed. The goal is to replace this with a flat pipeline that is easier to inspect and maintain.

Thesis: Use Ecto.Multi for Declarative, Atomic Pipelines

Ecto.Multi lets you describe a sequence of database operations by name. The whole sequence runs in a single transaction; if any step fails, the transaction rolls back automatically. Each step receives the accumulated changes from previous steps, so later operations can reference earlier results without pattern matching on nested tuples.

Section 1 – Building a Multi Step‑by‑Step

Below is a minimal example that creates a user, a profile, and logs an audit entry. Replace the placeholder modules and schemas with your own.

defmodule MyApp.Accounts do
  import Ecto.Query
  alias MyApp.{Repo, User, Profile, Audit}

  def register_user(attrs) do
    user_changeset = User.changeset(%User{}, attrs)

    multi =
      Ecto.Multi.new()
      |> Ecto.Multi.insert(:user, user_changeset)
      |> Ecto.Multi.insert(:profile, fn %{user: user} ->
           Profile.changeset(%Profile{}, %{user_id: user.id, bio: ""})
         end)
      |> Ecto.Multi.insert(:audit, fn %{user: user, profile: profile} ->
           Audit.changeset(%Audit{}, %{user_id: user.id, profile_id: profile.id, action: "create"})
         end)

    case Repo.transaction(multi) do
      {:ok, changes} -> {:ok, changes}
      {:error, failed_step, changes, _changes_so_far} ->
        {:error, %{step: failed_step, changes: changes}}
    end
  end
end

Key points:

• Ecto.Multi.insert/3 can accept a function that receives the accumulated changes; this is how later steps reference earlier results.
• The result of Repo.transaction/1 is {:ok, changes} on success or {:error, failed_step, changes, changes_so_far} on failure, exposing exactly which step broke.
• All operations run in a single database transaction, guaranteeing atomicity.

Section 2 – Adding Arbitrary Logic with Ecto.Multi.run/3

Sometimes you need to perform a calculation or call a helper that isn’t a database operation. Use Ecto.Multi.run/3 to inject a function that must return a tagged tuple.

|> multi = multi
|>   |> Ecto.Multi.run(:send_welcome, fn %{user: user} ->
|>         case MyApp.Mailer.send_welcome(user.email) do
|>           :ok -> {:ok, :sent}
|>           {:error, reason} -> {:error, reason}
|>         end
|>       end)

Remember: if the function returns {:error, _}, the whole transaction aborts and rolls back.

Section 3 – Common Pitfalls and Trade‑offs

• Long‑running external calls are a no‑go. A Multi holds a database connection for the entire transaction. HTTP requests, file uploads, or email delivery should be performed outside the Multi or delegated to a background job.
• Partial success is impossible. If you need to keep some changes when another fails, you must split the logic into separate transactions or use compensating actions.
• Return shape matters. Every step must return {:ok, value} or {:error, reason}. Raising an exception or returning a bare value will crash the process and leave the transaction in an undefined state.
• Single‑repository limitation. Multi guarantees atomicity only within the repository you pass to Repo.transaction/1. It cannot coordinate across multiple databases or external services.

Section 4 – Verifying Behavior in Your Environment

1. Check your Ecto version: Application.spec(:ecto, :vsn) or mix help ecto.
2. Open the docs in IEx: h Ecto.Multi to confirm the exact return tuple structure for your version.
3. Write an ExUnit test using the SQL sandbox:

   test "multi rolls back on failure" do
       multi = Ecto.Multi.new()
             |> Ecto.Multi.insert(:user, User.changeset(%User{}, %{email: "[contact removed]"}))
             |> Ecto.Multi.insert(:invalid, fn _ -> %{invalid: true} end) # forces error
   
       assert {:error, :invalid, _, _} = Repo.transaction(multi)
       refute Repo.exists?(from u in User, where: u.email == "[contact removed]")
     end

4. Run the test. If it passes, you have confidence that the rollback works in your setup.

Actionable Closing

Switching to Ecto.Multi turns spaghetti nested case logic into a readable, testable pipeline. Start by refactoring one multi‑step feature—create a user, profile, and audit record. Verify the transaction rolls back on failure with a sandbox test. Once comfortable, replace other nested patterns. Remember to keep external calls outside the Multi and to handle any {:error, reason} cleanly. Your code will be easier to maintain, and your database will stay consistent.