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

Sort by any field

?sort=name:asc

Searching

Global recursive search

?search=john

Core Features

Advanced Filtering

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

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.

Ascending

?sort=name:asc

Descending

?sort=created_at:desc

Nested Field

?sort=role.name:asc

Deep Nesting

?sort=role.department.name:desc

Recursive Global Search

Automatically search across all fields and relationships with intelligent type detection.

String Fields

Case-insensitive ILIKE search

Enum Fields

Matches enum values

Integer Fields

Exact numeric match

Boolean Fields

True/false matching

bash

# Search across User, Role, and Department models
GET /users?search=engineering

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

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

SQL: age = 25

$ne

Not Equal

Matches values not equal to specified value

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

SQL: status != '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

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

SQL: status IN (...)

$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%'

$ncontains

Not Contains

Does not contain substring

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

SQL: name NOT ILIKE '%test%'

$startswith

Starts With

Matches strings starting with value

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

SQL: email ILIKE 'admin%'

$endswith

Ends With

Matches strings ending with value

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

SQL: email 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