Best Practices when working with Postgres Enum types

[cirezd]

Hello,

I really like this project and want to use it more in the future. I use repositories and services and I have Enum types defined in Postgres. This is managed automatically using alembic-postgresql-enum. However, one aspect I am struggling with is converting from/to pydantic BaseModels to/from SqlAlchemy models. At the moment, I need to cast every Enum input to its name representation manually.

Example:

from sqlalchemy import Enum as SQLAlchemyEnum


class SearchOutputSensitivity(Enum):
    LOW = 0.2
    MEDIUM = 0.15
    HIGH = 0.1

class ApplicationDB(Base):
    __tablename__: str = "applications"

    search_output_sensitivity: Mapped[SearchOutputSensitivity] = mapped_column(
        SQLAlchemyEnum(SearchOutputSensitivity, name="search_output_sensitivity"),
        nullable=True,
    )

# conversion in service.py

class ApplicationService(
    SQLAlchemyAsyncRepositoryService[ApplicationDB, ApplicationRepository]
):
    repository_type = ApplicationRepository

    async def create_application(
            self,
            data: ApplicationAPICreate,
            created_by: DashboardActor,
        ) -> ApplicationAPI:    
            application_db = ApplicationDB(
                search_output_sensitivity=SearchOutputSensitivity[
                    data.search_output_sensitivity.name
                ]
            )
            application = await self.create(application_db, auto_commit=True)
            return self.to_schema(application, schema_type=ApplicationAPI)

It means that I cannot really use the utility methods such as to_schema or the create service method, because they don't do this conversion automatically. What is the best way to deal with this? If I have to do the conversion manually anyways and write basically custom service methods, then it removes a bit the advantages of using advanced_alchemy as opposed to just using SqlAlchemy itself. Any input or hints would be appreciated. Thanks

[cirezd]

I realized that the core issue is that Sqlalchemy stores the enum members/names, and not the values. Once I change it and dump the pydantic models to the repository methods, everything works.

[Harshal6927]

Hi, did you try just using python inbuilt enums?

class UserStatus(StrEnum):
    AVAILABLE = "available"
    CONNECTING = "connecting"
    BUSY = "busy"

class User(BigIntAuditBase):
    __tablename__ = "users"

    name: Mapped[str]
    email: Mapped[str]
    status: Mapped[UserStatus] = mapped_column(default=UserStatus.AVAILABLE)

If you do something like my example, you can just use UserStatus in your code.