Feature: decoupling foreign keys (FKs) from table declarations

[AlexanderPodorov]

In some cases it's unavoidable to have circular FK dependencies. E.g. let's say we have user and currency table. Both tables may have created_by field with the reference to user table, and user table may have currency_id field with the reference to the currency table.
In my env.py I have the following process_revision_directives snippet which essentially moves FKs declarations from table declarations, move them to the end of upgrade function in the migration script, and then adds the same FKs to the beginning of downgrade script. All in proper order.
Here it is:

from collections.abc import MutableSequence, Sequence
from typing import cast
from alembic.operations.ops import (
    CreateForeignKeyOp, CreateTableOp, DowngradeOps, DropConstraintOp, MigrateOperation, MigrationScript, UpgradeOps
)
from alembic.runtime.migration import MigrationContext
from sqlalchemy import ForeignKeyConstraint

def process_revision_directives(
    _context: MigrationContext, _revision: tuple[str, str], directives: Sequence[MigrateOperation]
) -> None:
    upgrades = cast(UpgradeOps, cast(MigrationScript, directives[0]).upgrade_ops).ops
    downgrades = cast(DowngradeOps, cast(MigrationScript, directives[0]).downgrade_ops).ops

    changes = {
        upgrade_index: [
            column_index for column_index, column in enumerate(cast(CreateTableOp, upgrade).columns)
            if isinstance(column, ForeignKeyConstraint)
        ]
        for upgrade_index, upgrade in enumerate(upgrades)
        if isinstance(upgrade, CreateTableOp)
    }

    for upgrade_index, column_indexes in changes.items():
        upgrade = cast(CreateTableOp, upgrades[upgrade_index])
        columns = cast(MutableSequence[ForeignKeyConstraint], upgrade.columns)

        for column_index in reversed(column_indexes):
            column = columns.pop(column_index)
            upgrades.append(CreateForeignKeyOp.from_constraint(column))
            downgrades.insert(0, DropConstraintOp.from_constraint(column))

And I'm wondering is it possible or makes sense to have it built-in in alembic? Thanks!

[zzzeek]

yes it makes sense, the issue to do this feature is #326. SQLAlchemy itself already knows how to do this when you run metadata.create_all(), the idea would be to replicate the same logic in alembic autogenerate by leveraging sort_tables_and_constraints directly.

there's no motivated resources for this issue at this time but we can accept PRs that include tests. it would likely be a very involved feature add and probably should be "opt in" with a flag of some kind to avoid regressions.

[AlexanderPodorov]

Thanks! I agree about that this feature should be "opt in", totally fine.
I'm personally not able to submit a PR for that, I don't know sqlalchemy and alembic codebases well enough to do that, sorry. And it is very likely to miss some edge cases.
One thing to add: if we get it done, I think it does not make sense to show a "Circular dependencies" warning during alembic revision --autogenerate anymore. Thanks!

[zzzeek]

well if it's "opt in" then we could make the warning refer to this new flag that will resolve things automatically.

[AlexanderPodorov]

It's actually the great idea!