Error when using Relationships with one-to-one models

[br-follow]

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

class User(UserBase, table=True):
    __tablename__ = "users"

    id: uuid.UUID = Field(
        default_factory=uuid.uuid4,
        primary_key=True,
        index=True,
        nullable=False,
    )

    contact_info: Optional["ContactInfo"] = Relationship(
        sa_relationship=RelationshipProperty(
            "ContactInfo", uselist=False, back_populates="user"
        ),
    )

class ContactInfo(ContactInfoBase, table=True):
    __tablename__ = "contact_info"

    id: uuid.UUID = Field(
        default_factory=uuid.uuid4,
        primary_key=True,
        index=True,
        nullable=False,
    )

    # Connect to users table
    user_id: uuid.UUID = Field(default=None, foreign_key="users.id")
    user: User = Relationship(back_populates="contact_info")

class Repository:
    def __init__(self, *, user_repository: UserRepository = Depends()):
        self.user_repository = user_repository

    def create_contact_info(
        self,
        *,
        session: Session,
        contact_info_create: ContactInfoCreate,
        user: User,
    ) -> ContactInfo:
        db_contact_info = ContactInfo.from_orm(contact_info_create)
        db_contact_info.user = user

        session.add(db_contact_info)
        session.commit()
        session.refresh(db_contact_info)
        return db_contact_info

Description

An exception is raised when trying to set the user field on the ContactInfo.

