sqlalchemy postgresql enum does not create type on db migrate

Question

I develop a web-app using Flask under Python3. I have a problem with postgresql enum type on db migrate/upgrade.

I added a column "status" to model:

class Banner(db.Model):
    ...
    status = db.Column(db.Enum('active', 'inactive', 'archive', name='banner_status'))
    ...

Generated migration by python manage.py db migrate is:

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('banner', sa.Column('status', sa.Enum('active', 'inactive', 'archive', name='banner_status'), nullable=True))

def downgrade():
    op.drop_column('banner', 'status')

And when I do python manage.py db upgrade I get an error:

...
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) type "banner_status" does not exist
LINE 1: ALTER TABLE banner ADD COLUMN status banner_status

 [SQL: 'ALTER TABLE banner ADD COLUMN status banner_status']

Why migration does not create a type "banner_status"?

What am I doing wrong?

$ pip freeze
alembic==0.8.6
Flask==0.10.1
Flask-Fixtures==0.3.3
Flask-Login==0.3.2
Flask-Migrate==1.8.0
Flask-Script==2.0.5
Flask-SQLAlchemy==2.1
itsdangerous==0.24
Jinja2==2.8
Mako==1.0.4
MarkupSafe==0.23
psycopg2==2.6.1
python-editor==1.0
requests==2.10.0
SQLAlchemy==1.0.13
Werkzeug==0.11.9

From reading stackoverflow.com/questions/14845203/… and bitbucket.org/zzzeek/alembic/issues/67/… it'd seem you might have to manually create and drop the enums. — Ilja Everilä
– Ilja Everilä, Commented Jun 16, 2016 at 9:28

I159 · Accepted Answer · 2022-06-27 08:44:31Z

145

I decided on this problem using that.

I changed the code of migration, and migration looks like this:

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

def upgrade():
    banner_status = postgresql.ENUM('active', 'inactive', 'archive', name='banner_status')
    banner_status.create(op.get_bind())

    op.add_column('banner', sa.Column('status', sa.Enum('active', 'inactive', 'archive', name='banner_status'), nullable=True))

def downgrade():
    op.drop_column('banner', 'status')

    banner_status = postgresql.ENUM('active', 'inactive', 'archive', name='banner_status')
    banner_status.drop(op.get_bind())

And now python manage.py db upgrade\downgrade is successfully executed.

edited Jun 27, 2022 at 8:44

I159

31.4k32 gold badges102 silver badges138 bronze badges

answered Jun 16, 2016 at 13:32

Alexey Egorov

2,4402 gold badges20 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Geekfish Over a year ago

For the downgrade you can also avoid duplication by executing SQL directly (at least for postgres): op.execute("DROP TYPE banner_status;")

radzak Over a year ago

@Geekfish Why not move postgresql.ENUM('active', 'inactive', 'archive', name='banner_status') outside of the upgrade and downgrade methods? Would there be any downsides?

Geekfish Over a year ago

@Jatimir it's been a while since I looked into it but your suggestion it's probably fine!

kevlarr Over a year ago

You can actually pass banner_status as the column type directly, rather than redefining it via sa.Enum(...)

UselesssCat Over a year ago

you dont need to declare all enumeration options on downgrade, just bind = op.get_bind() then sa.Enum(name='banner_status').drop(bind, checkfirst=False)

Tri · Accepted Answer · 2019-08-04 14:18:46Z

4

I think this way is more simple:

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

def upgrade():
    # others_column = ...
    banner_status = postgresql.ENUM('active', 'inactive', 'archive', name='banner_status', create_type=False), nullable=False)

Also added the postgresql.ENUM to your downgrade() function if that needed.

answered Aug 4, 2019 at 14:18

Tri

3,1096 gold badges43 silver badges73 bronze badges

1 Comment

Cesar Flores Over a year ago

What happens if you do need to create the TYPE at upgrade?

Collectives™ on Stack Overflow

sqlalchemy postgresql enum does not create type on db migrate

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related