tiangolo
make relationship nicely inheritable
First Check
- [X] I added a very descriptive title to this issue.
- [X] I used the GitHub search to find a similar issue and didn't find it.
- [X] I searched the SQLModel documentation, with the integrated search.
- [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
- [X] I already read and followed all the tutorial in the docs and didn't find an answer.
- [X] I already checked if it is not related to SQLModel but to Pydantic.
- [X] I already checked if it is not related to SQLModel but to SQLAlchemy.
Commit to Help
- [X] I commit to help with one of those options 👆
Example Code
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select
from sqlalchemy.orm import declared_attr, relationship
class Team(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
headquarters: str
class HeroBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
team_id: Optional[int] = Field(default=None, foreign_key="team.id")
@declared_attr
def team(self) -> Optional[Team]:
return relationship("Team") # type: ignore
class AnimalHero(HeroBase, table=True):
species: str
class RobotHero(HeroBase, table=True):
model_number: int
engine = create_engine("sqlite://")
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
peace = Team(name="peace", headquarters="earth")
evil = Team(name="evil", headquarters="mars")
session.add_all((peace, evil))
session.commit()
chickenman = AnimalHero(name="chickenman", species="chickens", team_id=peace.id)
siri = RobotHero(name="siri", model_number=0x6af, team_id=evil.id)
session.add_all((chickenman, siri))
session.commit()
with Session(engine) as session:
animals = session.exec(select(AnimalHero)).all()
robots = session.exec(select(RobotHero)).all()
print(f"{animals=}")
print(f"{robots=}")
assert all(hasattr(h, "team") for h in animals)
assert all(hasattr(h, "team") for h in robots)
assert animals == [AnimalHero(id=1, species='chickens', team_id=1, name='chickenman')]
assert robots == [RobotHero(id=1, model_number=1711, team_id=2, name='siri')]
print(f"{[h.team for h in animals]=}")
print(f"{[h.team for h in robots]=}")
assert [h.team for h in animals] == [Team(name='peace', id=1, headquarters='earth')]
assert [h.team for h in robots] == [Team(name='evil', id=2, headquarters='mars')]
Description
I want to create an SQLModel base class that contains a relationship which i can inherit.
in the example there is a HeroBase which is inherited by AnimalHero and RobotHero. It inherits the columns correctly but with the relationships, i need to use sqlalchemy.orm.declared_attr, sqlalchemy.orm.relationship and # type: ignore so the type checker doesnt get mad.
then in insertion i need to first insert the teams, commit, then link the heroes via the team_id manually instead of doing AnimalHero(name="chickenman", species="chickens", team=peace), so the current way is very similar to this
Wanted Solution
I just want to inherit the relationship provided by SQLModel.Relationship and doing the insertion this way.
Wanted Code
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select, Relationship
class Team(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
headquarters: str
class HeroBase(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
team_id: Optional[int] = Field(default=None, foreign_key="team.id")
team: Team = Relationship()
class AnimalHero(HeroBase, table=True):
species: str
class RobotHero(HeroBase, table=True):
model_number: int
engine = create_engine("sqlite://")
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
peace = Team(name="peace", headquarters="earth")
evil = Team(name="evil", headquarters="mars")
chickenman = AnimalHero(name="chickenman", species="chickens", team=peace)
siri = RobotHero(name="siri", model_number=0x6af, team=evil)
session.add_all((chickenman, siri))
session.commit()
with Session(engine) as session:
animals = session.exec(select(AnimalHero)).all()
robots = session.exec(select(RobotHero)).all()
print(f"{animals=}")
print(f"{robots=}")
assert all(hasattr(h, "team") for h in animals)
assert all(hasattr(h, "team") for h in robots)
assert animals == [AnimalHero(id=1, species='chickens', team_id=1, name='chickenman')]
assert robots == [RobotHero(id=1, model_number=1711, team_id=2, name='siri')]
print(f"{[h.team for h in animals]=}")
print(f"{[h.team for h in robots]=}")
assert [h.team for h in animals] == [Team(name='peace', id=1, headquarters='earth')]
assert [h.team for h in robots] == [Team(name='evil', id=2, headquarters='mars')]
Alternatives
Using directly SQLAlchemy instead of SQLModel
Operating System
Linux
Operating System Details
No response
SQLModel Version
0.0.4
Python Version
Python 3.9.8
Additional Context
The reason for me to go to this route is to implement generic tables/associations and tried to look for examples in SQLAlchemy and tried to use SQLAlchemy-Utils generic_relationship with Relationship(sa_relationship=generic_relationship("object_id", "object_type")), although the later works for insertion, it doesnt work when you try to getattr the relationship after selection.
The wanted code is reasonable considering this is my second day using SQLModel and it feels intuitive to do it that way.
+1 for this working roughly the same way as @declared_attr does in SQLAlchemy: https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/mixins.html#mixing-in-columns-in-inheritance-scenarios
Currently, the FK fields generate the correct DB columns, but the relationship attrs are dropped silently.
+1 - I couldn't figure out why Relationships were not working when I had declared them in a "base" class.
The reason why I put it in the Base class was that I was sure it would work the same as with Fields like written here: https://sqlmodel.tiangolo.com/tutorial/fastapi/multiple-models/?h=base#multiple-models-with-inheritance and https://sqlmodel.tiangolo.com/tutorial/fastapi/multiple-models/?h=base#multiple-models-with-inheritance
It would be awesome if Relationships could work the same.
For who still interest in this issue. I create following monkey patching function to patch SQLModelMetaclass new magic method with some dirty hacks, the relationship in base classes can be inheritable. It would be nice if sqlmodel can have building feature to support relationship inherit. But I think it is not a easy task which can be seen by the dirty hacks. For example, the foriegn_keys field in the base classes should be string type for this monkey patch to work, which make this solution is not so general.
from typing import Any, Type
from pydantic import create_model
from sqlmodel import Relationship, SQLModel
from sqlmodel.main import SQLModelMetaclass, RelationshipInfo
model_metaclass_new_patched = False
def patch_inherit_relationships():
global model_metaclass_new_patched
if model_metaclass_new_patched:
return
origin_new_func = SQLModelMetaclass.__new__
def patch_relationship_new(
cls,
name: str,
bases: tuple[Type[Any], ...],
class_dict: dict[str, Any],
**kwargs: Any,
):
new_class_dict = {**class_dict}
new_bases= []
for base_class in bases:
if hasattr(base_class, '__sqlmodel_relationships__') and len(base_class.__sqlmodel_relationships__)>0:
base_relationship = base_class.__sqlmodel_relationships__
for rel_field_name,v in base_relationship.items():
# create new Relationship, not in sito change base_class Relationship field
v: RelationshipInfo = v
rel_info = Relationship(
back_populates=v.back_populates,
cascade_delete=v.cascade_delete,
passive_deletes=v.passive_deletes,
link_model=v.link_model,
sa_relationship_args=[*v.sa_relationship] if v.sa_relationship else None,
sa_relationship_kwargs={**v.sa_relationship_kwargs} if v.sa_relationship_kwargs else None
)
new_class_dict[rel_field_name] = rel_info
if hasattr(rel_info, 'sa_relationship_kwargs') and len(rel_info.sa_relationship_kwargs)>0:
# patch foreign_keys field, it is important in the base class, the foreign_keys is write in strings:
if foreign_keys:=rel_info.sa_relationship_kwargs.get('foreign_keys'):
# TODO, list string foreign_keys
rel_info.sa_relationship_kwargs['foreign_keys'] = f'{name}.{foreign_keys}'
# it is important to add type annotations of relationship field
if '__annotations__' in new_class_dict:
new_class_dict['__annotations__'][rel_field_name] = base_class.__annotations__[rel_field_name]
else:
new_class_dict['__annotations__'] = {rel_field_name: base_class.__annotations__[rel_field_name]}
relationship_field_keys = set(base_relationship.keys())
# remove relationship field in the origin base_class, or the relationship field would be inherited to children pydantic model
base_fields = {name: (field.annotation, field) for name, field in base_class.model_fields.items() if name not in relationship_field_keys}
# dynamically create new sqlmodel
model_name = f"StripRelationship{base_class.__name__}"
new_base_class = create_model(
model_name,
__base__=SQLModel,
**base_fields,
)
new_base_class.model_rebuild()
new_bases.append(new_base_class)
else:
new_bases.append(base_class)
new_bases = tuple(new_bases)
new_cls = origin_new_func(
cls, name, new_bases, new_class_dict, **kwargs
)
return new_cls
SQLModelMetaclass.__new__ = patch_relationship_new
model_metaclass_new_patched = True
