Async Database Operations with SQLModel
4 min read
So far, we've covered how to manage database migrations with SQLModel and Alembic. Next, we are going to perform database operations asynchronously.
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
>>> from sqlalchemy.ext.asyncio import create_async_engine >>> engine = create_async_engine(DATABASE_URL)
Use the engine to create a database session using
>>> 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
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,
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
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
It is recommended that you use IPython.
We use the engine created in the previous step and bind it to the
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
# ... # 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
Once committed, the objects must be refreshed with
# ... 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
Now that we've learnt how to use database operations asynchronously, we'll look at how to use SQLModel to construct relationships between models.
Did you find this article valuable?
Support Arunanshu Biswas by becoming a sponsor. Any amount is appreciated!