✨ Allow specifying the foreign_key using an instance of ForeignKey

[earshinov]

…to let the user define additional sqlalchemy.orm.ForeignKey attributes, such as ondelete and onupdate, for foreign keys defined in a base model.

UPD YuriiMotov:

Currently we can specify foreign_key as an instance of sqlalchemy.ForeignKey passing it via sa_column_args:

    class Item(SQLModel, table=True):
        owner_id: Optional[int] = Field(
            default=None, sa_column_args=(ForeignKey("user.id", ondelete="SET NULL"),)
        )

But in current implementation it doesn't work if we use inheritance:

    class Base(SQLModel):
        owner_id: Optional[int] = Field(
            default=None, sa_column_args=(ForeignKey("user.id", ondelete="SET NULL"),)
        )

    class Asset(Base, table=True):
        id: Optional[int] = Field(default=None, primary_key=True)

    class Document(Base, table=True):
        id: Optional[int] = Field(default=None, primary_key=True)

The code above will lead to "This ForeignKey already has a parent" error.

This PR fixes that error and allows passing an instance of ForeignKey as a value of foreign_key parameter of Field:

    class Item(SQLModel, table=True):
        owner_id: Optional[int] = Field(
            default=None,
            foreign_key=ForeignKey("user.id", ondelete="SET NULL"),
        )

[YuriiMotov]

@earshinov, thanks for your interest and efforts!

IMO, the option with sa_column_args=[ForeignKey(...)] looks better than with sa_foreign_key_kwargs={} - in first case we have type hints and it's more readable.

As I understand, the problem is that in case of inheritance the same ForeignKey instance is being re-used in several models and it causes that exception.
What if we approach to solve this in a different way - use copy of ForeignKey object instead of that object itself?
I mean in get_column_from_field extract ForeignKey from sa_column_args (if exists) and pass its copy instead

[earshinov]

@YuriiMotov , thanks for a useful suggestion. Indeed, the approach you described is working and provides more type safety. I updated the PR.

[earshinov]

Docs will need to be updated, too, to make the users aware that foreign_key can now be given as a sqlalchemy.schema.ForeignKey instance instead of a string. I am not sure what would be the best place to mention this.

[YuriiMotov]

@earshinov, please review the changes I made (commits and PR description). Is everything Ok from your point of view?

[YuriiMotov]

LGTM!

[earshinov]

@earshinov, please review the changes I made (commits and PR description). Is everything Ok from your point of view?

Yes, looks good to me! Thank you for the edits

[juliahuang]

Hello! I'm interested in this feature as well - any chance this could get merged ?