Example: Understanding inheritance and relationships between model classes

[clstaudt]

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

class Contract(SQLModel):
    """A contract defines the business conditions of a project"""
    title: str = Field(description="Short description of the contract.")
    client: Client = Relationship(
        back_populates="contracts",
    )
    # Contract n:1 Client
    client_id: Optional[int] = Field(
        default=None,
        foreign_key="client.id",
    )

    currency: str  
    term_of_payment: Optional[int] = Field(
        description="How many days after receipt of invoice this invoice is due.",
        default=31,
    )

    
class TimeContract(Contract, table=True):
    """A time-based contract with a rate per time unit"""
    id: Optional[int] = Field(default=None, primary_key=True)

    rate: condecimal(decimal_places=2) = Field(
        description="Rate of remuneration",
    )

    unit: TimeUnit = Field(
        description="Unit of time tracked. The rate applies to this unit.",
        sa_column=sqlalchemy.Column(sqlalchemy.Enum(TimeUnit)),
        default=TimeUnit.hour,
    )

class WorksContract(Contract, table=True):
    """A contract with a fixed price"""
    id: Optional[int] = Field(default=None, primary_key=True)
    price: condecimal(decimal_places=2) = Field(
        description="Price of the contract",
    )
    deliverable: str = Field(description="Description of the deliverable")

class Client(SQLModel, table=True):
    """A client the freelancer has contracted with."""

    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    contracts: List["Contract"] = Relationship(back_populates="client")

Description

I would like to understand inheritance better using the following example:

• There are two types of contracts, a time-based contract with a rate per time unit, and a works contract with a fixed price.
• Every contract is related to a client.

The code example is my first attempt at implementing this. However, It is not yet correct:

InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'mapped class Client->client'. Original exception was: When initializing mapper mapped class Client->client, expression 'Contract' failed to locate a name ('Contract'). If this is a class name, consider adding this relationship() to the <class 'tuttle.model.Client'> class after both dependent classes have been defined.

Questions:

• Every Contract is related to a client. That makes me think client should be a member of the Contract base class. However, I don't understand how to properly implement the relationship. Is it necessary to move client to the table classes (thereby duplicating it)?
• When using inheritance from a model class, can I still select from Contract (rather than from the different contract types separately)?

Operating System

macOS, Other

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.10

Additional Context

No response

[meirdev]

