by Josh Branchaud on December 1, 2016

At Hashrocket, most of what we build are applications backed by relational databases. Our relational database of choice is PostgreSQL. We tend to offload as much work as possible on Postgres and we take joy in the opportunity to use all the features Postgres has to offer. Of course, using Postgres to this extent has implications for how we develop applications, in particular for the way we write database migrations. Though frameworks tend to come with fairly full-featured ORMs, these ORMs limit what we can do with Postgres. A cool way to get around these limitations is by writing migrations in SQL[^1].

Writing migrations in SQL means we no longer get the down migration for free, as we would when using an ORMs DSL. This means we have to write custom down migrations along with our up migrations. For the basic kinds of migrations this is a straightforward task. When the migrations become more complex, perhaps even irreversible, we really have to think through and work out the exact details. Once both the up and down migrations are written, we like to migrate up, back down, and then up again. This helps us ensure with some level of confidence[^2] that our migration is fully functional.

For years we've had an alias for Rails projects to migrate up, down, up -- it's called twiki. I decided it's about time that our Elixir projects using Ecto get a twiki of their own.

I wrote the following mix task to make it just as easy to migrate up, down, up in Elixir projects.

defmodule Mix.Tasks.Ecto.Twiki do
  use Mix.Task

  @shortdoc "Ecto Migration: Up, Down, Up"

  @moduledoc """
    This will migrate the latest Ecto migration, roll it back, and then
    migrate it again. This is an easy way to ensure that migrations can go
    up and down.
  """

  def run(args) do
    Application.ensure_all_started(Mix.Project.config[:app])

    repos = Mix.Ecto.parse_repo(args)

    twiki(repos)
  end

  defp twiki(repo) when is_atom(repo) do
    migration_dir =
      repo
      |> Mix.Ecto.source_repo_priv
      |> Path.absname
      |> Path.join("migrations")

    count = down_count(repo, migration_dir)

    Enum.each([:up, :down, :up], fn(direction) ->
      migrate(direction, repo, migration_dir, [step: count])
    end)
  end
  defp twiki([repo]) do
    twiki(repo)
  end
  defp twiki([_repo | _more_repos] = repos) do
    Mix.shell.info """
      Ecto.Twiki only supports migrating a single repo.
      However, we found multiple repos: #{inspect repos}
    """
  end

  defp migrate(direction, _repo, _migration_dir, [step: 0]) do
    Mix.shell.info "Already #{direction}"
    []
  end
  defp migrate(direction, repo, migration_dir, opts) do
    Mix.shell.info "Migrating #{direction}"
    Ecto.Migrator.run(repo, migration_dir, direction, opts)
  end

  defp down_count(repo, migration_dir) do
    direction_count(:down, repo, migration_dir)
  end

  defp direction_count(direction, repo, migration_dir) do
    Ecto.Migrator.migrations(repo, migration_dir)
    |> Enum.filter(fn({status, _, _}) -> status == direction end)
    |> Enum.count
  end
end

It functions in the following ways: If there are no pending migrations, it reports as much and does nothing. If there are one or more pending migrations, it migrates that many migrations, rolls them back, and then migrates them again. In this way, it works well if you have a couple new migrations ready to go. If there are issues with any of the migrations, you will see the error messages reported just as if you'd run the migration by hand. Lastly, because it is integrated with Ecto using the Migrator module (as opposed to simply shelling out), it is extensible.

To twiki your latest Ecto migration, add this task to lib/mix/tasks/ecto.twiki.ex and run mix ecto.twiki.

[^1]: One tradeoff to using SQL instead of an ORM's DSL is that your application's migrations are not necessarily portable across relational databases. We are fine with this tradeoff because we find that in practice we rarely, if ever, switch off of Postgres.

[^2]: Depending on how closely the data in our development database mimics the data in our staging and production databases, we may experience a false positive of sorts. Everything migrates fine in our development environment, but there are conflicts, perhaps in the form of constraint violations, when we try to migrate elsewhere. To avoid this, we like to use scrubbed copies of production data in our development environment when possible.