How to make enum column to work with SQLModel? · Issue #96 · fastapi/sqlmodel · GitHub

First Check

I added a very descriptive title to this issue.
I used the GitHub search to find a similar issue and didn't find it.
I searched the SQLModel documentation, with the integrated search.
I already searched in Google "How to X in SQLModel" and didn't find any information.
I already read and followed all the tutorial in the docs and didn't find an answer.
I already checked if it is not related to SQLModel but to Pydantic.
I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

I commit to help with one of those options 👆

Example Code

from sqlmodel import SQLModel, Field, JSON, Enum, Column
from typing import Optional
from pydantic import BaseModel
from datetime import datetime

class TrainingStatus(str, enum.Enum):
    scheduled_for_training = "scheduled_for_training"
    training = "training"
    trained = "trained"

class model_profile(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    training_status: Column(Enum(TrainingStatus))
    model_version: str

Description

I am trying to create an enum column and use the value in the database. But I am getting this error:

RuntimeError: error checking inheritance of Column(None, Enum('scheduled_for_training', 'training', 'trained', name='trainingstatus'), table=None) (type: Column)

Does anyone knows how to help me? I tried

training_status: Column('value', Enum(TrainingStatus))

but it doesn't seem to work as I don't understand where the 'value' should be coming from 😓 I would really appreciate any input

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.7.9

Additional Context

No response

training_status: TrainingStatus = Field(sa_column=Column(Enum(TrainingStatus)))

Just make sure that if you're using Alembic migration autogeneration and you require values to be stored as Enum in the database and not String, you modify the column type in the generated migration script.

training_status: TrainingStatus = Field(sa_column=Column(Enum(TrainingStatus)))

Just make sure that if you're using Alembic migration autogeneration and you require values to be stored as Enum in the database and not String, you modify the column type in the generated migration script.

This worked for me. However, I think it would be a bit nicer if we could just specify

training_status: Enum[TrainingStatus]

or similar as the type.

Enum fixes (typing and inheritance)

Thank you!

training_status: TrainingStatus = Field(sa_column=Column(Enum(TrainingStatus)))

Just make sure that if you're using Alembic migration autogeneration and you require values to be stored as Enum in the database and not String, you modify the column type in the generated migration script.

Thanks @yasamoka This solution worked for me. Just one thing Enum on training_status: TrainingStatus = Field(sa_column=Column(Enum(TrainingStatus))) is imported from sqlmodel and Enum on TrainingStatus is imported from enum.Enum I lost some time until I realized this.

training_status: TrainingStatus = Field(sa_column=Column(Enum(TrainingStatus)))

Just make sure that if you're using Alembic migration autogeneration and you require values to be stored as Enum in the database and not String, you modify the column type in the generated migration script.

I do not get how I should "require values to be stored as Enum" when using postgresql engine for example. Is there a flag somewhere or how does that work?

I'm getting an error which looks like sqlmodel is trying to save the enum as a string:

sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input value for enum objecttypes: "type1"

I do not get how I should "require values to be stored as Enum" when using postgresql engine for example. Is there a flag somewhere or how does that work?

I'm getting an error which looks like sqlmodel is trying to save the enum as a string:
sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input value for enum objecttypes: "type1"

Here is a sample migration from a project of mine that shows how this is done. This is a migration script that changes a column from String to Enum.

"""language_fluency

Revision ID: 5342a2459462
Revises: 919490ea9497
Create Date: 2021-09-17 03:49:14.251234

"""
from alembic import op
import sqlalchemy as sa
import sqlmodel
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision = '5342a2459462'
down_revision = '919490ea9497'
branch_labels = None
depends_on = None

fluency = sa.Enum('WEAK', 'MODERATE', 'GOOD', name='fluency')


def upgrade():
    fluency.create(op.get_bind())
    op.alter_column('language', 'fluency', type_=fluency, postgresql_using='fluency::text::fluency')


def downgrade():
    op.alter_column('language', 'fluency', type_=sqlmodel.sql.sqltypes.AutoString())
    fluency.drop(op.get_bind())

do I really need a working alembic setup if I start from scratch? I can see that the table is created correctly but for some reason sqlmodel / psycopg2==2.9.3 are creating wrong queries. This is what the db looks like:

                    Table "public.items"
 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 type   | itemtypes         |           |          | 
 id     | uuid              |           | not null | 
 name   | character varying |           | not null |

Does alembic change how sqlmodel/sqlalchemy generates statements and passes the parameters to that?

What do you mean by starting from scratch? Do you mean that you have an existing database and you have yet to setup Alembic for migrations? If so, then no, you do not need a working alembic setup if you can manually define your column type correctly on the database schema itself directly.

Alembic doesn't change anything related to how SQLModel / SQLAlchemy generates statements. Alembic compares your existing database against the schema it reads from your code and generates schema migrations based on that. Regardless of whether those migrations are applied, it is SQLAlchemy that ultimately generates statements based on your models and tables as defined in your code. If there is still a mismatch between your actual database and the models / tables you have defined, then you get an error.

From scratch I mean that I create an empty postgresql server and use the SQLModel.metadata.create_all(engine) to create tables. I got this working with mysql so I guess the main issue I'm having is that sqlmodel does not create the postgresql enum correctly. Postgres shows

docker compose exec db psql -U postgres -c "select enum_range(null::itemtypes);"
 enum_range 
------------
 {}
(1 row)

Maybe alembic works by correcting that enum.

From scratch I mean that I create an empty postgresql server and use the SQLModel.metadata.create_all(engine) to create tables. I got this working with mysql so I guess the main issue I'm having is that sqlmodel does not create the postgresql enum correctly. Postgres shows
docker compose exec db psql -U postgres -c "select enum_range(null::itemtypes);"
 enum_range 
------------
 {}
(1 row)
Maybe alembic works by correcting that enum.

The Alembic migration I showed you was not autogenerated. It was written manually (alembic revision -m "message").

Hello, @alhoo I had the same problem and I noticed that the error happens if the table you are adding the new enum column already exists. In my case, I deleted the table and created a new migration and it worked, new types were created. I am not sure why alembic does not create a new enum type automatically on existing tables. This is not the best solution but works.

The following solution worked fine for me:

import enum

from sqlalchemy import (Column, Integer)
from sqlalchemy_utils import ChoiceType
from sqlmodel import SQLModel, Field

class LightType(enum.IntEnum):
    off = 0
    on = 1
    flashing = 2

class Events(SQLModel, table=True):
    green: LightType = Field(sa_column=Column(ChoiceType(LightType, impl=Integer()), nullable=False))

Thanks for the help everyone :)

