Starlette Integration#

AdvancedAlchemy manages engine startup and shutdown for Starlette ASGI applications and provides lazy per-request SQLAlchemy sessions on request.state.

What’s Included for Starlette#

Feature

Capabilities

Reference

Session Middleware

Lazily creates a SQLAlchemy session on request.state on first access and closes it when the request finishes.

API

Commit Modes

Supports manual commits, "autocommit" on 2xx, and "autocommit_include_redirect".

API

Multi-Database Binds

Connects multiple sync or async databases in a single Starlette app via bind_key.

API

Installation#

uv add "advanced-alchemy[starlette,aiosqlite]"
pip install "advanced-alchemy[starlette,aiosqlite]"
pdm add "advanced-alchemy[starlette,aiosqlite]"
poetry add "advanced-alchemy[starlette,aiosqlite]"

Setup and Route Usage#

Attach AdvancedAlchemy to your Starlette app and retrieve the current request’s session with alchemy.get_async_session(request):

from starlette.applications import Starlette
from advanced_alchemy.extensions.starlette import (
    AdvancedAlchemy,
    AsyncSessionConfig,
    SQLAlchemyAsyncConfig,
)

alchemy_config = SQLAlchemyAsyncConfig(
    connection_string="sqlite+aiosqlite:///:memory:",
    session_config=AsyncSessionConfig(expire_on_commit=False),
    commit_mode="autocommit",
    create_all=True,
)

app = Starlette()
alchemy = AdvancedAlchemy(config=alchemy_config, app=app)
from uuid import UUID
import msgspec
from sqlalchemy.orm import Mapped
from starlette.requests import Request
from starlette.responses import JSONResponse, Response
from starlette.routing import Route
from advanced_alchemy.extensions.starlette import base, filters, repository, service


class AuthorModel(base.UUIDBase):
    __tablename__ = "starlette_author"
    name: Mapped[str]


class AuthorSchema(msgspec.Struct):
    id: UUID
    name: str


class AuthorService(service.SQLAlchemyAsyncRepositoryService[AuthorModel]):
    class Repo(repository.SQLAlchemyAsyncRepository[AuthorModel]):
        model_type = AuthorModel

    repository_type = Repo


async def list_authors(request: Request) -> Response:
    page = int(request.query_params.get("currentPage", 1))
    size = int(request.query_params.get("pageSize", 10))
    limit_offset = filters.LimitOffsetFilter(limit=size, offset=size * (page - 1))

    authors_service = AuthorService(session=alchemy.get_async_session(request))
    results, total = await authors_service.get_many_and_count(limit_offset)
    payload = authors_service.to_schema(
        results,
        total,
        filters=[limit_offset],
        schema_type=AuthorSchema,
    )
    return Response(content=msgspec.json.encode(payload), media_type="application/json")


async def healthcheck(request: Request) -> JSONResponse:
    db_session = alchemy.get_async_session(request)
    assert db_session is not None
    return JSONResponse({"dialect": alchemy.get_async_engine().dialect.name, "status": "ok"})


app.router.routes.extend(
    [
        Route("/authors", endpoint=list_authors, methods=["GET"]),
        Route("/health", endpoint=healthcheck, methods=["GET"]),
    ],
)

Commit Modes and Multiple Databases#

redirect_config = SQLAlchemyAsyncConfig(
    connection_string="sqlite+aiosqlite:///:memory:",
    commit_mode="autocommit_include_redirect",
)
redirect_app = Starlette()
redirect_alchemy = AdvancedAlchemy(config=redirect_config, app=redirect_app)
from advanced_alchemy.extensions.starlette import SQLAlchemySyncConfig

multi_db_app = Starlette()
multi_alchemy = AdvancedAlchemy(
    config=[
        SQLAlchemyAsyncConfig(connection_string="sqlite+aiosqlite:///:memory:"),
        SQLAlchemySyncConfig(connection_string="sqlite+pysqlite:///:memory:", bind_key="reporting"),
    ],
    app=multi_db_app,
)
assert multi_alchemy.get_async_config().bind_key == "default"
assert multi_alchemy.get_sync_config("reporting").bind_key == "reporting"

See Starlette for full API signatures.