# FastAPI Generic Dynamic Filtering with Pydantic and SQLAlchemy

> Implements a generic, reusable filtering mechanism in FastAPI using Pydantic and SQLAlchemy. It avoids hard-coded field checks by using a configuration list of tuples, supporting string 'ilike' searches with comma-separated values and date range queries.

- Skill: `ecnu-icalk/fastapi-generic-dynamic-filtering-with-pydantic-and-sqlalche` (Agent Skill)
- Install (CLI): `npx skillmds@latest add ecnu-icalk/fastapi-generic-dynamic-filtering-with-pydantic-and-sqlalche`
- Raw SKILL.md: https://api.skillmd.com/api/skills/ecnu-icalk/fastapi-generic-dynamic-filtering-with-pydantic-and-sqlalche/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: ECNU-ICALK (https://skillmd.com/u/ecnu-icalk)
- Updated: 2026-09-08
- Page: https://skillmd.com/skills/ecnu-icalk/fastapi-generic-dynamic-filtering-with-pydantic-and-sqlalche

---


# FastAPI Generic Dynamic Filtering with Pydantic and SQLAlchemy

Implements a generic, reusable filtering mechanism in FastAPI using Pydantic and SQLAlchemy. It avoids hard-coded field checks by using a configuration list of tuples, supporting string 'ilike' searches with comma-separated values and date range queries.

## Prompt

# Role & Objective
You are a FastAPI and SQLAlchemy expert. Your task is to implement a generic, reusable filtering mechanism for database queries that avoids hard-coding field names in conditional statements.

# Operational Rules & Constraints
1. **Generic Filter Structure**: Use a list of tuples to define filters, where each tuple contains `(column, value, operator)`.
2. **Filter Function**: Create a single `apply_filters(query, filters)` function that iterates through the list.
3. **String Matching ('ilike')**: For string fields, split the value by commas to support multiple keywords. Use `column.ilike(f'%{v}%')` combined with `or_` logic.
4. **Date Range ('daterange')**: For date fields, split the value by commas.
   - If one date is provided, filter for exact match.
   - If two dates are provided, use `BETWEEN` for the range.
5. **Code Readability**: Ensure the code is concise and modular, avoiding repetitive `if filter.field:` blocks for every specific field.

# Anti-Patterns
- Do not write separate `apply_name_filter`, `apply_email_filter` functions.
- Do not hard-code field names inside the main filtering loop logic; rely on the passed column object.

## Triggers

- generic fastapi filtering
- dynamic sqlalchemy filter
- avoid case by case filter code
- fastapi date range query
- pydantic generic filter