I have attempted to follow the instructions from sqlalchemy about one-to-one, though there is no documentation on that kind of relationship from SQLModel.

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/uvicorn/protocols/http/httptools_impl.py", line 375, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "/usr/local/lib/python3.9/site-packages/uvicorn/middleware/proxy_headers.py", line 75, in __call__
    return await self.app(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/fastapi/applications.py", line 208, in __call__
    await super().__call__(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/starlette/applications.py", line 112, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/starlette/middleware/errors.py", line 181, in __call__
    raise exc from None
  File "/usr/local/lib/python3.9/site-packages/starlette/middleware/errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "/usr/local/lib/python3.9/site-packages/starlette/middleware/cors.py", line 86, in __call__
    await self.simple_response(scope, receive, send, request_headers=headers)
  File "/usr/local/lib/python3.9/site-packages/starlette/middleware/cors.py", line 142, in simple_response
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/starlette/exceptions.py", line 82, in __call__
    raise exc from None
  File "/usr/local/lib/python3.9/site-packages/starlette/exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 580, in __call__
    await route.handle(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 241, in handle
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 52, in app
    response = await func(request)
  File "/usr/local/lib/python3.9/site-packages/fastapi/routing.py", line 226, in app
    raw_response = await run_endpoint_function(
  File "/usr/local/lib/python3.9/site-packages/fastapi/routing.py", line 161, in run_endpoint_function
    return await run_in_threadpool(dependant.call, **values)
  File "/usr/local/lib/python3.9/site-packages/starlette/concurrency.py", line 40, in run_in_threadpool
    return await loop.run_in_executor(None, func, *args)
  File "/usr/local/lib/python3.9/concurrent/futures/thread.py", line 52, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/app/./app/api/routes/kyc.py", line 43, in create_contact_info
    kyc_repository.create_contact_info(
  File "/app/./app/api/repositories/kyc_repository.py", line 28, in create_contact_info
    db_contact_info.user = user
  File "/usr/local/lib/python3.9/site-packages/sqlmodel/main.py", line 516, in __setattr__
    set_attribute(self, name, value)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 2240, in set_attribute
    state.manager[key].impl.set(state, dict_, value, initiator)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 1253, in set
    value = self.fire_replace_event(state, dict_, value, old, initiator)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 1279, in fire_replace_event
    value = fn(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 1714, in emit_backref_from_scalar_set_event
    instance_state(child),
AttributeError: Could not locate column in row for column '_sa_instance_state'

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.9.7

Additional Context

No response

[yasamoka]

This is related to SQLAlchemy and not to SQLModel.

[br-follow]

Perhaps, though the SQLAlchemy team believed this was a SQLModel issue.

[zzzeek]

This is related to SQLAlchemy and not to SQLModel.

the given MCVE does not even have enough information for you to know that, as it does not include enough information for it to be run. on the SQLAlchemy side, we dont run examples that include third party dependencies like SQLModel since we dont have the bandwidth to a. learn all the third party systems b. encourage users to send complete examples using said third party system c. debug the program in terms of the third party system and then d. 50-90% of the time the issue is in the third party system.

from my pov the issue looks very much like a mapping that is incorrect in some way, as "_sa_instance_state" is a private attribute that would never be requested as a column. No such issue has ever been reported and I can hardly imagine how such a thing would happen, except if one were using some custom attribute mapping system that is somehow confusing the attributes on the class, such as SQLModel. I see things like class User(UserBase, table=True): that's SQLModel's syntax which tells me that just such a system is in place. Additionally, the stack trace above shows that SQLModel's own attribute system is in the middle of what is producing this very strange error at File "/usr/local/lib/python3.9/site-packages/sqlmodel/main.py", line 516, in __setattr__ so I'm more or less 90% or more this is a SQLModel issue, but it's difficult to say as the example program is not complete nor does it illustrate how to reproduce the problem.

edit: to be clear, the stack trace looks completely nonsensical in fact as the exception message stated would be the result of the execution of SQL, which is absent in the stack trace , and not from the "instance_state()" callable which curiously is what actually tries to access the "_sa_instance_state" attribute, but there is no way I can imagine the attribute error would be stated in terms of a column missing in a SQL query as there is no connection between the "instance_state()" callable and anything to do with running SQL queries. The trace as shown should be completely impossible. The example refers to symbols such as "ContactInfo", "UserRepository" and "Depends" which I have no idea what those are, so without a way to run a complete program showing exactly how such a trace is generated we can't offer any guidance.

[yasamoka]

@zzzeek yes, you're right. I wanted to come back to this when I had time. I just had a cursory look and saw that the error was emitted by SQLAlchemy. What I had meant to say was that the error was being emitted there, not that there was an issue with SQLAlchemy causing this. We have many instances here where newcomers raise issues that are not bugs since they're unfamiliar with SQLAlchemy. I ran into a similar problem when I was using SQLModel recently and it very likely has something to do with the way the relationship is written. I'll have a go at trying to resolve this issue as soon as I have some time.

[br-follow]

Indeed, I was able to work around the issue by setting the relevant fields using the SQLAlchemy conventions, instead of SQLModel:

class User(UserBase, table=True):
    __tablename__ = "users"

    id: uuid.UUID = Field(
        default_factory=uuid.uuid4,
        primary_key=True,
        index=True,
        nullable=False,
    )
    contact_info: Optional["ContactInfo"] = Relationship(
        sa_relationship=RelationshipProperty(
            "ContactInfo", back_populates="user", uselist=False
        ),
    )

class ContactInfo(ContactInfoBase, table=True):
    user_id: uuid.UUID = Field(
        sa_column=Column("user_id", ForeignKey("users.id"), nullable=False)
    )
    user: User = Relationship(
        sa_relationship=RelationshipProperty("User", back_populates="contact_info")
    )

[arkryonia]

Indeed, I was able to work around the issue by setting the relevant fields using the SQLAlchemy conventions, instead of SQLModel:

class User(UserBase, table=True):
    __tablename__ = "users"

    id: uuid.UUID = Field(
        default_factory=uuid.uuid4,
        primary_key=True,
        index=True,
        nullable=False,
    )
    contact_info: Optional["ContactInfo"] = Relationship(
        sa_relationship=RelationshipProperty(
            "ContactInfo", back_populates="user", uselist=False
        ),
    )

class ContactInfo(ContactInfoBase, table=True):
    user_id: uuid.UUID = Field(
        sa_column=Column("user_id", ForeignKey("users.id"), nullable=False)
    )
    user: User = Relationship(
        sa_relationship=RelationshipProperty("User", back_populates="contact_info")
    )

It works! Good Job @brapaport !

[AthreyVinay]

@br-allstreet Hi Ben - I am facing a similar issue (I think). Would you be able to help me please? Thanks.
#284

[waketzheng]

Indeed, I was able to work around the issue by setting the relevant fields using the SQLAlchemy conventions, instead of SQLModel:

class User(UserBase, table=True):
    __tablename__ = "users"

    id: uuid.UUID = Field(
        default_factory=uuid.uuid4,
        primary_key=True,
        index=True,
        nullable=False,
    )
    contact_info: Optional["ContactInfo"] = Relationship(
        sa_relationship=RelationshipProperty(
            "ContactInfo", back_populates="user", uselist=False
        ),
    )

class ContactInfo(ContactInfoBase, table=True):
    user_id: uuid.UUID = Field(
        sa_column=Column("user_id", ForeignKey("users.id"), nullable=False)
    )
    user: User = Relationship(
        sa_relationship=RelationshipProperty("User", back_populates="contact_info")
    )

Worked! Thanks~
For who want to add unique=True, code can be:

from __future__ import annotations
from sqlalchemy.orm import RelationshipProperty

class User(UserBase, table=True):
    ...
    contact_info: ContactInfo | None = Relationship(
        sa_relationship=RelationshipProperty(
            "ContactInfo", back_populates="user", uselist=False
        ),
    )

class ContactInfo(ContactInfoBase, table=True):
    user_id: uuid.UUID = Field(
        foreign_key="users.id", nullable=False, ondelete="CASCADE", unique=True
    )
    user: User = Relationship(back_populates="contact_info")

[YuriiMotov]

Code example from initial comment works well in current version (0.0.24):

See runnable code example in the details:

import uuid
from typing import Optional

from sqlalchemy.orm import RelationshipProperty
from sqlmodel import (
    Field,
    Relationship,
    Session,
    SQLModel,
    create_engine,
)


class UserBase(SQLModel):
    pass

class ContactInfoBase(SQLModel):
    pass

class ContactInfoCreate(ContactInfoBase):
    pass


# ===================================================================================
# Copied from the issue as is

class User(UserBase, table=True):
    __tablename__ = "users"

    id: uuid.UUID = Field(
        default_factory=uuid.uuid4,
        primary_key=True,
        index=True,
        nullable=False,
    )

    contact_info: Optional["ContactInfo"] = Relationship(
        sa_relationship=RelationshipProperty(
            "ContactInfo", uselist=False, back_populates="user"
        ),
    )

class ContactInfo(ContactInfoBase, table=True):
    __tablename__ = "contact_info"

    id: uuid.UUID = Field(
        default_factory=uuid.uuid4,
        primary_key=True,
        index=True,
        nullable=False,
    )

    # Connect to users table
    user_id: uuid.UUID = Field(default=None, foreign_key="users.id")
    user: User = Relationship(back_populates="contact_info")

# ===================================================================================


def main():
    engine = create_engine("sqlite:///")
    SQLModel.metadata.create_all(engine)

    contact_info_create = ContactInfoCreate()
    user = User()

    with Session(engine) as session:
        # ===========================================================================
        # copied from the issue with one modification (resolve deprecation warning)

        db_contact_info = ContactInfo.model_validate(contact_info_create)  # Changed from `from_orm` to `model_validate`
        db_contact_info.user = user

        session.add(db_contact_info)
        session.commit()
        session.refresh(db_contact_info)
        # ===========================================================================

        print(db_contact_info)


if __name__ == "__main__":
    main()

Output:

user_id=UUID('85494375-6f44-4883-a150-652e7f0d515d') id=UUID('4d921ec4-bcc7-4ea7-80c4-3927b4d5ecad')