@jonra1993 Using this approach how would you set a default value, either/both at the database level and sqlmodel level

EDIT: Got it, you can just set a default inside the Field(), ie, Field(default=SomeEnum.foo)

The solution provided here doesn't automatically create the enum type in case it is missing with postgresql, at least with my setting:

sqlmodel: 0.0.8
python: 3.10.11
macOS: Ventura 13.3.1 (22E261)

I am calling create manually (Enum(TrainingStatus).create(engine)) to fix this

@turangojayev could you please provide more details for your proposed fix? E.g. when and where exactly do you call create?

sorry for late response, well, I basically do

sa_TrainingStatus = Enum(TrainingStatus)

def init_db():
    sa_TrainingStatus.create(engine, checkfirst=True)
    SQLModel.metadata.create_all(engine)


@asynccontextmanager
async def lifespan(app: FastAPI):
    create_db_and_tables()
    yield


app = FastAPI(lifespan=lifespan)

If you want enum types to be recognized by alembic, do the following.
Installation

  SQLModel: 0.0.18 
  Python: 3.11

pip install alembic-postgresql-enum or poetry add alembic-postgresql-enum

Add import to envy.py
import alembic_postgresql_enum

When you update enum types, alembic will detect the changes and automatically generate migrations.

def upgrade() -> None:
    # ### commands auto-generated by Alembic - please adjust! ###
    op.sync_enum_values('public', 'channel', ['USSD', 'WHATSAPP'],
                        [('subscription', 'channel')],
                        enum_values_to_rename=[])
    # ### end Alembic commands ###


def downgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.sync_enum_values('public', 'channel', ['WHATSAPP'],
                    [('subscription', 'channel')],
                    enum_values_to_rename=[])
# ### end Alembic commands ###

Can we change the scheme of where the Enum type is stored?

I prefre to use sa_type:

training_status: TrainingStatus = Field(sa_type=Enum(TrainingStatus))

I prefre to use sa_type:

training_status: TrainingStatus = Field(sa_type=Enum(TrainingStatus))

this create the following error for me:

class NotificationPriority(Enum):
    LOW = "LOW"
    MEDIUM = "MEDIUM"
    HIGH = "HIGH"
    CRITICAL = "CRITICAL"
    
....    
File "/workspaces/api/src/n0tifs/models.py", line 255, in Notification
    priority: NotificationPriority = Field(sa_type=Enum(NotificationPriority))
                                                   ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/vscode/.local/lib/python3.12/site-packages/sqlalchemy/sql/sqltypes.py", line 1397, in __init__
    self._enum_init(enums, kw)
  File "/home/vscode/.local/lib/python3.12/site-packages/sqlalchemy/sql/sqltypes.py", line 1427, in _enum_init
    self._default_length = length = max(len(x) for x in self.enums)
                                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/vscode/.local/lib/python3.12/site-packages/sqlalchemy/sql/sqltypes.py", line 1427, in <genexpr>
    self._default_length = length = max(len(x) for x in self.enums)
                                        ^^^^^^
