How database transactions work in Ecto and why Elixir makes it awesome?

Today we're going to look at how Ecto, Elixir's first-choice database access library, handles relational (SQL) database transactions.

All followed with examples in our GitHub repository.

This is a thorough guide for SQL experts and newbies alike, so let's summarize what we'll talk about in this article:

• We'll explain what the purpose and characteristics of transactions are
• Then, we'll outline how to approach them in Elixir, using "the Ecto way"
• Finally, we'll get to explain how it feels superior to different ORMs

However obscure it might sound, the combination of Elixir's functional character and other traits make it so easy to translate business requirements to algorithms powering excellent apps - and, with this guide, we're going to show you part of the reason why.

What is a database transaction?

If you're familiar with SQL databases, feel free to skip this section. Otherwise, let's delve a bit deeper into the mechanisms of database services.

Consider a bank transfer, consisting of subtracting money from account A and then adding to B. It is mandatory that both operations are performed inseparably, because no money can be lost.

Since our systems are capable of handling multiple operations concurrently, we don't want any agent to read either account's balance before the transfer is completed. We require that it not be possible to transfer $100 from A when its balance is only $50 (surprise, eh?).

On top of that, we obviously want all of this to hold true even when our system crashes.

What does it mean in technical terms? It means that our database is ACID. This slightly scary term is just an acronym for: Atomicity, Consistency, Isolation & Durability.

• Atomicity. A transaction being atomic means that each of its steps is inseparable from the others. When any step fails or the application decides so, the database will be rolled back to the state before transaction started.
• Consistency. The database must be consistent at all times, i.e. all integrity constraints must always be met. This is related to atomicity, because any transaction abort must transition the database to a previously consistent
• Isolation - core to how databases handle concurrency, transaction A's intermediate steps should not be visible to other transactions before transaction A is committed. (Though actually, that's a simplification. Read up if you're interested in the full story).
• Durability - the database must be crash-resilient, which means all data committed before a crash must still be seen as committed after the system is up again.

Ruby v Elixir - Executing a transaction

In object-oriented languages object-relational mapping libraries dealt with managing database connection, constructing queries and mapping the results to _objects_. So in Java there is Hibernate, .NET has its Entity Framework, and Ruby has ActiveRecord.

Elixir's different because it's free from OOP's heavy, bloated objects that always carry a lot of internal state information. It relies on simple data structures such as maps to carry all needed information and metadata. So while Ecto is kind of the equivalent to ActiveRecord et al., it's not exactly an object-relational mapper because there are no "objects" in Elixir.

It hardly matters, though. What matters for us is that these libraries talk to the database to execute operations and transactions, which are committed or rolled back dependent on the app's business logic.

So in Ruby's beloved (well, really?) ActiveRecord you had this:

transfer = 50

ActiveRecord::Base.transaction do
  acc_a, acc_b = Account.find([1, 2])
  raise ActiveRecord::Rollback, :balance_too_low if acc_a.balance < transfer
  acc_a.balance -= transfer
  acc_b.balance += transfer
  acc_a.save!
  acc_b.save!
  [acc_a, acc_b]
end

=> [<Account:...>, <Account:...>]

The Ecto equivalent would be this. Not much of a win yet, but if you want a literal translation, here it is. Test it out by running iex -S mix run priv/script1.exs from our repository.

import Ecto.Query
alias TransactApp.Bank.Account
alias TransactApp.Repo

transfer = 50
result = Repo.transaction(fn ->
  [acc_a, acc_b] = from(acc in Account, where: acc.id in [1, 2]) |> Repo.all()
  if Decimal.compare(acc_a.balance, transfer) == :lt, do: Repo.rollback(:balance_too_low)
  update1 = acc_a |> Account.changeset(%{balance: acc_a.balance - 50}) |> Repo.update!()
  update2 = acc_b |> Account.changeset(%{balance: acc_b.balance + 50}) |> Repo.update!()
  [update1, update2]
end)

=> {:ok, [%Account{...}, %Account{...}]}

There is an interesting difference, though, in the result of a rollback operation that might occur here:

• ActiveRecord will return nil even though we passed :balance_to_low as the exception's argument. This would often lead to trouble debugging because the real reason the transaction was rolled back wasn't easy to track.
• Ecto will return {:error, :balance_too_low}. This allows for easy pattern matching and tracking down what caused the rollback.

We said it's not much of a win yet, but read further to learn about Ecto's idiomatic, functional ways of managing and composing transactional pipelines.

Organizing transactional code with Ecto.Multi

