alembic
alembic copied to clipboard
sqlalchemy
Autogenerate doesn't correctly handle postgresql enums
Migrated issue, originally created by Ulrich Petri (@ulope)
According to #67 enum types used in columns have to be explicitly created and destroyed.
However the autogenerator currently doesn't produce those explicit commands. This problem is made worse by the fact that the autogenerated migration appears to work fine the first time it is applied and even removed. The dangling enum type only causes a conflict the second time the migration is applied.
edit by mike: note also that anything we do for PG native enums also has to run outside of a transaction block, so this is really not going to be easy. #123 provides the feature for this however if autogenerate, or the operation itself, is going to make that happen automatically, that's a very dramatic behavior.
Michael Bayer (@zzzeek) wrote:
#67 is probably not a great built-in solution to this issue, as that solution does not accommodate for the fact that ENUM has different implementations on different backends.
The issue here might be mostly the same as #122. The issue of PG ENUM is mostly unhandled as there are many issues and use cases to be worked out. The emission of the CREATE/DROP might be best automated as within the handling of Enum itself, but then this is tricky as in #122 the type might already exist or not.
Basically I don't have a solution to this right now which covers every case and that is the majority of work to be done.
Leonardo (@leotada) wrote:
Why does not check if the type exists and tries to create if not, or alter if the name is the same. https://stackoverflow.com/questions/7624919/check-if-a-user-defined-type-already-exists-in-postgresql
Michael Bayer (@zzzeek) wrote:
@leotada sure. The work is, "works with the generic Enum as well as PG ENUM, doesn't interfere with other backends, has plenty of functional tests".
Changes by Michael Bayer (@zzzeek):
- removed labels: autogenerate - rendering
- added labels: postgresql typing issues
Changes by Michael Bayer (@zzzeek):
- changed title from "Autogenerate doesn't correctly handle enums" to "Autogenerate doesn't correctly handle postgresql e"
I'm quite confused right now.
Issue #471 was closed because it was deemed a duplicate of this issue (which is specific to postgresql ENUMs).
But downgrade scripts aren't being generated for sqlalchemy.Enums either.
But downgrade scripts aren't being generated for
sqlalchemy.Enums either.
A generic Enum is a VARCHAR in the database. If you add or remove items to the enum, there is no datatype to be changed. There is a CHECK constraint, unfortunately, which Alembic does not have support for right now, support for this would be #363.
Lot of dupes but hard to find information for how to do this yourself. Here's an example of a migration to add a postgres enum to a pre-existing table.
def upgrade():
status_enum = postgresql.ENUM('pending', 'accepted', 'canceled', name='mytable_status_enum')
status_enum.create(op.get_bind())
op.add_column('mytable', sa.Column('status', status_enum))
def downgrade():
op.drop_column('mytable', 'status')
op.execute("DROP TYPE mytable_status_enum;")
Workaround to reuse enum in backend-agnostic (I hope) case:
def upgrade():
connection = op.get_bind()
if connection.dialect.name == "postgresql":
status_enum = postgresql.ENUM('pending', 'accepted', 'canceled', name='mytable_status_enum', create_type=False)
else:
status_enum = sa.Enum('pending', 'accepted', 'canceled', name='mytable_status_enum')
op.add_column('mytable', sa.Column('status', status_enum))
Workaround based on @zzzeek's answer from sqlalchemy/sqlalchemy#5593:
def upgrade():
status_enum_postgres = postgresql.ENUM('pending', 'accepted', 'canceled', name='mytable_status_enum', create_type=False)
status_enum = sa.Enum('pending', 'accepted', 'canceled', name='mytable_status_enum')
status_enum.with_variant(status_enum_postgres, 'postgresql')
op.add_column('mytable', sa.Column('status', status_enum))
It seems to be working fine.
Workaround based on @zzzeek's answer from sqlalchemy/sqlalchemy#5593:
def upgrade(): status_enum_postgres = postgresql.ENUM('pending', 'accepted', 'canceled', name='mytable_status_enum', create_type=False) status_enum = sa.Enum('pending', 'accepted', 'canceled', name='mytable_status_enum') status_enum.with_variant(status_enum_postgres, 'postgresql') op.add_column('mytable', sa.Column('status', status_enum))It seems to be working fine.
This solution seems to not work for that latest version 1.5.8. We tried this codes:
status_enum_postgres = postgresql.ENUM('pending', 'accepted', 'canceled', name='emotion', create_type=False)
status_enum_postgres.create(op.get_bind(), checkfirst=False)
op.add_column('mytable', sa.Column('status', status_enum_postgres, nullable=True))
It works great.
@JohnnyFee My solution works for me at alembic 1.5.8, but I still use sqlalchemy 1.3, which may be the difference. I still encounter type "mytable_status_enum" already exists error, when I try your solution.
Is there a way to just reuse the enum? or should I create another different one for each table?
like I have this enum: enabled, disabled, pending
In lots of different places, the best way would be to create a different enum for each one?
Is there a way to just reuse the enum? or should I create another different one for each table?
like I have this enum: enabled, disabled, pending
In lots of different places, the best way would be to create a different enum for each one?
you can make just one Enum object and reuse it, sure. In PostgreSQL, the enum is a totally separate construct independent of the table.
Workaround based on @zzzeek's answer from sqlalchemy/sqlalchemy#5593:
def upgrade(): status_enum_postgres = postgresql.ENUM('pending', 'accepted', 'canceled', name='mytable_status_enum', create_type=False) status_enum = sa.Enum('pending', 'accepted', 'canceled', name='mytable_status_enum') status_enum.with_variant(status_enum_postgres, 'postgresql') op.add_column('mytable', sa.Column('status', status_enum))It seems to be working fine.
This solution seems to not work for that latest version 1.5.8.
@JohnnyFee (and for posterity) - the snippet which @ziima posted appears to not work for me also (sqla 1.4.22, alembic 1.6.5), but it's because with_variant() actually returns the variant type, rather than modifying the type in place. I didn't follow your approach because I didn't want to do postgres stuff directly in the migration.
My specific use case is to use an existing enum for a column in a new table which is being constructed from an auto-generated migration. I've found that I can use the with_variant() trick like so (with imports included to make it more clear to other new folks where ENUM() comes from):
import sqlalchemy as sa
import sqlalchemy.dialects.postgresql as sad_psql
def upgrade():
vals, name = ("foo", "bar", "baz"), "my_enum"
sa_enum = sa.Enum(*vals, name=name)
psql_enum = sad_psql.ENUM(*vals, name=name, create_type=False)
variant_enum = sa_enum.with_variant(psql_enum, "postgresql")
# Now use `variant_enum` anywhere the autogenerated `sa.Enum()` is used below
# e.g.
op.create_table("my_table",
sa.Column("val", variant_enum),
)
This works for my case where the revision history ensures that the enum is already present in the database. I'm not confident that there's an easy way to create the enum only if necessary while creating a table since passing checkfirst=True down to the enum looks complicated. The variant approach is quite nice since it allows us to avoid doing anything postgres-y unless the migration is actually running against a backend using that dialect, but the logic is well-defined for that case.
I've also toyed with the idea of creating the table and then immediately doing an op.add_column() without specifically calling enum_type.create(op.get_bind()), since I know it's already defined and the column can be added without error. I prefer including it in the table creation using the variant trick, but if I were to have to add a column to an existing table it could look like:
def upgrade():
vals, name = ("foo", "bar", "baz"), "my_enum"
# This works if the enum is already defined since adding a column doesn't attempt to create the enum
op.add_column("my_table", sa.Column("val", sa.Enum(*vals, name=name)))
or:
def upgrade():
vals, name = ("foo", "bar", "baz"), "my_enum"
sa_enum = sa.Enum(*vals, name=name)
# This will create the enum if it's not already present which is helpful
sa_enum.create(op.get_bind(), checkfirst=True)
op.add_column("my_table", sa.Column(sa_enum))
or even:
def upgrade():
vals, name = ("foo", "bar", "baz"), "my_enum"
sa_enum = sa.Enum(*vals, name=name)
psql_enum = sad_psql.ENUM(*vals, name=name, create_type=False)
variant_enum = sa_enum.with_variant(psql_enum, "postgresql")
# This doesn't seem to provide much value beyond an `sa.Enum().create(.., checkfirst=True)
# but maybe there's an edge case where it'd be useful - or it could be preferable to always use
# the variant type to stay DRY?
op.add_column("my_table", sa.Column(variant_enum))
I'm using the second version proposed by @maybe-sybr and it works like a charm. For downgrading I use:
def downgrade():
sa_enum = sa.Enum(name='my_enum')
sa_enum.drop(op.get_bind(), checkfirst=True)
def upgrade(): vals, name = ("foo", "bar", "baz"), "my_enum" sa_enum = sa.Enum(*vals, name=name) psql_enum = sad_psql.ENUM(*vals, name=name, create_type=False) variant_enum = sa_enum.with_variant(psql_enum, "postgresql") # This doesn't seem to provide much value beyond an `sa.Enum().create(.., checkfirst=True) # but maybe there's an edge case where it'd be useful - or it could be preferable to always use # the variant type to stay DRY? op.add_column("my_table", sa.Column(variant_enum))
def upgrade():
vals, name = ("foo", "bar", "baz"), "my_enum"
sa_enum = sa.Enum(*vals, name=name)
psql_enum = sad_psql.ENUM(*vals, name=name, create_type=False)
variant_enum = sa_enum.with_variant(psql_enum, "postgresql")
# This doesn't seem to provide much value beyond an `sa.Enum().create(.., checkfirst=True)
# but maybe there's an edge case where it'd be useful - or it could be preferable to always use
# the variant type to stay DRY?
op.add_column("my_table", sa.Column(variant_enum))
Doesn't work with 1.80 on Ubuntu/Python 3.10.4
and
def upgrade():
vals, name = ("foo", "bar", "baz"), "my_enum"
sa_enum = sa.Enum(*vals, name=name)
# This will create the enum if it's not already present which is helpful
sa_enum.create(op.get_bind(), checkfirst=True)
op.add_column("my_table", sa.Column(sa_enum))
works for me
#from ... import StatusEnum
class StatusEnum(enum.Enum):
none ="none"
ready="ready"
runing="running"
fail="fail"
success="success"
class Author(db.Model):
__tablename__ = "authors"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String, nullable=False)
status = db.Column( db.Enum(StatusEnum),default=StatusEnum.none)
def __repr__(self):
return "<Author(name={self.name!r})>".format(self=self)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
status = sa.Enum(StatusEnum, name="statusenum")
status.create(op.get_bind(), checkfirst=True)
op.add_column('authors', sa.Column('status', sa.Enum('none', 'ready', 'running', 'fail', 'success', name='statusenum'), server_default='none', nullable=False))
# ### end Alembic commands ###
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_column('authors', 'status')
status = sa.Enum(StatusEnum, name="statusenum")
status.drop(op.get_bind())
# ### end Alembic commands ###
works for me
This 3rd party hook worked for me, to autogenerate migrations to changes in enum values with pg, https://github.com/dw/alembic-autogenerate-enums
I have had success modifying the render_item function in the env.py file as shown below, am I introducing any obvious errors?:
# normal env.py content
from sqlalchemy import Enum
def render_item(type_, obj, autogen_context):
"""Apply custom rendering for Postgres enum,
this is a "temporary" fix until this alembic issue is resolved: https://github.com/sqlalchemy/alembic/issues/278.
"""
if type_ == "type" and isinstance(obj, Enum):
enums = ", ".join([f"'{enum_val}'" for enum_val in obj.enums])
return f'sa.Enum({enums}, name="{obj.name}", create_type=False)'
# default rendering for other objects
return False
def run_migrations_online() -> None:
# ...
with connectable.connect() as connection:
context.configure(
connection=connection,
target_metadata=target_metadata,
render_item=render_item,
)
with context.begin_transaction():
context.run_migrations()
A sample use is:
class User(Base):
__tablename__ = "users"
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, unique=True)
role = Column(RoleEnumType)
roles = Column(ARRAY(RoleEnumType))
# this part is shown here: https://www.horsfallnathan.com/blog/sqlalchemy-enums-in-python
class RoleEnum(str, Enum):
ADMIN = "admin"
GUEST = "guest"
RoleEnumType = Enum(
RoleEnum,
name="role_enum_type",
values_callable=lambda obj: [item.value for item in obj],
)
The autogenerate works for me if I bind it to the engine and do something like Model.__table__.create(bind=raw_engine). Using CreateTable leads to strange behavior like "enum already exists" or if it doesn't exist, complains that it can't create it.
import sqlalchemy as sa
existing_type = sa.Enum(
"A",
"B",
name="{type_name}",
)
new_type = sa.Enum(
"A",
"B",
"C",
name="{type_name}",
)
def upgrade():
op.alter_column(
"{table_name}",
"{column_name}",
existing_type=existing_type,
type_=new_type,
existing_server_default="{default_value}", # needed for mysql
existing_nullable=False, # needed for mysql
)
def downgrade():
op.alter_column(
"{table_name}",
"{column_name}",
existing_type=new_type,
type_=existing_type,
existing_server_default="{default_value}",
existing_nullable=False,
)
Although this issue is for postgresql, fyi, I was able to do this with mysql
I don't think it will actually add a new enum value using the above example on postgressql.
It should render an ALTER TYPE {type_name} ADD VALUE 'C' AFTER 'B', but there is currently nothing that does this in alembic, so you will need to run this command manually
I have created a library that can help with this issue.
It will track the occurrence, deletion and modification of enums and add the appropriate commands to the migration
Just install library
pip install alembic-postgresql-enum
and add the line in migrations/env.py
import alembic_postgresql_enum
You won't have to manually write raw sql enum migrations anymore
@RustyGuard nice work. Quickly glancing at the lib I think some of the simple parts could be integrated in alembic, but I would not be very comfortable with the enum alter operations.
In any case, maybe it makes sense mentioning this library somewhere in the docs. @zzzeek is the best place the cookbook?