sqlalchemy
batch_alter_table forgets unchanged Enum CHECK constraint
Migrated issue, originally created by Jeremy Field (@jdf-id-au)
Using batch_alter_table to change an Enum in sqlite causes CHECK constraint for another Enum to be lost.
Working example at repo.
The documentation makes it look like this shouldn't happen.
When trying to reinstate lost Enum constraints on other columns, only the last batch_op.alter_column call seems to result in a CHECK constraint being created.
Michael Bayer (@zzzeek) wrote:
An enum or boolean cannot be detected via reflection so the docs need to be updated. All the verbiage here refers to explicitly stated types.
More expediently, check constraint support would be added as upstream supports this now.
Jeremy Field (@jdf-id-au) wrote:
Thanks for lightning-quick response.
Is reflection necessary in this instance? Couldn't all enum/boolean-related CHECKs just be regenerated with each migration, in the knowledge that they would otherwise be missing in the new schema?
Also, what is the reason for the "only the last batch_op.alter_column" problem I described?
I see the upstream feature. Sorry I'm not able to make a PR for this.
Michael Bayer (@zzzeek) wrote:
the batch feature relies upon reflection to formulate how to recreate the table with a new name, unless you hand it that table explicitly. SQLite does allow any arbitrary name for a type so I think SQLAlchemy emits BOOLEAN for a boolean type (meaning this bug report would not apply to booleans, but would be nice to confirm), however for enum I think it emits VARCHAR so there's no way to reflect a type as "ENUM" from SQLite.
your test succeeds for the second enum because you are stating it explicitly in your batch migration.
Michael Bayer (@zzzeek) wrote:
incidentally this is one reason it will be very hard to add CHECK constraint support to batch mode, because it will attempt to double-create those CHECK constraints that correspond to BOOLEAN/ENUM.
Changes by Jeremy Field (@jdf-id-au):
- changed title from "batch_alter_table forgets unchanged Enum" to "batch_alter_table forgets unchanged Enum CHECK con"
@buxx there's no secret discussion that goes on for issues. any updates would be on this now four year old issue. I would advise using create_constraint=False for your Enum and Boolean types.
here is an update. that I am proposing disabling these poorly considered CHECK constraint options by default so that the vast majority of users who don't care will not have to be bothered with them: https://github.com/sqlalchemy/sqlalchemy/issues/5367
I just encountered this issue on enum fields when resolving changes from alembic 1.7, specifically #884. I'm quite confused by contradictory information on the topic.
Alembic documentation https://alembic.sqlalchemy.org/en/latest/batch.html#including-check-constraints describes how to handle custom table constraints, but it also states explicitely that
Note this only includes CHECK constraints that are explicitly stated as part of the table definition, not the CHECK constraints that are generated by datatypes such as Boolean or Enum.
Which I understand in way, that Enum constraints should be taken care of by the batch mode, but it doesn't seem to be happening, at least not on sqlite.
I'm still using SQLAlchemy 1.3 and I noticed check constraints are disabled by default since 1.4, which makes me more confused on the topic.
What's the proposed best practice for migration of non-native enums, e.g. in sqlite? Do I need to switch to SQLAchemy 1.4? Do I need to fix my migrations somehow?
give the CHECK constraint in the Enum a name, using the "name" argument. we can't regenerate unnamed CHECK constraints safely.
I use the PEP-435 Enum class for the column values. As I understand docs, that should be equivalent. Did I miss something else?
i can't reproduce your issue. Can you please open a new issue with an MCVE? Here's my script:
"""r1
Revision ID: 9545365ed377
Revises:
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy import Column, Integer
import enum
# revision identifiers, used by Alembic.
revision = "9545365ed377"
down_revision = None
branch_labels = None
depends_on = None
class MyEnum(enum.Enum):
foo = 1
bar = 2
def upgrade() -> None:
op.create_table(
'tname',
Column('id', Integer, nullable=False),
Column("enum", sa.Enum(MyEnum, create_constraint=True)),
Column("data", sa.String)
)
with op.batch_alter_table('tname') as operation:
operation.alter_column(column_name="data", new_column_name="data2")
def downgrade() -> None:
pass
output of the initial create table, then the recreate:
CREATE TABLE tname (
id INTEGER NOT NULL,
enum VARCHAR(3),
data VARCHAR,
CONSTRAINT myenum CHECK (enum IN ('foo', 'bar'))
)
CREATE TABLE _alembic_tmp_tname (
id INTEGER NOT NULL,
enum VARCHAR(3),
data2 VARCHAR,
CONSTRAINT myenum CHECK (enum IN ('foo', 'bar'))
)
the named CHECK constraint is included.
OK, I'm currently swamped by fixing the production, but I will definitely have to get back to this. Thanks for now.
I got back to the issue, but I wasn't able to reproduce the erroneous behavior.
I suspect I probably forgot to add create_constraint=True to some of the migrations on the first attempt and it had impact on other migrations down the line.
OK, moving on then. the CHECK constraints for enum/boolean were an awful idea on my part due to the migrations complexity (that said I really didnt want to write the "batch" feature in the first place).