Changing the Type of a Column in Table

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:

1 Like