SQLModel

Relationship attributes in SQLModel provide an abstraction over foreign keys. While a Field (like team_id) stores the actual integer ID in the database column, a Relationship attribute (like team) allows you to access the entire related object directly.

Key Characteristics:

• Not a Column: They do not represent a column in the database table.
• Object Access: Accessing a relationship attribute returns the related instance (or a list of instances) rather than a scalar value.
• Nullability: If the underlying foreign key is nullable (e.g., team_id: int | None), the relationship attribute must also be annotated as nullable (e.g., team: Team | None) to avoid errors when no relationship exists.
• Collections: For one-to-many relationships, the attribute is typically annotated as a list (e.g., list[Hero]).

# Example of accessing a relationship attribute
if hero.team:
    print(hero.team.name)

To create relationships between models (e.g., a Team having many Heroes), use the Relationship() function.

Key rules for relationships:

1. Table Models Only: Relationship() attributes should only be defined in table models (models that inherit from SQLModel with table=True). This allows SQLModel to use SQLAlchemy to handle automatic data fetching.
2. Foreign Keys: Use the foreign_key parameter in a field definition to link a model to another table. You can define foreign_key in a base data model (where table=False), but it only takes effect when that field is inherited by a model where table=True.
3. Relationship Attributes: These allow you to access related objects directly (e.g., hero.team or team.heroes) instead of manually querying by ID.

from typing import List, Optional
from sqlmodel import Field, Relationship, SQLModel

class TeamBase(SQLModel):
    name: str
    headquarters: str

class Team(TeamBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    # Relationship to the Hero table
    heroes: List["Hero"] = Relationship(back_populates="team")

class HeroBase(SQLModel):
    name: str
    secret_name: str
    # team_id can be defined in the base model as an optional integer
    team_id: Optional[int] = Field(default=None, foreign_key="team.id")

class Hero(HeroBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    # Relationship to the Team table
    team: Optional[Team] = Relationship(back_populates="heroes")

When testing FastAPI applications with SQLModel, it is best practice to use pytest fixtures to manage the lifecycle of the database session and the TestClient.

By separating the setup into two distinct fixtures—one for the session and one for the client—you gain flexibility:

1. The session fixture: Manages the database connection and transaction lifecycle for the testing database.
2. The client fixture: Provides a TestClient instance configured to use the testing database (often by overriding dependencies).

Key Pattern: Requiring Multiple Fixtures If a test needs to both interact with the API via the client AND manipulate the database directly (e.g., to seed data before a request), you should require both the client and the session fixtures in your test function. Because fixtures can depend on other fixtures, the client fixture and your test function will share the same underlying session instance, ensuring data consistency during the test.

# Example pattern for a test requiring both client and session

def test_read_heroes(client: TestClient, session: Session):
    # 1. Seed the database using the session fixture
    hero_1 = Hero(name="Deadpool", secret_identity="Wade Wilson")
    session.add(hero_1)
    session.commit()
    session.refresh(hero_1)

    # 2. Use the client fixture to make the API request
    response = client.get("/heroes/")

    # 3. Assert the results
    assert response.status_code == 200
    assert response.json() == [{"id": hero_1.id, "name": "Deadpool", "secret_identity": "Wade Wilson"}]

To create a clear API contract for clients (frontend, mobile, etc.), use specific type annotations in your SQLModel classes:

• For Creation (HeroCreate): Omit fields that the database generates automatically (like id). This prevents clients from attempting to overwrite existing IDs.
• For Responses (HeroPublic): Declare fields that are guaranteed to exist in the database as required (e.g., id: int). This allows client-side code generators to avoid unnecessary null-checks.
• For Database (Hero): Use Optional[int] = Field(default=None, primary_key=True) for the id so the model can exist in memory before it is persisted and assigned an ID by the database.

By using response_model in FastAPI, the outgoing data is validated against your public schema, which also acts as a security filter to remove any fields not explicitly declared in the response model (like hashed passwords).

In SQLModel, select() and .join() serve distinct purposes that mirror SQL behavior:

1. select(...): Defines which columns/data you want to retrieve. If you want to access attributes of a joined model (e.g., hero.team.name), you must include that model in the select() call.
2. .join(...): Defines how tables are connected and how rows are filtered. It establishes the relationship between tables so the database knows how to match them.

Filtering without selecting data

If you call .join(Team) but only select(Hero), you can use the Team model in a .where() clause to filter the results, but the resulting objects will not contain the Team data.

Filtering and selecting data

To both filter by a related table AND retrieve its data, you must include both models in the select() call and use .join() to link them.

# Scenario 1: Filter by Team, but ONLY get Hero data
# The 'team' attribute on the Hero object will be empty/None
statement = select(Hero).join(Team).where(Team.name == 'Preventers')

# Scenario 2: Filter by Team AND get both Hero and Team data
# The 'team' attribute on the Hero object will be populated
statement = select(Hero, Team).join(Team).where(Team.name == 'Preventers')