Add kwargs to ForeignKey definitions

[farzadsbakhtiar]

ForeignKey definitions in SqlAlchemy accept kwargs such as onupdate and ondelete that determine the behaviour of the foreign key at the DB level. This PR adds the option to provide kwargs to the ForeignKey definition under the hood. In my case, I used it to control delete behaviour when deleting a model that did not maintain back references (relationships) with its child models. Example -- an application where users can have 0 or 1 "Repository":

class Repository(SQLModel, table=True):
  id: int = Field(primary_key=True)
  ...

class User(SQLModel, table=True):
  ...
  name: str
  repository_id: int | None = Field(foreign_key='repository.id', 
                                                        foreign_key_kwargs={'ondelete': 'SET NULL'})
  repository: Repository | None = Relationship()


# -- first create a user without a repository
user = User(name='some user')
session.add(user)
session.flush()

# -- create and add the repository
rep = Repository()
user.repository = rep

session.add(rep)
session.flush()

session.commit()

# -- now delete the user's repository
session.delete(rep)
session.commit()

If we didn't set the ondelete="SET NULL" option on the user.repository_id foreign key we would get this error:

psycopg2.errors.ForeignKeyViolation: update or delete on table "repository" violates foreign key constraint "user_repository_id_fkey" on table "user". Key (id)=(1) is still referenced from table "user" .

A workaround for this would be to keep a user: User = Relationship(back_populates='repository') attribute which I didn't want to do because in my case it would clutter my model. I'm wondering whether there are more concrete use cases for the foreign_key_kwargs option.

[antont]

I guess this would support this case as well? #436

I don't know if it's the way forward, or not, but am curious about opinions.

[farzadsbakhtiar]

I think so.

[vlntsolo]

Any hints how to properly set ondelete behavior with foreign keys? I'm trying to figure out how to SET NULL when foreign key is being deleted, but couldn't find a solution which wouldn't throw errors.

Django ORM does a great job by exposing model methods, but here with SQLAlchemy, kwargs would also do. Just need a working solution.

[nick-catalist]

@tiangolo Any takes here? There is an issue I'm running into where defining a foreign_key as part of the "sa_column_args" in a BaseModel for a given object (let's say HeroBase) causes the relationships defined in a HeroRead that inherits from it to fail to find the FK. It works if I define it in the foreign_key value at the field level instead of at the sa_column_args level, and it works if I define it in a field with the sa_column_args within HeroRead and not in HeroBase. I'd prefer to do things the "proper" way and keep it in the BaseModel (as its something all my Hero classes want to have), but there is then no way to specify the ondelete kwarg for my FK :(

Though perhaps it would be even better to solve the issue of FKs defined at the sa level not being resolvable by classes that inherit.

[YuriiMotov]

In current version there is ondelete parameter that serves exactly this reason:

    repository_id: int | None = Field(
        foreign_key="repository.id", ondelete="SET NULL"
    )

This is documented here: https://sqlmodel.tiangolo.com/tutorial/relationship-attributes/cascade-delete-relationships/

And, if you need to pass other ForeignKey-related parameters, you can use sa_column_args to pass ForeignKey instance configured as you need.

    repository_id: int | None = Field(
        sa_column_args=[ForeignKey("repository.id", ondelete="SET NULL")]
    )

IMO, this way is better since you will have all type hints (as opposed to foreign_key_kwargs).

Having this, I suggest we close this PR.
@thedamnedrhino, thanks for working on this!

[github-actions]

This pull request has a merge conflict that needs to be resolved.

[tiangolo]

Thanks @YuriiMotov for the clear clarification! 🙌

Agreed with all, yep, for now I'll pass on this one. Thanks! ☕