Skip to main content

Alchemy FilterSet 🚀

A powerful, dynamic, and type-safe filtering architecture for SQLAlchemy 2.0 and Advanced Alchemy, inspired by django-filters.

Why Alchemy FilterSet?

While SQLAlchemy and Advanced Alchemy provide excellent tools for querying databases, handling complex HTTP query parameters (like nested relationships, dynamic ordering, and multi-field search) often leads to messy, repetitive, and hard-to-maintain code.

alchemy-filterset bridges the gap between your Web Framework (FastAPI, Litestar, etc.) and your Database by providing a declarative, Pydantic-powered filter class that securely translates user requests into efficient SQL EXISTS expressions.

Design Goals

Alchemy FilterSet is designed around a few core principles:

  • Declarative API inspired by Django FilterSet while remaining SQLAlchemy-native.
  • No automatic joins; relationship filters are translated into EXISTS expressions instead. Ordering is the one exception — see Dynamic Ordering.
  • Type-safe query parsing powered by Pydantic v2.
  • Extensible lookup registry for custom operators.
  • Framework agnostic (FastAPI, Litestar, Starlette, etc.).

Key Features

  • 🔗 Deep Nested Relationships: Seamlessly filter across infinite layers of relationships (e.g., province__country__name__icontains="Iran").
  • 🚫 Negation Support: Easily exclude records using the not__ prefix (e.g., not__status="deleted") — works uniformly on standard lookups, nested relationships, and custom filter_<field> methods.
  • 🗂 Smart Pagination: Built-in, declarative pagination controls with frontend limits and backend enforcement.
  • 🔍 Global Multi-Field Search: Search across multiple columns and related tables simultaneously with a single ?search= parameter. Unknown field names in search_fields are ignored rather than raising an error.
  • ↕️ Dynamic Ordering: Sort by any direct field, or by a nested relationship if you join the related table yourself (e.g., ?ordering=-province__name,created_at) — see Dynamic Ordering.
  • 🧩 Association Proxy Support: Query and order through SQLAlchemy's AssociationProxy, whether it points to another model or straight to a scalar column.
  • 🛡 Type-Safe: Built with Pydantic v2 and Python 3.10+ types.

📦 Installation

This package requires Python 3.10+ and SQLAlchemy 2.0+.

pip install alchemy-filterset
uv add alchemy-filterset
poetry add alchemy-filterset

⚡ Quick Start

Imagine you have two SQLAlchemy models: Country and Province.

1. Define your FilterSet

Inherit from SQLAlchemyFilterSet and declare the allowed filters as Pydantic fields.

from uuid import UUID
from alchemy_filterset import SQLAlchemyFilterSet
from my_app.models import Province

class ProvinceFilter(SQLAlchemyFilterSet):
    # 1. Bind to your SQLAlchemy Model
    model_cls = Province

    # 2. Define fields for global search (?search=...)
    search_fields = {"name", "country__name", "country__code"}

    # 3. Define allowed query parameters using double-underscore syntax
    name__icontains: str | None = None
    population__gt: int | None = None
    is_active: bool | None = None

    # 4. Filter across relationships seamlessly!
    country__id: UUID | None = None
    country__code__in: list[str] | None = None

2. Use it in your API (FastAPI / Litestar Example)

Pass the query parameters to the FilterSet, call to_statement_filters(), and pass the result to your Advanced Alchemy repository.

# Example using FastAPI/Litestar dependency injection
@app.get("/provinces")
async def get_provinces(
    filters: ProvinceFilter = Depends(),
    repo: ProvinceRepository = Depends()
):
    # 1. Translate Pydantic model to SQLAlchemy expressions
    sql_filters = filters.to_statement_filters()

    # 2. Pass them to Advanced Alchemy repository
    provinces = await repo.get_many(*sql_filters)

    return provinces

Now, your API automatically supports queries like: GET /provinces?country__code__in=IR,US&population__gt=1000000&ordering=-name&page=2


📖 Feature Guide & Examples

1. Standard Lookups

By default, fields use the exact equality (eq) operator. You can append lookups using the __ syntax.

