So far, we've covered how to manage database migrations with SQLModel and Alembic. Next, we are going to perform database operations asynchronously.
TL;DR
-
Install an async-powered database engine (like, aiosqlite, asyncpg etc.)
poetry add aiosqlite
-
Set up a database URL that includes the name of the async database engine.
>>> DATABASE_URL = "sqlite+aiosqlite:///path/to/database.db" >>> # Note the use of aiosqlite
-
Create a database engine with
sqlalchemy.ext.asyncio.create_async_engine()
.
>>> from sqlalchemy.ext.asyncio import create_async_engine >>> engine = create_async_engine(DATABASE_URL)
-
Use the engine to create a database session using
sqlmodel.ext.asyncio.session.AsyncSession
.
>>> from sqlmodel.ext.asyncio.session import AsyncSession >>> async with AsyncSession(engine) as session: ... # perform database operations
-
Use the session to perform database operations.
>>> async with AsyncSession(engine) as session: ... db_user = models.User(name="chonk") ... session.add(db_user) ... await session.commit() ... await session.refresh(db_user) >>> print(db_user.id) 2
Goal
Our goal remains the same. We want to create a basic pet management app.
In part 1 of this blog series, we discussed how to manage database migrations with SQLModel and Alembic. We created two models, User
and Pet
.
Setting Up the Environment
We use Poetry for dependency management.
Our project environment looks like this:
sqlmodel-alembic-async/
├── sqlmodel_alembic_async/
│ ├── __init__.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"
Make sure you activate the Poetry shell using
poetry shell
.
Installing an Async DB Engine
In this tutorial, we use SQLite as the database with aiosqlite
as the database engine. Install it using:
poetry add aiosqlite
If you're using a different database, you may utilise alternative engines like asyncpg
for Postgres and asyncmy
for MySQL or MariaDB, among others.
Create Async Engine
# sqlmodel_alembic_async/databases.py
from sqlalchemy.ext.asyncio import create_async_engine
from sqlmodel.ext.asyncio.session import AsyncSession
DATABASE_URL = "sqlite+aiosqlite:///./data.db"
engine = create_async_engine(DATABASE_URL, echo=True)
A better solution would be to derive the DATABASE_URL
from the environment. However, that is out of the scope of this tutorial.
Create Async Session
NOTE: In this step, and the following steps we are going to use REPL. However, Python's default REPL does NOT have support for bare
await
statements.It is recommended that you use IPython.
We use the engine created in the previous step and bind it to the AsyncSession
.
>>> from sqlmodel.ext.asyncio.session import AsyncSession
>>> from sqlmodel_alembic_async.databases import engine
>>>
>>> session = AsyncSession(engine)
Performing Database Operations
Now, we just have to create the model objects. We created the model objects in part 1.
>>> # ...
>>> from sqlmodel_alembic_async.models import User, Pet
>>> user_chonky = User(name="chonky")
>>> pet_frog = Pet(name="phroge")
We add our object to the session and commit to the database using session.commit()
.
>>> # ...
>>> # Use the session to perform database operations
>>> session.add_all((user_chonky, pet_tiger))
>>> await session.commit()
INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine INSERT INTO user (name) VALUES (?)
INFO sqlalchemy.engine.Engine [generated in 0.00023s] ('chonky',)
INFO sqlalchemy.engine.Engine INSERT INTO pet (name) VALUES (?)
INFO sqlalchemy.engine.Engine [generated in 0.00018s] ('phroge',)
INFO sqlalchemy.engine.Engine COMMIT
Where is the
INFO sqlalchemy...
log coming from?SQLAlchemy is logging its operations since we passed
echo=True
tocreate_async_engine
.
Once committed, the objects must be refreshed with session.refresh()
.
>>> # ...
>>> 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.00016s] (3,)
>>>
>>> await session.refresh(pet_tiger)
INFO sqlalchemy.engine.Engine SELECT pet.id, pet.name
FROM pet
WHERE pet.id = ?
INFO sqlalchemy.engine.Engine [generated in 0.00018s] (2,)
Let's inspect our model objects:
>>> # ...
>>> print(user_chonky)
User(id=3, name="chonky")
>>> print(pet_frog)
Pet(id=2, name="phroge")
And once you're done with it, you can close
the session.
>>> # ...
>>> await session.close()
INFO sqlalchemy.engine.Engine ROLLBACK
You can also use the session
object as an async context manager like this:
>>> async with AsyncSession(engine) as session:
... db_user = models.User(name="chonk")
... session.add(db_user)
... await session.commit()
... await session.refresh(db_user)
... # perform some other operation
What's next?
Now that we've learnt how to use database operations asynchronously, we'll look at how to use SQLModel to construct relationships between models.
Top comments (0)