Problem with foreign keys being NULLED after commit and refresh if you create object using ID of related object

[SMorawiec]

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 Team(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str

    heroes: list["Hero"] = Relationship(back_populates="team")


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)

    team_id: int | None = Field(default=None, foreign_key="team.id")
    team: Team | None = Relationship(back_populates="heroes")

@fixture
def fake_db_session():
    engine = create_engine(
        "sqlite://",
        connect_args={"check_same_thread": False},
        poolclass=StaticPool,
    )
    SQLModel.metadata.create_all(engine)

    with Session(engine) as session:
        yield session

class TestCreate:
    @staticmethod
    def test_create_location_to_location(fake_db_session: Session):
        team = Team(name="Test Team", headquarters="Test Headquarters")
        hero = Hero(name="Test Hero", secret_name="Test Secret Name", age=30, team_id=team.id)
        #hero = Hero(name="Test Hero", secret_name="Test Secret Name", age=30, team=team)
        hero_db = Hero.model_validate(hero)
        
        fake_db_session.add(team)
        fake_db_session.add(hero_db)
        fake_db_session.commit()
        fake_db_session.refresh(team)
        fake_db_session.refresh(hero_db)
        breakpoint()

Description

Im running very simple example code (basically taken from tutorial.
I thought there are 2 ways of creating relationship.- using team or team_id (One hero instance creating is commented out).
The problem is that when I run the code above, Relationship is not created, and team_id of object hero_db is NULL.
Removing model_validate from the code resolves the problem, but in more complex examples, it is required to properly parse input.
Am I doing something wrong or is it really bad bug? Because I cant imagine passing the entire objects instead of IDs is a must for bigger objects.

Operating System

macOS

Operating System Details

No response

SQLModel Version

Name: sqlmodel Version: 0.0.22

Python Version

Python 3.12.7

Additional Context

No response

[arka-na]

We’re encountering the exact same issue. Initially, we thought it was related to SQLAlchemy itself, but after extensive debugging, we found that it only happens when implementing our models with SQLModel. The same logic works fine when using SQLAlchemy directly.
Has anyone found a workaround or a deeper explanation for why this happens specifically with SQLModel?

[SMorawiec]

I can share how we dealt with it - basically following tutorials (although it is blurry to my why it has to be that way:
You create HeroBase class that does NOT create a Table, and you use that as an input.
HeroBase consists of all fields you need in your data-input part (so everything except things like ID, or Relationship()'s, or Created_at fields.
Then you inherit from HeroBase into Hero(HeroBase, table=True) class
and in the code you do
Hero.model_validate(hero_base_object)

I dont have much time rn to give exact code to make it work, but I hope this helps you anyhow.

And again: I know how to make it work, I just dont understand why is it so difficult. Also if this line from my code does not work properly for some internal SQLModel reasones
hero_db = Hero.model_validate(hero) - it should produce error or at least WARNING that FK was not properly applied

[arka-na]

Thanks for the suggestion! We'll consider whether it's feasible for our codebase, which is quite large 😅
This used to work correctly, but after updating to the latest version (0.24, we were on the 0.11), we encountered this issue.