Supported Lookups:

  • Comparison: eq, ne, gt, ge, lt, le, between
  • Collection: in, notin
  • Text: contains, icontains, not_contains, not_icontains, startswith, endswith
  • Null Check: is_null, not_null
GET /api/users?age__between=18,30
GET /api/users?status__in=active,pending
GET /api/users?email__endswith=@gmail.com
GET /api/users?deleted_at__is_null=true

2. Negation / Exclude (The not__ prefix)

You can negate any standard lookup, nested relationship, or custom filter simply by prefixing it with not__. This dynamically translates to !=, NOT IN, or NOT EXISTS in SQL.

class UserFilter(SQLAlchemyFilterSet):
    model_cls = User

    # Simple Negation (e.g., status != 'banned')
    not__status: str | None = None

    # Nested Negation (Users who DO NOT have a specific role)
    not__roles__name__icontains: str | None = None

    # Negation also works on custom filter_<field> methods (see #7 below) —
    # the condition the method returns is inverted, whatever it is.
    has_avatar: bool | None = None
    not__has_avatar: bool | None = None

    def filter_has_avatar(self, value: bool):
        if value:
            return User.avatar_url.is_not(None)
        return User.avatar_url.is_(None)

3. Deep Nested Relationships (The Magic ✨)

You don't need to write complex JOINs or EXISTS subqueries manually. Just chain relationship names separated by __.

class CityFilter(SQLAlchemyFilterSet):
    model_cls = City

    # City -> Province -> Country -> name
    province__country__name__icontains: str | None = None

Under the hood, this generates efficient SQL EXISTS queries using SQLAlchemy's .has() and .any() — no JOIN required, regardless of how deep the chain goes.

Define search_fields on your class. If a user passes the ?search= parameter, the system will apply an icontains filter to all specified fields, joining them with an OR operator.

class PostFilter(SQLAlchemyFilterSet):
    model_cls = Post
    search_fields = {"title", "content", "author__username"}

GET /api/posts?search=python will search for "python" in the title, content, OR the author's username.

A misconfigured or renamed field in search_fields won't raise an error — it's silently skipped, and the remaining valid fields are still searched.

5. Dynamic Ordering

Users can sort results using the ordering parameter. Prefix with - for descending order. Separate multiple fields with commas.

GET /api/users?ordering=-created_at,last_name

You can also order by a field on a related model:

GET /api/cities?ordering=-province__name,created_at

⚠️ Nested ordering requires you to add the JOIN yourself. Filtering across relationships uses a correlated EXISTS subquery, which never needs a JOIN — but SQL's ORDER BY does. In line with this library's "no automatic joins" design goal, alchemy-filterset does not add that join for you. If you order by a nested field (or an AssociationProxy that points across a relationship) without joining the related table into your base query, the database will raise an error, since the referenced table won't be in the query's FROM/JOIN clause.

Add the join yourself and pass the resulting statement as the base query, e.g. via Advanced Alchemy's statement parameter:

from sqlalchemy import select

base_statement = select(City).join(City.province)
cities = await repo.list(*filters.to_statement_filters(), statement=base_statement)

Unknown or misspelled field names passed in ordering are silently ignored rather than raising an error.

Controlling where NULL values appear

Databases disagree on where NULL sorts: PostgreSQL puts NULLs last for ASC and first for DESC, while MySQL and SQLite do the opposite. To get the same result everywhere, say it explicitly.

Per field, by adding :nulls_first or :nulls_last in the query string:

GET /articles?ordering=-published_at:nulls_last,title

Per FilterSet, by setting a default for every ordered field:

class ArticleFilterSet(SQLAlchemyFilterSet):
    model_cls = Article
    default_nulls = "last"
  • An explicit :nulls_first / :nulls_last overrides default_nulls.
  • Modifiers are case-insensitive.
  • An ordering entry with an unknown modifier (for example published_at:nulls_lst) is ignored completely, just like an unknown field.
  • Requires advanced-alchemy>=1.12.0.

Note: The :nulls_* query syntax is experimental and may change if Advanced Alchemy ships its own filtering API.

