one-to-one relationships with sqlmodel

Question

After working through the tutorial of SQLModel, I don't remember seeing anything on how to implement 1:1 relationships using Relationship attributes.

I found documentation for SQLAlchemy, but it's not immediately clear how this applies to SQLModel.

Code example: How to enforce that User and ICloudAccount have a 1:1 relationship?

class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    icloud_account_id: Optional[int] = Field(default=None, foreign_key="icloudaccount.id")
    icloud_account: Optional["ICloudAccount"] = Relationship(back_populates="users")


class ICloudAccount(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    user_name: str
    users: List[User] = Relationship(back_populates="icloud_account")

Ralston

Answer 1

You can turn off the list functionality to allow SQLModel to foreign key as a one-to-one. You do this with the SQLalchemy keyword uselist

class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    icloud_account_id: Optional[int] = Field(default=None, foreign_key="icloudaccount.id")
    icloud_account: Optional["ICloudAccount"] = Relationship(back_populates="user")


class ICloudAccount(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    user_name: str
    user: Optional["User"] = Relationship(
        sa_relationship_kwargs={'uselist': False},
        back_populates="icloud_account"
    )

Confine