Establish relationship using Relationship feature in Postgres #141

Activity

1. cabudies

   added

   questionFurther information is requestedFurther information is requested

   on Oct 20, 2021
2. JLHasson commented on Oct 26, 2021

   

   JLHasson

   on Oct 26, 2021

   More actions

   I'm not sure what exactly the problem with your code is. One small issue is that you're instantiating db_unit before you've declared CreateUnit. I've used the code below to try to replicate and I was unsuccessful:

   from typing import Optional, List
   
   from sqlmodel import Field, Session, SQLModel, create_engine, select, Relationship
   
   
   class CreateUnitType(SQLModel, table=True):
       id: Optional[int] = Field(default=None, primary_key=True)
       mass: str
   
       units: List["CreateUnit"] = Relationship(back_populates="createunittype")
   
   
   class CreateUnit(SQLModel, table=True):
       id: Optional[int] = Field(default=None, primary_key=True)
       unit: str
   
       unit_type_id: Optional[int] = Field(default=None, foreign_key="createunittype.id")
       createunittype: Optional[CreateUnitType] = Relationship(back_populates="units")
   
   
   sqlite_file_name = "database.db"
   sqlite_url = f"sqlite:///{sqlite_file_name}"
   
   engine = create_engine(sqlite_url, echo=True)
   
   if __name__ == "__main__":
       SQLModel.metadata.create_all(engine)
   
       unit = CreateUnit(unit="unit", createunittype=CreateUnitType(mass="1kg"))
       print(unit)
   
       with Session(engine) as session:
           session.add(unit)
   
           session.commit()
       with Session(engine) as session:
           statement = select(CreateUnit)
           results = session.exec(statement)
           for unit in results:
               print(unit)
           statement = select(CreateUnitType)
           results = session.exec(statement)
           for unit in results:
               print(unit)

   Output:

   2021-10-26 00:33:23,703 INFO sqlalchemy.engine.Engine SELECT createunit.id, createunit.unit, createunit.unit_type_id
   FROM createunit
   2021-10-26 00:33:23,703 INFO sqlalchemy.engine.Engine [no key 0.00014s] ()
   unit='unit' id=1 unit_type_id=1
   2021-10-26 00:33:23,705 INFO sqlalchemy.engine.Engine SELECT createunittype.id, createunittype.mass
   FROM createunittype
   2021-10-26 00:33:23,705 INFO sqlalchemy.engine.Engine [no key 0.00013s] ()
   id=1 mass='1kg'
   2021-10-26 00:33:23,705 INFO sqlalchemy.engine.Engine ROLLBACK
   

   Granted I tested this with a SQLite DB but I'm not sure that it should make a different (afaik Postgres should behave the same way).

   Can you try the code I've pasted above and let me know if it works for you?

   👍React with 👍1Reacted by Toni Alatalo
3. fastapi

   locked and limited conversation to collaborators on Aug 12, 2025
4. YuriiMotov

   converted this issue into a discussion #1503 on Aug 12, 2025