How to use an existing sqlalchemy Enum in an Alembic migration (Postgres)

Question

At some point in the past I've run an alembic migration which creates a users table like...

def upgrade():
    ...
    op.create_table(
        "users",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        ...
        sa.Column("type", sa.Enum("Foo", "Bar", "Baz", name="usertype"), nullable=False),
        ...
    )
    ...

...which automatically creates the enum named usertype with the values "Foo", "Bar", "Baz".

Now, I want to make some other table which also references that same enum. e.g.,

def upgrade():
    ...
    op.create_table('foobar',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        ...
        sa.Column('user_type', sa.Enum(< ???????? >), nullable=False),
        ...
    )

What is the syntax to reference the existing enum?

I can't seem to find an answer in the docs: https://docs.sqlalchemy.org/en/13/core/type_basics.html#sqlalchemy.types.Enum

score 29 · Answer 1 · edited Jul 21 '22 at 06:03

29

There are two parts to make this happen with Postgres.

Specify create_type=False
Use sqlalchemy.dialects.postgresql.ENUM (not sqlalchemy.Enum)

Eg:

from sqlalchemy.dialects import postgresql

sa.Column('my_column', postgresql.ENUM(name='my_enum', create_type=False))

edited Jul 21 '22 at 06:03

Alex

9,313
1
39
44

answered Feb 11 '21 at 00:28

Dean

782
7
15

Can the enum's values be obtained through introspection so they don't need to be repeated in the migration? – pdoherty926 Oct 08 '21 at 18:18
2

I haven't tried it, but I think you may be able to omit the enum values: `postgresql.ENUM(name='my_enum', create_type=False)`. – Dean Oct 10 '21 at 02:40
1

Looks like you can, will update my answer – Dean Oct 12 '21 at 23:18

score 5 · Answer 2 · answered Sep 23 '22 at 09:12

You might need to pass enum object instead of its name as string.

    entity = Column(
        postgresql.ENUM(
            SocialType,
            create_type=False,
            checkfirst=True,
            inherit_schema=True,
        )
    )

checkfirst=True and create_type=False doesn't get detected by alembic. So you need to add it manually. Finally, alembic migration should look like

sa.Column('entity', postgresql.ENUM('github', 'twitter', name='socialtype', schema='dashboard', inherit_schema=True, create_type=False, checkfirst=True), nullable=True),

that's what I found too - create_type is ignored by alembic. — Alleo, Oct 04 '22 at 07:23

score -1 · Answer 3 · answered Sep 27 '22 at 01:50

-1

For a simple, effective, and backend agnostic solution to this problem please see my answer in another thread.

answered Sep 27 '22 at 01:50

Brent

1,195
10
9

score -3 · Answer 4 · answered Jan 24 '21 at 19:41

-3

Couldn't find much information on how to fix this error but here is what you need to do.

after you autogenerated the migration just add create_type=False, to the enum field in the migration file.

sa.Column('user_type', sa.Enum(< ???????? >, create_type=False), nullable=False),

answered Jan 24 '21 at 19:41

Ali Kazai

383
3
7

1

sa.Enum does not have that keyword argument. – Alex Grönholm Nov 25 '21 at 12:27

How to use an existing sqlalchemy Enum in an Alembic migration (Postgres)

4 Answers4

Linked