Heading image for post: Up, Down, Up with Ecto Migrations


Up, Down, Up with Ecto Migrations

Profile picture of Josh Branchaud

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].

Up, Down, Up

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.

A Twiki Mix Task

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

    repos = Mix.Ecto.parse_repo(args)


  defp twiki(repo) when is_atom(repo) do
    migration_dir =
      |> 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])
  defp twiki([repo]) do
  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}

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

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

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

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.

More posts about Elixir ecto

  • Adobe logo
  • Barnes and noble logo
  • Aetna logo
  • Vanderbilt university logo
  • Ericsson logo

We're proud to have launched hundreds of products for clients such as LensRentals.com, Engine Yard, Verisign, ParkWhiz, and Regions Bank, to name a few.

Let's talk about your project