Best Practices when working with Postgres Enum types #515
Description
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