FastAPI QueryBuilder - Advanced Query Building for FastAPI

Installation

Install FastAPI QueryBuilder using pip. It's that simple!

bash

pip install fastapi-querybuilder

Requirements

Python 3.10+
FastAPI 0.115+
SQLAlchemy 2.0+
fastapi-pagination 0.13.2+

Quick Start

Get up and running in less than a minute with this simple example.

python

from fastapi import FastAPI, Depends
from fastapi_querybuilder import QueryBuilder
from sqlalchemy.ext.asyncio import AsyncSession

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.

Filtering

Advanced JSON-based filters

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

Sorting

Multi-field sorting with nested paths

?sort=status:asc,created_at:desc

Searching

Smart search with explicit fields

?search=john

Core Features

Advanced Filtering

Build complex queries with JSON-based filters and multiple operators.

Case Sensitivity

String filters using $eq, $ne, and $in are case-insensitive by default.

Set case_sensitive=true to use legacy case-sensitive string filtering.

For enum columns (for example PostgreSQL enums), case-insensitive matching is enum-safe and uses text casting internally.

Simple Filter

json

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

Multiple Conditions

json

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

Logical OR

json

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

Dynamic Sorting

Sort results by any field, including nested relationships. Supports multiple sort clauses and both dot and double-underscore notation.

Case-Insensitive Text Sorting

String sorting is case-insensitive by default. Use case_sensitive=true to restore legacy case-sensitive text ordering.

Enum sorting is also safe in case-insensitive mode and enum fields are never treated as timestamp-like strings based on field name suffixes.

Ascending

?sort=name:asc

Descending

?sort=created_at:desc

Multiple Fields

?sort=status:asc,created_at:desc

Nested Field (dot)

?sort=role.name:asc

Nested Field (underscore)

?sort=role__name:asc

Deep Nesting

?sort=role.department.name:desc

Legacy Case-Sensitive

?sort=name:asc&case_sensitive=true

Multi-Sort Details

Left to Right: Sort clauses are applied in order (left to right)
Flexible Syntax: Use dot notation (role.name) or double-underscore (role__name)
Default Behavior: String sorting is case-insensitive unless case_sensitive=true
Enum Safety: Enum fields are cast safely for case-insensitive sorting and are excluded from timestamp-like date casting rules
Date-Aware: Timestamp-like string fields (created_at/updated_at) are sorted chronologically
Validated: Invalid directions return clear 400 error messages
Smart Joins: Automatically creates necessary joins for nested relationships

Smart Search with Field Control

Powerful search with explicit field control for optimal performance and predictable results.

Performance-Optimized Search

By default, search only queries the root model's fields (5-100x faster). Use search_fields parameter to explicitly search nested relationships.

bash

# Default: Search only User model fields (fast, no joins)
GET /users?search=john

# Explicit fields: Search specific top-level fields
GET /users?search=john&search_fields=name,email

# Nested search: Search in related models
GET /users?search=admin&search_fields=name,role.name

# Deep nesting: Multiple relationship levels
GET /users?search=Engineering&search_fields=role.department.name

# Mixed: Top-level + nested fields
GET /users?search=dev&search_fields=name,email,role.name,role.department.name

Search Field Types

String Fields

Case-insensitive ILIKE search

Enum Fields

Matches enum values

Integer Fields

Exact numeric match

Boolean Fields

True/false matching

Key Benefits

Fast Default: Top-level search = 0 joins, no DISTINCT overhead
Explicit Control: Specify exactly which fields to search
Nested Paths: Use dot notation for relationships (role.name, role.department.name)
Safe: Circular reference detection prevents infinite loops
Smart Joins: Creates joins only for specified nested paths

Pagination Support

Seamless integration with fastapi-pagination for easy result pagination.

python

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

@app.get("/users/paginated", 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

Matches values equal to specified value (case-insensitive for strings by default)

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

SQL: LOWER(name) = 'john' or LOWER(CAST(status AS VARCHAR)) = 'active' or age = 25

$ne

Not Equal

Matches values not equal to specified value (case-insensitive for strings by default)

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

SQL: LOWER(name) != 'inactive' or LOWER(CAST(status AS VARCHAR)) != 'inactive'

$gt

Greater Than

Matches values greater than specified value

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

SQL: age > 18

$gte

Greater/Equal

Matches values greater than or equal to

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

SQL: age >= 21

$lt

Less Than

Matches values less than specified value

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

SQL: age < 65

$lte

Less/Equal

Matches values less than or equal to

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

SQL: age <= 64

$in

In Array

Matches any value in an array (case-insensitive for string arrays by default)

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

SQL: LOWER(name) IN ('active', 'pending') or LOWER(CAST(status AS VARCHAR)) IN ('active', 'pending')

$isanyof

Alias: $in

Alternative syntax for $in operator

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

SQL: role IN (...)

String Operators

$contains

Contains

Case-insensitive substring match

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

SQL: name ILIKE '%john%' or CAST(status AS VARCHAR) ILIKE '%john%'

$ncontains

Not Contains

Does not contain substring

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

SQL: name NOT ILIKE '%test%' or CAST(status AS VARCHAR) NOT ILIKE '%test%'

$startswith

Starts With

Matches strings starting with value

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

SQL: email ILIKE 'admin%' or CAST(status AS VARCHAR) ILIKE 'admin%'

$endswith

Ends With

Matches strings ending with value

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

SQL: email ILIKE '%.com' or CAST(status AS VARCHAR) ILIKE '%.com'

Null/Empty Operators

$isempty

Is Null

Matches null/empty values

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

SQL: description IS NULL

$isnotempty

Not Null

Matches non-null values

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

SQL: description IS NOT NULL

Logical Operators

$and

AND

All conditions must be true

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

SQL: age >= 18 AND is_active = true

$or

OR

At least one condition must be true

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

SQL: name ILIKE '%john%' OR email ILIKE '%john%'

Advanced Features

Nested Relationships

Query and filter through nested relationships using dot notation.

json

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

Tip

You can nest relationships to any depth. QueryBuilder automatically creates the necessary joins.

Soft Delete Support

Automatically excludes soft-deleted records when your model has a deleted_at field.

python

class User(Base):
    __tablename__ = "users"
    # ... other fields ...
    deleted_at: Mapped[datetime] = mapped_column(DateTime, nullable=True)

# QueryBuilder automatically adds: WHERE deleted_at IS NULL

Smart Date Handling

Automatic date range processing for date-only strings.

Date Only

{"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 Formats

YYYY-MM-DD - Date only
YYYY-MM-DDTHH:MM:SS - ISO format
YYYY-MM-DD HH:MM:SS - Space separated
YYYY-MM-DDTHH:MM:SSZ - UTC timezone

Real-World Examples

Find active admin users in Engineering

bash

GET /users?filters={
  "is_active": {"$eq": true},
  "role.name": {"$eq": "admin"},
  "role.department.name": {"$eq": "Engineering"}
}

Complex query with multiple conditions

bash

GET /users?filters={
  "$and": [
    {
      "$or": [
        {"name": {"$contains": "john"}},
        {"email": {"$contains": "john"}}
      ]
    },
    {"age": {"$gte": 18}},
    {"role.name": {"$in": ["admin", "user"]}}
  ]
}&sort=created_at:desc

Date range with sorting and search

bash

GET /users?filters={
  "created_at": {"$gte": "2023-01-01", "$lt": "2024-01-01"}
}&search=engineer&sort=role.name:asc

Paginated results with filters

bash

GET /users/paginated?page=1&size=20&filters={
  "age": {"$gte": 25},
  "status": {"$in": ["active", "pending"]}
}&sort=name:asc