User Profile — One-to-One Relationship

A one-to-one relationship pairs one user with one profile, one order with one shipping address, one company with one billing account. In SQLAlchemy, one-to-one is a special case of one-to-many where the “many” side is constrained to at most one row — enforced by a UNIQUE constraint on the foreign key column and by using Mapped["Profile | None"] (not Mapped[list["Profile"]]). The UserProfile pattern is common: users have a profile with bio, avatar, and social links stored separately to keep the core users table lean and allow profile fields to be null without cluttering the main table.

UserProfile Model — One-to-One #

# app/models/user_profile.py
from sqlalchemy import String, Text, ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.sql import func
from datetime import datetime
from app.database import Base

class UserProfile(Base):
    __tablename__ = "user_profiles"

    id:          Mapped[int]          = mapped_column(primary_key=True)
    # UNIQUE on user_id enforces the one-to-one constraint
    user_id:     Mapped[int]          = mapped_column(
                     ForeignKey("users.id", ondelete="CASCADE"),
                     unique=True,        # ← the key to one-to-one
                     index=True,
                 )
    bio:         Mapped[str | None]   = mapped_column(Text,        nullable=True)
    avatar_url:  Mapped[str | None]   = mapped_column(String(500), nullable=True)
    website_url: Mapped[str | None]   = mapped_column(String(500), nullable=True)
    twitter:     Mapped[str | None]   = mapped_column(String(100), nullable=True)
    github:      Mapped[str | None]   = mapped_column(String(100), nullable=True)
    updated_at:  Mapped[datetime]     = mapped_column(
                     server_default=func.now(), onupdate=func.now())

    # ── Relationship ──────────────────────────────────────────────────────────
    user: Mapped["User"] = relationship(back_populates="profile")


# In User model:
# profile: Mapped["UserProfile | None"] = relationship(
#     back_populates="user",
#     uselist=False,         # ← critical: makes it return one object, not a list
#     cascade="all, delete-orphan",
# )

Profile Schemas and Router #

from pydantic import BaseModel, Field, AnyHttpUrl
from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy import select
from sqlalchemy.orm import Session, joinedload

class ProfileUpdate(BaseModel):
    bio:         str | None = Field(None, max_length=500)
    avatar_url:  str | None = None
    website_url: str | None = None
    twitter:     str | None = Field(None, max_length=50, pattern=r"^@?[\w]+$")
    github:      str | None = Field(None, max_length=50, pattern=r"^[\w-]+$")

class ProfileResponse(BaseModel):
    model_config = {"from_attributes": True}
    bio:         str | None
    avatar_url:  str | None
    website_url: str | None
    twitter:     str | None
    github:      str | None

class UserWithProfile(BaseModel):
    model_config = {"from_attributes": True}
    id:      int
    name:    str
    email:   str
    profile: ProfileResponse | None = None

router = APIRouter(prefix="/users", tags=["Users"])

@router.get("/{user_id}", response_model=UserWithProfile)
def get_user(user_id: int, db: Session = Depends(get_db)):
    user = db.scalars(
        select(User)
        .options(joinedload(User.profile))   # eager-load profile
        .where(User.id == user_id)
    ).first()
    if not user:
        raise HTTPException(404, "User not found")
    return user

@router.put("/me/profile", response_model=ProfileResponse)
def update_my_profile(
    update_in:    ProfileUpdate,
    db:           Session = Depends(get_db),
    current_user: User    = Depends(get_current_user),
):
    # Get or create profile
    profile = db.scalars(
        select(UserProfile).where(UserProfile.user_id == current_user.id)
    ).first()

    if not profile:
        profile = UserProfile(user_id=current_user.id)
        db.add(profile)

    for field, value in update_in.model_dump(exclude_none=True).items():
        setattr(profile, field, value)

    db.flush()
    db.refresh(profile)
    return profile

Auto-Create Profile at Registration #

@router.post("/register", response_model=UserWithProfile, status_code=201)
def register(user_in: UserCreate, db: Session = Depends(get_db)):
    # Create user
    user = User(
        email         = user_in.email,
        name          = user_in.name,
        password_hash = hash_password(user_in.password),
    )
    db.add(user)
    db.flush()   # get user.id

    # Automatically create empty profile
    profile = UserProfile(user_id=user.id)
    db.add(profile)
    db.flush()
    db.refresh(user)
    return user

Common Mistakes #

Mistake 1 — Forgetting uselist=False (profile returned as list) #

❌ Wrong — profile is a list:

profile: Mapped["UserProfile"] = relationship(back_populates="user")
# user.profile → [UserProfile(...)] not UserProfile(...)!

✅ Correct:

profile: Mapped["UserProfile | None"] = relationship(
    back_populates="user", uselist=False   # ✓ returns one object
)

Mistake 2 — Missing UNIQUE constraint (two profiles for one user) #

❌ Wrong — no uniqueness enforced at DB level:

user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))   # no unique!

✅ Correct:

user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), unique=True)   # ✓

Mistake 3 — Lazy loading profile outside session #

❌ Wrong — profile accessed after session closed:

user = db.get(User, user_id)
return UserWithProfile(id=user.id, profile=user.profile)   # profile lazy load fails!

✅ Correct — eager load with joinedload before accessing.

Quick Reference #