Async Database¶

An asyncio-compatible database interface is the sqlservice.async_database.AsyncDatabase class which has the same interface as the sqlservice.database.Database class.

Before we get to the good stuff, let’s first start by creating our asynchronous database object:

from sqlservice import AsyncDatabase

# Let's assume you've define the User, UserAbout, UserDevice, and UserDeviceKey
# as illustrated in the "Model" section of the docs in a module called models.py
# with a declarative base named Model.
from models import Model

db = Database("sqlite+aiosqlite://", model_class=Model)

Note

For details on the available configuration values, see sqlservice.async_database.AsyncDatabase.

The AsyncDatabase class can be used as a provider of database connections and sessions using the 2.0 style of SQLAlchemy:

conn = await db.connect()
session = await db.session()

# Or as context-managers...
async with db.connect() as conn:
    # high-level DB-API connection

async with db.session() as session:
    # ORM session, commit as you go

async with db.begin() as session:
    # ORM session, automatic commit when context-manager exits

Engine¶

Nothing fancy here. The sqlservice.async_database.AsyncDatabase.engine` is created using sqlalchemy.create_async_engine under the hood using the database settings provided. Typically, the engine won’t be used directly. Instead, database connections and sessions can be created using these methods:

sqlservice.async_database.AsyncDatabase.connect() - Returns an instance of sqlalchemy.ext.asyncio.AsyncConnection
sqlservice.async_database.AsyncDatabase.session() - Returns an instance of sqlservice.async_session.AsyncSession` which is a subclass of sqlalchemy.ext.asyncio.AsyncSession
sqlservice.async_database.AsyncDatabase.begin() - Begins a transaction and returns an instance of sqlservice.session.Session`

Session¶

The sqlservice.async_database.AsyncDatabase.session() and sqlservice.async_database.AsyncDatabase.begin() methods are factories that provide ORM sessions. The main difference is that begin() is a context-manager that will start a transaction when entering the context-manager, commit the transaction on exit, and automatically rollback if an unhandled exception occurs.

The default class used to create sessions is sqlservice.async_session.AsyncSession which extends sqlalchemy.ext.asyncio.AsyncSession. You can override the session class using AsyncDatabase(session_class=MySession).

For more details, see the sqlservice.async_session module.

ORM Models¶

The declarative base model passed into AsyncDatabase (or autogenerated when excluded) has its metadata available at sqlservice.async_database.AsyncDatabase.metadata. There are several metadata based methods available.

Create Model Tables¶

Create all ORM model tables with:

await db.create_all()

This will issue the appropriate SQL DDL statements that can get your database up and running quickly. For full migration integration, see alembic.

Drop Model Tables¶

Drop all ORM model tables with:

await db.drop_all()

Reflect Models¶

Reflect existing database schema without predefining ORM models or Table objects:

await db.reflect()
print(db.tables)

For more details, see the sqlservice.async_database module.