Likes and Reactions — User-to-Post Association with Counts

The likes (reactions) system is a user-to-post association where each user-post pair is unique — you can only like a post once. This is a many-to-many relationship with the user on one side and posts on the other, implemented as an explicit PostLike model (rather than a plain association table) so it can carry extra data like the reaction type or timestamp. The key API pattern is a toggle: one endpoint that likes the post if the user has not liked it yet, and unlikes it if they have — idempotent from the user’s perspective. Returning whether the current user has liked each post in list responses requires efficient batch loading.

PostLike Model #

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

class PostLike(Base):
    __tablename__ = "post_likes"
    __table_args__ = (
        UniqueConstraint("post_id", "user_id", name="uq_post_likes"),
    )

    id:         Mapped[int]      = mapped_column(primary_key=True)
    post_id:    Mapped[int]      = mapped_column(
                    ForeignKey("posts.id", ondelete="CASCADE"), index=True)
    user_id:    Mapped[int]      = mapped_column(
                    ForeignKey("users.id", ondelete="CASCADE"), index=True)
    created_at: Mapped[datetime] = mapped_column(server_default=func.now())

    post: Mapped["Post"] = relationship(back_populates="likes")
    user: Mapped["User"] = relationship(back_populates="liked_posts")


# In Post model — add:
# likes: Mapped[list["PostLike"]] = relationship(back_populates="post",
#                                                cascade="all, delete-orphan")
# like_count: Mapped[int] = mapped_column(Integer, default=0)  # counter cache

Like Toggle Endpoint #

from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy import select, delete
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session

router = APIRouter(prefix="/posts", tags=["Posts"])

@router.post("/{post_id}/like", status_code=200)
def toggle_like(
    post_id:      int,
    db:           Session = Depends(get_db),
    current_user: User    = Depends(get_current_user),
):
    """Like the post if not yet liked; unlike if already liked."""
    post = db.get(Post, post_id)
    if not post or post.deleted_at:
        raise HTTPException(404, "Post not found")

    # Check if already liked
    existing_like = db.scalars(
        select(PostLike).where(
            PostLike.post_id == post_id,
            PostLike.user_id == current_user.id,
        )
    ).first()

    if existing_like:
        # Unlike — remove the like row
        db.delete(existing_like)
        post.like_count = max(0, post.like_count - 1)
        db.flush()
        return {"liked": False, "like_count": post.like_count}
    else:
        # Like — add a like row
        try:
            like = PostLike(post_id=post_id, user_id=current_user.id)
            db.add(like)
            post.like_count += 1
            db.flush()
            return {"liked": True, "like_count": post.like_count}
        except IntegrityError:
            # Race condition: two simultaneous likes
            db.rollback()
            return {"liked": True, "like_count": post.like_count}

Efficient Batch Like Status for Lists #

from fastapi import APIRouter, Depends
from sqlalchemy import select
from sqlalchemy.orm import Session
from typing import Optional

@router.get("/", response_model=list[PostWithLikeResponse])
def list_posts_with_likes(
    page:         int = 1,
    page_size:    int = 10,
    db:           Session = Depends(get_db),
    current_user: Optional[User] = Depends(get_optional_user),  # optional auth
):
    posts = db.scalars(
        select(Post)
        .where(Post.status == "published", Post.deleted_at.is_(None))
        .order_by(Post.created_at.desc())
        .offset((page - 1) * page_size)
        .limit(page_size)
    ).all()

    # Batch-load liked post IDs for current user (2 queries total, not N+1)
    liked_ids: set[int] = set()
    if current_user:
        post_ids = [p.id for p in posts]
        liked_ids = set(db.scalars(
            select(PostLike.post_id)
            .where(PostLike.user_id == current_user.id,
                   PostLike.post_id.in_(post_ids))
        ).all())

    return [
        {
            **PostResponse.model_validate(p).model_dump(),
            "liked_by_me": p.id in liked_ids,
            "like_count":  p.like_count,
        }
        for p in posts
    ]

Common Mistakes #

Mistake 1 — No UniqueConstraint on (post_id, user_id) #

❌ Wrong — two concurrent like requests can both succeed:

class PostLike(Base):
    post_id: Mapped[int] = mapped_column(ForeignKey("posts.id"))
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    # No UniqueConstraint — race condition allows duplicate likes!

✅ Correct — add UniqueConstraint as shown above.

Mistake 2 — N+1 liked_by_me check #

❌ Wrong — one DB query per post to check if liked:

for post in posts:
    like = db.scalars(select(PostLike).where(...post_id==post.id, user_id=user.id)).first()
    post.liked_by_me = like is not None   # N queries!

✅ Correct — single IN query for all post IDs, use a set for lookups.

Mistake 3 — Not decrementing like_count below zero #

❌ Wrong — like_count can go negative:

post.like_count -= 1   # what if like_count is already 0?

✅ Correct:

post.like_count = max(0, post.like_count - 1)   # ✓ cannot go negative

Quick Reference #