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
Create the foreign key in a model using
sqlmodel.Field
.Migrate the database using
alembic revision --autogenerate -m "migration message"
andalembic upgrade head
.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
tocreate_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 todo
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. 😏
Top comments (0)