sqlspec

Configure a typed adapter like AsyncpgConfig, then use async with config.create_driver() to get a driver and call methods like select_many() with your SQL and parameters. Match the parameter style to your adapter: $1 for asyncpg, %s for psycopg, ? for sqlite, and :name for oracledb.

SQLSpec is a type-safe SQL query mapper, not an ORM. It gives you direct SQL control, a fluent query builder, and 15+ driver adapters with Arrow support, while ORMs like SQLAlchemy or advanced-alchemy provide repository patterns, identity mapping, and attribute-style row access.

Yes, the FastAPI extension provides Depends-based factories: provide_session() injects a per-request driver session and provide_filters() generates query-parameter-driven filter dependencies. Configuration lives under extension_config["starlette"] since the FastAPI plugin extends the Starlette plugin.

Pass LimitOffsetFilter and OrderByFilter objects directly to driver methods like select_many() alongside your SQL string. Filters are composable, applied in order, and modify the statement before execution without manual LIMIT/OFFSET string concatenation.

Yes, select_to_arrow() returns a pyarrow.Table with zero-copy transfer on DuckDB, ADBC, BigQuery, and Spanner adapters; other adapters convert rows to Arrow. Use copy_from_arrow() for bulk loading Arrow data back into a database table.

The autocommit middleware commits only on 2xx responses and rolls back everything else, including 4xx. Add the status code to extra_commit_statuses in extension_config, or switch commit_mode to manual and manage transactions explicitly.