Changing the Type of a Column in Table (Elixir, Fly)

Questions / Help
,
1

Hello, I am Elixir newbie. I need to change the user schema field role from a string to a array for multiple roles in one user.
In general, the solution should be like this:
Multi deployment strategy:

  1. Create a new column
  2. Edit the column for the default value
  3. In application code, write to both columns
  4. Backfill data from old column to new column
  5. In application code, move reads from old column to the new column
  6. In application code, remove old column from Ecto schemas.
  7. Drop the old column.

I found an extensive article on fly.io about migrations, but I don’t understand some parts:

  1. If I have each part of the process in a separate migration, how can I run the individual migrations one after the other. For example, on the database located on fly.io
  2. How do I make a backup for the users table on the fly servers? I can somehow create it via mix ecto.dump, is there a way to do it for a specific table? I couldn’t find it anywhere.
  3. I also does not fully understand the transfer of data from column to column within a single table. Based on this article.

Now my understanding of the Backfill is that I have to make a temporary copy of the users table by copying the values ​​over the schema in “roles_migratins_schema.ex” which refers to the same table as the schema in “users.ex”. This temporary table cannot have any ids. Subsequently, I have to tell Backfill migration to look at the data of this table and change all values ​​in column roles to values ​​in column role for matching records. In the event of an interruption of the migration, we can continue from the index where we finished the migration and do not have to start everything from the beginning. Once done, the temporary table is deleted and we have the data copied over in our users table. I will check if the data fits and then I can delete the role column from the schema and then remove it from the DB through migration, I will only have the roles column, which is an array, and we can therefore add more roles for one user. Now what am I misunderstanding please?

Thanks for any advice and help. :slight_smile:

2

Hi @janjakubek,

The Safe Ecto Migrations guide is really intended for migrating production data where you want to avoid downtime. If you are doing this migration on a hobby project or you can tolerate some brief downtime, then you can be more aggressive in how you solve it.

The general idea of a multi-stage migration is…

  • Migrations change the database structure
  • Then a separate data migration can migrate the data from the old structure to the new structure.
  • Doing these as separate steps lets you avoid service interruptions. If you don’t care about that, then you can do it all together if want.

In answer to your questions:

  1. Keep the additional migrations on separate branches. The point is, they aren’t deployed at the same time.
  2. There is no built-in way to do an export/backup of a single table.
  3. You create a module in your code that does the performs the data migration from the source field to the converted target field. You can deploy the application and when you are ready, get an IEx shell into your running application and manually execute the data migration code. It’s a one-time migration step anyway.

Again, these more broken out steps are for avoiding downtime or service interruptions on production systems. Just make sure you’re performing this additional work when it’s needed and not for personal dev project. :slight_smile:

1 Like
3

Thank you very much for your answer! :slight_smile:

1 Like