TypeError: object of type 'type' has no len()

@bashtian-fr I have the same issue SQLModel version 0.0.21

@bashtian-fr I have the same issue SQLModel version 0.0.21

Hey there, I solved it by doing the following:

import enum
from sqlmodel import Column, Enum, Field

class NotificationPriority(str, enum.Enum):
    LOW = "LOW"
    MEDIUM = "MEDIUM"
    HIGH = "HIGH"
    CRITICAL = "CRITICAL"
    
class Notification(TableMixin, NotificationBase, table=True):
    id: int | None = Field(default=None, primary_key=True)
    priority: NotificationPriority = Field(sa_column=Column(Enum(NotificationPriority)))

Note, that the enum class extend str AND Enum, which is from the enum module, while I use SQLModel Enum for the column

To summarize what helped me:

UseCase: I want to have an enum in python and the keys to use in Python should be UPPERCASE. The values should be different in the database and I want to reference them. Should be done with SQLModel and alembic.

Solution

import enum

from sqlmodel import INTEGER, Column, Enum, Field, SQLModel


def enum_values(enum_class: type[enum.Enum]) -> list:
    """Get values for enum."""
    return [status.value for status in enum_class]

# watch out which enum you use. This should be the python enum
class Alive(enum.Enum):
    NO = "no"
    YES = "yes"

 
# here the database representation, where a column `alive` will be created with - the values: [no, yes] of the enum, 
# instead of the keys `NO,YES`
class AliveDB(SQLModel, table=True):
    __tablename__ = "Alive"
    id: int | None = Field(
        sa_column=Column(
            name="alive_id",
            nullable=False,
            type_=INTEGER,
            primary_key=True,
            default=None,
        )
    )
    alive: Alive = Field(
        sa_column=Column(
            name="alive",
            nullable=False,
            type_= Enum(Alive, values_callable=enum_values),
        )
    )

from sqlmodel import Field, SQLModel, Enum
from sqlalchemy import Enum as SAEnum
import enum


class Statuses(str, enum.Enum):
    published = "published"
    draft = "draft"
    archived = "archived"

 status: Statuses = Field(
        sa_type=SAEnum(Statuses, name="statuses_enum"),
        sa_column_kwargs={"server_default": text(f"'{Statuses.published.value}'")},
        default=Statuses.published,
        nullable=False,
    )

I prefre to use sa_type:

training_status: TrainingStatus = Field(sa_type=Enum(TrainingStatus))

Yes! I forgot to mention, use Enum from sqlalchemy, not from python enum.

I also have an advance solution! That what I am doing in my project. Sqlalchemy has TypeDecorator which you can use to customize a Enum Type.

See my pr related to IntEnum column: #1337
Also, there is another pr about how to have a JSON type column: #1324

You can easily have your own StrEnum by using sqlalchemy TypeDecorator

How to make enum column to work with SQLModel? · Issue #96 · fastapi/sqlmodel · GitHub

Repository navigation

How to make enum column to work with SQLModel? #96

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

2 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

How to make enum column to work with SQLModel? · Issue #96 · fastapi/sqlmodel · GitHub

Repository navigation

How to make enum column to work with SQLModel? #96

Description

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Activity

yasamoka commented on Sep 16, 2021

JLHasson commented on Nov 20, 2021

haja-k commented on Dec 8, 2021

vargasjona commented on May 17, 2022

alhoo commented on May 31, 2022

yasamoka commented on May 31, 2022

alhoo commented on May 31, 2022

yasamoka commented on May 31, 2022

alhoo commented on May 31, 2022

yasamoka commented on May 31, 2022

vargasjona commented on Jun 2, 2022

malf2 commented on Sep 22, 2022

2 remaining items

haja-k commented on Nov 15, 2022

Pk13055 commented on Feb 23, 2023

turangojayev commented on Jul 26, 2023

christianholland commented on Jul 27, 2023

turangojayev commented on Aug 17, 2023

tjeaneric commented on Nov 14, 2023

dipsx commented on Apr 4, 2024

sunkunxi commented on Jul 3, 2024

bashtian-fr commented on Oct 2, 2024

Mekacher-Anis commented on Oct 19, 2024

Tyskiep99 commented on Oct 20, 2024

weitzj commented on Jan 25, 2025

kroawen commented on Dec 10, 2025

sunkunxi commented on Jan 31, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions