tiangolo
Add kwargs to ForeignKey definitions
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.
I guess this would support this case as well? https://github.com/tiangolo/sqlmodel/pull/436
I don't know if it's the way forward, or not, but am curious about opinions.
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.
@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.
