Alembic autogenerate is unnecessarily detecting removed, added foreign keys

[gkrwns7566]

base.py

• Base is for tables in scheme "qb_editor"
• QBBase is for tables in scheme "qb_beta"

class BaseMixin(object):
    __table_args__ = {"schema": "qb_editor"}

    created_at = Column(DateTime, default=lambda: datetime.now(config.KO_TIMEZONE))
    updated_at = Column(DateTime, onupdate=lambda: datetime.now(config.KO_TIMEZONE))


QBBase = declarative_base()
Base = declarative_base(cls=BaseMixin)

alembic/env.py

from logging.config import fileConfig

from alembic import context
from sqlalchemy import engine_from_config
from sqlalchemy import pool

from app.core.config import config as my_config
from app.user.models import *
from app.project.models import *

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = Base.metadata ########## <- points schema "qb_editor" #####

# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.
command_name = context.config.cmd_opts.cmd[0].__name__


db_url = my_config.rds_url ########## <- points schema "qb_editor" #####
config.set_main_option("sqlalchemy.url", db_url)


# def include_name(name, type_, parent_names):
#     if type_ == "schema":
#         return name in [my_config.RDS_SCHEMA]
#     else:
#         return True


def run_migrations_offline() -> None:
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    """
    context.configure(
        url=db_url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )

    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online() -> None:
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            # include_schemas=True,
            # include_name=include_name,
        )

        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

I have two schemas in a single DBMS and my main schema for the project is "qb_editor". Some models in "qb_editor" has to have foreign keys with tables in "qb_beta" and I am not willing to migrate anything with schema "qb_beta".

The problem is that after I create tables with these configurations(which worked fine), alembic detects added/removed foreign keys even though I have done nothing after the recent alembic upgrade.

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected removed foreign key (grade_category_id)(id) on table grade
INFO  [alembic.autogenerate.compare] Detected added foreign key (grade_category_id)(id) on table qb_editor.grade
INFO  [alembic.autogenerate.compare] Detected removed foreign key (subject_id)(id) on table project
INFO  [alembic.autogenerate.compare] Detected removed foreign key (school_id)(id) on table project
INFO  [alembic.autogenerate.compare] Detected removed foreign key (project_category_id)(id) on table project
INFO  [alembic.autogenerate.compare] Detected added foreign key (subject_id)(id) on table qb_editor.project
INFO  [alembic.autogenerate.compare] Detected added foreign key (school_id)(id) on table qb_editor.project
INFO  [alembic.autogenerate.compare] Detected added foreign key (project_category_id)(id) on table qb_editor.project
INFO  [alembic.autogenerate.compare] Detected removed foreign key (subsection_id)(id) on table question
INFO  [alembic.autogenerate.compare] Detected removed foreign key (question_category_id)(id) on table question
INFO  [alembic.autogenerate.compare] Detected added foreign key (question_category_id)(id) on table qb_editor.question
INFO  [alembic.autogenerate.compare] Detected removed foreign key (subject_category_id)(id) on table subject
INFO  [alembic.autogenerate.compare] Detected added foreign key (subject_category_id)(id) on table qb_editor.subject
INFO  [alembic.autogenerate.compare] Detected removed foreign key (grade_id)(id) on table subject_grade
INFO  [alembic.autogenerate.compare] Detected removed foreign key (subject_id)(id) on table subject_grade
INFO  [alembic.autogenerate.compare] Detected removed foreign key (region_id)(id) on table subregion
INFO  [alembic.autogenerate.compare] Detected added foreign key (region_id)(id) on table qb_editor.subregion
INFO  [alembic.autogenerate.compare] Detected removed foreign key (subregion_id)(id) on table user
INFO  [alembic.autogenerate.compare] Detected removed foreign key (job_id)(id) on table user
INFO  [alembic.autogenerate.compare] Detected added foreign key (job_id)(id) on table qb_editor.user
INFO  [alembic.autogenerate.compare] Detected added foreign key (subregion_id)(id) on table qb_editor.user

I have tried things like

1. configuring 'include_schemas' and 'include_name'
2. setting target_metadata = [Base.metadata, QBBase.metadata]

which didnt work, but configuring 'include_schemas' and 'include_name' resulted in detecting all tables again even after the tables are already present.

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'grade_category'
INFO  [alembic.autogenerate.compare] Detected added table 'job'
INFO  [alembic.autogenerate.compare] Detected added table 'project_category'
INFO  [alembic.autogenerate.compare] Detected added table 'question_category'
INFO  [alembic.autogenerate.compare] Detected added table 'region'
INFO  [alembic.autogenerate.compare] Detected added table 'subject_category'
INFO  [alembic.autogenerate.compare] Detected added table 'grade'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_qb_editor_grade_grade_category_id' on '['grade_category_id']'
INFO  [alembic.autogenerate.compare] Detected added table 'question'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_qb_editor_question_subsection_id' on '['subsection_id']'
INFO  [alembic.autogenerate.compare] Detected added table 'subject'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_qb_editor_subject_subject_category_id' on '['subject_category_id']'
INFO  [alembic.autogenerate.compare] Detected added table 'subregion'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_qb_editor_subregion_region_id' on '['region_id']'
INFO  [alembic.autogenerate.compare] Detected added table 'project'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_qb_editor_project_project_category_id' on '['project_category_id']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_qb_editor_project_school_id' on '['school_id']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_qb_editor_project_subject_id' on '['subject_id']'
INFO  [alembic.autogenerate.compare] Detected added table 'user'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_qb_editor_user_job_id' on '['job_id']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_qb_editor_user_subregion_id' on '['subregion_id']'
INFO  [alembic.autogenerate.compare] Detected added table 'subject_grade'

What can be possible reasons for this problem??

Environments

• database: Mysql 8.0.33
• alembic version: 1.12.1
• python version: 3.9
• sqlalchemy version: 1.4.49

[zzzeek]

your database connection seems to be configued such that qb_schema is the default schema already, so the constructs are being detected as being dropped in your default schema and added in your additional schema:

INFO [alembic.autogenerate.compare] Detected removed foreign key (grade_category_id)(id) on table grade
INFO [alembic.autogenerate.compare] Detected added foreign key (grade_category_id)(id) on table qb_editor.grade

alembic keys a table to either <tablename> if no schema name is needed to reference it, or <schema>.<tablename> if a schema name is needed to reference it. it can't do both at the same time since it would not know these are the same table.

therefore solutions are:

1. change your database connection URL to not have qb_edtior as your default database , or
2. leave connection URL the same and remove qb_editor "schema" parameter from all metadata (you also would not need to use include_schemas)

[gkrwns7566]

First of all, sorry for the confusion.
My base.py was actually configured this way, and the removing/adding fk behavior right after table creation is the same as mentioned originally.

base.py

class BaseMixin:
    __table_args__ = {"schema": "qb_editor"}

    created_at = Column(DateTime, default=lambda: datetime.now(config.KO_TIMEZONE))
    updated_at = Column(DateTime, onupdate=lambda: datetime.now(config.KO_TIMEZONE))


class QBBaseMixin:
    __table_args__ = {"schema": "qb_beta"} 


Base = declarative_base(cls=BaseMixin)
QBBase = declarative_base(cls=QBBaseMixin)

For solution 1,

if i change the connection URL that is not configured to qb_editor, for example qb_beta, it migrates the tables in qb_beta while I have to migrate tables in qb_editor.
What is the suggested structure for the new url? It gives an error is no database is defined.

For solution 2,

while remaining the connection URL, and not using any of include_schemas, include_name parameters,
i changed my base.py as below to remove schema informations, which resulted in alembic upgrade errors when creating the tables in the first place.

class BaseMixin:
    # __table_args__ = {"schema": config.RDS_SCHEMA}

    created_at = Column(DateTime, default=lambda: datetime.now(config.KO_TIMEZONE))
    updated_at = Column(DateTime, onupdate=lambda: datetime.now(config.KO_TIMEZONE))



Base = declarative_base(cls=BaseMixin)
QBBase = declarative_base()

This was because alembic couldn't detect schema "qb_beta" that tables in "qb_editor" were targeting to create foreign keys.
For example,

    ...
    sa.ForeignKeyConstraint(['question_category_id'], ['question_category.id'], ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['subsection_id'], ['subsection.id'], ondelete='SET NULL'), <---------------------------------
    sa.PrimaryKeyConstraint('id'),
    schema='qb_editor'
    )

Alembic upgrade works only if the script goes this way, when I left the schema metadata in bases Base and QBBase.

    ...
    sa.ForeignKeyConstraint(['subsection_id'], ['qb_beta.subsection.id'], ondelete='SET NULL'),
    ...

Is there any other method to "remove qb_editor "schema" parameter from all metadata"??

For your reference,
the sqlalchemy model is made this way

from app.etc.qb_models import  Subsection

...

class Question(Base):
    __tablename__ = "question"

    id = Column(BigInteger, primary_key=True)
    subsection_id = Column(
        BigInteger, ForeignKey(Subsection.id, ondelete="SET NULL"), index=True <---------------------------------------------
    )
    ...

where class Question is from "qb_editor" and class Subsection is from "qb_beta".
If I change to code to

class Question(Base):
    __tablename__ = "question"

    id = Column(BigInteger, primary_key=True)
    subsection_id = Column(
        BigInteger, ForeignKey("qb_beta.subsection.id", ondelete="SET NULL"), index=True <---------------------------------------------
    )
    ...

alembic revision gives

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'question.subsection_id' could not find table 'qb_beta.subsection' with which to generate a foreign key to target column 'id'

Thank you for your answer!! @zzzeek

[CaselIT]

sorry @gkrwns7566 but I did not understand if you managed to solve it or if you are still looking for a solution

[gkrwns7566]

I wasn't able to solve the problem with those 2 solutions @CaselIT