René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Migrations drop and identically recreate certain foreign keys","articleBody":"Hello! I'm opening a new issue, but this looks quite possibly related to [issue 231](https://github.com/googleapis/python-spanner-sqlalchemy/issues/231).\r\n\r\nFollowing the release of version 1.2.2, we observe a new, strange behavior in migrations regarding a slightly tricky foreign key we use.\r\n\r\nIn short, we are linking two entities together in a table, and we want to enforce that a certain property of entities A and B match. This worked perfectly well in 1.2.1.\r\n\r\nIn 1.2.2, the keys' enforcement actually still works, but every new migration we create drops that linking table's foreign keys and recreates them identically. This does not happen only once, but in every subsequent migration.\r\n\r\nHere are the relevant package versions we use:\r\n\r\nalembic                                  1.8.1\r\ngoogle-cloud-spanner           3.22.2\r\nSQLAlchemy                           1.4.41\r\nsqlalchemy-spanner               1.2.2\r\n\r\nAnd here's a minimalist setup that recreates the problem.\r\n\r\n```\r\nfrom __future__ import annotations\r\n\r\nfrom sqlalchemy import Column, ForeignKey, ForeignKeyConstraint, PrimaryKeyConstraint, String\r\nfrom sqlalchemy.ext.declarative import declared_attr\r\nfrom sqlalchemy.orm import relationship\r\n\r\nfrom shared.database_engine import Base\r\n\r\n### Base's definition from shared.database_engine (it's pretty standard):\r\n# from sqlalchemy import MetaData, create_engine\r\n# from sqlalchemy.orm import declarative_base\r\n# engine = create_engine(shared_settings.SPANNER_URL, pool_recycle=shared_settings.SPANNER_SESSION_RECYCLE_AGE)\r\n# metadata = MetaData(bind=engine)\r\n# Base = declarative_base(bind=engine, metadata=metadata)\r\n\r\n\r\nclass EnvironmentMixin:\r\n    @declared_attr\r\n    def environment_id(self) -\u003e Column[String]:\r\n        return Column(String, ForeignKey('environment.id'), nullable=False)\r\n\r\n\r\nclass Environment(Base):\r\n    __tablename__ = 'environment'\r\n\r\n    id = Column(String, primary_key=True)\r\n\r\n\r\nclass A(Base, EnvironmentMixin):\r\n    __tablename__ = 'a'\r\n\r\n    id = Column(String, primary_key=True)\r\n\r\n\r\nclass B(Base, EnvironmentMixin):\r\n    __tablename__ = 'b'\r\n\r\n    id = Column(String, primary_key=True)\r\n\r\n\r\nclass Link(Base, EnvironmentMixin):\r\n    __tablename__ = 'link'\r\n    __table_args__ = (\r\n        # Enforce at DB-level that associated A and B must share the same Environment\r\n        PrimaryKeyConstraint('environment_id', 'a_id', 'b_id', name='pk_test'),\r\n        ForeignKeyConstraint(\r\n            ['environment_id', 'a_id'],\r\n            ['a.environment_id', 'a.id'],\r\n            name='fk_1',\r\n        ),\r\n        ForeignKeyConstraint(\r\n            ['environment_id', 'b_id'],\r\n            ['b.environment_id', 'b.id'],\r\n            name='fk_2',\r\n        ),\r\n    )\r\n\r\n    a_id = Column(String, nullable=False)\r\n    b_id = Column(String, nullable=False)\r\n\r\n    a: A = relationship('A', foreign_keys=[a_id])\r\n    b: B = relationship('B', foreign_keys=[b_id])\r\n```\r\n\r\nUsing this file, I ran `alembic revision --autogenerate` for the initial setup, then `alembic upgrade head`, then immediately another `alembic revision --autogenerate`.\r\n\t\r\nOur migrations are enclosed in an autocommit block following [this issue](https://github.com/googleapis/python-spanner-sqlalchemy/issues/229).\r\n\r\nHere's the initial generation:\r\n\r\n```\r\n\"\"\"setup\r\n\r\nRevision ID: 485161853e62\r\nRevises: \r\nCreate Date: 2022-10-14 14:46:50.269607\r\n\r\n\"\"\"\r\nfrom alembic import op\r\nimport sqlalchemy as sa\r\n\r\n\r\n# revision identifiers, used by Alembic.\r\nrevision = '485161853e62'\r\ndown_revision = None\r\nbranch_labels = None\r\ndepends_on = None\r\n\r\n\r\ndef upgrade():\r\n    with op.get_context().autocommit_block():\r\n        # ### commands auto generated by Alembic - please adjust! ###\r\n        op.create_table('environment',\r\n        sa.Column('id', sa.String(), nullable=False),\r\n        sa.PrimaryKeyConstraint('id')\r\n        )\r\n        op.create_table('a',\r\n        sa.Column('id', sa.String(), nullable=False),\r\n        sa.Column('environment_id', sa.String(), nullable=False),\r\n        sa.ForeignKeyConstraint(['environment_id'], ['environment.id'], ),\r\n        sa.PrimaryKeyConstraint('id')\r\n        )\r\n        op.create_table('b',\r\n        sa.Column('id', sa.String(), nullable=False),\r\n        sa.Column('environment_id', sa.String(), nullable=False),\r\n        sa.ForeignKeyConstraint(['environment_id'], ['environment.id'], ),\r\n        sa.PrimaryKeyConstraint('id')\r\n        )\r\n        op.create_table('link',\r\n        sa.Column('a_id', sa.String(), nullable=False),\r\n        sa.Column('b_id', sa.String(), nullable=False),\r\n        sa.Column('environment_id', sa.String(), nullable=False),\r\n        sa.ForeignKeyConstraint(['environment_id', 'a_id'], ['a.environment_id', 'a.id'], name='fk_1'),\r\n        sa.ForeignKeyConstraint(['environment_id', 'b_id'], ['b.environment_id', 'b.id'], name='fk_2'),\r\n        sa.ForeignKeyConstraint(['environment_id'], ['environment.id'], ),\r\n        sa.PrimaryKeyConstraint('environment_id', 'a_id', 'b_id', name='pk_test')\r\n        )\r\n        # ### end Alembic commands ###\r\n\r\n\r\ndef downgrade():\r\n    with op.get_context().autocommit_block():\r\n        # ### commands auto generated by Alembic - please adjust! ###\r\n        op.drop_table('link')\r\n        op.drop_table('b')\r\n        op.drop_table('a')\r\n        op.drop_table('environment')\r\n        # ### end Alembic commands ###\r\n\r\n```\r\nAnd here's every subsequent autogenerated migration:\r\n\r\n\r\n\r\n```\r\n\"\"\"no modification\r\n\r\nRevision ID: 508dda784ffa\r\nRevises: 485161853e62\r\nCreate Date: 2022-10-14 14:47:00.349500\r\n\r\n\"\"\"\r\nfrom alembic import op\r\nimport sqlalchemy as sa\r\n\r\n\r\n# revision identifiers, used by Alembic.\r\nrevision = '508dda784ffa'\r\ndown_revision = '485161853e62'\r\nbranch_labels = None\r\ndepends_on = None\r\n\r\n\r\ndef upgrade():\r\n    with op.get_context().autocommit_block():\r\n        # ### commands auto generated by Alembic - please adjust! ###\r\n        with op.batch_alter_table('link', schema=None) as batch_op:\r\n            batch_op.drop_constraint('fk_2', type_='foreignkey')\r\n            batch_op.drop_constraint('fk_1', type_='foreignkey')\r\n            batch_op.create_foreign_key('fk_2', 'b', ['environment_id', 'b_id'], ['environment_id', 'id'])\r\n            batch_op.create_foreign_key('fk_1', 'a', ['environment_id', 'a_id'], ['environment_id', 'id'])\r\n\r\n        # ### end Alembic commands ###\r\n\r\n\r\ndef downgrade():\r\n    with op.get_context().autocommit_block():\r\n        # ### commands auto generated by Alembic - please adjust! ###\r\n        with op.batch_alter_table('link', schema=None) as batch_op:\r\n            batch_op.drop_constraint('fk_1', type_='foreignkey')\r\n            batch_op.drop_constraint('fk_2', type_='foreignkey')\r\n            batch_op.create_foreign_key('fk_1', 'a', ['environment_id', 'a_id'], ['id', 'environment_id'])\r\n            batch_op.create_foreign_key('fk_2', 'b', ['environment_id', 'b_id'], ['id', 'environment_id'])\r\n\r\n        # ### end Alembic commands ###\r\n```","author":{"url":"https://github.com/louimet","@type":"Person","name":"louimet"},"datePublished":"2022-10-14T15:10:44.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":3},"url":"https://github.com/271/python-spanner-sqlalchemy/issues/271"}