CODEWITHSUNDEEP
postgresqlenumselixirecto
Jean-Pierre Bécotte
Jean-Pierre Bécotte

Reputation: 508

How to use Postgres' enumerated type with Ecto

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?

Upvotes: 19

Views: 8900

Answers (6)

toraritte
toraritte

Reputation: 8303

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

Upvotes: 12

jnmandal
jnmandal

Reputation: 628

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.

Upvotes: 5

apelsinka223
apelsinka223

Reputation: 533

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

Upvotes: 5

swennemen
swennemen

Reputation: 955

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

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

Upvotes: 10

NoDisplayName
NoDisplayName

Reputation: 15746

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.

Upvotes: 30

asonge
asonge

Reputation: 1267

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.

Upvotes: 6

Related Questions