Skip to content

Starlette Integration

sqlspec.extensions.starlette provides SQLSpecPlugin for Starlette (>= 1.10.0), managing database connection pools in Starlette's lifespan and attaching request-scoped driver sessions to request.state.

Installation

uv add "sqlspec[starlette]"

Basic Setup

Register your database config on SQLSpec, create db_plugin = SQLSpecPlugin(spec), call db_plugin.init_app(app), and retrieve the session in route handlers via db_plugin.get_session(request):

docs/examples/frameworks/starlette/basic_setup.py
"""Attach SQLSpecPlugin to a Starlette application and query an async SQLite session."""

from starlette.applications import Starlette
from starlette.requests import Request
from starlette.responses import JSONResponse
from starlette.routing import Route

from sqlspec import SQLSpec
from sqlspec.adapters.aiosqlite import AiosqliteConfig, AiosqliteDriver
from sqlspec.extensions.starlette import SQLSpecPlugin

__all__ = ("main",)


def main() -> Starlette:
    """Create a Starlette application initialized with SQLSpecPlugin."""
    spec = SQLSpec()
    spec.add_config(AiosqliteConfig(connection_config={"database": ":memory:"}))
    db_plugin = SQLSpecPlugin(spec)

    async def health(request: Request) -> JSONResponse:
        db: AiosqliteDriver = db_plugin.get_session(request)
        return JSONResponse(await db.select_one("SELECT 1 AS ok"))

    app = Starlette(routes=[Route("/health", health)])
    db_plugin.init_app(app)
    return app


if __name__ == "__main__":
    print(main())

Multi-Database Setup

Bind multiple async or sync configurations by specifying unique session_key, connection_key, and pool_key values inside extension_config["starlette"] (StarletteConfig):

docs/examples/frameworks/starlette/multi_database.py
"""Query both async primary and sync ETL SQLSpec sessions inside a Starlette endpoint."""

from starlette.applications import Starlette
from starlette.requests import Request
from starlette.responses import JSONResponse
from starlette.routing import Route

from sqlspec import SQLSpec
from sqlspec.adapters.aiosqlite import AiosqliteConfig, AiosqliteDriver
from sqlspec.adapters.sqlite import SqliteConfig, SqliteDriver
from sqlspec.extensions.starlette import SQLSpecPlugin

__all__ = ("main",)


def main() -> Starlette:
    """Create a Starlette application wired to async and sync SQLite databases."""
    spec = SQLSpec()
    spec.add_config(
        AiosqliteConfig(
            connection_config={"database": ":memory:"},
            extension_config={
                "starlette": {"session_key": "db", "connection_key": "db_connection", "pool_key": "db_pool"}
            },
        )
    )
    spec.add_config(
        SqliteConfig(
            connection_config={"database": ":memory:"},
            extension_config={
                "starlette": {"session_key": "etl_db", "connection_key": "etl_connection", "pool_key": "etl_pool"}
            },
        )
    )

    db_plugin = SQLSpecPlugin(spec)

    async def report(request: Request) -> JSONResponse:
        db: AiosqliteDriver = db_plugin.get_session(request, "db")
        etl_db: SqliteDriver = db_plugin.get_session(request, "etl_db")
        users = await db.select("SELECT 1 AS id, 'Alice' AS name")
        metrics = etl_db.select("SELECT 'metric1' AS name, 100 AS value")
        return JSONResponse({"users": users, "metrics": metrics})

    app = Starlette(routes=[Route("/report", report)])
    db_plugin.init_app(app)
    return app


if __name__ == "__main__":
    print(main())

StarletteConfig Options

Key Type Default Description
session_key str "db_session" Key on request.state used by db_plugin.get_session(request, key).
connection_key str "db_connection" Key on request.state used by db_plugin.get_connection(request, key).
pool_key str "db_pool" Key on app.state storing the active connection pool.
commit_mode "manual" | "autocommit" | "autocommit_include_redirect" "manual" Controls automatic commit/rollback middleware behavior based on response status codes.
extra_commit_statuses set[int] set() Additional HTTP status codes that trigger an automatic commit.
extra_rollback_statuses set[int] set() Additional HTTP status codes that trigger an automatic rollback.
enable_correlation_middleware bool False Propagates X-Request-ID / X-Correlation-ID headers into CorrelationContext.
enable_sqlcommenter_middleware bool False Populates SQLCommenterContext (framework="starlette", route path, method).

Recap

  • Call db_plugin.init_app(app) on your Starlette instance and retrieve sessions with db_plugin.get_session(request).
  • Use extension_config["starlette"] to configure commit_mode, multi-database keys, and sqlcommenter middleware.
  • See the Starlette Extension Reference for SQLSpecPlugin and StarletteConfig details.