Using MYSQL enum in ecto migrations

Questions / Help
,
1

I am using MYSQL instead of Postgres for my database. For Postgres it’s simple we can create a type using enum and use it in the migration to allow the values in the enum, but MYSQL doesn’t support this. Now, If I want the enum in the migrations. How can I do that with MYSQL? Let’s say this is my migration.

create table(:user) do

  add(:role, :string) # 'owner', 'worker', 'manager', 'pending_approval'
  add(:business_id, :string, null: false, default: "")
end

I only want to allow owner, worker, manager, pending approvals for role field.
Thanks.

2

I figure out a workaround for this. After creating a table with ecto migration. I executed SQL command to alter the table and add the enum column like this:

create table(:users) do
  add(:business_id, :string, null: false, default: "")
end

execute "ALTER TABLE users
   ADD role ENUM('owner', 'worker', 'manager', 'pending_approval');"
3 Likes
3

If someone comes across this, “quoted atoms” has been an undocumented way of supporting this and other interesting things in MariaDB/MySQL, but it’s now documented:

https://hexdocs.pm/ecto_sql/3.7.2/Ecto.Migration.html#module-field-types

create table("my_table") do
  # ...
  add :my_enum, :"ENUM('value1', 'value2', 'value3')", null: false
end

This is because the migrator just passes the stringified-atom through if it’s something it doesn’t specifically handle.