Ecto - migrate table column into its own join table (shifting the DATA to the new tables)

I would like to go from this:

  schema "products" do
    field :name, :string
    field :brand, :string
    ...more fields...
  end

To this:

  schema "products" do
    field :name, :string
    ...more fields...
  end

  schema "brands" do
    field :name, :string
    ...more fields...
  end

  schema "product_brand_joins" do
    field :p_id, :integer
    field :b_id, :integer
    ...more fields...
  end

Without losing my current data which has products with a string :brand field.

I understand how to create the new brands table and product_brand_joins tables and remove ":brand" from products table, with an ecto migration. But is there a way to manipulate my current data into the new tables?

Solution

You can execute SQL statements using the execute/1 or execute/2 functions, the one that accepts two arguments is a rollbackable version of the first one, which expects a statement to execute when migrating and one to execute when rolling back.

So, you can use that to define what should happen when migrating and it becomes a matter of defining your statement. At the end of this docs page from PostgreSQL there's an example of inserting based on a subquery, which we'll use here.

In your migration .exs file:

use Ecto.Migration

def up do
  create table(:brands) do
    add :name, :string,
    # add other fields
  end

  create table("product_brand_joins") do
    add :product_id, references: :products
    add :brand_id, references: :brands
  end

  # Insert into the brands table a brand with each name found in products
  execute(
    "INSERT INTO brands(name) 
      SELECT DISTINCT(brand) FROM products"
  )

  # Insert into the joined table the product and brand ids
  execute(
    "INSERT INTO product_brand_joins(product_id, brand_id)
      SELECT p.id, b.id FROM products p LEFT JOIN brands b ON p.brand = b.name"
  )

  # And finally delete the column from products
  alter table(:products) do
    remove :brand
  end
end

And then for the rollback you would implement the down function in the migration to revert the process using similar logic: creating the brand column in products, filling it with the name of the corresponding brand depending on the product id, and removing the new tables.