<!-- LLM_VERSION_INFO
FORMAT: text/markdown
CONTENT_TYPE: article
ORIGINAL_URL: https://fly.io/phoenix-files/loading-structure-sql-on-prod-without-mix
ALTERNATE_VERSION: phoenix-files/loading-structure-sql-on-prod-without-mix/index.html (text/html)
EXTRACTION_DATE: 2026-04-18T22:16:10.392Z

This is the markdown version with text-only content (images converted to alt-text).
For rich formatting with images, request the HTML version at: phoenix-files/loading-structure-sql-on-prod-without-mix/index.html
-->

**Name**  
Mark Ericksen  
[@brainlid](https://twitter.com/brainlid)  
  
Image by [Annie Ruygt](https://annieruygtillustration.com/)

We’re Fly.io. We run apps for our users on hardware we host around the world. This post is about how to deploy your Elixir app to a fresh database after you’ve run `mix ecto.dump` and deleted old migration files. Fly.io happens to be a great place to run Phoenix applications. Check out how to [get started](/content/docs/elixir/index.html)!

## Problem  
At some point, you or your team ran [`mix ecto.dump`](https://hexdocs.pm/ecto_sql/Mix.Tasks.Ecto.Dump.html) 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`](https://hexdocs.pm/ecto_sql/Mix.Tasks.Ecto.Load.html). If you were migrating your database to a new server, there are other [options available](/content/docs/postgres/getting-started/migrate-from-heroku/index.html).

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](/content/docs/hands-on/launch-app/index.html) 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](/content/phoenix-files/developing-after-a-mix-ecto-dump/index.html) 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](https://hexdocs.pm/ecto_sql/Ecto.Adapter.Structure.html#c: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.

```dockerfile
# ... 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.

**NOTE:** If the Postgres package is added to an earlier build layer, **it will not be available on the server**.

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:

```elixir
  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](https://github.com/elixir-ecto/ecto_sql/blob/v3.10.1/lib/mix/tasks/ecto.load.ex#L116) 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](/content/phoenix-files/developing-after-a-mix-ecto-dump/index.html) 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.

# Fly.io ❤️ Elixir

Fly.io is a great way to run your Phoenix LiveView app close to your users. It’s really easy to get started. You can be running in minutes.