The Async Side of SQLModel Relationships — Part 1
Tags: python, database, async, tutorial
Published: 2023-01-31 17:51:06

So far, we've covered how to perform database operations asynchronously. Next, we are going to create relationships between models and discover a related pitfall.

## TL;DR

1. Create the foreign key in a model using `sqlmodel.Field`.
    
2. Migrate the database using `alembic revision --autogenerate -m "migration message"` and `alembic upgrade head`.
    
3. Add relationship attributes using `sqlmodel.Relationship(back_populates="column")`.
    

Since step 3 has a trap that needs careful attention, you might wish to read the article.

## Goal

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

In [part 2 of this blog series](https://arunanshub.hashnode.dev/async-database-operations-with-sqlmodel), we discussed how to perform database operations asynchronously.

## 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`*.*

## Creating Relationships with SQLModel

## Add a Foreign Key

So, the problem is this: **one user can own multiple pets.** Or in other words: **one pet is owned by one user.**

We can see that it's a one-to-many relationship. Understand this with a diagram:

```plaintext
              is owned by
1 Pet ────────────────────────────► 1 User


                     ┌───────► Pet
                     │
           owns      │
1 User ──────────────┼───────► Pet  (multiple Pets)
                     │
                     │
                     └───────► Pet
```

In the database, we use a foreign key to represent our one-to-many relationship.

```diff
# sqlmodel_alembic_async/models.py
from typing import Optional
from sqlmodel import Field, SQLModel

metadata = SQLModel.metadata


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


class Pet(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
+   user_id: Optional[int] = Field(default=None, foreign_key="user.id")
```

## Create Revision and Migrate

### Create a Revision

This step is pretty easy.

```bash
alembic revision --autogenerate -m "add Pet.user_id foreign key"
# INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
# INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
# INFO  [alembic.autogenerate.compare] Detected added column 'pet.user_id'
# INFO  [alembic.autogenerate.compare] Detected added foreign key (user_id)(id) on table pet
#  Generating ./sqlmodel-alembic-async/migrations/versions/<somehash>_add_pet_user_id_foreign_key.py ...  done
```

### Inspect the Revision

```python
# migrations/versions/<somehash>_add_pet_user_id_foreign_key.py
"""add Pet.user_id foreign key

Revision ID: <some hash>
Revises: <some hash>
Create Date: <some date>
"""
from alembic import op
import sqlalchemy as sa
import sqlmodel.sql.sqltypes


# revision identifiers, used by Alembic.
revision = '<some hash>'
down_revision = '<some hash>'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('pet', schema=None) as batch_op:
        batch_op.add_column(sa.Column('user_id', sa.Integer(), nullable=True))
        batch_op.create_foreign_key(batch_op.f('fk_pet_user_id_user'), 'user', ['user_id'], ['id'])

    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('pet', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_pet_user_id_user'), type_='foreignkey')
        batch_op.drop_column('user_id')

    # ### end Alembic commands ###
```

Examine the following lines:

```python
        # ...
        batch_op.create_foreign_key(batch_op.f('fk_pet_user_id_user'), 'user', ['user_id'], ['id'])
        # ...
```

This name `fk_pet_user_id_user` is generated using the naming convention we created in [part 1 of this blog series.](https://arunanshub.hashnode.dev/using-sqlmodel-with-alembic#heading-sql-constraint-naming-convention)

### Migrate the Database

```bash
alembic upgrade head
# INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
# INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
# INFO  [alembic.runtime.migration] Running upgrade <some hash> -> <some hash>, add Pet.user_id foreign key
```

## Playing with Database Models

### Create a Session

Before we begin, we must create a session and bind it with an engine. In [part 2 of this blog series](https://arunanshub.hashnode.dev/async-database-operations-with-sqlmodel?t=1675144488785#heading-create-async-session), we saw how to create an async session.

```python-repl
>>> from sqlmodel.ext.asyncio.session import AsyncSession
>>> from sqlmodel_alembic_async.databases import engine
>>>
>>> session = AsyncSession(engine)
```

We are now ready to experiment with our database models.

### The Experiment

First, we import the database models.

```python-repl
>>> # ...
>>> from sqlmodel_alembic_async.models import User, Pet
```

Let's create a User called "chonky". She happens to own a pet called "phroge".

```python-repl
>>> # ...
>>> user_chonky = User(name="chonky")
>>> pet_frog = Pet(name="phroge")
```

To link the user to the pet, we would need the value of the `id` of `user_chonky`. But our user does not have an `id` yet. So, we need to commit it to the database.

```python-repl
>>> session.add(user_chonky)
>>> await session.commit(user_chonky)
INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine INSERT INTO user (name) VALUES (?)
INFO sqlalchemy.engine.Engine [generated in 0.00016s] ('chonky',)
INFO sqlalchemy.engine.Engine COMMIT
```

And then we refresh the `user_chonky` object to update its attributes.

```python-repl
>>> await session.refresh(user_chonky)
INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine SELECT user.name, user.id 
FROM user 
WHERE user.id = ?
INFO sqlalchemy.engine.Engine [generated in 0.00018s] (1,)
>>>
>>> print(user_chonky)
User(id=2, name='chonky')
```

And boom! We now have a value of the `id`!

> **Where is the** `INFO sqlalchemy...` **log coming from?**
> 
> SQLAlchemy is logging its operations since we passed `echo=True` to `create_async_engine`. This has been described in [part 2 of the blog series.](https://arunanshub.hashnode.dev/async-database-operations-with-sqlmodel?t=1675144488785#heading-create-async-engine)

```python-repl
>>> pet_frog = user_chonky.id
>>> await session.commit()
>>> # ... log messages skipped ...
>>> await session.refresh(pet_frog)
>>> # ... log messages skipped ...
>>> print(pet_frog)
Pet(id=1, name='phroge', user_id=2)
```

And remember to close the database session once you're done.

```python-repl
>>> await session.close()
```

And tada! We successfully created a relationship between two objects using SQLModel.

**However, using the** `id` **to create relationships seems cumbersome. It would be nice if we could somehow interact todo**

## Relationship Attributes

Using relationship attributes, we can directly interact with the users and their pets using familiar python attributes.

todo

```diff
# sqlmodel_alembic_async/models.py
# ...
+from typing import List, Optional

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

+   pets: List["Pet"] = Relationship(back_populates="user")


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")
```

The `List["Pet"]` and `Optional[User]` are type hints. It is assumed that you know what type hints are. [SQLModel's docs](https://sqlmodel.tiangolo.com/tutorial/relationship-attributes/define-relationships-attributes/#declare-relationship-attributes) go into detail on using type hints and relationship attributes.

## Using Models with Relationship Attributes

Start by importing the required classes and instances.

```python-repl
>>> from sqlmodel_alembic_async.models import User, Pet
>>> from sqlmodel_alembic_async.databases import engine
>>> from sqlmodel.ext.asyncio.session import AsyncSession
>>>
>>> session = AsyncSession(engine)
```

We create a user and its pet.

```python-repl
>>> # ...
>>> user_someone = User(name="someone")
>>> pet_something = Pet(name="something")
```

Now we tell `pet_something` that its "owner" is `user_someone`.

```python-repl
>>> # ...
>>> pet_something.user = user_someone
```

Let's see what the `pet_something` and `user_someone` look like.

```python-repl
>>> # ...
>>> pet_something
Pet(id=None, name='something', user_id=None, user=User(name='someone', id=None, pets=[Pet(id=None, name='something', user_id=None, user=User(name='someone', id=None, pets=[...]))]))
>>>
>>> user_someone
User(name='someone', id=None, pets=[Pet(id=None, name='something', user_id=None, user=User(name='someone', id=None, pets=[...]))])
```

**What's going on? Why the** `[...]`**? And is that a recursive object?**

Yes! It is indeed a recursive object. It shows that SQLModel understands that there is a relationship (one-to-many, to be precise) between the objects.

Understand it like this:

```plaintext
              is owned by
1 Pet ────────────────────────────► 1 User


                     ┌───────► Pet
                     │
           owns      │
1 User ──────────────┼───────► Pet  (multiple Pets)
                     │
                     │
                     └───────► Pet
```

Now let's add it to the database, commit it and then refresh the objects.

```python-repl
>>> # ...
>>> session.add_all((user_someone, pet_something))
>>> await session.commit()
... # log message skipped
>>> await session.refresh(user_someone)
... # log message skipped
>>> await session.refresh(pet_something)
... # log message skipped
```

Let's try to inspect `user_someone`.

```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)
```

Oh no! It fails with some obscure `MissingGreenlet` error. What is going on!?

## Analysing the Attribute Access Error

Let's inspect the log message and the error message.

```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,)
```

In the log message, it is evident that SQLAlchemy makes a query to the database. It is trying to fetch the pets that have the foreign key `user_id` equal to 3. **That means queries are being built without any failure.** So far, so good.

```python-repl
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)
```

But this error says that some "IO operation was carried out when it was not expected." Also, it seems to use **Greenlet**. Looks like the error happens when SQLAlchemy tries to execute the query.

[The documentation for Greenlet](https://greenlet.readthedocs.io/en/latest/) describes itself as a library for concurrent programming. Taking our error into account, we may conclude that SQLAlchemy attempted but failed to start a Greenlet coroutine. **This shows that the error occurs when SQLAlchemy attempts to do an implicit I/O operation while not in an "async context."**

## What's next?

Now that we've learnt how to create relationships between models and how performing an SQL query outside an "async context" leads to errors, we will look at some ways to fix the errors.

Or you can say, *we will improve our relationship with Async SQL operations.* 😏

## Image Source

Photo by <a href="https://unsplash.com/@fabioha?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText">fabio</a> on <a href="https://unsplash.com/photos/oyXis2kALVg?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText">Unsplash</a>