Loading a structure.sql file on Prod without mix

Problem

At some point, you or your team ran mix ecto.dump and generated a priv/repo/structure.sql file. Then old migrations files were removed. Things were great and you could handle the local dev and test setup using mix ecto.load. If you were migrating your database to a new server, there are other options available.

The problem we’re facing comes after building a mix release and we are deploying to an empty database in a production environment where mix is not available. How do we load our structure.sql file on the server when mix ecto.load is not available?

When might this happen?

• Deploying a new clean staging environment.
• Testing a mature app on Fly.io before bringing over the full database.

Solution

Assuming the mix ecto.dump command was used, we have a priv/repo/structure.sql file generated for us. That file should be committed to our version control system. When we run fly launch or fly deploy, by default the file will be included and that means it will be present on the server. That gets us part way there.

The problem we have in our production environment is that the development tool mix is not available on the server after we build and deploy a release. This means our mix ecto.setup solution doesn’t work in production.

We need to take extra steps to get our structure.sql loaded. We will rely on the Ecto.Adapter.Structure.structure_load/2 function to do a lot of the work, but it depends on the psql command being available on the server.

To run psql on the server, we need to add the postgresql-client package to our Dockerfile in the final layer. Let’s do that now.

# ... build layer

# start a new build stage so that the final image will only contain
# the compiled release and other runtime necessities
FROM ${RUNNER_IMAGE}

RUN apt-get update -y && apt-get install -y libstdc++6 openssl libncurses5 locales postgresql-client \
  && apt-get clean && rm -f /var/lib/apt/lists/*_*

#...

We added postgresql-client to the end of our installed packages. These are the packages included in the final runtime layer.

The big change happens in our release.ex file. When we run fly launch, it executes mix phx.gen.release for us and that generates a MyApp.Release module. This module defines the migrate/0 function that gets executed in our production environment.

Here’s an example of how to update the migrate/0 function and add a check_and_execute_structure_sql/1 function that runs the structure.sql file when needed:

  require Logger

  def migrate do
    load_app()

    for repo <- repos() do
      # Ensure the repo is started. Then, if the "schema_migrations"
      # table does not exist, run the `structure.sql` file for the repo.
      {:ok, _, _} = Ecto.Migrator.with_repo(repo, &check_and_execute_structure_sql(&1))

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

  # ...

  defp check_and_execute_structure_sql(repo) do
    config = repo.config()
    app_name = Keyword.fetch!(config, :otp_app)

    if Ecto.Adapters.SQL.table_exists?(repo, "schema_migrations") do
      Logger.info("schema_migrations table already exists")
      :ok
    else
      case repo.__adapter__().structure_load(
             Application.app_dir(app_name, "priv/repo"),
             repo.config()
           ) do
        {:ok, location} = success ->
          Logger.info("The structure for #{inspect(repo)} has been loaded from #{location}")
          success

        {:error, term} when is_binary(term) ->
          Logger.error("The structure for #{inspect(repo)} couldn't be loaded: #{term}")
          {:error, inspect(term)}

        {:error, term} ->
          Logger.error("The structure for #{inspect(repo)} couldn't be loaded: #{inspect(term)}")
          {:error, inspect(term)}
      end
    end
  end

The needed code was taken and adapted from the mix ecto.load task in ecto_sql. Pulling the code out of the mix task was necessary because the dev tool mix isn’t available on the server after generating a release. Ideally, this code could be extracted and made more readily available upstream.

With these additional steps, we can ensure that our structure.sql file is loaded in both a local environment and on the server. With that taken care of, we can run mix ecto.dump, delete old migrations, and still support deploying to a fresh database when needed.