Ecto Auto Migrator - (thoughts appreciated)

OK, so I have made a few tiny tweaks to this upstream. Sure it’s nothing hugely inciteful, but perhaps it offers inspiration to others who want to attach the Ecto migrations to startup of the app, rather than via a strictly separate offline process.

In my case I’m running a headless router, so nobody to fix it if it goes wrong. What that looks like as an example of how to use this library is as follows:

In my config I have:

config :my_app, run_migrations: System.get_env("RUN_MIGRATIONS")

The app is then started with something like the following in dev, (set the env variable appropriately using your release process)
RUN_MIGRATIONS=1 iex -s mix

Then in my case I wanted to try the migrations, but if they fail then I wanted the app to start no matter what, so I blow away the DB. Figuring out how to do that for sqlite wasn’t totally trivial, so note the incantations below:

This module is started in my application tree soon after the Repo module

defmodule Database.Repo.Migrator do
  use Ecto.AutoMigrator
  require Logger

  @doc """
  Entry point

  Run DB migrations and try to ensure they succeed.
  Specifically we will delete all the DBs if migrations fail and try to re-run migrations from scratch
  """
  @impl true
  def migrate() do
    if run_migrations?() do
      load_app()

      try_migrations_1(repos())
    end

    :ok
  end

  # Run migrations, if they fail then blow away the DBs and retry the migrationss from scratch
  defp try_migrations_1(repos) do
    case try_migrations(repos) do
      :error ->
        Logger.critical("migration failure. Purging databases to attempt to continue")

        delete_databases(repos)

        # Retry from scratch and hope we can complete
        try_migrations_2(repos)

      :ok ->
        :ok
    end
  end

  # retry migrations second time
  defp try_migrations_2(repos) do
    case try_migrations(repos) do
      :error ->
        Logger.critical("migration retry failure. Continuing, but anticipate that app is unstable")
        :error

      :ok ->
        :ok
    end
  end

  # Delete all database files associated with all 'repos'
  # Currently assumes sqlite DBs
  defp delete_databases(repos) do
    for repo <- repos do
      repo.__adapter__.storage_down(repo.config)
      repo.__adapter__.storage_up(repo.config)
      # Purge all in use connections or we will still be using the old DB files
      repo.stop(5)
    end
  end

  # Try and run migrations, wrapping any exceptions and converting to :error/:ok result
  defp try_migrations(repos) do
    try do
      run_all_migrations(repos)
    rescue
      _ -> :error
    else
      _ -> :ok
    end
  end
end

I think this needs more attention. I decided to search the forum for other examples of this procedure but it appears nobody has really taken interest in this post or sharing other methods. I have been considering writing a library for this too but I’m short on time. I’m curious if you’d be interested in taking a look at the example I’ve put up here as it demonstrates the method I’ve been using which was created by Pleroma (https://pleroma.social)

https://github.com/feld/elixir-auto-migration-example/commit/1f304e714780bd8a8e260d1e17ce1f45d2256d85

It would be nice if there was a common library that everyone uses for this purpose.

I think this didn’t get much traction because that’s a rather unusual approach to migrations. Depending on the environment/build env, the usual thing to do is:

Local development (:dev):
manually run

mix ecto.migrate

Local testing/CI (:test):
run migrations as part of the tests - the test task is aliased to

test: ["ecto.create --quiet", "ecto.migrate", "test"]

in mix.exs

Production (:prod):

• Package the app as a release,
• Add a release task,
• Run the migrations at startup.

If you’re using Docker, this could be:

CMD ["sh", "-c", "bin/app eval MyApp.Release.migrate && bin/app start"]

Usually there are healthchecks in place, so if the migrations fail, the deployment process should pick up on that and abort the deployment.

The case in the original post was about running the new version regardless if the migrations succeed or not, but I think this can be easily achieved with running the migrations as a separate command and just ignoring any errors.

Without disagreeing. My use case was an embedded device, so we will be booting the box after an upgrade when we realise we need to do a migration

I speculate that it would be helpful for SAAS kind of products. Eg I migrated away from gitlab to gitea because all that having to run migrations in the right order and remember all the commands was killing me. I just want the software to know what migrations to run and run them (sure if it goes wrong I will need to step in, but for many use cases short of clustered things, you will want the software to run the migrations I think?

Thanks for taking the time to reply! Appreciated

If you follow what @stefanchrobot wrote then running code automatically when starting the application vs. “from the outside” as shown is just a matter of calling MyApp.Release.migrate() within your application startup logic like e.g. MyApp.Application.start instead of via bin/app eval. In the end the created module is just a module with functions like any other. You can call them in a way which fits your project.

The benefit to doing migrations ouf of band is that you can uncouple software deploys from migrations, which is usually done for derisking deployment steps.

Hmm, that doesn’t seem to be the “OTP Way” though?

How/Where do you run this code? How will you deal with failure? Retries? Ensuring that the rest of the app can start up around it? It may be an umbrella app with various dependencies that the OS author doesn’t know about ahead of time?

I’m struggling to see how you would implement your suggestion in various use cases. eg imagine I compile some app to be something like a desktop app for a user. This is a normal end user, who is expecting to double click and go. Now the app is upgraded (brew upgrade, etc) and they expect to be able to double click and go again. Where will this deploy logic fit?

Similarly, I have an embedded app, running on a box which is in the middle of a field that is a 10 hour journey away from any engineer who can visit it. It gets a remote upgrade (could be via a USB stick, remote net connection, whatever). It reboots and needs to deal with upgrading itself from whatever previous position it found itself in. For extra fun that app will be an umbrella app with various optional modules, eg we might have a choice of two control UIs, one is a basic web UI, simple DB used for storing config. The other is a sophisticated IOT device which needs a completely different DB to store all it’s config. We don’t necessarily know ahead of time which the box has installed, and of course the needs or quantity of DBs may change dynamically through time, so I can’t assume that the OS level knows enough to run the migrations

So yes, it’s all just code… So, agreed, it makes little practical difference if the OS pre-runs app.migrate, then boots the app, or if it just boots the app and the app runs app.migrate as part of startup. However, it may make a big speed difference on a little box which is upgraded infrequently, but needs to boot rapidly. So it’s impractical to run a separate migrate step on each boot. OK, so now we need a boot step which runs the migrations and in turn cascades those calls out to an as yet unknown number of apps within the umbrella, ie I need an app which is composable at runtime and doesn’t necessarily know about all the migrations of each of it’s subcomponents. So sure, I can run some code at app boot, but what if that code fails? How to re-run it, create a dependency tree, ie do all the OTP stuff. Well then it seems like we want an OTP module to be running our migratations and for that to be happening as part of the normal startup process - then we get all the goodness of our OTP system to manage failure, retries, etc.

Which brings us full circle to:

• tada. Here’s some skeleton code to make it easy to build an OTP module that you can boot as part of your app startup to wrap your Repo.migration stuff and ensure that a) it’s run each startup and b) it’s within the OTP framework in order to be reliable

