Why shouldn’t you use Elixir code in database migrations?
Elixir code used in migrations can cause troubles when we decide to start up our system with an empty database. To understand the problem, let’s take the same example as the one used in my previous article entitled “How to migrate live production data“.
Each year, triathlon races are held in many countries. Triathletes from around the world can sign up for one of 4 distances.
Notice that every time someone tries to register, we have to fetch the participant count. We also have to use a lock to prevent race conditions. This can be slow when many people try to register at the same time.
To solve that, we decide to add a new field with confusing name entries.
While signing up for a race, we can check the left entries based on the triathlon_races table. When a user successfully registers, the number of available entries decreases.
After a few months, we got confused whether the entries field represented the number of entries which either remained or were already sold. To make this field explicit, its name is changed to entries_left.
The code is more understandable now. Success!
What could go wrong?
Let’s wonder what will happen if we run these all migrations on an empty database.
Every migration will be executed one by one, by the time the system meets the migration with the Elixir code.
In the moment of running this migration our schema looks as follows:
We don’t have our entries field anymore. It has been changed for entries_left.
Execution of the Elixir code in the migration will fail and return error as there are no entries key in the triathlon_races schema.
The project’s setup is destroyed and until we remove the Elixir code from the migration, the system won’t start up.
How to solve it?
One way to avoid such implications is to use pure SQL in migrations instead of writing code in Elixir.
Both approaches, theoretically, do the same. Notwithstanding, there is a small difference between them.
SQL query uses a database structure with such a version of it which exists in the moment of executing migration. The code always uses the most recent version of the database schema.
As you can see on the diagram below, the database’s structure changes along with the running migrations. The Elixir code is always the same during that process.
Such migration with SQL query can look like the one below:
Problem solved! Now, if we change code in the project and then start up it from scratch, it won’t cause any troubles as the migrations rely only on a database’s structure.
SUM UP
It’s worth remembering that migrations are an immutable log of changes. These adjustments should always give the same effect. Some serious problems may arise, when references to code, which can be amended in every moment, appear in that changes log. Suddenly, the migration written some months ago uses stale code from that period instead of the new one which is currently in a project.
Originally published at https://patrykbak.com on June 15, 2020.
