relationships_in_sqlalchemy.md

📘 SQLAlchemy Relationship Types

This guide demonstrates the four common types of relationships in SQLAlchemy using realistic examples:

• ✅ One-to-One: User → UserProfile

• ✅ One-to-Many: Blog → BlogPost

• ✅ Many-to-One: Reverse of one-to-many

• ✅ Many-to-Many: Student ↔ Course

---

🔗 1. One-to-One — User → UserProfile

Each user has one profile, and each profile belongs to one user.

# Association via ForeignKey and uselist=False
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True, nullable=False)
    profile = relationship("UserProfile", back_populates="user", uselist=False)
class UserProfile(Base):
tablename = "user_profiles"
id = Column(Integer, primary_key=True)
bio = Column(Text)
user_id = Column(Integer, ForeignKey("users.id"), unique=True)
user = relationship("User", back_populates="profile")

uselist=False ensures the relationship is one-to-one, not one-to-many.

---

📚 2. One-to-Many — Blog → BlogPost

A blog can contain many blog posts, but each post belongs to one blog.

class Blog(Base):
    __tablename__ = "blogs"
    id = Column(Integer, primary_key=True)
    title = Column(String)
    posts = relationship("BlogPost", back_populates="blog")
class BlogPost(Base):
tablename = "blog_posts"
id = Column(Integer, primary_key=True)
title = Column(String)
content = Column(Text)
blog_id = Column(Integer, ForeignKey("blogs.id"))
blog = relationship("Blog", back_populates="posts")

Blog.posts is a list; BlogPost.blog is a single object.

---

🔁 3. Many-to-One — BlogPost → Blog

This is the inverse of one-to-many. Already covered above:

# BlogPost.blog is a many-to-one reference
blog_id = Column(Integer, ForeignKey("blogs.id"))
blog = relationship("Blog", back_populates="posts")

---

🎓 4. Many-to-Many — Student ↔ Course

Each student can enroll in many courses, and each course can have many students.

# Association table
student_course = Table(
    "student_course",
    Base.metadata,
    Column("student_id", ForeignKey("students.id"), primary_key=True),
    Column("course_id", ForeignKey("courses.id"), primary_key=True),
)
class Student(Base):
tablename = "students"
id = Column(Integer, primary_key=True)
name = Column(String)
courses = relationship("Course", secondary=student_course, back_populates="students")
class Course(Base):
tablename = "courses"
id = Column(Integer, primary_key=True)
title = Column(String)
students = relationship("Student", secondary=student_course, back_populates="courses")

secondary=student_course connects them via the join table.

---

🧪 Bonus: CRUD Example (One-to-Many)

# Create a blog and posts
blog = Blog(title="My Tech Blog")
post1 = BlogPost(title="Intro to SQLAlchemy", content="...", blog=blog)
post2 = BlogPost(title="FastAPI + SQLModel", content="...", blog=blog)
session.add(blog)
session.commit()
# Query posts for a blog
blog = session.query(Blog).first()
for post in blog.posts:
print(post.title)
# Query blog for a post
post = session.query(BlogPost).first()
print(post.blog.title)

---

✅ Summary Table

Relationship	Direction	Example	Technique
One-to-One	1 ⟷ 1	User → UserProfile	uselist=False, unique=True FK
One-to-Many	1 ⟶ *	Blog → BlogPost	relationship(back_populates)
Many-to-One	* ⟶ 1	BlogPost → Blog	ForeignKey in child model
Many-to-Many	* ⟷ *	Student ↔ Course	secondary=Table, relationship()

---