I realise that this forum has a large number of developers who are specifically paid to manage individual services and babysit their upgrade process. However, there is a large opportunity space for apps which are deployed without this babysitting and still need to be highly reliable, and manage their own upgrades without the benefit of a skilled developer babysitting it. If this isn’t you, then no problems, but I personally hate Gitlab and the immensely long and complicated Rails migration scripts that are needed. If the migration process is fixed then have the app run it as part of startup?!!

I claim that the rest of you guys are all wrong… I claim that (in many cases) the evolution is from being given some SQL to run as part of the upgrade, through to wrapping this in a migrator which is run as a separate “mix migrate” step, through to running this step automatically as part of the app’s normal (release) startup process (which of course should be done through OTP friendly mechanisms). In my case I add a few conditionals to this migration to avoid it being done repeatedly by automated tools within my editor (eg test watchers or linters, etc), I would usually avoid it being run automatically in dev env, however, I would probably have it run automatically in release/test environments.

My 2p

Re @feld’s comment. I do agree there are real needs for carefully managed migrations, where these need to be carefully controlled, run by an skilled engineer and failure is a showstopper.

However, I would claim that we should be working from the other end to get here. We should be
a) treating those migrations as code which is carefully tested (why should this be a third class citizen which requires a skilled user to carefully design a process around, separate to running the app? Generally I close my email program, upgrade it, open it again and expect it to take care of the details, not drop me to a shell and have me type stuff in…)

b) Once the migrations are a testable chunk of code which the app verifies are run and valid as part of startup, then we can work backwards, if required, and have separate pre-start process, which just part boots the app and checks that migrations are valid, or runs the migrations, or rolls them back or whatever.

This naturally leads to

a) thinking of migrations beyond just ecto databases, eg I might have other data which needs migrating, eg my “cubdb” database, or my crypto keyring or my exchange rates or my ssl trust chain, etc.

b) migrations can be “composable”. eg right now if I push some code to hex, and that code perhaps needs it’s own data store to store some state, then how do we pass control of that migration of state back to the top level of the app? Either the developer reads the README for the dependency and writes code, or the dependency just gets on with the job of migrating the data store when it starts up?

c) Similarly if I have an umbrella app, there may be optional pieces which may or may not have database dependencies. Now yes, “mix migrate” runs through all the sub apps and calls their mix commands, but if we were doing this as a release then we need more care as we need to write a function and have that function know about all the sub apps and whether those migrations succeed or not.

d) We can start to make these migrations OTP processes which can retry or handle failure or otherwise be smarter about success and failure. eg if we need to obtain a new SSL keychain, that might need downloading, it might need to handle failure, timeout, etc. Success or failure of that may or may not be a critical fail, but it might mean that further sub tasks are or are not run as a result and so on

