SQLModel Related Tables and Delete Policies

SQLModel: Advanced Relationship Management

Reading Relationships

• Many-to-One Relationships (from the 'many' side to the 'one' side)

  • To select a specific Hero (e.g., "Spider-Boy"): statement = select(Hero).where(Hero.name == "Spider-Boy").

  • Execute the statement and retrieve the one result: hero_spider_boy = result.one().

  • Access the related Team object: print("Spider-Boy's team again:", hero_spider_boy.team).

  • This demonstrates accessing the 'one' side (Team) from the 'many' side (Hero).

• One-to-Many Relationships (from the 'one' side to the 'many' side)

  • To select a specific Team (e.g., "Preventers"): statement = select(Team).where(Team.name == "Preventers").

  • Execute the statement and retrieve the one result: team_preventers = result.one().

  • Access the related list of Hero objects: print("Preventers heroes:", team_preventers.heroes).

  • This demonstrates accessing the 'many' side (list of Heroes) from the 'one' side (Team).

• Lazy Execution

  • SQLModel, powered by SQLAlchemy, often employs lazy execution for relationships.

  • This means that related objects (e.g., hero_spider_boy.team or team_preventers.heroes) are not loaded from the database until they are explicitly accessed.

  • This optimizes performance by only fetching necessary data.

Removing Relationships (Via Updates)

• To remove a relationship, such as a Hero from their Team, you can update the 'many' side object.

• Steps:

  1. Fetch the Hero object: hero_spider_boy = session.exec(select(Hero).where(Hero.name == "Spider-Boy")).one().

  2. Set the team attribute of the Hero to None: hero_spider_boy.team = None.

  3. Add the updated object back to the session: session.add(hero_spider_boy).

  4. Commit the changes to the database: session.commit().

  5. Refresh the object to reflect changes from the database: session.refresh(hero_spider_boy).

• This operation sets the team_id (foreign key) in the Hero table to NULL, effectively disassociating the hero from their team.

Relationship back_populates

• The back_populates feature in SQLModel relationships synchronizes relationship data on the Python side before changes are committed to the database.

• It ensures that when an object's relationship attribute (like hero.team) is modified, the corresponding related object's collection (like team.heroes) is also immediately updated in Python memory.

• Example Scenario:

  • Retrieve hero_spider_boy and preventers_team.

  • Initially, print("Preventers Team Heroes:", preventers_team.heroes) would show Spider-Boy as a member.

  • Set hero_spider_boy.team = None.

  • With back_populates: print("Preventers Team Heroes again:", preventers_team.heroes) would immediately show Spider-Boy removed from the preventers_team.heroes list, even before session.commit().

  • Without back_populates: The preventers_team.heroes list would still contain Spider-Boy until the session is committed and possibly refreshed, leading to inconsistencies in the application's immediate view of the data.

Managing Deletion in Relationships: Delete Policies

• When a foreign key exists (e.g., team_id in the Hero table refers to Team.id), a crucial question arises: What should happen to the related 'child' rows when a 'parent' row (the referenced row) is deleted?

• Two Scenarios for Deletion:

  1. Deletion performed programmatically via Python SQLModel (using SQLAlchemy).

  2. Deletion performed directly on the database (e.g., using SqlAdminer).

• Application-Dependent Policies: There is no single