How to use Postgres' enumerated type with Ecto

Question

With PostgreSQL, we can do something like this:

CREATE TYPE order_status AS ENUM ('placed','shipping','delivered')

From Ecto's official doc, there is no native type to map the Postgres' enumerated type. This module provides a custom type for enumerated structures, but it maps to an integer in the database. I could easily use that library, but I would prefer using the native enumerated type that ships with the database.

Ecto provides also a way to create custom types, but as far as I can see, the custom type must map to a native Ecto type...

Anyone knows if this can be done in a schema with Ecto? If yes, how would the migration work?

Martres

Answer 1

Ecto_enum now supports postgres enum type https://github.com/gjaldon/ecto_enum#using-postgress-enum-type

Tannertannery

Looks like ecto_enum is no longer maintained – Ministry

For real it is no really need in enum since ecto has changeset validation validate_inclusion and store values just as string. – Tannertannery

Answer 2

Maybe I did something wrong but I just created the type and field like this:

# creating the database type
execute("create type post_status as enum ('published', 'editing')")

# creating a table with the column
create table(:posts) do
  add :post_status, :post_status, null: false
end

and then just made the field a string:

field :post_status, :string

and it seems to work.

Nip

For those brand new to the framework, JustMichael's solution works, but I thought I would add where the code needs to go. The first code section is in the migration file, inside of the change do block. The second block goes inside of the model file, inside of the schema do block. – Bloodline

What happens when you pass a string that isn't published or editing? What type of error occurs? – Intercalate

@TerenceChow DB will most likely raise something and your DB operation will fail. – Nip

I have to correct my previous comment, it should be validate_inclusion(changeset, :post_status, ["published", "editing"]) – Leigh

Answer 3

Summarizing all the bits and pieces here and there in the answers and comments. See the "Enumerated Types" in the PostgreSQL manual for more on the SQL commands used.

Ecto 3.0.0 and above

Since Ecto 3.0.0, there is Ecto.Migration.execute/2 that "Executes reversible SQL commands" therefore it can be used in change/0:

Migration

After generating a migration with mix ecto.gen.migration create_orders:

defmodule CreateOrders do
  use Ecto.Migration

  @type_name :order_status

  def change do    
    execute(
      """
      CREATE TYPE #{@type_name}
        AS ENUM ('placed','shipping','delivered')
      """,
      "DROP TYPE #{@type_name}"
     )

    create table(:orders) do
      add :order_status, @type_name, null: false
      timestamps()
    end
  end
end

Schema

This is the same as under "Ecto 2.x.x and below".

Ecto 2.x.x and below

Migration

After generating a migration with mix ecto.gen.migration create_orders:

defmodule CreateOrders do
  use Ecto.Migration

  @type_name :order_status

  def up do    
    execute(
      """
      CREATE TYPE #{@type_name}
        AS ENUM ('placed','shipping','delivered'})
      """)

    create table(:orders) do
      add :order_status, @type_name, null: false
      timestamps()
    end
  end

  def down do
    drop table(:orders)
    execute("DROP TYPE #{@type_name}")
  end
end

Schema

Because the schema is unable to see the database type created in the migration, using Ecto.Changeset.validate_inclusion/4 in Order.changeset/2 to ensure valid input.

defmodule Order do

  use Ecto.Schema
  import Ecto.Changeset

  schema "orders" do
    field :order_status, :string    
    timestamps()
  end

  def changeset(
    %__MODULE__{} = order,
    %{} = attrs
  ) do

    fields = [ :order_status ]

    order
    |> cast(attrs, fields)
    |> validate_required(fields)
    |> validate_inclusion(
         :order_status,
         ~w(placed shipping delivered)
       )
  end
end

Mayonnaise

Thanks for your proposed solution. I saw it and was ready to implement it in my codebase, but it just felt wrong including live production backend code as part of a migration. It is likely that the statuses function on your order schema will change (e.g. name changed/function removed), which will prevent the app from compiling. It is best IMO to keep live code out of the migration and treat it as an evergreen document that can always compile and run. If a new status is added, create a new migration to document that (you'll never run an old migration in hopes of upgrading the types anyway). – Forswear

You're right, and I was totally overcomplicating it. I also just edited the answer. Thanks for your insight! – Mayonnaise

Answer 4

Small enhancement for @JustMichael. If you need to rollback, you can use:

def down do
  drop table(:posts)
  execute("drop type post_type")
end

Brokaw

Answer 5

You need to create an Ecto type for each postgresql enum. In the schema definition, you simply have the type be :string. In migrations, you set the type to be the module name. This can get really tedious, though, so I have the following macro in my project that uses Postgresql enums:

defmodule MyDB.Enum do

  alias Postgrex.TypeInfo

  defmacro defenum(module, name, values, opts \\ []) do
    quote location: :keep do
      defmodule unquote(module) do

        @behaviour Postgrex.Extension

        @typename unquote(name)
        @values unquote(values)

        def type, do: :string

        def init(_params, opts), do: opts

        def matching(_), do: [type: @typename]

        def format(_), do: :text

        def encode(%TypeInfo{type: @typename}=typeinfo, str, args, opts) when is_atom(str), do: encode(typeinfo, to_string(str), args, opts)
        def encode(%TypeInfo{type: @typename}, str, _, _) when str in @values, do: to_string(str)
        def decode(%TypeInfo{type: @typename}, str, _, _), do: str

        def __values__(), do: @values

        defoverridable init: 2, matching: 1, format: 1, encode: 4, decode: 4

        unquote(Keyword.get(opts, :do, []))
      end
    end
  end

end

Possible usage:

import MyDB.Enum
defenum ColorsEnum, "colors_enum", ~w"blue red yellow"

ColorsEnum will be the module name, "colors_enum" will be the enum name internal to Postgresql: you will need to add a statement to create the enum type in your database migrations. The final argument is a list of enum values. I used a ~w sigil that will split the string by whitespace to show how concise this can be. I also added a clause that converts atom values to string values when they pass through an Ecto schema.

Heidelberg

Thanks for your answer! This looks promising, but I don't see how to use ColorsEnum in the schema. (I get the migration part). When I add the field in the schema, what type should I use? :string ? – Destrier

Yes, you should use :string in the actual schema definition. The reason why the Enum type is necessary for postgrex to do its thing, is that it maps the internal postgresql oid to the Elixir type, which is a string in this case. – Heidelberg

@Heidelberg While I respect your Elixir Fu, I personally don't need an Ecto type for each postgresql enum. Maybe I'm missing sth but why would you need it? Just use strings and a changeset validator hexdocs.pm/ecto/Ecto.Changeset.html#validate_inclusion/4 – Midweek

@DaveGoulash I can't remember if this predated changesets, but they definitely weren't quite popular yet. You still might want to use enums as an Ecto type depending on what API layer of Ecto you want to support. Changesets are just 1 API among many in Ecto, and you don't have to use them, and in practice, what if you forget to put your validate_inclusion on one changeset but not another? In modern practice now, you're right. Most people use nothing but the changeset API for updating or inserting data. – Heidelberg

@Heidelberg What if the programmer decides to do some updates bypassing Ecto? ;-) No I do know what your saying, but I'm OK with not catering for every possible input error in Elixir code. I guess I'm okay with Postgres being the final data guard for some scenarios. – Midweek

Answer 6

adding to what @JustMichael and @swennemen have said... as of ecto 2.2.6 we have Ecto.Migration.execute/2 which takes an up and a down arg. So we can do:

execute("create type post_status as enum ('published', 'editing')", "drop type post_status")

In our migration file inside the change block, and ecto will be able to rollback effectively.

Rizzi