litestar-data-services

With advanced-alchemy, declare create_filter_dependencies on the Controller, call list_and_count on the service, and return to_schema with the filters to get an OffsetPagination envelope. With sqlspec, inject LimitOffsetFilter and OrderByFilter and pass them to the driver call.

SQLAlchemyAsyncRepositoryService is an opinionated async service base class providing CRUD methods like get, list_and_count, create, update, upsert, and to_schema. Subclass it and set repository_type to a SQLAlchemyAsyncRepository to get a full data-access surface without writing SELECT statements.

Use advanced-alchemy when you want an ORM repository service with audit fields, filters, and pagination built in. Use sqlspec when you want explicit SQL, 15+ database adapters, or Arrow-native result streams for analytics workloads.

No, create_filter_dependencies comes from advanced-alchemy's Litestar extension. With raw SQLAlchemy you must apply .limit() and .offset() on Core statements manually and compute totals with a separate count query.

Drop to hand-written SQLAlchemy for multi-table aggregate joins, window functions, recursive CTEs, or vendor-specific features like Postgres JSONB operations. Keep the query inside the service class and never push raw SQL into Controllers.