How should I declare enums in SQLAlchemy using mapped_column (to enable type hinting)?

I am trying to use Enums in SQLAlchemy 2.0 with mapped_column. So far I have the following code (taken from another question):

from sqlalchemy.dialects.postgresql import ENUM as pgEnum
import enum

class CampaignStatus(str, enum.Enum):
    activated = "activated"
    deactivated = "deactivated"

CampaignStatusType: pgEnum = pgEnum(
    CampaignStatus,
    name="campaignstatus",
    create_constraint=True,
    metadata=Base.metadata,
    validate_strings=True,
)

class Campaign(Base):
    __tablename__ = "campaign"

    id: Mapped[UUID] = mapped_column(primary_key=True, default=uuid4)
    created_at: Mapped[dt.datetime] = mapped_column(default=dt.datetime.now)
    status: Mapped[CampaignStatusType] = mapped_column(nullable=False)

However, that gives the following error upon the construction of the Campaign class itself.

Traceback (most recent call last):
  File "<stdin>", line 27, in <module>
    class Campaign(Base):
...
AttributeError: 'ENUM' object has no attribute '__mro__'

Any hint about how to make this work?

The response from https://stackoverflow.com/questions/28894257/enum-type-in-sqlalchemy-with-postgresql does not apply as I am using version 2 of SQLAlchemy and those answers did not use mapped_column or Mapped types. Also, removing str from CampaignStatus does not help.

The crux of the issue relating to __mro__ causing the AttributeError is that CampaignStatusType is not a class, but rather an instance variable of type sqlalchemy.dialects.postgresql.ENUM (using pyright may verify this - given that it complains about Mapped[CampaignStatusType] being an "Illegal type annotation: variable not allowed unless it is a type alias"). As a test, replacing the type annotation for status with Mapped[CampaignStatus] does resolve the issue (and pyright reports no errors), but that does not hook the column type to the enum with postgresql dialect that is desired.

So the only way around this while using the dialect specific enum type is to use the non-annotated construct:

    status = mapped_column(CampaignStatusType, nullable=False)

However, if type annotation is still desired, i.e. whatever being Mapped must be a type, and that sqlalchemy.dialects.postgresql.ENUM (which was imported as pgEnum) is the underlying type for the instance CampaignStatusType, it may be thought that the following might be a solution

    # don't do this erroneous example despite it does run
    status: Mapped[sqlalchemy.dialects.postgresql.ENUM] = mapped_column(
        CampaignStatusType,
        nullable=False,
    )

While it works, it does NOT actually reflect what will be represented by the data, so DO NOT actually do that. Moreover, it only works because the type annotation is ignored when the specific column type is passed, so putting anything in there will work while having an invalid type.

Now, given that SQLAlchemy is now 2.0 (as the question explicitly want this newer version), perhaps reviewing the documentation and see now native enums should be handled now.

Adapting the examples in the documentation, the following MVCE may now be derived, using all the intended keyword arguments that was passed to the PostgreSQL dialect specific ENUM type passed generic sqlalchemy.Enum instead (aside from metadata=Base.metadata as that's completely superfluous):

from typing import Literal
from typing import get_args
from sqlalchemy import Enum
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column

CampaignStatus = Literal["activated", "deactivated"]

class Base(DeclarativeBase):
    pass

class Campaign(Base):
    __tablename__ = "campaign"
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    status: Mapped[CampaignStatus] = mapped_column(Enum(
        *get_args(CampaignStatus),
        name="campaignstatus",
        create_constraint=True,
        validate_strings=True,
    ))

Note the use of typing.get_args on CampaignStatus and splat it to the Enum here as opposed to what the official examples have done in repeating themselves. Now to include the usage:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

def main():
    engine = create_engine('postgresql://postgres@localhost/postgres')
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    session.add(Campaign(status='activated'))
    session.add(Campaign(status='deactivated'))
    session.commit()

    s = 'some_unvalidated_string'
    try:
        session.add(Campaign(status=s))
        session.commit()
    except Exception:
        print("failed to insert with %r" % s)

if __name__ == '__main__':
    main()

The above will produce failed to insert with 'some_unvalidated_string' as the output, showing that unvalidated strings will not be inserted, while validated strings that are mapped to some enum are inserted without issues. Moreover, pyright will not produce errors (though honestly, this is not necessarily a good metric because type hinting in Python is still fairly half-baked, as pyright did not detect the erroneous example as an error in the very beginning no matter what went inside Mapped, but I digress).

Viewing the newly created entities using psql

postgres=# select * from campaign;
 id |   status    
----+-------------
  1 | activated
  2 | deactivated
(2 rows)

postgres=# \dt campaign;
                                Table "public.campaign"
 Column |      Type      | Collation | Nullable |               Default                
--------+----------------+-----------+----------+--------------------------------------
 id     | integer        |           | not null | nextval('campaign_id_seq'::regclass)
 status | campaignstatus |           | not null | 
Indexes:
    "campaign_pkey" PRIMARY KEY, btree (id)

postgres=# \dT+ campaignstatus;
                                             List of data types
 Schema |      Name      | Internal name  | Size |  Elements   |  Owner   | Access privileges | Description 
--------+----------------+----------------+------+-------------+----------+-------------------+-------------
 public | campaignstatus | campaignstatus | 4    | activated  +| postgres |                   | 
        |                |                |      | deactivated |          |                   | 
(1 row)

The enum of course cannot be dropped without dropping the campaign table:

postgres=# drop type campaignstatus;
ERROR:  cannot drop type campaignstatus because other objects depend on it
DETAIL:  column status of table campaign depends on type campaignstatus
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

So the enum more or less behaves as expected despite only using generic SQLAlchemy types, without needing dialect specific imports.