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}@{db_hostname}:{port}/{database_name}

Asynchronous Sqlite

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

SqlAlchemy

Install package: sqlalchemy[asyncio]

Main classes for asynchronous I/O.

• 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
            )