Async Database Operations with SQLModel

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

1. Install an async-powered database engine (like, aiosqlite, asyncpg etc.)

    poetry add aiosqlite
   

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

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

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

5. 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 asyncpgfor Postgres and asyncmy for MySQL or MariaDB, among others.

Create Async Engine

Create a new file sqlmodel_alembic_async/databases.py. This is where we are going to store our database configurations.

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

Now, our project environment looks like this:

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

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

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