Ecto Migrations in Production

July 5, 2024 - 6 mins read

Migrations with Elixir Releases

Since I’m using releases for the production environment, I won’t have the Mix tool to run migrations. I didn’t have Ecto included with my original Phoenix project so the files generated for releases did not include anything for running Ecto migrations from a release. I generate these missing migration files by running mix phx.gen.release again which adds the Minotaur.Release module and a migrate script.

# lib/minotaur/release.ex

defmodule Minotaur.Release do
  @moduledoc """
  Used for executing DB release tasks when run in production without Mix
  installed.
  """
  @app :minotaur

  def migrate do
    load_app()

    for repo <- repos() do
      {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, :up, all: true))
    end
  end

  def rollback(repo, version) do
    load_app()
    {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, :down, to: version))
  end

  defp repos do
    Application.fetch_env!(@app, :ecto_repos)
  end

  defp load_app do
    Application.load(@app)
  end
end

# rel/overlays/bin/migrate
#!/bin/sh
set -eu

cd -P -- "$(dirname -- "$0")"
exec ./minotaur eval Minotaur.Release.migrate

I commit these files to the project repository and pull the changes from my production server. I create a new Docker image and test the migration script by running a container with an entrypoint override.

$ docker run --entrypoint /app/bin/migrate -e DATABASE_URL=$MINOTAUR_DATABASE_URL -e SECRET_KEY_BASE=$SECRET_KEY_BASE --add-host=host.docker.internal:host-gateway minotaur:ecto
[info] Migrations already up

The migrate script ran successfully. There are no migrations to run yet, so the resulting log message is expected. Now that I know I can run migrations in production, I will create some migration files to test the database user has all the necessary permissions.

Writing migrations

From my local development environment, I generate a new migration file in my project by running mix ecto.gen.migration add_users_table which creates priv/repo/migrations/20240705205730_add_users_table.exs with the following content:

defmodule Minotaur.Repo.Migrations.AddUsersTable do
  use Ecto.Migration

  def change do

  end
end

I update the change function to create a new table with a few fields and a unique index constraint. This migration is just for testing permissions so I don’t care about the table or field names at this point.

def change do
  create table(:users) do
    add :first_name, :string
    add :last_name, :string
    add :email, :string

    timestamps()
  end

  create unique_index(:users, [:email])
end

I run the migration locally with mix ecto.migrate which successfully creates the table and index in the local development database.

I also create a User module to test the insert functionality.

defmodule User do
  use Ecto.Schema
  import Ecto.Changeset

  schema "users" do
    field :first_name, :string
    field :last_name, :string
    field :email, :string

    timestamps()
  end

  def changeset(user, attrs) do
    user
    |> cast(attrs, [:first_name, :last_name, :email])
    |> validate_required([:first_name, :last_name, :email])
    |> unique_constraint(:email)
  end
end

I start a REPL with iex -S mix phx.server, create a test user record, and delete it.

iex(1)> attrs = %{first_name: "John", last_name: "Doe", email: "[email protected]"}
%{first_name: "John", last_name: "Doe", email: "[email protected]"}

iex(2)> user = User.changeset(%User{}, attrs)
#Ecto.Changeset<
  action: nil,
  changes: %{first_name: "John", last_name: "Doe", email: "[email protected]"},
  errors: [],
  data: #User<>,
  valid?: true
>

iex(3)> Minotaur.Repo.insert(user)
{:ok,
 %User{
   __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
   id: 1,
   first_name: "John",
   last_name: "Doe",
   email: "[email protected]",
   inserted_at: ~N[2024-07-05 21:23:53],
   updated_at: ~N[2024-07-05 21:23:53]
 }}

iex(4)> users = Minotaur.Repo.all(User)
[
  %User{
    __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
    id: 1,
    first_name: "John",
    last_name: "Doe",
    email: "[email protected]",
    inserted_at: ~N[2024-07-05 21:23:53],
    updated_at: ~N[2024-07-05 21:23:53]
  }
]

iex(5)> Minotaur.Repo.delete(List.first(users))
{:ok,
 %User{
   __meta__: #Ecto.Schema.Metadata<:deleted, "users">,
   id: 1,
   first_name: "John",
   last_name: "Doe",
   email: "[email protected]",
   inserted_at: ~N[2024-07-05 21:23:53],
   updated_at: ~N[2024-07-05 21:23:53]
 }}

