Ecto migration for renaming table with indexes and constraints

instructions.md

Renaming table in Ecto migration

I recently wanted to rename a model and its postgres table in a Phoenix app. Renaming the table was simple and documented, but the table also had constraints, sequences, and indexes that needed to be updated in order for the Ecto model to be able to rely on default naming conventions. I couldn't find any examples of what this would look like but was eventually able to figure it out. For anyone else in the same situation, hopefully this example helps.

In the example below, I'm renaming the Permission model to Membership. This model belongs to a User and an Account, so it has foreign key constraints that need to be renamed.

defmodule MyApp.Repo.Migrations.RenamePermissionsToMemberships do
  use Ecto.Migration

  def up do
    # Drop the existing forgeign key and primary key constraints by their
    # default names from when they were generated automatically by Ecto.
    drop constraint(:permissions, "permissions_user_id_fkey")
    drop constraint(:permissions, "permissions_account_id_fkey")
    drop constraint(:permissions, "permissions_pkey")

    # Rename the table
    rename table(:permissions), to: table(:memberships)

    alter table(:memberships) do
      # "Modifying" the columns rengenerates the constraints with the correct
      # new names. These were the same types and options the columns were
      # originally created with in previous migrations.
      modify :id, :bigint, primary_key: true
      modify :user_id, references(:users)
      modify :account_id, references(:accounts)
    end

    # Rename the ID sequence. I don't think this affects Ecto, but it keeps
    # the naming and structure of the table more consistent.
    execute "ALTER SEQUENCE permissions_id_seq RENAME TO memberships_id_seq;"
  end
  
  # This is just the inverse, restoring the table and its constraints,
  # indexes, and sequences to their original names.
  def down do
    drop constraint(:memberships, "memberships_user_id_fkey")
    drop constraint(:memberships, "memberships_account_id_fkey")
    drop constraint(:memberships, "memberships_pkey")

    rename table(:memberships), to: table(:permissions)

    alter table(:permissions) do
      modify :id, :bigint, primary_key: true
      modify :user_id, references(:users)
      modify :account_id, references(:accounts)
    end

    execute "ALTER SEQUENCE memberships_id_seq RENAME TO permissions_id_seq;"
  end
end

This was super helpful! Thanks!

You could also use ALTER INDEX RENAME and ALTER TABLE RENAME CONSTRAINT

execute "ALTER INDEX RENAME permissions_pkey TO memberships_pkey"
execute "ALTER TABLE memberships RENAME CONSTRAINT permissions_user_id_fkey TO memberships_user_id_fkey"
execute "ALTER TABLE memberships RENAME CONSTRAINT permissions_account_id_fkey TO memberships_account_id_fkey"

which might be a little safer if you're running against a database that's in use, as the constraints never get dropped.

@cschiewek
ALTER INDEX RENAME permissions_pkey TO memberships_pkey
should be
ALTER INDEX permissions_pkey RENAME TO memberships_pkey

👍

I do not like to write down migrations and I had a lot of tables, so I decided to write some helpers
Here is how migration in the example above looks like with these helpers:

  def change do
    # let's say indexes are present
    # rename_index_table(from: :permissions, to: :memberships, fields: [:account_id, :user_id])
    rename_fkey_table(from: :permissions, to: :memberships, field: :user_id)
    rename_fkey_table(from: :permissions, to: :memberships, field: :account_id)
    rename_pkey_table(from: :permissions, to: :memberships)
    rename_seq_table(from: :permissions, to: :memberships, field: :id)
    rename(table(:permissions), to: table(:memberships))
  end

  defp rename_fkey_table(from: from_table_name, to: to_table_name, field: field) do
    from_fkey = build_identifier(from_table_name, field, :fkey)
    to_fkey = build_identifier(to_table_name, field, :fkey)

    rename_constraint(from_table_name, from_fkey, to_fkey)
  end

  defp rename_pkey_table(from: from_table_name, to: to_table_name) do
    from_pkey = build_identifier(from_table_name, nil, :pkey)
    to_pkey = build_identifier(to_table_name, nil, :pkey)

    rename_constraint(from_table_name, from_pkey, to_pkey)
  end

  defp rename_seq_table(from: from_table_name, to: to_table_name, field: field) do
    to_seq = build_identifier(to_table_name, field, :seq)
    from_seq = build_identifier(from_table_name, field, :seq)

    execute(
      "ALTER SEQUENCE #{from_seq} RENAME TO #{to_seq};",
      "ALTER SEQUENCE #{to_seq} RENAME TO #{from_seq};"
    )
  end

  defp rename_index_table(from: from_table_name, to: to_table_name, fields: fields) do
    to_index = build_identifier(to_table_name, fields, :index)
    from_index = build_identifier(from_table_name, fields, :index)

    execute(
      """
      ALTER INDEX #{from_index} RENAME TO #{to_index};
      """,
      """
      ALTER INDEX #{to_index} RENAME TO #{from_index};
      """
    )
  end

  defp rename_constraint(table, from, to) do
    execute(
      """
      ALTER TABLE #{table} RENAME CONSTRAINT "#{from}" TO "#{to}";
      """,
      """
      ALTER TABLE #{table} RENAME CONSTRAINT "#{to}" TO "#{from}";
      """
    )
  end

  @max_identifier_length 63
  defp build_identifier(table_name, field_or_fields, ending) do
    ([table_name] ++ List.wrap(field_or_fields) ++ List.wrap(ending))
    |> Enum.join("_")
    |> String.slice(0, @max_identifier_length)
  end

👍

Thanks! Very useful 👍

@fuelen Very useful helper! Were you able to create unit tests for this? Tried imitating https://github.com/elixir-ecto/ecto_sql/blob/5f144a9cca515c1d4e26f05147fc0a2505c8989d/test/ecto/migration_test.exs#L4 but struggling to implement tests for this.
any tips?

No, I don't test such migrations with unit tests. I simply check the result of migration using DB management tool