Relationships in SQLAlchemy

I got curious when I was looking over a Claude designed model for my weight tracker app. My intent was to learn why it didn’t full out normalize some tables. It scoffed and told me not to over-engineer my tables due to the scope of the audience. I might have made up the scoffing part, but my feelings were still hurt. Anyway, I noticed the relationship() and wanted to know more. Is it like a foreign key? That’s when I learned how SQLAlchemy was using a relationship feature and abstracting some work behind the scenes.

class WeightEntry(Base):
    __tablename__ = "weight_entries"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=lambda: str(uuid.uuid4()))
    user_id: Mapped[str] = mapped_column(
        String, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True
    )
    weight_value: Mapped[float] = mapped_column(Float, nullable=False)
    recorded_at: Mapped[date] = mapped_column(Date, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    user = relationship("User", back_populates="weight_entries")

Here’s the tables for reference.

It does what it says. It creates a relationship and lets you move through objects without have to write JOIN queries. For example:

without the relationship()

# without the relationship()
user = db.query(User).filter(User.id == entry.user_id).first()

# with the relationship
entry.user

The SQL still happens, but it’s done behind the scene.

There’s another interesting thing we get with the relationship….cascading. because there’s now a relationship between the user of users and entries of weight_entries, we can use this relationship to run through the entries automatically. For example in the user model there is:

weight_entries = relationship("WeightEntry", back_populates="user", cascade="all, delete-orphan")

class User(Base):
    __tablename__ = "users"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=lambda: str(uuid.uuid4()))
    email: Mapped[str] = mapped_column(String(255), unique=True, nullable=False, index=True)
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    hashed_password: Mapped[str] = mapped_column(Text, nullable=False)
    weight_unit: Mapped[str] = mapped_column(String(3), nullable=False, default="lbs")
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    weight_entries = relationship("WeightEntry", back_populates="user", cascade="all, delete-orphan")

So now when someone wants to delete their weight profile

db.delete(current_user)

runs through and deletes all their entries. Cool.

I had originally intended this to be about why Claude chose not to normalize. I’m really curious about that, but I got side tracked on this relationship thing. 🙂