iex(6)> Minotaur.Repo.all(User)
[]

Everything seems to be working correctly in the development environment.

I commit these changes to a new branch and check them out on my production server. I build a new image tagged as minotaur:tmp-migration and run the following command to apply the migration:

$ docker run --entrypoint /app/bin/migrate -e DATABASE_URL=$MINOTAUR_DATABASE_URL -e SECRET_KEY_BASE=$SECRET_KEY_BASE --add-host=host.docker.internal:host-gateway minotaur:tmp-migration
22:03:26.122 [info] == Running 20240705205730 Minotaur.Repo.Migrations.AddUsersTable.change/0 forward
22:03:26.128 [info] create table users
22:03:26.167 [info] create index users_email_index
22:03:26.178 [info] == Migrated 20240705205730 in 0.0s

The migration looks good so I’ll now start a REPL with a production node to test the database user permissions. I start the container with an interactive IEx terminal.

$ docker run --entrypoint /app/bin/minotaur -e DATABASE_URL=$MINOTAUR_DATABASE_URL -e SECRET_KEY_BASE=$SECRET_KEY_BASE --add-host=host.docker.internal:host-gateway -it minotaur:tmp-migration start_iex
22:17:51.696 [info] Starting Horde.RegistryImpl with name Minotaur.GameEngine.SessionRegistry
22:17:51.697 [info] Starting Horde.DynamicSupervisorImpl with name Minotaur.GameEngine.SessionSupervisor
22:17:51.703 [info] Configuration :server was not enabled for MinotaurWeb.Endpoint, http/https services won't start
Erlang/OTP 26 [erts-14.2.5] [source] [64-bit] [smp:1:1] [ds:1:1:10] [async-threads:1] [jit:ns]

Interactive Elixir (1.16.2) - press Ctrl+C to exit (type h() ENTER for help)
iex([email protected])1>

I then run the same commands as with the development environment to make sure the CRUD permissions are working. I see the same results where the user record is created and deleted successfully.

I create two new migration files to test table permissions. The first migration alters the users table by dropping the last_name column. The second migration drops the users table completely.

defmodule Minotaur.Repo.Migrations.DeleteUsersLastName do
  use Ecto.Migration

  def change do
    alter table(:users) do
      remove :last_name
    end
  end
end

defmodule Minotaur.Repo.Migrations.DropUsersTable do
  use Ecto.Migration

  def change do
    drop table(:users)
  end
end

The migrations run successfully on the local development database so I push these changes to the same branch as the previous migration changes and pull the changes from the production server. I rebuild the docker image and run the migration script again.

$ docker run --entrypoint /app/bin/migrate -e DATABASE_URL=$MINOTAUR_DATABASE_URL -e SECRET_KEY_BASE=$SECRET_KEY_BASE --add-host=host.docker.internal:host-gateway minotaur:tmp-migration
22:47:56.614 [info] == Running 20240705223300 Minotaur.Repo.Migrations.DeleteUsersLastName.change/0 forward
22:47:56.619 [info] alter table users
22:47:56.629 [info] == Migrated 20240705223300 in 0.0s
22:47:56.706 [info] == Running 20240705224038 Minotaur.Repo.Migrations.DropUsersTable.change/0 forward
22:47:56.707 [info] drop table users
22:47:56.712 [info] == Migrated 20240705224038 in 0.0s

The permissions look good for altering and dropping tables in production. Next, I want to automate running migrations with deployments.

Automating migrations at startup

It would be very convenient to have any outstanding migrations automatically run before booting the application on production deploys. Ecto migrations lock the schema_migrations table by default so there isn’t a concern about automatically running migrations during startup even when there are multiple instances starting at once.

A simple change to the Dockerfile is all it takes to implement this behavior:

- CMD ["/app/bin/server"]
+ CMD /app/bin/migrate && /app/bin/server

I rebuild the image and deploy the change to the Swarm service with docker service update --image minotaur:auto-migrate. The service logs show the expected Migrations already up message before the application boot up messages.

And that’s it for Ecto setup!

I think I’m finished tinkering with operations tasks for a bit. A couple items I’ve left on my to-do list are automating deploys with a pipeline runner and to replace the hard-coded IP matching in the release environment script with a build-time argument. I’ll leave those be for now since they aren’t urgent and I’m eager to get back to writing Elixir code.