Ecto Changeset: modify column type from :string to {:array, :string}

Hi,

I’d like to change a database column’s type from :string to {:array, :string}. Migration should work as follows: "my_value" -> ["my_value"].

I’ve looked in the Ecto documentation, and I tried doing the migration via

modify :column_name, {:array, :string}, from: :string

But that doesn’t work. I’ve also seen from the Postgres documentation that I can use the USING option to indicate how to do the conversion, and the error message when I try to run the migration indicates as much:

hint: You might need to specify "USING column_name::character varying(255)[]"

I don’t see how to specify this option in the modify function, though.

What’s the most straightforward way to handle this conversion? Am I missing something obvious? Do I need to use raw Postgres, or something more complicated? Thanks!

2 Likes

I’m assuming the migration isn’t working because it’s not able to convert the current values in the column to the new type.

You might want to do this in multiple migrations:

  • A migration to create a new column with type {:array, :string} and copy all the old column values to the new column as a list of strings.
  • A migration to delete the old column.
  • A migration to rename the new column to be the name of the deleted column.
1 Like

Thanks for the suggestions. After sleeping on it, I ended up deciding to pluralize the column name. So this simply became a matter of moving the values from one column to the other.

Here’s what I ended up doing:

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

  alias MyApp.{Repo, MyTable}
  import Ecto.Query

  def change do
    alter table :my_table do
      add :list_column_name, {:array, :string}, default: []
    end

    flush()

    Repo.all(MyTable)
    |> Enum.each(fn row ->
      row
      |> MyTable.changeset(%{ name: row.name, list_column_name: [ row.column_name ]})
      |> Repo.update()
    end)

    alter table :my_table do
      remove :column_name
    end

  end
end

You should try to not use Repo in migrations. You don’t want to fall in a situation, where you need a migration to be applied to be able to successfully start the repo, while the migration needs the repo to be applied.

The other reason is that the repo is started by your application, so your whole application will be started up – which might have other sideeffects – just for migrations to be applied. While without the repo it’s enough for your app to be loaded (for the credentials), but only ecto needs to be started.

4 Likes

Okay, is there a way to solve this problem without using Repo?

I see Repo used in Programming Ecto when changing both the table structure and the data, as is the case here.

There’s execute for executing arbitrary sql in migrations.

2 Likes

Yep, better use raw SQL, otherwise you might find yourself in a situation when the runtime doesn’t allow you to use Elixir code to do or rollback a migration.

1 Like

Thanks @dimitarvp and @LostKobrakai, here’s what I ended up settling on:

defmodule Database.Repo.Migrations.StringToListMigration do
  use Ecto.Migration

  def change do
    execute(&string_to_list/0, &list_to_string/0)
  end

  defp string_to_list() do
    repo().query!("alter table my_table alter column_name type varchar(255)[] using array[column_name], alter column_name set default '{}';", [], [log: :info])
  end

  defp list_to_string() do
    repo().query!("alter table my_table alter column_name type varchar(255) using column_name[1], alter column_name drop default;", [], [log: :info])
  end
end

I do the renaming / other cleanup operations in a separate migration.

Mostly good, but remove the repo().query!(...) stuff from the front. The logging level has a default so you shouldn’t worry about it. Just use execute with both strings as they are and nothing else.

1 Like

I see.

Actual final solution:

defmodule Database.Repo.Migrations.StringToListMigration do
  use Ecto.Migration

  def change do
    execute(string_to_list(), list_to_string())
  end

  defp string_to_list() do
    "alter table my_table alter column_name type varchar(255)[] using array[column_name], alter column_name set default '{}'"
  end

  defp list_to_string() do
    "alter table my_table alter column_name type varchar(255) using column_name[1], alter column_name drop default"
  end
end
2 Likes

For my situation, using alter table profiles alter ethnicity type varchar(255)[] using string_to_array(ethnicity, ','), alter ethnicity set default '{}' on a PostgreSQL database separated my string into a list for me.

Likewise, if you need to rollback you can do alter table profiles alter ethnicity type varchar(255) using array_to_string(ethnicity, ','), alter column_name drop default

1 Like