Ecto Schema: Enum Data Type?

Questions / Help
,
1

Is the only way to support enum columns in an Ecto schema by using a 3rd party package like https://github.com/gjaldon/ecto_enum ?

Thanks!

2

No. You just specify the enum column as string in the schema.

I can’t tell you exactly what the migration will look like; we have two projects where we use enums pretty heavily. The first project uses Ecto, Ecto migrations, and Ecto.Enum. The second uses Ecto, Sqitch (which uses straight-up SQL), and does not use Ecto.Enum.

Based on our experiences, I would recommend not using Ecto.Enum if you’re using PostgreSQL. There’s nothing explicitly wrong with the library itself (the documentation is a different matter), but it provides exactly two conveniences:

  1. The ability to specify enums in queries using atoms instead of binaries (:active instead of "active").

  2. The ability to create new enums without knowing the PostgreSQL ‘magic’ (which isn’t really that magic). This really isn’t that hard:

    def up do
  execute ~s"""
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'user_status') THEN
    CREATE TYPE user_status AS ENUM ('active', 'inactive');
  END IF;
  """
end

    Note that you have to drop to SQL anyway to add a new value to an enum, and the Ecto.Enum documentation is flat out wrong about the syntax to use (it will cause your migrations to fail if you have to roll back at all, because there’s no possible down phase to ALTER TYPE type ADD VALUE 'value'). Instead of what the Ecto.Enum documentation recommends, use ALTER TYPE type ADD VALUE IF NOT EXISTS 'value' (I submitted this change as a PR, but the maintainer rejected this for some reason).

3 Likes
3

Maybe my comment on EctoEnum for ActiveRecord-like enums in Ecto updated! would help.

4

I used enum in rails before, once upon a time, a co-worker just inserted a value in the middle of the enum array and it destroyed the data. :+1:

4 Likes
5

Were you using postgres?

idiot@/tmp:enum_test> create type hello_type as enum ('a', 'b', 'c');
CREATE TYPE
Time: 0.021s
idiot@/tmp:enum_test> create table test (name varchar, type hello_type);
CREATE TABLE
Time: 0.018s
idiot@/tmp:enum_test> insert into test values ('hello', 'a');
INSERT 0 1
Time: 0.007s
idiot@/tmp:enum_test> insert into test values ('hello', 'b');
INSERT 0 1
Time: 0.001s
idiot@/tmp:enum_test> insert into test values ('hello', 'c');
INSERT 0 1
Time: 0.001s
idiot@/tmp:enum_test> select * from test;
+--------+--------+
| name   | type   |
|--------+--------|
| hello  | a      |
| hello  | b      |
| hello  | c      |
+--------+--------+
SELECT 3
Time: 0.008s
idiot@/tmp:enum_test> alter type hello_type add value 'd' after 'a';
ALTER TYPE
Time: 0.004s
idiot@/tmp:enum_test> insert into test values ('hello', 'd');
INSERT 0 1
Time: 0.001s
idiot@/tmp:enum_test> select * from test;
+--------+--------+
| name   | type   |
|--------+--------|
| hello  | a      |
| hello  | b      |
| hello  | c      |
| hello  | d      |
+--------+--------+
SELECT 4
Time: 0.008s

Inserting new enum values works fine there. Deleting them is a bit more involved, but not by much, and it definitely wouldn’t destroy your data if done properly. Instead, using enum types would help keeping it consistent.

6

This is the reason you should always specify the numeric values in an enum. (This is true for both Ruby and any other language with numeric enums like any C-like language)

2 Likes
7

yea, should have used hash enum.

8

Thank you all for the replies. I think you’ve convinced me to just add a separate normalized table where I can map the ids to human-readable names. That will make it easier for any BI reporting anyhow.