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

Answer 1

There are two parts to make this happen with Postgres.

1. Specify create_type=False
2. 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))

Answer 2

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),