An easy way to filter, sort, paginate SQLAlchemy queries

Documentation: https://sqlalchemy-filterset.github.io/sqlalchemy-filterset

Source Code: https://github.com/sqlalchemy-filterset/sqlalchemy-filterset

The library provides a convenient and organized way to filter your database records. By creating a FilterSet class, you can declaratively define the filters you want to apply to your SQLAlchemy queries. This library is particularly useful in web applications, as it allows users to easily search, filter, sort, and paginate data.

The key features are:

• Declarative definition of filters.
• Keeping all of your filters in one place, making it easier to maintain and change them as needed.
• Constructing complex filtering conditions by combining multiple simple filters.
• Offer of a standard approach to writing database queries.
• Reduction of code duplication by reusing the same filters in multiple places in your code.
• Sync and Async support of modern SQLAlchemy.

pip install sqlalchemy-filterset

Requirements: Python 3.7+ SQLAlchemy 2.0+

In this example we specify criteria for filtering the database records by simply setting the attributes of the ProductFilterSet class. This is more convenient and easier to understand than writing raw SQL queries, which can be more error-prone and difficult to maintain.

from sqlalchemy_filterset import BaseFilterSet, Filter, RangeFilter, BooleanFilter

from myapp.models import Product


class ProductFilterSet(BaseFilterSet):
    id = Filter(Product.id)
    price = RangeFilter(Product.price)
    is_active = BooleanFilter(Product.is_active)

import uuid
from pydantic import BaseModel


class ProductFilterSchema(BaseModel):
    id: uuid.UUID | None
    price: tuple[float, float] | None
    is_active: bool | None

# Connect to the database
engine = create_engine("postgresql://user:password@host/database")
Base.metadata.create_all(bind=engine)
SessionLocal = sessionmaker(bind=engine)
session = SessionLocal()

# Define sqlalchemy query
query = select(Product)

# Define parameters for filtering
filter_params = ProductFilterSchema(price=(10, 100), is_active=True)

# Create the filterset object
filter_set = ProductFilterSet(query)

# Apply the filters to the query
query = filter_set.filter_query(filter_params.dict(exclude_unset=True))

# Execute the query
session.execute(query).unique().scalars().all()

This example will generate the following query:

select product.id, product.title, product.price, product.is_active
from product
where product.price >= 10
  and product.price <= 100
  and product.is_active = true;

This project is licensed under the terms of the MIT license.