Tip (PostgreSQL): DESC NULLS LAST and ASC NULLS FIRST need a matching index to avoid an extra sort on large tables, for example Index("ix_article_published_at", Article.published_at.desc().nulls_last()).

6. Pagination Control

Pagination is enabled by default. If the user does not provide page or page_size, the defaults are used.

class HeavyReportFilter(SQLAlchemyFilterSet):
    model_cls = Report

    # Customize pagination limits per class
    default_page_size = 10
    max_page_size = 50

Disabling Pagination: If an API should return all records (e.g., a dropdown list), set enable_pagination = False.

class DropdownFilter(SQLAlchemyFilterSet):
    model_cls = Category
    enable_pagination = False # Limits/Offsets are entirely ignored

7. Custom Filter Methods

Need complex logic that doesn't fit standard lookups? Write a custom method! Name it filter_<field_name>.

class UserFilter(SQLAlchemyFilterSet):
    model_cls = User

    has_avatar: bool | None = None

    def filter_has_avatar(self, value: bool):
        if value:
            return User.avatar_url.is_not(None)
        return User.avatar_url.is_(None)

If the method returns None, no condition is added for that field — useful for a value that means "don't filter on this at all."

Custom filter methods also honor the not__ prefix (see Negation); whatever condition your method returns gets inverted.


🛠 Advanced Usage: Association Proxies

alchemy-filterset natively supports SQLAlchemy's AssociationProxy, whether it proxies to another mapped model or straight to a plain column on the far side of the relationship.

class Post(Base):
    __tablename__ = "posts"
    # ...
    # Association proxy straight to a scalar column
    tags: AssociationProxy[list[str]] = association_proxy("post_tags", "tag_name")

class PostFilter(SQLAlchemyFilterSet):
    model_cls = Post
    tags__icontains: str | None = None

This resolves to the real tag_name column and compiles into an EXISTS over post_tags — the same .any()/.has() approach used for ordinary nested relationships, so it works without a JOIN.

Ordering by an AssociationProxy is also supported, but since it still traverses a relationship under the hood, it's subject to the same rule as any nested field: you need to join the related table yourself (see Dynamic Ordering).

License

MIT

Metadata

Release files for alchemy-filterset 0.3.0

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for alchemy-filterset 0.3.0
File Size Uploaded Provenance Actions
alchemy_filterset-0.3.0.tar.gz 11.4 kB Without provenance Details for alchemy_filterset-0.3.0.tar.gz

Built distribution (wheel)

Table of built distributions (wheels) for alchemy-filterset 0.3.0
File Interpreter ABI Platform Actions
alchemy_filterset-0.3.0-py3-none-any.whl Python 3 none any Details for alchemy_filterset-0.3.0-py3-none-any.whl

Total release size: 24.1 kB

Release files / alchemy_filterset-0.3.0.tar.gz

Download URL alchemy_filterset-0.3.0.tar.gz
Size 11.4 kB
Tags Source
SHA-256 checksum
How to use checksums
563f92aee400c8c8d0557132c8ce11504dfe5f6d5528a58adedc35ada8fbc08f
BLAKE2b-256 checksum
How to use checksums
30a1c9cbc712ce70c2e1bca68ab86e63ccaeb0617f3335cb9e507bcc1563bc62
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.13.5 {"installer":{"name":"uv","version":"0.13.5","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"macOS","version":null,"id":null,"libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":null}

Release files / alchemy_filterset-0.3.0-py3-none-any.whl

Download URL alchemy_filterset-0.3.0-py3-none-any.whl
Size 12.7 kB
Tags Python 3
SHA-256 checksum
How to use checksums
c4fceecbd40520ac967a3cdc4fe14147b9a6441c340d6d3bd9404526cb50b2ee
BLAKE2b-256 checksum
How to use checksums
e3c00ae6c0095b7d1d82d346528f9dfe643236b5a0fa1c906d23f0e9229293ff
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via uv/0.13.5 {"installer":{"name":"uv","version":"0.13.5","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"macOS","version":null,"id":null,"libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":null}

Security for alchemy-filterset 0.3.0

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

No provenance found

None of the files in this release were uploaded with provenance metadata.

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page