alembic
alembic copied to clipboard
sqlalchemy
op.add_column() and op.drop_column() should check for SchemaType, run through the events for that type, or perhaps just create/drop
Migrated issue, originally created by Wichert Akkerman (@wichert)
I have a data model which has a simple enum column:
size_type = schema.Column(
types.Enum('normal', 'plus', name='size_type_type'),
nullable=False, default='normal')
as part of an upgrade routine I need to add that column if it is missing:
alembic.add_column('article_variant',
ArticleVariant.__table__.c['size_type'].copy())
alembic is an operation instance here. This operation breaks due to a missing check if the enum type already exists. The resulting error is:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) type "size_type_type" does not exist
LINE 1: ALTER TABLE article_variant ADD COLUMN size_type size_type_t...
^
'ALTER TABLE article_variant ADD COLUMN size_type size_type_type NOT NULL' {}
Wichert Akkerman (@wichert) wrote:
In case it is useful I added a local workaround which adds the type if it is missing:
context = alembic.get_context()
if context.bind.dialect.name == 'postgresql':
has_size_type = context.bind.execute(
"select exists (select 1 from pg_type "
"where typname='size_type')").scalar()
if not has_size_type:
alembic.execute("CREATE TYPE size_type AS ENUM ('normal', 'plus')")
Michael Bayer (@zzzeek) wrote:
The creation/dropping of the associated type for ENUM and on some platforms boolean is a "schema event" which responds to metadata or Table oncreate/drop. There's currently not an event for "on column add/drop". So Alembic's add_column() and drop_column() should probably run through the "on table create/drop" events for the type.
What you should do right now is use the create() method on the enumerated type:
ArticleVariant.table.c.size_type.create(alembic.get_bind(), checkfirst=True)
Michael Bayer (@zzzeek) wrote:
correction:
bind = alembic.get_bind()
typ = ArticleVariant.__table__.c.size_type.type
impl = typ.dialect_impl(bind.dialect)
impl.create(bind, checkfirst=True)
or this:
bind = alembic.get_bind()
typ = ArticleVariant.__table__.c.size_type.type
typ._on_table_create(ArticleVariant.__table__, bind)
sc68cal (@sc68cal) wrote:
Just want to close the loop - I used a similar bit of code that @wichert posted for a migration in OpenStack Neutron - just don't forget to also add code to the downgrade part of your script to delete the type
Anonymous wrote:
Slightly different for me I had to do
bind = op.get_bind()
typ = ArticleVariant.__table__.c.size_type.type
typ._on_table_create(ArticleVariant.__table__, bind, checkfirst=True)
Maik Derstappen (@mrtango) wrote:
I have created a helper file which use the reflection.Inspector and the get_columns method. This also works for checking befor dropping a column. http://www.derstappen-it.de/tech-blog/sqlalchemie-alembic-check-if-table-has-column
But it would be very usefull to have that in the alembic package.
Changes by Michael Bayer (@zzzeek):
- changed title from "Adding column with native enum fails" to "op.add_column() and op.drop_column() should check "
please see https://github.com/sqlalchemy/alembic/issues/868#issuecomment-867914756 for rationale why none of these issues are resolved.
Easy workaround:
def downgrade():
op.execute("""DROP TYPE name_of_enum""")
Alternative workaround:
from alembic import op
import sqlalchemy as sa
def downgrade():
sa.Enum(name="name_of_enum").drop(op.get_bind(), checkfirst=False)
*Answer copied from here: https://github.com/miguelgrinberg/Flask-Migrate/issues/48
