The Async Side of SQLModel Relationships — Part 1

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, we discussed how to perform database operations asynchronously.

Setting Up the Environment

We use Poetry for dependency management.

Our project environment looks like this:

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

At the time of writing, the dependencies are:

# 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:

              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.

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

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

# 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:

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

Migrate the Database

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, we saw how to create an async session.

>>> 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.

>>> # ...
>>> from sqlmodel_alembic_async.models import User, Pet

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

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

>>> 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.

>>> 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.

>>> 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.

>>> 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 with the related models as Pythonic attributes.

Relationship Attributes

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

todo

# 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 go into detail on using type hints and relationship attributes.

Using Models with Relationship Attributes

Start by importing the required classes and instances.

>>> 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.

>>> # ...
>>> user_someone = User(name="someone")
>>> pet_something = Pet(name="something")

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

>>> # ...
>>> pet_something.user = user_someone

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

>>> # ...
>>> 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:

              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.

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

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

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

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 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. 😏