Database Transactions or: How I Learned to Stop Worrying and Love Ecto.Multi

Intention #

In the sample problem that follows, we've set up a simple Elixir toy app to demonstrate how we can set up a GraphQL endpoint for registering users, and storing user and other relevant information to a database. Our goal is to persist only data that is valid, or return a helpful error otherwise. We’ll leverage Ecto.Multi and Ecto.Repo.transaction to write a clean user registration flow and handle errors elegantly.

Problem #

NomNomsFarm is a new food cart serving the neighborhood with local, organic, fair trade, free-range, farm-fresh, artisinal delicacies. We're in charge of running logistics for NomNomsFarm and we need to build an online registration flow for local farms to become part of the food cart's supply chain.

The problems we will be solving are:

1. Incomplete registration states. #

In the event of a problem with the registration, we want to make sure our operation is atomic. That is to say, we want to make sure that none of the data we were trying to persist is left behind in the database. This is especially important for fields that are required to be unique, e.g., a user's email address.

2. Code readability when there are many unhappy paths. #

In this example, there is only one happy path: the successful registration of a user and their farm. However there are many unhappy paths, including:

• The email address already being taken.
• An invalid USDA UID.
• The USDA UID being registered to another user already.
• Problems connecting to the database/timeouts.

If any of these failures occurs after we've already started optimistically saving records in the database, we will have to keep track of all the already-created records and make sure to delete them. This approach would result in code that's very redundant and unreadable.

3. Testing error messages that are coupled to specific copy. #

We want to reduce user friction and frustration:

One way to do this is by returning error messages that are descriptive and helpful. However, we don't want to have to change the tests every time copy-editors make tweaks.

System Setup #

To implement a solution, we'll need a database for saving the data. Let's start by setting up some lovely database tables:

** = This table is prepopulated with (fake) USDA data

The designers and front-end engineers have just finished the registration form that collects all the relevant data. It looks like this but better:

Now we just need to build out the code for the API endpoint to tie it all together. Let's get started!

Solution #

