fastapi-sqlalchemy-querykit 0.2.0

Deny-by-default declarative filtering, sorting, and searching for FastAPI + SQLAlchemy 2.0.

fastapi-sqlalchemy-querykit

Deny-by-default declarative filtering, sorting, and searching for FastAPI + SQLAlchemy 2.0 (async-safe). A field is filterable, sortable, or searchable only if the model explicitly declares it — the opposite of the open-by-default library this is derived from.

pip install fastapi-sqlalchemy-querykit

Distribution: fastapi-sqlalchemy-querykit · import package: querybuilder. Full docs in docs/.

Why

• Deny by default. Undeclared fields return a generic 400, never a silent query.
• Declare policy on the model. A Queryable mixin lists the allowed fields once; endpoints may narrow but never widen.
• Correct relationships. Filters and search across relationships compile to correlated EXISTS (.has() / .any()) — no row fan-out, no DISTINCT, to-many supported; two paths to the same model never collide. Sorting uses path-keyed joins.
• Type-aware & enum-safe. Operators are validated against the column type; enums match by value (Postgres-safe).
• Caller-owned base scope. The builder is purely additive — it never adds or inspects your own WHERE (tenant, soft-delete, visibility).

Quick example

from querybuilder import Queryable, QueryParams, build_query

class User(Base, Queryable):
    __filterable__ = frozenset({"status", "username", "age", "organization.name"})
    __sortable__   = frozenset({"created_at", "username"})
    __searchable__ = frozenset({"username", "organization.name"})

# inside your data/query layer
async def list_users(db, params: QueryParams):
    stmt = build_query(User, params)                 # filters + search + sort
    stmt = stmt.where(User.is_deleted.is_(False))     # caller-owned base scope
    return (await db.execute(stmt)).scalars().all()

GET /users?filters={"status":{"$eq":"active"},"organization.name":{"$contains":"acme"}}&sort=created_at:desc&search=jdoe

The filter travels as one JSON object, {field: {operator: value}}, with $and / $or for grouping and a dot for relationship traversal (organization.name).

Operators

$eq $ne $gt $gte $lt $lte $in $nin · $contains $ncontains $startswith $endswith · $isnull $isnotnull · logical $and $or.

String comparisons are case-insensitive by default (case_sensitive=true to opt out). Enum filters resolve the operand to the matching member, so you filter by the enum value regardless of how SQLAlchemy stores it.

Endpoint-level narrowing

The model declares the maximum; an endpoint may expose a subset (never a superset):

stmt = build_query(User, params, allowed_filters={"status", "username"})

Documentation

• Operator reference — wire format, every operator, the per-type matrix
• Guide — Queryable, relationships, search, sort, errors, limits, startup validation
• FastAPI integration
• Pagination & query-config — the Page envelope, list_and_paginate, build_query_config

Build the docs site locally: pip install -e ".[docs]" && mkdocs serve.

Development

python -m venv .venv && source .venv/bin/activate
pip install -e ".[dev]"
pytest

License

MIT. Derivative work of fastapi-querybuilder by bhadri01 — see LICENSE and NOTICE.