FastAPI QueryBuilder - Documentation

Installation

Install FastAPI QueryBuilder using pip:

pip install fastapi-querybuilder

Quick Start

Get started with FastAPI QueryBuilder in just a few lines of code:

from fastapi import FastAPI, Depends
from fastapi-querybuilder.dependencies import QueryBuilder
from sqlalchemy.ext.asyncio import AsyncSession
from your_models import User

app = FastAPI()

@app.get("/users")
async def get_users(
    query = QueryBuilder(User),
    session: AsyncSession = Depends(get_db)
):
    result = await session.execute(query)
    return result.scalars().all()

That's it! Your endpoint now supports filtering, sorting, and searching through query parameters.

Basic Usage

Once you've set up your endpoint, you can use these query parameters:

Filtering

Use JSON-based filters to query your data

?filters={"name": {"$eq": "John"}}

Sorting

Sort by any field in ascending or descending order

?sort=name:asc

Searching

Global search across all string fields

?search=john

Features

Advanced Filtering

FastAPI QueryBuilder supports powerful JSON-based filtering with multiple operators:

Simple Equality Filter

{"name": {"$eq": "John Doe"}}

Multiple Conditions

{
    "$and": [
        {"age": {"$gte": 18}},
        {"is_active": {"$eq": true}},
        {"status": {"$in": ["active", "pending"]}}
    ]
}

Logical Operators

{
  "$or": [
    {"name": {"$contains": "John"}},
    {"email": {"$contains": "john"}}
  ]
}

Dynamic Sorting

Sort your results by any field, including nested relationships:

Basic Sorting: ?sort=name:asc

Descending Order: ?sort=created_at:desc

Nested Relationships: ?sort=role.name:asc

Global Search

Search across all string, enum, integer, and boolean fields automatically:

String Fields

Case-insensitive search using ILIKE

Enum Fields

Matches enum values containing search term

Integer Fields

Exact match for numeric searches

Boolean Fields

Matches "true" or "false" strings

Pagination Support

Works seamlessly with fastapi-pagination:

from fastapi_pagination import Page, add_pagination
from fastapi_pagination.ext.sqlalchemy import paginate

@app.get("/users", response_model=Page[UserResponse])
async def get_users_paginated(
    query = QueryBuilder(User),
    session: AsyncSession = Depends(get_db)
):
    return await paginate(session, query)

add_pagination(app)

API Reference

Comparison Operators

$eq

Equal to

{"age": {"$eq": 25}}

$ne

Not equal to

{"status": {"$ne": "inactive"}}

$gt

Greater than

{"age": {"$gt": 18}}

$gte

Greater than or equal

{"age": {"$gte": 21}}

$lt

Less than

{"age": {"$lt": 65}}

$lte

Less than or equal

{"age": {"$lte": 64}}

$in

In array

{"status": {"$in": ["active", "pending"]}}

$isanyof

Is any of (alias for $in)

{"role": {"$isanyof": ["admin", "user"]}}

String Operators

$contains

Contains substring

{"name": {"$contains": "john"}}

$ncontains

Does not contain

{"name": {"$ncontains": "test"}}

$startswith

Starts with

{"email": {"$startswith": "admin"}}

$endswith

Ends with

{"email": {"$endswith": ".com"}}

Null/Empty Operators

$isempty

Is null/empty

{"description": {"$isempty": true}}

$isnotempty

Is not null/empty

{"description": {"$isnotempty": true}}

Logical Operators

$and

Logical AND

{"$and": [{"age": {"$gte": 18}}, {"is_active": {"$eq": true}}]}

$or

Logical OR

{"$or": [{"name": {"$contains": "john"}}, {"email": {"$contains": "john"}}]}

Date Handling

FastAPI QueryBuilder automatically handles date ranges for date-only strings:

Date-only string: {"created_at": {"$eq": "2023-12-01"}}

Matches entire day (00:00:00 to 23:59:59)

Full datetime: {"created_at": {"$eq": "2023-12-01T10:30:00"}}

Exact timestamp match

Supported Date Formats

YYYY-MM-DD (e.g., "2023-12-01")
YYYY-MM-DDTHH:MM:SS (e.g., "2023-12-01T10:30:00")
YYYY-MM-DD HH:MM:SS (e.g., "2023-12-01 10:30:00")
YYYY-MM-DDTHH:MM:SSZ (e.g., "2023-12-01T10:30:00Z")

Real-World Examples

Complete Model Setup

from sqlalchemy import String, ForeignKey, DateTime
from sqlalchemy.orm import Mapped, mapped_column, relationship, declarative_base
from datetime import datetime, timezone

Base = declarative_base()

class Role(Base):
    __tablename__ = "roles"
    
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String, unique=True)
    
    users: Mapped[list["User"]] = relationship("User", back_populates="role")

class User(Base):
    __tablename__ = "users"
    
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String)
    email: Mapped[str] = mapped_column(String, unique=True)
    role_id: Mapped[int] = mapped_column(ForeignKey("roles.id"))
    age: Mapped[int] = mapped_column(nullable=True)
    is_active: Mapped[bool] = mapped_column(default=True)
    created_at: Mapped[datetime] = mapped_column(default=lambda: datetime.now(timezone.utc))
    deleted_at: Mapped[datetime] = mapped_column(nullable=True)  # Soft delete support
    
    role: Mapped["Role"] = relationship("Role", back_populates="users")

Advanced Filtering Examples

Find active users older than 25

GET /users?filters={"$and": [{"age": {"$gt": 25}}, {"is_active": {"$eq": true}}]}

Find users by role name (nested relationship)

GET /users?filters={"role.name": {"$eq": "admin"}}

Complex query with multiple conditions

{
  "$and": [
    {
      "$or": [
        {"name": {"$contains": "john"}},
        {"email": {"$contains": "john"}}
      ]
    },
    {"age": {"$gte": 18}},
    {"role.name": {"$in": ["admin", "user"]}}
  ]
}

URL Examples

Basic filtering: GET /users?filters={"name":{"$eq":"John"}}

With sorting: GET /users?filters={"is_active":{"$eq":true}}&sort=name:asc

With search: GET /users?search=john&sort=created_at:desc

Combined: GET /users?filters={"age":{"$gte":18}}&search=admin&sort=role.name:asc

Advanced Features

Nested Relationships

Query and sort by nested relationship fields using dot notation:

{
  "role.name": {"$eq": "admin"},
  "department.company.name": {"$contains": "Tech"}
}

Soft Delete Support

Automatically excludes soft-deleted records if your model has a deleted_at field:

class User(Base):
    # ... other fields ...
    deleted_at: Mapped[datetime] = mapped_column(nullable=True)
    
# QueryBuilder automatically adds: WHERE deleted_at IS NULL

Error Handling

FastAPI QueryBuilder provides clear error messages for invalid queries:

Invalid JSON: 400 Bad Request: Invalid filter JSON

Invalid field: 400 Bad Request: Invalid filter key: nonexistent_field

Invalid operator: 400 Bad Request: Invalid operator '$invalid' for field 'name'