Async Database Operations with SQLModel
Tags: fastapi, python, sql, tutorial
Published: 2023-01-30 14:30:03

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.)
    
    ```bash
    poetry add aiosqlite
    ```
    
2. Set up a database URL that includes the name of the async database engine.
    
    ```python-repl
    >>> 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()`.
    
    ```python-repl
    >>> 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`.
    
    ```python-repl
    >>> from sqlmodel.ext.asyncio.session import AsyncSession
    >>> async with AsyncSession(engine) as session:
    ...     # perform database operations
    ```
    
5. Use the session to perform database operations.
    
    ```python-repl
    >>> 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](https://arunanshub.hashnode.dev/using-sqlmodel-with-alembic), 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:

```plaintext
sqlmodel-alembic-async/
├── sqlmodel_alembic_async/
│   ├── __init__.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"
```

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

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

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

```python-repl
>>> 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.](https://arunanshub.hashnode.dev/using-sqlmodel-with-alembic#heading-creating-models-using-sqlmodel)

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

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

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

```python-repl
>>> # ...
>>> 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.

```python-repl
>>> # ...
>>> await session.close()
INFO sqlalchemy.engine.Engine ROLLBACK
```

You can also use the `session` object as an async context manager like this:

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