Integrate with SQLModel

[karolzlot]

SQLModel was released 4 days ago

I'm not even sure if such integration would be helpul or if it is a good idea. (or maybe it isn't needed and things will just work?)

But I can imagine how it would make things simpler:
You define database model in SQLModel (which is also a Pydantic model) and the same model is working as GraphQL endpoint thanks to strawberry.

This is assuming that you want to expose all columns from a database table, which is often not the case. I suspect it may require more work if you would want to expose only some columns.

Related: #444

[jkimbo]

I think we absolutely should integrate with SQLModel, which looks like a very cool project. I actually decided to try and see if it would work with our current pydantic integration and it does! The following works in my local tests:

    class UserModel(SQLModel, table=True):
        id: Optional[int] = Field(default=None, primary_key=True)
        age: int
        password: Optional[str]

    @strawberry.experimental.pydantic.type(UserModel, fields=["age", "password"])
    class User:
        pass

    @strawberry.type
    class Query:
        @strawberry.field
        def user(self) -> User:
            return User(age=1, password="ABC")

    schema = strawberry.Schema(query=Query)

    query = "{ user { age } }"

    result = schema.execute_sync(query)

    assert not result.errors
    assert result.data == { "user": { "age": 1 } }

Now we should create a specific integration for SQLModel but it shouldn't be that different to the pydantic integration which is great.

[giulianomarinella]

@jkimbo I'm using SQLModel and Strawberry in my project, but I don't figue out how to make it work with sqlmodel.Relationship(). Did you try using it?

[jkimbo]

I didn't try that unfortunately. I think we'd have to do a proper integration to get that to work.

[giulianomarinella]

I'll leave this example here about how I made it work with Relationship fields!

import strawberry
from typing import List, Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select, Relationship

class TeamModel(SQLModel, table=True):
    __tablename__ = 'team'
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    headquarters: str

    heroes: List["HeroModel"] = Relationship(back_populates="team")


class HeroModel(SQLModel, table=True):
    __tablename__ = 'hero'
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None

    team_id: Optional[int] = Field(default=None, foreign_key="team.id")
    team: Optional[TeamModel] = Relationship(back_populates="heroes")


@strawberry.experimental.pydantic.type(model=HeroModel, fields=['name', 'secret_name', 'age', 'team_id', 'team'])
class Hero: 
    pass

@strawberry.experimental.pydantic.type(model=TeamModel, fields=['name', 'headquarters', 'heroes', 'id'])
class Team:
    heroes: List["Hero"] = None

sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

connect_args = {"check_same_thread": True}
engine = create_engine(sqlite_url, connect_args=connect_args)


SQLModel.metadata.create_all(engine)

def create_heroes():
    with Session(engine) as session:
        
        team_preventers = TeamModel(name="Preventers", headquarters="Sharp Tower")
        team_z_force = TeamModel(name="Z-Force", headquarters="Sister Margaret’s Bar")

        hero_deadpond = HeroModel(
            name="Deadpond", secret_name="Dive Wilson", team=team_z_force
        )
        hero_rusty_man = HeroModel(
            name="Rusty-Man", secret_name="Tommy Sharp", age=48, team=team_preventers
        )
        hero_spider_boy = HeroModel(name="Spider-Boy", secret_name="Pedro Parqueador")
        session.add(hero_deadpond)
        session.add(hero_rusty_man)
        session.add(hero_spider_boy)
        session.commit()

        session.refresh(hero_deadpond)
        session.refresh(hero_rusty_man)
        session.refresh(hero_spider_boy)

        print("Created hero:", hero_deadpond)
        print("Created hero:", hero_rusty_man)
        print("Created hero:", hero_spider_boy)

        hero_spider_boy.team = team_preventers
        session.add(hero_spider_boy)
        session.commit()
        session.refresh(hero_spider_boy)
        print("Updated hero:", hero_spider_boy)

        hero_black_lion = HeroModel(name="Black Lion", secret_name="Trevor Challa", age=35)
        hero_sure_e = HeroModel(name="Princess Sure-E", secret_name="Sure-E")
        team_wakaland = TeamModel(
            name="Wakaland",
            headquarters="Wakaland Capital City",
            heroes=[hero_black_lion, hero_sure_e],
        )
        session.add(team_wakaland)
        session.commit()
        session.refresh(team_wakaland)
        print("Team Wakaland:", team_wakaland)

        hero_tarantula = HeroModel(name="Tarantula", secret_name="Natalia Roman-on", age=32)
        hero_dr_weird = HeroModel(name="Dr. Weird", secret_name="Steve Weird", age=36)
        hero_cap = HeroModel(
            name="Captain North America", secret_name="Esteban Rogelios", age=93
        )

        team_preventers.heroes.append(hero_tarantula)
        team_preventers.heroes.append(hero_dr_weird)
        team_preventers.heroes.append(hero_cap)
        session.add(team_preventers)
        session.commit()
        session.refresh(hero_tarantula)
        session.refresh(hero_dr_weird)
        session.refresh(hero_cap)