e) I guess migrations don’t always only happen at boot? I’m thinking about say my currency rates or keychains, etc?

I don’t think there is a one size fits all solution to be found. However, my thoughts would be:

• Make migrations a first class citizen of the app, test them
• Make migrations an OTP process so that we can wrap infrastructure around them
• Possibly we could make our apps have a skeleton which includes a “migrations” boot phase and encourage developers to put migrations into this area?
• It would be helpful if there were some standard patterns and tooling around booting and running only the migrations phases of the app for use with “enterprise” apps where the migrations need to be carefully monitored and controlled for success.

Thoughts?

I would think a desktop app would have them run during the install and update, which would be decoupled from the starting of the app. You probably want to be able to uninstall an update.

Might be the case. Alternatively, you’re solving for a different use case.

Most of the web development needs zero-downtime deployments. This means that:

• There is a time window during the deployment when the old version and the new version of the app are running at the same time,
• The old version of the app must work with the migrated schema/data.

Let’s consider a common relational DB schema migration: adding a column to a table. Let’s say the migration failed.

Your approach seems to be suggesting that the app should still boot and run just fine. How am I supposed to write the app to use the new column since it might not be there if the migration failed?

I choose to avoid that problem (and the effort to somehow solve it) altogether by not booting the new version of the app, aborting the deployment process and letting the old version run. You might not have that convenience, but most of microservices do, so I see no reason not to take advantage of it.

There’s not much babysitting, since the migrations are run automatically as part of the deployment. Whether this is a separate process or happens in the app doesn’t really matter. The key is to abort the app boot on failed migrations.

So there’s a lot to uppack here.

First of all I’d like to address the fact that you’ve quite a lot of assumptions about the people you’re interacting here, which might not hold up once challenged. As it happens to be I’m working on an embedded system at work as well. To execute ecto migrations we do MyApp.Migrator.migrate in MyApp.Application.start and that works fine for us. Though I guess that’s besides the point.

I feel a lot of the points you brough up are “how do I deal with the complexity of migrating in my project” rather than “how do I execute migrations”. The answers you got are about the latter, which imo are a lot simpler to answer and also completely separate to potential answers to the former. Also it sounds like you’re working on a project, which is a lot more complex than what most people using ecto work on. So it’s to be expected for that situation to have less resources be available.

Depends on the failure. How likely is it that the failure is based on some temporary condition? If it is then retries might help. For us this is reboot on startup error. If not then retries are a waste of time. If a migration fails because the data in the db is in a unexpected state than it most likely needs human intervention to fix. No amount of preexisting code will help here. You’ll likely need some kind of circuit breaker to prevent the failure from cascading onto parts of the system you use for communication, given what OTP gives us is meant to keep “the whole” system running and stop if it can’t. Even for temp. problems backoff would be useful, which you don’t get with default OTP restarts.

Most often this is handled again by spliting up migrations from software deployment. In case of embedded software you might deploy a firmware, which just adds the migration to expose new/changed data, but not remove the old access point nor change the software itself. Then it doesn’t matter if migration fails, because nothing is depending on the new stuff yet. Once that’s successfully deployed you can later deploy just a software update, which makes use of the new data available. If your embedded device supports rollback you could also use that to potentially consolidate migration and software deployment with a fallback in case of errors. If you (or whomever handles your subcomponents) cannot “babysit” migrations that closely then you need to be more radical in not running things if they don’t work. Depending on the project downtime might be acceptable if things go wrong, if problems are at least eventually fixable.

If you’re deploying to embedded devices without knowing your dependencies you’re up for having a bad time no matter what you do. Your dependencies might include NIFs, which can crash the beam and there’s no way to catch that from within the beam.

Migrations are not executed on each run. Which migrations where successfully applied is stored in the db. So if there are no new migrations then it’s a single db query + a directory listing on the folder of migrations to check for that. You might be under constraints where those are already to slow, but I guess that’s not the usual case.

That’s already the case. While the generated setup shipped with phoenix does run migrations before tests start that’s just something to get people started easily. Given you’ve more complex requirements you can use a more elaborate setup, which tests migrations. Ecto.Migrator includes API to do that. For this one I actually think a library might be useful to integrate that a little more declaratively in tests. Ecto.Repo.put_dynamic_repo might even allow for running concurrently to sandboxed tests.

Running migrations on boot is the way to go for embedded devices, but it’s against common and adviced practice for deployments for general webservices. I don’t see this becoming an encouraged practise.

Tbh I fail to see how a release with some module to run migrations doesn’t essentially give you that. If you want to just run migrations do bin/app eval SomeModule.run_migrations. If you want to run migrations on boot you can put the same call in a boot phase or within MyApp.Application.start and it’ll be run on boot.

The complex part will be within SomeModule.run_migrations and depend on the project being built.