Database Asynchronous Access

To improve throughput of a database-backed web service, we can use asynchronous database operations. PostgreSQL and Sqlite support asynchronous operations, but the support is less mature than the default synchronous operation.

Asynchronous PostreSQL

• Install package: asyncpg
• Database URL: postgresql+asyncpg://{db_username}:{db_secret}@{hostname}:{port}/{database_name}

Asynchronous Sqlite

• Install package: aiosqlite
• Database URL: sqlite+aiosqlite:///{database_name} May also include a host and path.

Async SqlAlchemy

Install package: sqlalchemy[asyncio]

Main classes for asynchronous I/O are

• AsyncEngine an async version of the standard Engine API.
• AsyncConnection an async Connection manager. Create using asyncEngine.connect() or asyncEngine.begin().
• AsyncSession represents a single, stateful database transaction. Concurrent tasks should each use a separate AsyncSession object.

Example from SqlAlchemy documentation:

Create database engine and async connection, using canonical SqlAlchemy 2.0 package names:

from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine, async_sessionmaker
from sqlalchemy.orm import declarative_base

engine - create_async_engine(
            database_url,
            future=True,   # Use SqlAlchemy 2.0 style API
            echo=True      # Optional, display SQL on console or log for testing
            )

See Also

Eager Loading of Relationships in Async Database Access for how to load related objects. Async operations do not support lazy loading of related objects outside a narrow context.