Examples of one-to-many and many-to-many relationships with SQLAlchemy ORM. This pattern demonstrates how to properly set up bidirectional relationships with cascade delete and lazy loading options.

SQLAlchemy Relationship Patterns.py

from sqlalchemy import Column, Integer, String, ForeignKey, Table
from sqlalchemy.orm import relationship, declarative_base

Base = declarative_base()

# Many-to-many association table
user_group_association = Table(
    'user_group_association',
    Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id')),
    Column('group_id', Integer, ForeignKey('groups.id'))
)

# User model with relationships
class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    username = Column(String, unique=True)
    email = Column(String, unique=True)
    
    # One-to-many: User has many posts
    posts = relationship(
        "Post",
        back_populates="author",
        cascade="all, delete-orphan",  # Delete posts when user is deleted
        uselist=True,  # This is a collection (list)
        lazy="select"  # Default loading strategy
    )
    
    # One-to-one: User has one profile
    profile = relationship(
        "Profile",
        back_populates="user",
        cascade="all, delete-orphan",
        uselist=False,  # This is a single object, not a collection
        lazy="joined"  # Always load together with user
    )
    
    # Many-to-many: Users belong to many groups
    groups = relationship(
        "Group",
        secondary=user_group_association,
        back_populates="members",
        lazy="selectin"  # Automatically load in a separate query
    )

# Post model (child in one-to-many)
class Post(Base):
    __tablename__ = 'posts'
    
    id = Column(Integer, primary_key=True)
    title = Column(String)
    content = Column(String)
    user_id = Column(Integer, ForeignKey('users.id'))
    
    # Many-to-one: Many posts belong to one user
    author = relationship("User", back_populates="posts")

# Profile model (one-to-one with User)
class Profile(Base):
    __tablename__ = 'profiles'
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'), unique=True)  # Unique constraint for one-to-one
    bio = Column(String)
    avatar_url = Column(String)
    
    # One-to-one: Profile belongs to one user
    user = relationship("User", back_populates="profile")

# Group model (many-to-many with User)
class Group(Base):
    __tablename__ = 'groups'
    
    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)
    description = Column(String)
    
    # Many-to-many: Groups have many members (users)
    members = relationship(
        "User",
        secondary=user_group_association,
        back_populates="groups"
    )