Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use Enum with SQLAlchemy and Alembic?

Tags:

Here's my Post model:

class Post(Base):
    __tablename__ = 'posts'

    title = db.Column(db.String(120), nullable=False)
    description = db.Column(db.String(2048), nullable=False)

I'd like to add Enum status to it. So, I've created a new Enum:

import enum

class PostStatus(enum.Enum):
    DRAFT='draft'
    APPROVE='approve'
    PUBLISHED='published'

And added a new field to model:

class Post(Base):
    ...
    status = db.Column(db.Enum(PostStatus), nullable=False, default=PostStatus.DRAFT.value, server_default=PostStatus.DRAFT.value)

After doing FLASK_APP=server.py flask db migrate, a such migration was generated:

def upgrade():
    op.add_column('posts', sa.Column('status', sa.Enum('DRAFT', 'APPROVE', 'PUBLISHED', name='poststatus'), server_default='draft', nullable=False))

After trying to upgrade DB, I'm getting:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) type "poststatus" does not exist
LINE 1: ALTER TABLE posts ADD COLUMN status poststatus DEFAULT 'draf...
                                            ^
 [SQL: "ALTER TABLE posts ADD COLUMN status poststatus DEFAULT 'draft' NOT NULL"]
  1. Why type poststatus was not created on DB-level automatically? In the similar migration it was.
  2. How to specify server_default option properly? I need both ORM-level defaults and DB-level ones, because I'm altering existing rows, so ORM defaults are not applied.
  3. Why real values in DB are 'DRAFT', 'APPROVE', 'PUBLISHED', but not draft, etc? I supposed there should be ENUM values, not names.

Thank you in advance.

like image 217
f1nn Avatar asked Nov 09 '17 15:11

f1nn


People also ask

What is enum in SQLAlchemy?

Enum is a class within the sqlalchemy. types module of the SQLAlchemy project.

How do I select enum type in postgresql?

Enumerated types can be selected in the same way as standard data types. You can pick the Enum type from the datatype drop-down. Tip: A default value can be defined for the column. Expand the column detail by clicking the arrow icon and specify the value in the Default value field.

How do I downgrade my alembic?

If you want to run to downgrade() of a version, you will need to run alembic downgrade the-version-before-it , which mean it will revert to the version after the version that you want to downgrade. Which is the version before the version that we want to revert.


1 Answers

Why real values in DB are 'DRAFT', 'APPROVE', 'PUBLISHED', but not draft, etc? I supposed there should be ENUM values, not names.

As Peter Bašista's already mentioned SQLAlchemy uses the enum names (DRAFT, APPROVE, PUBLISHED) in the database. I assume that was done because the enum values ("draft", "approve", ...) can be arbitrary types in Python and they are not guaranteed to be unique (unless @unique is used).

However since SQLAlchemy 1.2.3 the Enum class accepts a parameter values_callable which can be used to store enum values in the database:

    status = db.Column(
        db.Enum(PostStatus, values_callable=lambda obj: [e.value for e in obj]),
        nullable=False,
        default=PostStatus.DRAFT.value,
        server_default=PostStatus.DRAFT.value
    )

Why type poststatus was not created on DB-level automatically? In the similar migration it was.

I think basically you are hitting a limitation of alembic: It won't handle enums on PostgreSQL correctly in some cases. I suspect the main issue in your case is Autogenerate doesn't correctly handle postgresql enums #278.

I noticed that the type is created correctly if I use alembic.op.create_table so my workaround is basically:

enum_type = SQLEnum(PostStatus, values_callable=lambda enum: [e.value for e in enum])
op.create_table(
    '_dummy',
    sa.Column('id', Integer, primary_key=True),
    sa.Column('status', enum_type)
)
op.drop_table('_dummy')
c_status = Column('status', enum_type, nullable=False)
add_column('posts', c_status)
like image 193
Felix Schwarz Avatar answered Sep 19 '22 19:09

Felix Schwarz