Repository navigation
Saving Link Model with Extra fields fails #339
Description
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
# Models
class AssemblyEPDLink(SQLModel, table=True):
assembly_id: Optional[str] = Field(default=None, foreign_key="assembly.id", primary_key=True)
epd_id: Optional[str] = Field(default=None, foreign_key="epd.id", primary_key=True)
conversion_factor: float = 1.0
assembly: "Assembly" = Relationship(back_populates="layer_links")
epd: "EPD" = Relationship(back_populates="assembly_links")
class Assembly(SQLModel, table=True):
"""Assembly database class"""
id: Optional[str] = Field(default_factory=string_uuid, primary_key=True)
name: str = Field(index=True)
category: str
life_time: float = 50
meta_fields: dict = Field(default=dict, sa_column=Column(JSON), nullable=False)
unit: str = "M2"
conversion_factor: float = 1.0
layer_links: List[AssemblyEPDLink] = Relationship(back_populates="assembly")
class EPD(SQLModel, table=True):
"""EPD database class"""
id: Optional[str] = Field(default_factory=string_uuid, primary_key=True)
name: str = Field(index=True)
category: str
gwp_by_phases: dict = Field(default=dict, sa_column=Column(JSON), nullable=False)
version: str
expiration_date: date
date_updated: date
source: str
assembly_links: List[AssemblyEPDLink] = Relationship(back_populates="epd")
# Strawberry Mutation
@strawberry.type
class Mutation:
@strawberry.mutation(permission_classes=[IsAuthenticated])
async def add_assembly(
self,
info: Info,
name: str,
category: str,
life_time: float | None = 50,
meta_fields: Optional[JSON] = None,
layers: Optional[JSON] = None,
conversion_factor: float | None = 1,
) -> GraphQLAssembly:
if meta_fields is None:
meta_fields = {}
session = info.context.get("session")
assembly = Assembly(
name=name,
category=category,
life_time=life_time,
conversion_factor=conversion_factor,
meta_fields=meta_fields,
)
if layers:
query = select(EPD).where(col(EPD.id).in_([layer.get("id") for layer in layers]))
epds = await session.exec(query)
epds = epds.all()
for epd in epds:
link = AssemblyEPDLink(assembly=assembly, epd=epd, conversion_factor=2)
session.add(link)
else:
session.add(assembly)
await session.commit()
await session.refresh(assembly)
return assemblyDescription
- I start my test
- Set up a blank database and run
SQLModel.metadata.create_all(bind=engine) - I create three EPDs and save them to the database
- Then I post a new mutation to create a new Assembly with those EPDs in "layers"
- When I try to commit the AssemblyEPDLink to the session the database throws an error:
'asyncpg.exceptions.NotNullViolationError': null value in column "assembly_id" of relation "assemblyepdlink" violates not-null constraint
Operating System
Linux
Operating System Details
Ubuntu 22.04
SQLModel Version
0.0.6
Python Version
3.10
Additional Context
I have tried to follow the steps outlined in the docs, adapted to my situation, but somehow the link model doesn't want to save to the database.
When I create an Assembly without a link model everything works fine.
I have a setup with FastAPI, Strawberry, SQLModel and postgres.
Activity
[-]Failing saving Link Model with Extra fields[/-][+]Saving Link Model with Extra fields fails[/+]ocni-dtu commentedon May 17, 2022
So I did some investigation into this issue.
If I run the Tutorial 003 script with SQLAlchemy < 1.4.36 I get no problems. Likewise with postgres as the database.
If I use SQLAlchemy = 1.4.36 I get this error:
ocni-dtu commentedon May 17, 2022
If I instead change the version with postgres as database to async I get this error no mater, which version of SQLAlchemy I use:
ocni-dtu commentedon May 17, 2022
Okay, so with a little more tweaking of the async code I got it to work.
My SQLAlchemy is stil < 1.4.36:
sqlalchemy[asyncio] == 1.4.35What I needed was to add several
selectinloadoptions to the queries inupdate_heroesocni-dtu commentedon May 17, 2022
Related to #322
fullstack-spiderman commentedon May 26, 2022
I'm facing the same issue, how do we choose to use the older sqlalchemy version? We just install SQLModel and I'm not seeing the sqlalchemy in dependency list in pyproject.toml as I use poetry.. Im aware that SQLModel uses SQLAlchemy in backstage..
ocni-dtu commentedon May 29, 2022
@arjunkayalmoni you should just pin the version of sqlalchemy.
You can do that by installing sqlalchemy as the right version and Poetry will take care of resolving the dependencies of SQLModel
fullstack-spiderman commentedon May 30, 2022
Thank you @ocni-dtu, yes I done that already with a suggestion by my co-worker, and it indeed worked.. thank you again.. :)