This blog was last modified 580 days before.

We have enum package in Python and we can use enum.Enum as a super class to create an Enum class like below:

from enum import Enum

class Status(Enum):
    PENDING = "pending"
    RECEIVED = "received"
    COMPLETED = "completed"

In SQLAlchemy 2.0.0b4, we can use Enum type with ORM class, and in 2.0.1 SQLAlchemy also support Literal type.

Today we just going to discuss Enum type with SQLAlchemy.

Use Enum with ORM Class

2 Ways to create an Enum member field

Take the Status class above as example, we can directly anno the type with Mapped[]:

class SomeClass(Base):
    __tablename__ = "some_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    status: Mapped[Status]

Or we can also use mapped_column() to declare it explicitly:

class SomeClass(Base):
    __tablename__ = "some_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    status: Mapped[Status] = mapped_coloum(sqlalchemy.Enum(Status, name='status_enum'))
    str_enum: Mapped[Status] = mapped_column(sqlalchemy.Enum("pending", "received", "completed", name="status_enum"))

Notice in this second way, we are able to configure more detail of this enum field. Here are some params we can set in the sqlalchemy.Enum().

• *args The first positional params. Could be Python Enum type class (Actaully it could be any class which has __members__ special method), or several str type values indicating different enums.
• name Name of the type created in database. If *args is an Enum class, then the class name will be used as a default.
• native_enum If use the backend ENUM type is possible, default to True
• create_constraint Only meaningful when native_enum=False. If this param is True, then SQLAlchemy will build a CHECK constraint on the database against the valid values. However in most cases of using Enum type, is no need for more detailed configuration, and the method 1 is sufficient.

Check SQLAlchemy Docs - Enum for more params info.

SQLAlchemy will take use of the backend native ENUM type if possible. Otherwise, SQLAlchemy will try to use VARCHAR type to presist the enum type.

Input and Output

For an enum field created from Python enum.Enum class type, both the input and output will be converted into the Enum class.

For an enum field created by several literal str objects, output will be the literal str type object. Notice you can also add check constraint by settings create_constraint=True when creating this field, check the guide above.

Dive into SQLAlchemy Enum

How SQLAlchemy stores enum in database

Maybe not same to what we thought, in default cases, SQLAlchemy persist enum in database by enum key but NOT enum value. Use above Status enum class for example, SQLAlchemy will use the enum key PENDING but NOT value pending as presist data.

Why SQLAlchemy do this? This is because the value of the enum may not be persistable in all cases, but key is always persistable using native backend ENUM type or a VARCHAR type. So by this you are free to set whatever things you like as the value of the enum while keep it working with SQLAlchemy Enum.

It's still possible to use enum value as the persist content. To achieve this, we should provide a Enum.values_callable manually. It returns a list of str object which would be used to be persisted in database. For a simple enum.Enum class with all value is str type, a simple lambda lambda x: [e.value for e in x] is sufficient.

Configuring Default Enum type behaviour

This feature only available in SQLAlchemy version >= 2.0.1

If you have read other guide of this series, you may familiar with type_annotation_map. We can also use it to alter the default settings of the Enum type in ORM classes, check code example below:

class Base(DeclarativeBase):
    type_annotation_map = {
        enum.Enum: sqlalchemy.Enum(enum.Enum, native_enum=False),
        Status: sqlalchemy.Enum(enum.Enum, native_enum=False),
    }

With this code, we have changed the default settings of native_enum to False for all Enum type in ORM classes. That means if you don't explicitly set, all enum will not use native backend ENUM type now.

Also, you can change the dict key to control the scope, in the second line of the dict, we use Status as key, which means we will change the default settings for enum class with Status type anno.