fastapi-dynamic-filter

Dynamic, auto‑generating SQLAlchemy filters for FastAPI – with native support for JSONB operations, range queries, case‑insensitive search, and more.

---

Why this library?

When building a REST API with FastAPI and SQLAlchemy, you often need to let clients filter, search, sort, and apply range conditions on your models. Writing a separate Pydantic filter class for every endpoint is tedious and repetitive.

fastapi-dynamic-filter solves this by:

• Automatically generating filter fields from your SQLAlchemy model – just declare which columns you want to expose.
• Supporting PostgreSQL JSONB operators: @> (contains), ? (key presence), and ILIKE on string‑casted JSON values.
• Integrating seamlessly with fastapi-filter – you get all its power (sorting, pagination, etc.) without extra boilerplate.
• Generating OpenAPI (Swagger) documentation automatically – your API consumers see exactly what filters are available.

---

Installation

pip install fastapi-dynamic-filter

Quick Start

Assume you have a SQLAlchemy model User:

from sqlalchemy import Column, Integer, String, DateTime, Boolean
from sqlalchemy.dialects.postgresql import ARRAY, JSONB
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    email = Column(String, unique=True)
    full_name = Column(String)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime)
    tags = Column(ARRAY(String))          # PostgreSQL array
    metadata = Column(JSONB)              # PostgreSQL JSONB

Now create a filter class that inherits from DynamicFilter:

from fastapi_dynamic_filter import DynamicFilter

class UserFilter(DynamicFilter):
    db_model = User

    # Exact matches (equality)
    exact_fields = ["id", "email", "is_active"]

    # Range queries (__gte, __lte)
    range_fields = ["created_at"]

    # Case‑insensitive search (__ilike) – only for string columns
    search_fields = ["full_name"]

    # Array/JSONB contains & key presence
    contains_fields = ["tags", "metadata"]   # tags__contains, metadata__contains, metadata__has_key

    # Case‑insensitive search inside JSONB values (casts to TEXT and ILIKE)
    json_search_fields = ["metadata"]        # metadata__value_ilike

    # Default sorting (can be overridden by client)
    default_order_by = ["-created_at"]       # descending

That’s it! The filter class automatically gains all the corresponding Pydantic fields. Use it as a dependency in your FastAPI endpoint:

from fastapi import Depends, FastAPI
from sqlalchemy.orm import Session

app = FastAPI()

@app.get("/users")
def get_users(
    filter: UserFilter = Depends(UserFilter),
    db: Session = Depends(get_db),
):
    query = db.query(User)
    query = filter.filter(query)   # apply all filters
    query = filter.sort(query)     # apply sorting (if any)
    return query.all()

Your Swagger docs will now show a beautiful request body (or query parameters, depending on how you configure fastapi-filter) with all generated fields:

{
  "id": 1,
  "email": "john@example.com",
  "is_active": true,
  "created_at__gte": "2024-01-01T00:00:00Z",
  "created_at__lte": "2024-12-31T23:59:59Z",
  "full_name__ilike": "john",
  "tags__contains": ["admin"],
  "metadata__contains": {"role": "editor"},
  "metadata__has_key": "role",
  "metadata__value_ilike": "admin",
  "order_by": ["-created_at"]
}

Field Reference

Field list	Generated filter fields	Description
exact_fields	field (exact match)	Equality comparison (==)
search_fields	field__ilike	Case‑insensitive LIKE (only for string columns)
range_fields	field__gte, field__lte	Greater‑than‑or‑equal / less‑than‑or‑equal (works with numeric, date, etc.)
contains_fields	field__contains (for arrays/dicts)	PostgreSQL @> (array contains / JSONB contains)
	field__has_key (for dicts)	PostgreSQL ? (JSONB key exists)
json_search_fields	field__value_ilike	Casts JSONB to text and applies ILIKE (full‑text search inside JSON)
default_order_by	order_by (list of strings)	Default sorting direction (prepend - for descending)

All generated fields are optional – clients can send only the ones they need.

Why not just use fastapi-filter directly?

fastapi-filter is great, but it requires you to explicitly write every filter field and its type. For models with many columns, that's a lot of boilerplate. fastapi-dynamic-filter generates all those fields dynamically from your model, while still giving you full control over which columns are exposed.

Requirements

• Python 3.10+

• FastAPI 0.100+

• SQLAlchemy 2.0+

• fastapi-filter 2.0+