How do I use Ecto.Enum with postgres ENUM types?

Question

According to the documentation: https://hexdocs.pm/ecto/Ecto.Enum.html

It appears I should structure my migration as:

add :status, :string

The problem is, this doesn't take advantage of the Postgres ENUM type https://www.postgresql.org/docs/current/datatype-enum.html#id-1.5.7.15.5

The main purpose of having an ENUM is to reduce the field's storage footprint.

How should I structure the migration to take advantage of Postgres's built in ENUM, or should I use a different method?

Answer 1

As it is mentioned in the docs, Ecto.Enum can actually be used with ENUM types as well:

Some databases also support enum types, which you could use in combination with the above.

AFAIK, there is no migration function for it though, you need to write it in SQL:

execute("CREATE TYPE my_status AS ENUM ('todo', 'wip', 'done');")

create_table :tasks do
  ...
  add :status, :my_status, null: false
  ...
end

The main purpose of having an ENUM is to reduce the field's storage footprint.

Ecto.Enum has many advantages even if used with a string field:

• validates your schema, application side
• handles conversion to atoms
• using atoms is better than strings when working with known values, works well with typespecs and dialyzer

Besides, the use of an ENUM type on the database side has drawbacks as well, which I suppose is why it isn't pushed more in the docs. Updating the enum type to add/modify/fields can be tricky and comes with risks, as explained in the SQL Antipatterns book which recommends against it if the values might change later.

There is a third option in order to reduce the storage footprint, which is to store it as an integer. Quoting the docs:

# in migration:
add :status, :integer

# in schema:
field :status, Ecto.Enum, values: [foo: 1, bar: 2, baz: 5]