slymit/sa-filters

Purpose and Public API

sa-filters is a Python library that transforms SQLAlchemy ORM Select and legacy Query objects for API-facing filtering, sorting, restricted column loading, and pagination. Its package-level API is exposed through sa_filters/__init__.py; primary transformation entry points are implemented in sa_filters/filters.py, sa_filters/sorting.py, sa_filters/loads.py, and sa_filters/pagination.py.

Statement/Model Resolution Boundary

The library accepts declarative specification dictionaries rather than HTTP requests or database connections. It resolves requested models, table names, fields, and query participants against the input SQLAlchemy statement, including statements selecting columns, functions, or explicit select_from clauses. Shared ORM/query introspection and model resolution are centralized in sa_filters/models.py; invalid specifications surface through sa_filters/exceptions.py.

Filter Compilation

apply_filters converts sequential filter specs into SQLAlchemy predicates and returns a new statement/query. sa_filters/filters.py supports comparison, null, membership, pattern, and PostgreSQL ARRAY operators, plus nested and, or, and not expression groups. Fields may be mapped columns or SQLAlchemy hybrid properties/methods. This is a compilation layer: caller-provided dicts become ORM expressions, while SQL generation and execution remain SQLAlchemy’s responsibility.

Automatic Relationship Joining

Filtering and sorting can target models not already present in the input statement. The component detects model-targeted criteria and asks SQLAlchemy to add an implicit join when relationship/foreign-key metadata makes the join condition inferable. This behavior is shared conceptually by sa_filters/filters.py, sa_filters/sorting.py, and model inspection in sa_filters/models.py; callers can disable it for an explicit query-shape boundary.

Sort Compilation

apply_sort applies ordered ORDER BY clauses from model/field/direction specs in sa_filters/sorting.py. It supports ascending/descending ordering, hybrid attributes, optional automatic joins, and database-dependent NULLS FIRST/NULLS LAST modifiers. SQLAlchemy and the selected RDBMS determine final dialect compatibility.

Restricted ORM Loads

apply_loads in sa_filters/loads.py translates per-model field allowlists into SQLAlchemy deferred-load options. It constrains normally loaded columns, but does not control fields introduced by joined eager-loading semantics; those remain an explicit caller-side SQLAlchemy loader-option concern. Model discovery is coordinated with sa_filters/models.py.

Pagination and Execution Ownership

apply_pagination in sa_filters/pagination.py applies page offset/limit and returns both the transformed statement and pagination metadata. The caller supplies total_results, so count-query construction and all execution (Session.execute, Session.scalar, or legacy query evaluation) stay outside the library. This keeps pagination independent of session lifecycle and result materialization.

Runtime and Compatibility

Runtime dependencies are Python plus SQLAlchemy 1.4/2.0, declared in pyproject.toml. The library is database-agnostic across tested SQLite, MySQL, and PostgreSQL, with PostgreSQL-specific ARRAY filtering and dialect-sensitive null ordering. CI is defined in .github/workflows/ci.yml; database integration behavior is validated against ORM fixtures and interface tests under test/.