create_heroes()

def get_heroes() -> List[Hero]:
    with Session(engine) as session:
        heroes = session.exec(select(HeroModel)).all()
        return heroes

def get_team(team_id: int) -> Team:
    with Session(engine) as session:
        team = session.get(TeamModel, team_id)
        data = Team.from_pydantic(team)
        return data


@strawberry.type
class Query:
    heroes: List[Hero] = strawberry.field(resolver=get_heroes)
    team: Team = strawberry.field(resolver=get_team)

schema = strawberry.Schema(query=Query)

[lukesmurray]

One of the limitations I've run into with sqlmodel is bidirectional relationships. Here are two repl.its.

1. https://replit.com/@lukesmurray/strawberry-limitations#main.py
2. https://replit.com/@lukesmurray/strawberry-limitations-1#main.py

They use similar models to the code presented by @giulianomarinella.

The first one works because the Team type has access to heroes but the Hero type does not have access to Team.

@strawberry.experimental.pydantic.type(
    HeroModel, fields=["id", "name", "secret_name", "age", "team_id", "team"])
class Hero:
    pass


@strawberry.experimental.pydantic.type(
    TeamModel, fields=["id", "name", "headquarters", "heroes"])
class Team:
    heroes: List["Hero"]

The second one does not work because the Hero model has access to Team. we get a type hints error.

@strawberry.experimental.pydantic.type(
    HeroModel, fields=["id", "name", "secret_name", "age", "team_id", "team"])
class Hero:
    # now we make the relationship bidirectional and we fail
    team: Optional["Team"]


@strawberry.experimental.pydantic.type(
    TeamModel, fields=["id", "name", "headquarters", "heroes"])
class Team:
    heroes: List["Hero"]

Not sure if there is a way to get around this limitation but it is the only limitation I've run into so far.

[lovetoburnswhen]

You might need some combination of from __future__ import annotations and LazyType. I was able to get it working with the latter but I'm afk right now

[lukesmurray]

https://replit.com/@lukesmurray/strawberry-limitations-1#main.py

Here is an example which works

class TeamModel(SQLModel, table=True):
    __tablename__ = "team"
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    headquarters: str

    heroes: List["HeroModel"] = Relationship(back_populates="team")


class HeroModel(SQLModel, table=True):
    __tablename__ = "hero"
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None

    team_id: Optional[int] = Field(default=None, foreign_key="team.id")
    team: Optional[TeamModel] = Relationship(back_populates="heroes")

@strawberry.experimental.pydantic.type(
    HeroModel, fields=["id", "name", "secret_name", "age", "team_id", "team"])
class Hero:

    team: Optional[strawberry.LazyType["Team", __module__]]


@strawberry.experimental.pydantic.type(
    TeamModel, fields=["id", "name", "headquarters", "heroes"])
class Team:
    heroes: List["Hero"]


def get_heroes() -> List[Hero]:
    with Session(engine) as session:
        heroes = session.exec(select(HeroModel)).all()
        return heroes


def get_teams() -> List[Team]:
    with Session(engine) as session:
       # use eager loading for up to three level nesting. probably want to add this dynamically.
        heroes = session.exec(select(TeamModel).options(selectinload(TeamModel.heroes).selectinload(HeroModel.team))).all()
        return heroes


@strawberry.type
class Query:
    allHeroes: List[Hero] = strawberry.field(resolver=get_heroes)
    allTeams: Team = strawberry.field(resolver=get_teams)

works with the following query

query foo {
  allTeams {
    id
    name
    heroes {
      id
      name 
      team {
        name
      }
    }
  }
}

[SamuelSmets]

Hey @lukesmurray in your example, why can't you query the team which belongs to the hero?

I tried the query in your example:

query foo {
  allHeroes{
    name
    team {
      name
    }
  }
}

But I get the error:

{
  "data": null,
  "errors": [
    {
      "message": "Cannot query field 'team' on type 'Hero'. Did you mean 'teamId'?",
      "locations": [
        {
          "line": 35,
          "column": 5
        }
      ],
      "path": null
    }
  ]
}

How can I make sure that I actually can query the team on a Hero?

[lukesmurray]

haven't used this library in 2 years so sorry not sure.

[gazorby]

If anyone interested, #1637 attempts to add full support for SQLModel (and ormar)