SQLAlchemy Relationship Guide for iDrv5-MyFR8

This guide provides best practices and standardized patterns for defining SQLAlchemy relationships in the iDrv5-MyFR8 codebase.

Core Principles

1. Avoid Circular Imports
2. Maintain Consistency in Relationship Names
3. Define Both Sides of Each Relationship
4. Use Explicit Foreign Keys for Clarity
5. Consider Cascading Operations and Lazy Loading

Relationship Definition Guidelines

Basic One-to-Many Relationship

# Parent Model (One side)
class Customer(db.Model):
    __tablename__ = 'customers'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))

    # Define relationship to child model
    orders = db.relationship('Order', back_populates='customer')

# Child Model (Many side)
class Order(db.Model):
    __tablename__ = 'orders'

    id = db.Column(db.Integer, primary_key=True)
    customer_id = db.Column(db.Integer, db.ForeignKey('customers.id'))
    amount = db.Column(db.Numeric(10, 2))

    # Define relationship to parent model
    customer = db.relationship('Customer', back_populates='orders')

Many-to-Many Relationship

# Association Table
user_role_association = db.Table('user_role_association',
    db.Column('user_id', db.Integer, db.ForeignKey('users.id')),
    db.Column('role_id', db.Integer, db.ForeignKey('roles.id'))
)

# First Model
class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))

    # Many-to-many relationship
    roles = db.relationship('Role', secondary=user_role_association,
                           back_populates='users')

# Second Model
class Role(db.Model):
    __tablename__ = 'roles'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))

    # Many-to-many relationship
    users = db.relationship('User', secondary=user_role_association,
                           back_populates='roles')

Multiple Relationships to Same Table

class WorkflowState(db.Model):
    __tablename__ = 'workflow_states'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))

    # Relationships to the same table but with different foreign keys
    from_transitions = db.relationship('WorkflowTransition',
                                      foreign_keys='WorkflowTransition.from_state_id',
                                      back_populates='source_state')
    to_transitions = db.relationship('WorkflowTransition',
                                    foreign_keys='WorkflowTransition.to_state_id',
                                    back_populates='target_state')

class WorkflowTransition(db.Model):
    __tablename__ = 'workflow_transitions'

    id = db.Column(db.Integer, primary_key=True)
    from_state_id = db.Column(db.Integer, db.ForeignKey('workflow_states.id'))
    to_state_id = db.Column(db.Integer, db.ForeignKey('workflow_states.id'))

    # Relationships back to the same model but different attributes
    source_state = db.relationship('WorkflowState', foreign_keys=[from_state_id],
                                  back_populates='from_transitions')
    target_state = db.relationship('WorkflowState', foreign_keys=[to_state_id],
                                  back_populates='to_transitions')

Avoiding Circular Imports

DO

• Use string references for model names: relationship('User')
• Keep related models in the same file when practical
• Import models at the function level when needed

DON’T

• Use fully-qualified paths: relationship('models.auth.User')
• Import models at the top level between modules that reference each other
• Create complex cross-module relationship chains

Lazy Loading Options

• lazy='select' (default): Load relationship data when first accessed
• lazy='joined': Load relationship using JOIN when parent is queried
• lazy='subquery': Load relationship using a separate query
• lazy='dynamic': Return a query object that can be further filtered

Example:

# For frequently accessed relationships
user = db.relationship('User', lazy='joined')

# For large collections that need filtering
posts = db.relationship('Post', lazy='dynamic')

Cascade Options

Cascade options control what happens to related objects when changes are made to the parent:

• save-update: When parent is added to session, add children too
• delete: When parent is deleted, delete children
• delete-orphan: When child is removed from parent collection, delete it
• merge: When parent is merged, merge children too
• all: All cascades except delete-orphan

Example:

# Parent model with cascade delete
comments = db.relationship('Comment', cascade='all, delete-orphan')

Backref vs. Back_populates

Two ways to establish bidirectional relationships:

backref

# Only define relationship on one side
posts = db.relationship('Post', backref=db.backref('author', lazy='joined'))

back_populates (recommended)

# Define the relationship on both sides
posts = db.relationship('Post', back_populates='author')
# In Post model
author = db.relationship('User', back_populates='posts')

We recommend back_populates for explicit clarity and better IDE support.

Common Mistakes to Avoid

1. Missing Foreign Keys: Always define the foreign key column explicitly
2. Inconsistent Naming: Use matching names in back_populates attributes
3. Import Cycles: Use string references for model names to avoid circular imports
4. Mismatched Cascade Settings: Ensure cascade behaviors match your data requirements
5. Invalid Lazy Loading: Choose appropriate loading strategies based on usage patterns

Best Practices for iDrv5-MyFR8

1. Always use string model references (‘ModelName’) not direct class references
2. Define both sides of relationships with matching back_populates attributes
3. Keep relationship naming consistent with model names (singular for many-to-one, plural for one-to-many)
4. Use explicit foreign keys when multiple relationships point to the same table
5. Consider appropriate cascade settings for your domain logic
6. Use junction models for many-to-many relationships with additional attributes
7. Document relationship cascading behavior in complex cases