SQLAlchemy Relationships (One-to-Many, Many-to-Many)

 

Introduction

In real-world applications, tables are connected, not isolated.

Examples:

• A user can have many orders
• A student can enroll in many courses
• A product can belong to multiple categories

These connections are called relationships in SQLAlchemy.

Types of Relationships

Type	Example
One-to-One	User ↔ Profile
One-to-Many	User → Orders
Many-to-Many	Students ↔ Courses

1. One-to-Many Relationship

Scenario :One User can have multiple Orders

Step 1: Define Models

from sqlalchemy import Column, Integer, String, ForeignKey

from sqlalchemy.orm import declarative_base, relationship

Base = declarative_base()

class User(Base):

    __tablename__ = "users"

    

    id = Column(Integer, primary_key=True)

    name = Column(String)

    

    orders = relationship("Order", back_populates="user")

class Order(Base):

    __tablename__ = "orders"

    

    id = Column(Integer, primary_key=True)

    amount = Column(Integer)

    user_id = Column(Integer, ForeignKey("users.id"))

    

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

Key Concepts

• ForeignKey → Creates link between tables
• relationship() → Enables ORM-level connection
• back_populates → Two-way relationship

Example Usage

# Create user
user = User(name="John")

# Create orders
order1 = Order(amount=500)
order2 = Order(amount=1000)

# Link orders to user
user.orders = [order1, order2]

session.add(user)
session.commit()


Access Related Data
# Get user's orders
user = session.query(User).first()
print(user.orders)

# Get order's user
order = session.query(Order).first()
print(order.user.name)

Real-World Tip
Use lazy="select" (default) or optimize with eager loading (covered below).

2. Many-to-Many Relationship
Scenario :A Student can enroll in multiple Courses .A Course can have multiple Students.

Step 1: Association Table
from sqlalchemy import 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)
)


Step 2: Define Models
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")

Example Usage
student = Student(name="Alice")
course1 = Course(title="Python")
course2 = Course(title="SQL")

student.courses = [course1, course2]

session.add(student)
session.commit()


Access Data
student = session.query(Student).first()
print(student.courses)

course = session.query(Course).first()
print(course.students)


3. One-to-One Relationship
Scenario :One User has one Profile
Implementation

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    
    profile = relationship("Profile", uselist=False, back_populates="user")


class Profile(Base):
    __tablename__ = "profiles"
    
    id = Column(Integer, primary_key=True)
    bio = Column(String)
    user_id = Column(Integer, ForeignKey("users.id"))
    
    user = relationship("User", back_populates="profile")

Key Point : uselist=False makes it one-to-one

4.Cascade Operations
What is Cascade?
Automatically apply actions (delete, update) to related records
Example
orders = relationship("Order", cascade="all, delete")
If user is deleted → orders also deleted

5. Lazy vs Eager Loading
Lazy Loading (Default)
user = session.query(User).first()
print(user.orders)  # triggers query
Eager Loading (Optimized)
from sqlalchemy.orm import joinedload

user = session.query(User).options(joinedload(User.orders)).first()
Loads data in single query

6. Backref (Shortcut)
Instead of back_populates, you can use:
from sqlalchemy.orm import backref

orders = relationship("Order", backref="user")

Real-World Example (E-Commerce)
User → Orders (One-to-Many)
Product ↔ Category (Many-to-Many)
User → Profile (One-to-One)

Common Mistakes
Forgetting ForeignKey
Missing back_populates
Not handling cascade deletes
Ignoring performance (N+1 problem)