You need to use mapping (https://docs.sqlalchemy.org/en/14/orm/inheritance.html) to achieve this, but it's a bit tricky with sqlmodel.

1. If you inherit from the SQLModel class with table=True you need to explicitly add a mapping to the registry.
2. I still don't know how to prevent sqlmodel from creating the fields of the parent table in the child table.

Example of working code:

from typing import Optional, List

from sqlmodel import SQLModel, Field, Relationship, create_engine, Session
from sqlalchemy.orm import registry, with_polymorphic

mapper_registry = registry()


class Contract(SQLModel, table=True):
    """A contract defines the business conditions of a project"""

    id: Optional[int] = Field(default=None, primary_key=True)
    title: str = Field(description="Short description of the contract.")
    client: "Client" = Relationship(
        back_populates="contracts",
    )
    client_id: Optional[int] = Field(
        default=None,
        foreign_key="client.id",
    )

    currency: str
    term_of_payment: Optional[int] = Field(
        description="How many days after receipt of invoice this invoice is due.",
        default=31,
    )
    contact_type: str

    __mapper_args__ = {
        "polymorphic_identity": "contract",
        "polymorphic_on": "contact_type",
    }


@mapper_registry.mapped
class TimeContract(Contract, table=True):
    """A time-based contract with a rate per time unit"""

    contract_id: Optional[int] = Field(
        default=None, foreign_key="contract.id", primary_key=True
    )

    rate: float = Field(
        description="Rate of remuneration",
    )

    unit: str = Field(
        description="Unit of time tracked. The rate applies to this unit.",
        default="hour",
    )

    __mapper_args__ = {
        "polymorphic_identity": "time",
    }


@mapper_registry.mapped
class WorksContract(Contract, table=True):
    """A contract with a fixed price"""

    contract_id: Optional[int] = Field(
        default=None, foreign_key="contract.id", primary_key=True
    )

    price: float = Field(
        description="Price of the contract",
    )
    deliverable: str = Field(description="Description of the deliverable")

    __mapper_args__ = {
        "polymorphic_identity": "works",
    }


class Client(SQLModel, table=True):
    """A client the freelancer has contracted with."""

    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    contracts: List["Contract"] = Relationship(back_populates="client")


engine = create_engine(
    "sqlite:///",
    # echo=True,
)

with Session(engine) as session:
    SQLModel.metadata.create_all(engine)

    c = Client()
    c.name = "client name"
    c.contracts = [
        TimeContract(currency=12.2, title="title1", term_of_payment=1, rate=3.4),
        WorksContract(
            title="title2",
            currency=43.4,
            term_of_payment=6,
            price=344.2,
            deliverable="---",
        ),
        TimeContract(currency=13.2, title="title3", term_of_payment=12, rate=56.4),
    ]
    session.add(c)
    session.commit()
    session.refresh(c)

    contract_manager = with_polymorphic(Contract, [TimeContract, WorksContract])

    query = session.query(contract_manager)
    for i in query.all():
        print(i)

Edit:

Maybe we can fix the problem mentioned in 2 with this:

(main.py:292)

            for k, v in new_cls.__fields__.items():
                if isinstance(getattr(new_cls, k, None), InstrumentedAttribute):
                    continue
                col = get_column_from_field(v)

[clstaudt]

Thanks @meirdev for the code example, I will try that.

I still don't know how to prevent sqlmodel from creating the fields of the parent table in the child table.

If I do not care about minimizing the size of the database, is there still a problem with this?

[clstaudt]

Would it be too much to ask for a framework like SQLModel to abstract away these parts of the code so that it's straightforward to use inheritance in models? Perhaps some black Python magic could add this in the background when subclasses are created.

    __mapper_args__ = {
        "polymorphic_identity": "time",
    }

contract_manager = with_polymorphic(Contract, [TimeContract, WorksContract])

@mapper_registry.mapped

From a user perspective, querying by Contract base class rather than contract_manager is more intuitive. Could this be a direction for an enhancement? @tiangolo

[meirdev]

If I do not care about minimizing the size of the database, is there still a problem with this?

I think this is ok, and it looks like sqlalchemy updates both tables each time.

t = session.get(TimeContract, 1)
t.title = "new title"
session.add(t)
session.commit()

INFO sqlalchemy.engine.Engine UPDATE contract SET title=? WHERE contract.id = ?
INFO sqlalchemy.engine.Engine [generated in 0.00010s] ('new title', 1)
INFO sqlalchemy.engine.Engine UPDATE timecontract SET title=? WHERE timecontract.id = ? AND timecontract.contract_id = ?
INFO sqlalchemy.engine.Engine [generated in 0.00005s] ('new title', 1, 1)

All these configurations give you flexibility to manipulate and query your data, for example: if you want to know how much contract time you have, you don't need to use with_polymorphic:

session.query(TimeContract.id).count()

[mxdev88]

2. I still don't know how to prevent sqlmodel from creating the fields of the parent table in the child table.

SQLModel thinks the fields of the parent table are in the child table. This currently makes it unusable to use with joined table inheritance.

This workaround proposed here with mapper_registry = registry() does not work for me.

Ideally, joined table inheritance should work out of the box in SQLModel. In the meantime, does anyone have a fully working example?

[FredericLeuba]

Hello,
Is there anything new on this topic since then?
The proposed code fails with sqlmodel 0.0.16 (using pydantic 2.6.4 and sqlalchemy 2.0.28).
Is there another approach that is more SQLmodel-oriented?
Fred

[PaleNeutron]

Same issue here

[dclipca]

Inheritance support would be really nice

[alexjolig]

Is this really an inheritance issue? Looks more like an import issue.
I'm having the same issue, but when I merge all models in one module, then problem is gone.
The problem is I got many models with lots of code, so it's better to keep them separate. But to fix the circular import issue, I used the proposed workaround to use TYPE_CHEKING from the documents.
It fixes the circular import error, but seems like is causing this problem.

[KunxiSun]

I would like to suggest an enhancement for SQLModel to support single table inheritance, similar to what is available in SQLAlchemy. This feature would allow us to define a class hierarchy on a single database table, which can be extremely useful for scenarios where different types of entities share common attributes but also have their unique fields. It would greatly enhance the flexibility and power of SQLModel. Here's the SQLAlchemy documentation for reference: Single Table Inheritance.

[benmo1]

Agreed, I wouldn't see this as an optional feature

[HWiese1980]

I would so love to use SQLModel for something like

class MyBase(SQLModel, table=True):
  __tablename__ = "base"

  id: int | None = Field(default=None, primary_key=True)
  sub_objects: List["SubObject"] = Relationship(back_populates="base")
  type: str = Field()

  __mapper_args__ = {
      "polymorphic_identity": "base",
      "polymorphic_on": "type",
  }


class SubObject(SQLModel, table=True):
  __tablename__ = "subobjects"

  id: int | None = Field(default=None, primary_key=True)
  base_id: int | None = Field(default=None, foreign_key="base.id")
  base: MyBase | None = Relationship(back_populates="sub_objects")

#  class Config:
#    arbitrary_types_allowed = True

class MySubClass(MyBase):
  some_additional_column: str | None = Field(default = None)

  __mapper_args__ = {
      "polymorphic_identity": "subclass",
  }

class AnotherSubClass(MyBase):
  another_additional_column: str | None = Field(default = None)

  __mapper_args__ = {
      "polymorphic_identity": "another_subclass",
  }

trying to implement STI this way. However, I'm getting several errors

pydantic.errors.PydanticSchemaGenerationError: Unable to generate pydantic-core schema for sqlalchemy.orm.base.Mapped[list[sources.common.model.models_v2.example.SubObject]]. Set `arbitrary_types_allowed=True` in the model_config to ignore this error or implement `__get_pydantic_core_schema__` on your type to fully support it.

When adding arbitrary_types_allowed=True to the config, I am getting

ValueError: <class 'sqlalchemy.orm.base.Mapped'> has no matching SQLAlchemy type

I would love this to be simpler. I do not feel an intense desire to implement all this by hand using SQLAlchemy and Pydantic in parallel.

[Dude29]

Theres a MR for this by @PaleNeutron awaiting approval #1226

[Rami-Kassouf-FOO]

Bumping this as it is a really needed feature @tiangolo

[clstaudt]

Ever since 2022