What would be the best way to write this migration?

I have 2 tables trainings and training_instances. Please see the schema below:

trainings table:

column_name   data_type     is_nullable. foreign_key

id            uuid          NO           NULL
day           int4          NO           NULL
start_time    time(0)       NO           NULL
end_time      time(0)       NO           NULL
team_id       uuid          YES          public.teams(id)
inserted_at   timestamp(0)  NO           NULL
updated_at    timestamp(0)  NO           NULL

Note that trainings table does not have a date or start_date column yet. The column named day is the day of the week. One training_id can have multiple training instances.

training_instances table:

column_name   data_type     is_nullable. foreign_key
id            uuid          NO           NULL
date          date          NO           NULL
start_time    time(0)       NO           NULL
end_time      time(0)       NO           NULL
team_size     int4          YES          NULL
training_id   uuid          YES          public.trainings(id)
team_id       uuid          YES          public.teams(id)
inserted_at   timestamp(0)  NO           NULL
updated_at    timestamp(0)  NO           NULL

I am trying to create a new column named start_date in the trainings table and then insert the data here for existing trainings. I want to insert for every training the oldest date for its corresponding training instance in this new start_date column.

What would be the best way to write a migration here?

1 Like

Welcome to the forum :rocket:

I would separate the schema migration and data updating into separate concerns. See José’s answer elixir - Using a Repo in an Ecto migration - Stack Overflow.

And generally I wouldn’t do the data update in a migration script because if you import your schema into the migration it’ll work at the time that you’re doing this execise, but if you re-run your migrations to setup a new database in the future, there is no guarantee that the Elixir schema hasn’t been updated in the meantime. For example, if in the future you do another structural change, the future Elixir schema may not match the shape of what the older migration is trying to do and it’ll just crash.

Rather put the temporary data migration in a Mix.Task that you run once and then can delete from your code’s repo when you’re done, to prevent hitting a wall in the future.

1 Like