open ended discussion: use of PEP 593 in the "model" ecosystem

[adriangb]

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

See description

Description

This is a continuation of a discussion on Twitter: https://twitter.com/tiangolo/status/1484092599166287874

I think the work in SQLModel is amazing, I can't even begin to comprehend how complex it is behind the scenes. One thing I've been wondering about, not related to SQLModel in particular but rather to the general ecosystem of "models" in Python (classes with fields? not sure what the technical term is here. I'm referring to dataclasses, Pydantic, SQLAlchemy, Piccolo, etc.) is if we could use PEP 593's Annotated to increase composability between these libraries.

Most of these libraries use some sort of marker as a default value on fields to include metadata:

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

I'm only using SQLModel as an example here, but Field could be Pydantic's Field, SQLAlchemy's Column, dataclasses' field, etc.

The issue here is that Field is only valid in the context ofSQLModel (or Pydantic or SQLAlchemy or whatever particular library). And it has to contain information for Pydantic (like JSON schema examples) as well as SQLAlchemy (primary keys, etc.).

Using Annotated this could look like:

class Hero(SQLModel, table=True):
    id: Annotated[Optional[int], Field(examples=....), Column(primary_key=True)] = None

In other words, you can have any number of markers you want, and the libraries that use these markers can just ignore markers they don't recognize.

Possibly even more difficult, if we could move away from base classes with meta classes that would improve composability even further. Think from pydantic import to_json; to_json(SomeModel) instead of having Pydantic create SomeModel.json. I think that's a totally different topic though.

I'm not sure if this solves any problems, but I thought it's an interesting idea worth sharing.

Operating System

Linux

Operating System Details

No response

SQLModel Version

N/A

Python Version

N/A

Additional Context

No response

[ljluestc]

from typing import Optional from sqlmodel import SQLModel, Field from sqlalchemy import Column, Integer from sqlalchemy.ext.declarative import declarative_base from typing_extensions import Annotated

BaseSQLModel = declarative_base() # SQLAlchemy declarative base class

class SQLModelMixin(SQLModel): class Config: orm_mode = True

class AnnotatedField: def init(self, *annotations): self.annotations = annotations

class Hero(SQLModelMixin, BaseSQLModel): tablename = "heroes" id: Annotated[Optional[int], Field(default=None, primary_key=True), Column(Integer)] = None name: Annotated[str, Field(default=None), Column(String)]

Usage

hero_instance = Hero(id=1, name="Superman")

print(hero_instance)

[janheinrichmerker]

That would be totally awesome!

[janheinrichmerker]

@tiangolo Just did a quick test, because this now seems to be supported by Pydantic:

This works just as expected:

class Foo(SQLModel, table=True):
    id: Annotated[int | None, Field(primary_key=True)] = None
    uuid: Annotated[UUID, Field(unique=True)]

...and returns this SQL:

CREATE TABLE foo (
        id INTEGER NOT NULL, 
        uuid CHAR(32) NOT NULL, 
        PRIMARY KEY (id), 
        UNIQUE (uuid)
)

But this does not work:

class Foo(SQLModel, table=True):
    id: Annotated[int | None, Field(primary_key=True)] = None
    uuid: Annotated[UUID, Field(unique=True)]

class Bar(SQLModel, table=True):
    id: Annotated[int | None, Field(primary_key=True)] = None
    uuid: Annotated[UUID, Field(unique=True)]
    foo_id: Annotated[int, Field(foreign_key="foo.id")]
    foo: Annotated[Foo, Relationship()]

...instead, it throws a ValueError: <class 'example.Foo'> has no matching SQLAlchemy type.

When using the non-Annotated syntax for the relationship, it works as expected (notice the difference in the last line):

class Foo(SQLModel, table=True):
    id: Annotated[int | None, Field(primary_key=True)] = None
    uuid: Annotated[UUID, Field(unique=True)]

class Bar(SQLModel, table=True):
    id: Annotated[int | None, Field(primary_key=True)] = None
    uuid: Annotated[UUID, Field(unique=True)]
    foo_id: Annotated[int, Field(foreign_key="foo.id")]
    foo: Foo = Relationship()

...and correctly creates the foreign key in SQL:

CREATE TABLE foo (
        id INTEGER NOT NULL, 
        uuid CHAR(32) NOT NULL, 
        PRIMARY KEY (id), 
        UNIQUE (uuid)
)
CREATE TABLE bar (
        id INTEGER NOT NULL, 
        uuid CHAR(32) NOT NULL, 
        foo_id INTEGER NOT NULL, 
        PRIMARY KEY (id), 
        UNIQUE (uuid), 
        FOREIGN KEY(foo_id) REFERENCES foo (id)
)

I'm not sure how SQLModel "discovers" the relationship field, but it sounds not too complicated to adapt it to also look in the annotation, not just in the default value, given that Pydantic seems to parse these annotations already. Maybe there's some way to just access them.