Procedural code, as seen in examples above, is heavy on assigning data to variables and usually there is an assumption that the whole procedure succeeds, and when it doesn't, most languages rely on an exception handling mechanism to handle errors. Elixir can do that too, but it leads to writing solutions with coarse-grained error handling or even to ignoring errors, which is even worse.

I can't count how many times I've seen folks (myself included...) trying to update a record inside a transaction, which then failed because of validation errors, but the transaction kept executing, and there was an assumption that the update did succeed.

Elixir's pipe operator and its pattern matching capabilities mean that it's super easy to compose transactional code as pipelines of functions. This leads to managing transactional code with better control and confidence.

Here's an example code you could write as an Elixir script - it's also available in our repository:

import Ecto.Query
alias Ecto.Multi
alias TransactApp.Bank.Account

transfer_amount = 50

retrieve_accounts = fn repo, _ ->
  case from(acc in Account, where: acc.id in [1, 2]) |> repo.all() do
    [acc_a, acc_b] -> {:ok, {acc_a, acc_b}}
    _ -> {:error, :account_not_found}
  end
end

verify_balances = fn _repo, %{retrieve_accounts_step: {acc_a, acc_b}} ->
  # we don't do anything to account B, but we could
  if Decimal.compare(acc_a.balance, transfer_amount) == :lt,
    do: {:error, :balance_too_low},
    else: {:ok, {acc_a, acc_b, transfer_amount}}
  end

subtract_from_a = fn repo, %{verify_balances_step: {acc_a, _, verified_amount}} ->
  # repo.update will return {:ok, %Account{...}} or {:error, #Ecto.Changeset<...>}
  # {:ok, value} or {:error, value} is what these functions are expected to return.
  acc_a
  |> Account.changeset(%{balance: acc_a.balance - verified_amount})
  |> repo.update()
end

add_to_b = fn repo, %{verify_balances_step: {_, acc_b, verified_amount}} ->
  acc_b
  |> Account.changeset(%{balance: acc_b.balance + verified_amount})
  |> repo.update()
end

batch =
  Multi.new()
  |> Multi.run(:retrieve_accounts_step, retrieve_accounts)
  |> Multi.run(:verify_balances_step, verify_balances)
  |> Multi.run(:subtract_from_a_step, subtract_from_a)
  |> Multi.run(:add_to_b_step, add_to_b)

batch actually encapsulates a sequence of functions that represent single steps of our pipeline. This means we actually haven't executed anything in the DB yet, but we have a recipe that we can build, reuse and run whenever we want to.

Every function adheres to the same contract:

• Return {:ok, value},
• Expect as arguments: the current Repo, and a map.

Then, a particular function will also pattern match the map to verify that a certain precondition has been met. For instance, if we mistakenly try to use the subtract_from_a function without having a chance to insert the returning value of verify_balances_step into our context, pattern matching will fail.

