make relationship nicely inheritable

[5cat]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• 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.

[kellen]

+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.

[thomasborgen]

+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.

[zenglanmu]

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