Ecto.Multi: Atomic Multi‑Step Transactions in Elixir

Why Ecto.Multi Is the Right Tool for Atomic Workflows

When you need to perform several database operations that must either all succeed or all fail, the classic solution is a transaction. In Elixir, Ecto.Multi provides a declarative API that guarantees atomicity while letting you inspect intermediate results. The key advantage is that you can chain named steps, each returning a result tuple, and the entire chain is executed inside one Repo.transaction/1.

How Ecto.Multi Works

• Named operations – Each step is given a unique key (e.g., :user, :profile) that you can reference later.
• Result tuples – Every step returns {:ok, value} or {:error, reason}. If any step yields :error, the transaction aborts automatically.
• Result map – After a successful run, Ecto.Multi.run/2 returns a map containing the values of all successful steps, which you can use in downstream logic.
• – Allows custom logic that can read previous results and decide whether to continue or abort.

Concrete Example: Creating a User With a Profile and Settings

Below is a minimal but complete example that demonstrates the core features. It assumes you have three schemas: User, Profile, and Settings, and a Repo configured for PostgreSQL or MySQL.

defmodule MyApp.Accounts do
  import Ecto.Query, warn: false
  alias MyApp.Repo
  alias MyApp.Accounts.{User, Profile, Settings}

  def create_user_with_profile(attrs) do
    # 1️⃣ Start a new Multi and add steps
    multi =
      Ecto.Multi.new()
      # Insert a user
      |> Ecto.Multi.insert(:user, User.changeset(%User{}, attrs))
      # Insert a profile that references the newly created user
      |> Ecto.Multi.insert(:profile, fn %{user: user} ->
           Profile.changeset(%Profile{}, %{user_id: user.id, bio: "Hello!"})
         end)
      # Update user settings based on the created user
      |> Ecto.Multi.update(:settings, fn %{user: user} ->
           Settings.changeset(user.settings, %{notifications: true})
         end)
      # Optional custom logic that can abort the transaction
      |> Ecto.Multi.run(:validate_email, fn %{user: user} ->
           if String.contains?(user.email, "@example.com") do
             {:ok, :allowed}
           else
             {:error, :email_not_allowed}
           end
         end)

    # 2️⃣ Execute the transaction
    case Repo.transaction(multi) do
      {:ok, result_map} ->
        {:ok, result_map}
      {:error, failed_step, failed_value, _changes_so_far} ->
        {:error, failed_step, failed_value}
    end
  end
end

Explanation of the chain:

• :user – Inserts the new user record.
• :profile – Uses the user’s id from the previous step to create a profile.
• :settings – Updates the user’s settings atomically.
• :validate_email – A :run step that checks a business rule. Returning {:error, …} aborts the entire transaction.

Running the Example

1. Ensure the database is migrated and the Repo is configured in config.exs.
2. Call MyApp.Accounts.create_user_with_profile(%{email: "john@example.com", name: "John"}).
3. On success, you’ll receive a map:

   {:ok, %{user: %User{}, profile: %Profile{}, settings: %Settings{}, validate_email: :allowed}}

4. Check the database tables to confirm all rows exist.

Common Mistakes and How to Avoid Them

• Assuming :run Steps Execute Immediately – They run only within the transaction, after all previous steps have succeeded. Side‑effects outside the transaction (e.g., logging) may appear out of order if you’re not careful.
• Performing I/O Inside :run – HTTP requests, file I/O, or other blocking operations can delay the commit and increase the chance of a rollback. Keep :run logic lightweight.
• Mixing Ecto.Multi With Repo.transaction/1 Manually – Nesting a Repo.transaction inside a Multi step can lead to hidden transaction boundaries. Stick to one transaction per Multi.
• Ignoring the Result Map – The map returned on success contains all successful step results. If you need data from a step, reference it via the key you gave.
• Using Adapters Without Transaction Support – Adapters like SQLite may raise an error when a Multi is executed. Verify that your adapter supports transactions before deploying to production.

Limitations to Keep in Mind

• Transaction Size – Large Multi chains can keep database locks open for longer, potentially blocking other operations. Keep each operation lightweight and consider splitting very large workflows.
• Adapter Differences – PostgreSQL and MySQL support nested transactions via savepoints, but not all adapters do. Test the Multi on the target adapter before rolling out.
• Rollback Transparency – If a step raises an exception instead of returning {:error, …}, the transaction still rolls back, but the exception propagates. Handle exceptions explicitly in callbacks to provide clearer error messages.

Practical Verification Steps

1. Fresh Database Test – Create a new database, run the example, and confirm all rows are present.
2. Intentional Failure – Change the user email to a duplicate value or make :validate_email return {:error, :email_not_allowed}. Verify that none of the inserts or updates persist.
3. Cross‑Adapter Check – Run the same code against PostgreSQL and MySQL. The result map should be identical.
4. Timeout Observation – Add a Process.sleep(5000) inside a :run step and measure transaction duration. Use this to decide whether to refactor the step into a background job.

Conclusion

Ecto.Multi gives you a clean, composable way to build atomic database workflows. By naming each step, handling result tuples, and avoiding heavy I/O in callbacks, you can write robust, maintainable code that guarantees consistency across multiple tables.