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.