The Async Side of SQLModel Relationships — Part 2
Tags: python, tutorial, webdev, database
Published: 2023-02-01 13:00:55

So far, we've looked at how to use foreign keys and relationship attributes to create database relationships. We've also seen how doing an implicit SQL I/O throws errors. Next, we are going to cover the various ways in which we can fix the errors.

## TL;DR

### Eager Loading Approach

1. Add `sa_relationship_kwargs={"lazy": LAZY_STRATEGY}` to `Relationship(...)`.
    
    Values for `LAZY_STRATEGY` have been described in [SQLAlchemy's docs.](https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html#summary-of-relationship-loading-styles)
    

### Lazy Loading Approach

1. Generate queries using `select(User).where(User.id == 1).options(LOADER_API(User.pets))`.
    
    Values for `LOADER_API` have been described in [SQLAlchemy's docs on Relationship Loader API.](https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html#relationship-loader-api)
    

## Goal

Our goal remains the same. We want to create a pet management app.

In [**part 3 of this blog series**](https://arunanshub.hashnode.dev/the-async-side-of-sqlmodel-relationships), we discussed how to create relationships between SQLModel models.

## Setting Up the Environment

We use [Poetry](https://python-poetry.org/) for dependency management.

Our project environment looks like this:

```plaintext
sqlmodel-alembic-async/
├── sqlmodel_alembic_async/
│   ├── __init__.py
│   ├── databases.py
│   └── models.py
├── poetry.lock
└── pyproject.toml
```

At the time of writing, the dependencies are:

```ini
# pyproject.toml
[tool.poetry.dependencies]
python = "^3.8"
sqlmodel = "^0.0.8"
alembic = "^1.9.2"
aiosqlite = "^0.18.0" # you can use some other async database engine
```

> *Make sure you activate the Poetry shell using* `poetry shell`*.*

## Recap

```python-repl
>>> # ...
>>> user_someone.pets
INFO sqlalchemy.engine.Engine SELECT pet.id AS pet_id, pet.name AS pet_name, pet.user_id AS pet_user_id 
FROM pet 
WHERE ? = pet.user_id
INFO sqlalchemy.engine.Engine [generated in 0.00017s] (3,)

MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/14/xd2s)
```

According to the log message, SQLAlchemy is successfully constructing a query to retrieve pets with a foreign key called `user_id` equal to 3. However, on attempting to execute the query, an error occurs, indicating a failure in initiating a Greenlet coroutine, which is used for concurrent programming, most likely due to an implicit I/O operation not being performed in an async context.

## Lazy or Not

To solve the problem, we can take two approaches:

1. **Load everything in one go.** Also called eager loading.
    
2. **Load as required.** Lazy loading, but with async support.
    

### Load Everything in One Go

This is how the strategy works:

1. You query the database for an instance of a User using the ORM.
    
    ```python-repl
    >>> db_user = await db.get(User, 1) # this means: select user with id = 1
    INFO sqlalchemy.engine.Engine BEGIN (implicit)
    INFO sqlalchemy.engine.Engine SELECT user.name AS user_name, user.id AS user_id 
    FROM user 
    WHERE user.id = ?
    INFO sqlalchemy.engine.Engine [generated in 0.00021s] (1,)
    ```
    
    *(the logs continue below)*
    
2. The ORM issues an additional query to fetch the pets owned by the User.
    
    ```python-repl
    INFO sqlalchemy.engine.Engine SELECT pet.user_id AS pet_user_id, pet.id AS pet_id, pet.name AS pet_name 
    FROM pet 
    WHERE pet.user_id IN (?)
    INFO sqlalchemy.engine.Engine [generated in 0.00042s] (1,)
    >>> db_user.pets
    [...]
    ```
    

Different types of loading-related strategies in SQLAlchemy's ORM include **lazy loading**, **select-in loading**, **joined loading**, **raise loading**, **subquery loading**, **write-only loading**, and **dynamic loading**.

* The default style is **lazy loading**, which loads a related reference on a single object at a time.
    
* The **select-in loading** method loads all members of a linked collection/scalar references at the same time.
    
* **Joined loading** loads rows that are related in the same result set.
    
* **Raise loading** raises an exception if an unwanted lazy load occurs.
    
* **Subquery loading** uses a subquery to load all members of a related collection/scalar reference.
    
* **Write-only loading** only allows modification to a collection and requires a SELECT statement to query the collection.
    
* **Dynamic loading** generates a Query object for the collection, however, it is less effective for managing big collections and is being phased out in favour of write-only loading.
    

The settings are fully explained in [SQLAlchemy's documentation](https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html).

Also, the loading strategy you decide to use depends heavily on the use case. Each of them has its benefits and tradeoff. Start by looking into [N+1 selects problem faced by an ORM](https://stackoverflow.com/q/97197).

We will stick with `selectin` loading strategy for our application.

```diff
# sqlmodel_alembic_async/models.py
# ...

class User(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str

    pets: List["Pet"] = Relationship(
        back_populates="user",
+       sa_relationship_kwargs={"lazy": "selectin"},
    )

class Pet(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str

    user_id: int | None = Field(default=None, foreign_key="user.id")
    user: Optional[User] = Relationship(
        back_populates="pets",
+       sa_relationship_kwargs={"lazy": "selectin"},
    )
```

And now you can query the database as usual.

```python-repl
>>> from sqlmodel import select
>>> stmt = select(Pet).where(Pet.id == 1)
>>> pet = (await db.exec(stmt)).one()
>>> pet
Pet(user_id=1, name='phroge', id=1, user=User(id=1, name='chonky', pets=[Pet(user_id=1, name='phroge', id=1, user=User(id=1, name='chonky', pets=[...]))]))
>>> pet.user
User(id=1, name='chonky', pets=[Pet(user_id=1, name='phroge', id=1, user=User(id=1, name='chonky', pets=[...]))])
```

#### Benefits

* Very simple to implement.
    

#### Drawbacks

* If the related objects are big, then loading them unnecessarily would waste CPU cycles.
    
* It has been [described as a code smell](https://vladmihalcea.com/eager-fetching-is-a-code-smell/).
    

### Load as Required

In this strategy, we will use the loader strategies described above **within the query itself.**

```python-repl
>>> from sqlmodel import select
>>> from sqlalchemy.orm import selectinload
>>>
>>> stmt = select(User).where(User.id == 5).options(selectinload(User.pets))
>>>
>>> db_user = (await db.exec(stmt)).one()
>>>
>>> db_user
User(id=5, name='waby', pets=[Pet(user_id=5, name='bb', id=4, user=User(id=5, name='waby', pets=[...]))])
```

Let's break down the above code.

```python-repl
>>> stmt = select(User).where(User.id == 5).options(selectinload(User.pets))
```

Here, we ask the database to **select a User whose ID is equal to 1.** Additionally, **issue an additional query using** `selectinload` **strategy** that **loads the related objects** called `pets` that is accessed via `User.pets` attribute.

**Where did the** `selectinload()` **come from?** It is a part of [SQLAlchemy's Relationship Loader API](https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html#relationship-loader-api).

```python-repl
>>> db_user = (await db.exec(stmt)).one()
```

Then we execute the statement and pick one item from the result.

And that's the "Load as Required" strategy.

#### Benefits

* Truly lazy strategy: You load what you need.
    

#### Drawbacks

* Complex to work with
    

### A "Lazy" Tip

Should you choose to implement the "Load as Required" approach, it is possible to configure SQLAlchemy to raise an exception in the event of an unwanted implicit I/O operation being triggered by attribute access.

In the code below, we use the `lazy="raise"` strategy. Raise loading raises an exception if an **unwanted lazy load** occurs.

```diff
# sqlmodel_alembic_async/models.py
# ...

class User(UserBase, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str

    pets: List["Pet"] = Relationship(
        back_populates="user",
+       sa_relationship_kwargs={"lazy": "raise"},
    )

class Pet(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str

    user_id: int | None = Field(default=None, foreign_key="user.id")
    user: Optional[User] = Relationship(
        back_populates="pets",
+       sa_relationship_kwargs={"lazy": "raise"},
    )
```

## **What's next?**

Now that we've learnt how to take responsibility for our relationships 😌, we will learn how to integrate it into a working FastAPI application.

## Image Source

Photo by <a href="https://unsplash.com/es/@idahosam?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText">Stephen Meyers</a> on <a href="https://unsplash.com/photos/U43H95kjhog?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText">Unsplash</a>