To solve this problem, we will use Ecto.Repo.transaction (if you're not familiar, refer to the docs here). Transactions allow you to open a connection to the database and run a sequence of commands, with an option to rollback at anytime while the connection is open.

For each of the database models, we will set up a create function responsible for inserting a row to the table. For example, the create functions for user and farmer are shown below:

def create(username, password, name, email) do
  %User{}
  |> changeset(%{username: username, password: password, name: name, email: email})
  |> Repo.insert()
end

^{from: lib/nomnomsfarm/user.ex}

def create(user_id, farm_id, is_admin) do
  %Farmer{user_id: user_id, farm_id: farm_id, is_admin: is_admin}
  |> Repo.insert()
end

^{from: lib/nomnomsfarm/farmer.ex}

Once we have our model inserts set up, we can tie them together with a create_records function as follows:

def create_records(%{
  username: username,
  password: password,
  name: name,
  email: email,
  usda_farm_id: usda_farm_id,
  farm_name: farm_name
}) do
  Multi.new()
  |> Multi.run(:create_user, fn(_) ->
       User.create(username, password, name, email)
     end)
  |> Multi.run(:create_farm, fn(_) ->
       Farm.create(farm_name, usda_farm_id)
     end)
  |> Multi.run(:create_farmer, fn(multi) ->
       user_id = multi.create_user.id
       farm_id = multi.create_farm.id
       Farmer.create(user_id, farm_id, true)
     end)
  |> Repo.transaction()
  |> case do
       {:ok, result} ->
         {:ok, result.create_user.id}
       {:error, _failed_operation, failed_value, _changes}->
         error = inspect(failed_value.errors)
         {:error, error}
     end
end

^{from: lib/nomnomsfarm.ex}

The advantages of this approach are:

1. In case of failure, the transaction is rolled back automatically and returns the failed operation and failed value. Stale/inaccurate records are not left behind in the database.
2. Each database action is labeled with a custom title, like :create_farm, to store the result of the write or the associated errors. If the action succeeds, the value of each custom titled key will be the newly created record.
3. The failures are collected in the errors field of the failed_value so that we can easily identify which line caused the failure.
4. The availability of helper functions like .merge, .append, and .prepend (not used in this example but very helpful when composing several Ecto.Multis to run in one transaction).

Next, we can leverage Elixir's with.. do block to perform the other checks we need to run before writing to the database. These checks are validations that can be performed in individual database queries and shouldn't be included in the transaction which blocks the db connection. In our example, we will use this approach to validate that the USDA UID provided corresponds to a valid USDA farm in the database, and that that farm is not already claimed. Even though these unhappy paths are different failure modes from a failure in the Ecto.Repo.transaction code shown above, using this approach, we can make all unhappy paths converge by returning the unique {:error, _error_message} that corresponds to the failing line of the with.. do block.

def register_farm_admin(%{usda_uid: usda_uid} = args) do
  with {:ok, %{id: usda_farm_id, name: farm_name}} <- get_farm_by_usda_uid(usda_uid),
       :ok <- farm_is_available(usda_farm_id),
       merged_args = Map.merge(args, %{usda_farm_id: usda_farm_id, farm_name: farm_name}),
       {:ok, user_id} <- create_records(merged_args)
  do
    {:ok, user_id}
  else
    {:error, _} = e -> e
  end
end

^{from: lib/nomnomsfarm.ex}

Lastly, we can expose a new register_farm_admin endpoint, and hook up our register_farm_admin function in the resolver:

mutation do
  @desc "Register a new farm admin."
  field :register_farm_admin, :integer do
    arg :username, non_null(:string)
    arg :password, non_null(:string)
    arg :name, non_null(:string)
    arg :email, non_null(:string)
    arg :usda_uid, non_null(:string)
    resolve &NomNomsFarm.Resolver.register_farm_admin/3
  end
end

^{from: lib/nomnomsfarm/web/schema.ex}

def register_farm_admin(_, args, _) do
  NomNomsFarm.register_farm_admin(args)
end

^{from: lib/nomnomsfarm/resolvers/resolver.ex}

Calling the main registration mutation will result in either a successful user registration,

OR

a descriptive error message that can be displayed to the user.

Error Handling #

We can decouple the error message copy from the actual code by wrapping the errors in an error_message function that translates atoms to complete sentences as follows.

def error_message(:invalid_usda_uid), do: "Invalid USDA uid entered."
def error_message(:farm_already_claimed), do: "The USDA farm you selected has already been claimed."
def error_message(:email_already_claimed), do: "This email is already claimed."

^{from: lib/nomnomsfarm.ex}

One of the main advantages is that, this way, the user-facing copy can be tweaked without requiring devs to also edit tests. For example, an error case can be tested like this:

test "returns a helpful error message when request fails" do
  Repo.insert!(%User{email: "bart@bartlebysons.com"})

  mutation = """
    mutation {
      registerFarmAdmin(
        username: "bart",
        password: "password",
        name: "Bartleby",
        email: "bart@bartlebysons.com",
        usdaUid: "00920",
      )
    }
    """
  assert {:ok, %{errors: [error| _]}} = Absinthe.run(mutation, NomNomsFarm.Web.Schema)

  expected_message = NomNomsFarm.error_message(:email_already_claimed)
  assert String.contains?(error.message, expected_message)
end

^{from: test/graphql/register_farm_admin_test.exs}

Conclusion #

We hope this was a helpful demonstration of design patterns using Ecto.Multi and Ecto.Repo.transaction. If you're interested in more details, a full working solution can be found in the NomNomsFarm repo. There you can run the tests, serve the API to hit the mutation for yourself, and fork it to continue experimenting! Please reach out with questions, comments or suggestions for how we can improve.

Thanks for reading!