Finding a way to have these batches well-organized is just up to you. One approach you might use is a service module pattern. In Phoenix, the recommended way to encapsulate data access and validations is contexts (our Curiosum approach to them is extending them with subcontexts and services - you can watch our Elixir Meetup #1 recording to learn more.

The service module layer encapsulates an action that spans across multiple contexts, and using Ecto Multi for this purpose is very natural.

First, let's define a module defining Ecto.Multi batches related to the Bank context (also available in GitHub):

defmodule TransactApp.Bank.Batches do
  # Ecto.Multi batch definitions for the Bank context

  alias Ecto.Multi
  alias TransactApp.Bank.Account
  alias TransactApp.Bank.AccountQueries

  # It's handy to have the first argument default to Multi.new()
  # - this way such a batch definition will be fully composable
  # with others.
  def transfer_money(multi \\ Multi.new(), account_id_1, account_id_2, amount) do
    multi
    |> Multi.all(
      :retrieve_accounts_step,
      AccountQueries.account_by_ids([account_id_1, account_id_2])
    )
    |> run_verify_balances_step(amount)
    |> Multi.update(:subtract_from_acc_1_step, &subtract_from_acc_1_step/1)
    |> Multi.update(:add_to_acc_2_step, &add_to_acc_2_step/1)
  end

  defp run_verify_balances_step(multi, amount) do
    Multi.run(
      multi,
      :verify_balances_step,
      fn _repo, %{retrieve_accounts_step: [acc_1, _]} ->
        if Decimal.compare(acc_1.balance, amount) == :lt,
          do: {:error, :balance_too_low},
          else: {:ok, amount}
      end
    )
  end

  defp subtract_from_acc_1_step(changes) do
    %{
      retrieve_accounts_step: [acc_1, _],
      verify_balances_step: verified_amount
    } = changes

    Account.changeset(acc_1, %{balance: Decimal.sub(acc_1.balance, verified_amount)})
  end

  defp add_to_acc_2_step(%{
         retrieve_accounts_step: [_, acc_2],
         verify_balances_step: verified_amount
       }) do
    Account.changeset(acc_2, %{balance: Decimal.add(acc_2.balance, verified_amount)})
  end
end

Notice how we now not only use run/3, but we now also use all/4 that takes an Ecto Query (handily constructed using the concept of Query Modules, and update/4 that takes a changeset or a function that builds such a changeset - similarly, functions corresponding to one or insert also exist - look them up in the Ecto docs. An example of using these shorthand functions is also available in GitHub.

Then, let's define a module for constructing batches related to the Orders context - nothing special happens there, but just for completeness here it is (as well as in GitHub):

defmodule TransactApp.Orders.Batches do
  # Ecto.Multi batch definitions for the Orders context

  alias Ecto.Multi
  alias TransactApp.Orders

  def finalize_order(multi \\ Multi.new(), order) do
    # Notice that, in this function, each step does not depend
    # on any results of previous ones, so we don't need to pass functions
    # as the last arguments of update/3.
    multi
    |> Multi.update(
      :finalize_order,
      Orders.set_order_status_changeset(order, :finalized)
    )
    |> Multi.update(
      :set_order_line_items_locked,
      Orders.lock_order_line_items_changeset(order)
    )
  end
end

Finally, wrapping it all up, here's a service module that represents actually checking out an order - Repo.transaction() finally executes the recipe we composed using the OrderBatches and BankBatches modules. Find it in GitHub, too.

defmodule TransactApp.CheckoutService do
  # Cross-context service, finalizing order and transferring money between
  # parties
  alias Ecto.Multi
  alias TransactApp.Bank.Batches, as: BankBatches
  alias TransactApp.Orders.Batches, as: OrderBatches
  alias TransactApp.Repo

  def run(order) do
    OrderBatches.finalize_order(order)
    |> BankBatches.transfer_money(
      order.ordering_account_id,
      order.selling_account_id,
      order.total
    )
    |> Repo.transaction()
  end
end

Each Multi pipeline step will return {:ok, ...} or {:error, ...} - and Ecto will commit or rollback the transaction depending on that. No need to explicitly call Repo.rollback/1 or raise any errors.

TransactApp.Bank.Batches.transfer_money(1, 2, 50)
|> TransactApp.Repo.transaction()

# returns tuple with map denoting results of each step:
{:ok, %{retrieve_accounts_step: ..., verify_balances_step: ..., subtract_from_a_step: ..., add_to_b_step: ...}}

# or if an error occurs, it'll return the exact point of failure _and_ all previous step results, which is very handy:
{:error, :verify_balances_step, :balance_too_low, %{retrieve_accounts_step: ...}}

A script that pattern matches on this call and all the other modules described above are also available in our GitHub repository, so you can play around with it.

Conclusion

Several Elixir libraries, including Ecto, benefit from Elixir's nature of a functional language, and allow for easy definition of data processing pipelines. Its transaction handling facilities are just cool and simply get out of your way.

Since handling database transactions is an aspect that no serious application can evade, it's awesome that Elixir allows developers to manage them elegantly, which leads to software that does its business well.

FAQ

What is a database transaction in Ecto and Elixir?

A database transaction in Ecto and Elixir refers to a set of operations that are executed as a single unit, ensuring atomicity, consistency, isolation, and durability (ACID properties).

How does Ecto handle database transactions in Elixir applications?

Ecto handles database transactions through the Ecto.Repo.transaction/1 function, enabling atomic operations where all changes are either committed together or rolled back.

What are the benefits of using Ecto.Multi in Elixir?

Ecto.Multi allows the composition of multiple database operations into a single transaction, facilitating error handling and ensuring that all operations either succeed together or fail without making partial changes.

How do transactions in Ecto improve data consistency?

Transactions in Ecto ensure data consistency by adhering to ACID properties, which means that all operations within a transaction are treated as a single unit.

How can one use Ecto.Multi to organize transactional code in Elixir?

Ecto.Multi is used to organize transactional code by chaining multiple database operations, enabling clearer and more manageable transactional logic.

What are the common patterns for handling errors in Ecto transactions?

Common patterns include using pattern matching with the results of Ecto.Repo.transaction/1, and handling different outcomes explicitly to ensure robust error handling.

How does the functional nature of Elixir influence Ecto transaction handling?

The functional nature of Elixir, with features like the pipe operator and pattern matching, facilitates composing and managing transactional code effectively, contributing to cleaner and more maintainable code.