How to add Enum field? Sqlalchemy

Question

I need to add user_type to User model. But always getting sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError)

models/user.py

class UserType(enum.Enum):
    USER = "USER"
    SELLER = "SELLER"
    ADMIN = "ADMIN"

class User(ModelBase):
    __tablename__ = "user"
    username = Column(String(64), nullable=False)
    email = Column(String(64), nullable=False)
    phone_number = Column(String(32), unique=True, nullable=False)
    password = Column(String(128))
    avatar = Column(JSONB, nullable=True)
    address = Column(String(128), nullable=True)
    user_type = Column(Enum(UserType, name="user_type"), default=UserType.USER)  <-- here

alembic migration file

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user', sa.Column('user_type', sa.Enum('USER', 'SELLER', 'ADMIN', name='user_type'), nullable=True))
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('user', 'user_type')
    # ### end Alembic commands ###

Running alembic upgrade head gives me next:

sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.UndefinedObjectError'>: type "user_type" does
not exist
[SQL: ALTER TABLE "user" ADD COLUMN user_type user_type]
(Background on this error at: https://sqlalche.me/e/20/f405)

What am I doing wrong?

Does this answer your question? [sqlalchemy postgresql enum does not create type on db migrate](https://stackoverflow.com/questions/37848815/sqlalchemy-postgresql-enum-does-not-create-type-on-db-migrate) — BDL, Jul 12 '23 at 12:15

score 0 · Answer 1 · answered Aug 08 '23 at 18:56

You need to create enum type before using it in new column

user_type_enum = sa.Enum('USER', 'SELLER', 'ADMIN', name="user_type")

def upgrade():
    user_type_enum.create(op.get_bind())
    op.add_column('user', user_type_enum, nullable=True))

def downgrade():
    op.drop_column("task", "task_type")
    user_type_enum.drop(op.get_bind())

Also there is a possibility to automate adding and removing enums using alembic-postgresql-enum library.

Installation:

pip install alembic-postgresql-enum

Add import to env.py

import alembic_postgresql_enum

Migration supplied above will be generated automagically

How to add Enum field? Sqlalchemy

1 Answers1