Using mixins or .... to reduce boilerplate and junk in models

[jfb-workhelix]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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 Dataset (SQLModel, table=True):
  id: UUID = ...  # primary key
  provenance: str = ...
  created_at: datetime = ... # as it says

class A (SQLModel, table=True):
  __table_args__ = (PrimaryKeyConstraint("id", "dataset_id"),
                    ForeignKeyConstraint(("dataset_id",) ("dataset.id",), onupdate="CASCADE", ondelete="CASCADE", deferrable=True))
  id: UUID
  dataset_id: UUID 

  a_info: int

  dataset: Dataset = Relationship()

class B (SQLModel, table=True):
  __table_args__ = (PrimaryKeyConstraint("id", "dataset_id"), 
                    ForeignKeyConstraint(("dataset_id",) ("dataset.id",), onupdate="CASCADE", ondelete="CASCADE", deferrable=True))

  id: UUID
  dataset_id: UUID 

  b_info: float

  dataset: Dataset = Relationship()


class Foo (SQLModel, table=True):
  __table_args__ = (..., # key and relationship definitions
                    { "schema" : "my_schema_name"})

Description

I have a bunch of standard fields that occur in all (or nearly all!) of my models, and I heavily use Postgres schema to help organize by table spaces. Most of my models have a lot of identical or nearly identical boilerplate, which tends to obscure the actual logic. What I'd love to be able to do is lift these things into a base class, or series of base classes, and then my model definitions can concentrate on their actual logic.

For instance, I have the concept of a Dataset (see above), which is a related set of records that is a) read-only and b) has a lineage to the various versions of the models used to compute the records. So, every table that contains data that is associated with a given Dataset has the following standardized fields and relationships: a dataset_id, a foreign key from dataset_id to datasets.id, and a primary key over id,dataset_id.

I'd love to be able to have a common base class, that defines those columns and relationships. What I'm having trouble with is the class variable __table_args__, which needs to be extended, as these subclasses have other table level metadata that's not captured in the (mooted) base class' definition. A similar problem arises with defining the schema, as the best way that I can figure that out is by adding a dict at the end of the __table_args__, which again is going to not be easy to manage via inheritance. I'm very happy to take any direction here; I'm relatively new to Python but I'm very experienced with databases and SQL.

TIA!

Operating System

Linux, macOS

Operating System Details

No response

SQLModel Version

0.0.3

Python Version

3.11.2

Additional Context

No response

[jfb-workhelix]

I'm more flexible on the Python side than the SQL one, as I have non-python users of the databases, so I move as much correctness checking as I can get away with into the Postgres side.

[antont]

You can declare primary keys like this, so at least for that purpose you don't need to touch __table_args__, and mixins work.

    id: int = Field(primary_key=True)

[gamesbook]

I could not get the ForeignKeyConstraint syntax correct on my side, but here is some almost-similar code to handle __table_args__ inheritance:

class Dataset(SQLModel, table=True):
    id: uuid.UUID = Field(default=None, primary_key=True)


class MainBase(SQLModel):
  __table_args__ = (
      PrimaryKeyConstraint("id", "dataset_id"))
  id: uuid.UUID = Field(default=None, primary_key=True)
  dataset_id: uuid.UUID


class BaseA(MainBase):
    __table_args__ = tuple(list(MainBase.__table_args__) + [
        UniqueConstraint("dataset_id", "name", name="unique_mainbase_name")]
    )
    name: Optional[str] = Field(default=None)


class A(BaseA, table=True):
    counter: Optional[int]