- Courses
- 📚Libraries & Frameworks
- ⚡FastAPI
- Intermediate API Design
- Project: Build a Task Manager API (Part 2)
On this page
Build a Task Manager with FastAPI: Persistence & Pagination
Published on August 11, 2026
By Enrico Hochkoeppler
Web Developer & TutoriaLibre Founder
This continues a Task Manager API built with FastAPI and SQLModel, assuming a working Task model, a configured database engine, and a get_session dependency already exist in the project. From that starting point, every route below is wired to real, persistent storage, filtering and pagination are combined into a single endpoint rather than two separate ones, and the project is organized into a router that handles HTTP concerns and a service module that handles everything else.
info
You can find the first part of this tutorial here.
Wiring CRUD Operations to the Database
services/task_service.py:
from sqlmodel import Session, select
from models.task import Task
def create_task(task: Task, session: Session) -> Task:
session.add(task)
session.commit()
session.refresh(task)
return task
def get_task_by_id(task_id: int, session: Session) -> Task:
task = session.get(Task, task_id)
if not task:
raise ValueError(f"Task with id {task_id} not found")
return task
def update_task(task_id: int, updated: Task, session: Session) -> Task:
task = get_task_by_id(task_id, session)
task.title = updated.title
task.description = updated.description
task.completed = updated.completed
session.commit()
session.refresh(task)
return task
def delete_task(task_id: int, session: Session) -> None:
task = get_task_by_id(task_id, session)
session.delete(task)
session.commit()
get_task_by_id raises a plain ValueError rather than an HTTPException. This keeps task_service.py free of any dependency on FastAPI's HTTP layer — it could be called from a test, a script, or a background job with no HTTP request involved anywhere, and it will behave identically. Translating that ValueError into an actual 404 response is the router's job, covered further down.
Filtering and Pagination Together
Filtering by completion status and limiting how many results come back are both concerns of the same operation — listing tasks — so they belong in one function and one endpoint rather than two separate ones:
services/task_service.py:
def list_tasks(
session: Session,
completed: bool | None = None,
limit: int = 20,
offset: int = 0,
) -> list[Task]:
query = select(Task)
if completed is not None:
query = query.where(Task.completed == completed)
query = query.offset(offset).limit(limit)
return session.exec(query).all()
routers/tasks.py:
from typing import Annotated
from fastapi import APIRouter, Depends, HTTPException, Query
from sqlmodel import Session
from db import get_session
from models.task import Task
from services import task_service
router = APIRouter(prefix="/tasks", tags=["Tasks"])
SessionDep = Annotated[Session, Depends(get_session)]
@router.get("/", response_model=list[Task])
def get_all(
session: SessionDep,
completed: bool | None = Query(default=None),
limit: int = Query(default=20, ge=1, le=100),
offset: int = Query(default=0, ge=0),
):
return task_service.list_tasks(session, completed, limit, offset)
limit and offset both default to sensible values, so GET /tasks on its own returns the first 20 tasks with no extra query string required. GET /tasks?completed=true filters without paginating explicitly, since the pagination defaults still apply. GET /tasks?limit=5&offset=10 paginates without filtering. GET /tasks?completed=false&limit=3&offset=6 combines both. A single endpoint handling every combination is simpler to maintain than two separate ones that would otherwise duplicate the same filtering logic — ge=1, le=100 on limit also caps how much a single request can pull back, which matters the moment the table holds more than a handful of rows.
Handling a Single Task
@router.get("/{task_id}", response_model=Task)
def get_one(task_id: int, session: SessionDep):
try:
return task_service.get_task_by_id(task_id, session)
except ValueError as e:
raise HTTPException(status_code=404, detail=str(e))
@router.post("/", status_code=201, response_model=Task)
def create(task: Task, session: SessionDep):
return task_service.create_task(task, session)
@router.put("/{task_id}", response_model=Task)
def update(task_id: int, updated: Task, session: SessionDep):
try:
return task_service.update_task(task_id, updated, session)
except ValueError as e:
raise HTTPException(status_code=404, detail=str(e))
@router.delete("/{task_id}", status_code=204)
def delete(task_id: int, session: SessionDep):
try:
task_service.delete_task(task_id, session)
except ValueError as e:
raise HTTPException(status_code=404, detail=str(e))
Each try/except ValueError block is the exact point where a domain-level failure — "this task doesn't exist" — becomes an HTTP-level one — 404 Not Found with a matching detail message. The service function itself never makes that decision; it only reports that something went wrong in terms any caller can understand.
Project Structure
task_manager/
├── main.py
├── db.py
├── models/
│ └── task.py
├── routers/
│ └── tasks.py
└── services/
└── task_service.py
main.py:
from contextlib import asynccontextmanager
from fastapi import FastAPI
from sqlmodel import SQLModel
from db import engine
from routers import tasks
@asynccontextmanager
async def lifespan(app: FastAPI):
SQLModel.metadata.create_all(engine)
yield
app = FastAPI(lifespan=lifespan)
app.include_router(tasks.router)
SQLModel.metadata.create_all(engine) runs once, before yield, at application startup — it creates any tables that don't already exist and leaves existing ones untouched, which is why it's safe to run on every startup rather than just the first one.
Testing in Swagger UI
With the server running, http://127.0.0.1:8000/docs lists every endpoint with its parameters and response schema, including completed, limit, and offset on the combined GET /tasks route, each with the defaults and constraints already declared in code reflected directly in the interactive form. Sending requests through "Try it out" with different combinations of those three parameters confirms filtering and pagination are working together correctly before anything is tested from outside the browser.
Full Endpoint Recap
FeatureEndpointDescriptionCreate taskPOST /tasksAdd a new taskList tasksGET /tasksFilter by completed, paginate with limit/offsetRead one taskGET /tasks/{id}Get a task by IDUpdate taskPUT /tasks/{id}Replace a task in fullDelete taskDELETE /tasks/{id}Remove a task
Every route is backed by real, persistent storage, with the router handling HTTP concerns exclusively and task_service.py handling everything involving the database.
Extending the API: Toggle, Stats, and Search
services/task_service.py:
def toggle_task(task_id: int, session: Session) -> Task:
task = get_task_by_id(task_id, session)
task.completed = not task.completed
session.commit()
session.refresh(task)
return task
def get_task_stats(session: Session) -> dict:
all_tasks = session.exec(select(Task)).all()
completed_count = sum(1 for t in all_tasks if t.completed)
return {
"total": len(all_tasks),
"completed": completed_count,
"pending": len(all_tasks) - completed_count,
}
def search_tasks(query_text: str, session: Session) -> list[Task]:
query = select(Task).where(Task.title.ilike(f"%{query_text}%"))
return session.exec(query).all()
toggle_task reuses get_task_by_id, so a nonexistent task raises the same ValueError the rest of the service already relies on — no separate error handling needed for it. get_task_stats fetches every task once and computes both counts from that single list rather than running two separate queries, since total and pending are both trivially derivable once completed_count is known. search_tasks uses .ilike() rather than .like() specifically for portability: SQLite's own LIKE already happens to be case-insensitive for ASCII text by default, but ilike makes that case-insensitivity explicit and keeps the same code correct if the database is ever switched to PostgreSQL, where LIKE is case-sensitive.
routers/tasks.py:
@router.patch("/{task_id}/toggle", response_model=Task)
def toggle(task_id: int, session: SessionDep):
try:
return task_service.toggle_task(task_id, session)
except ValueError as e:
raise HTTPException(status_code=404, detail=str(e))
@router.get("/stats")
def stats(session: SessionDep):
return task_service.get_task_stats(session)
@router.get("/search", response_model=list[Task])
def search(query: str, session: SessionDep):
return task_service.search_tasks(query, session)
/tasks/stats and /tasks/search sit alongside /tasks/{task_id} on the same router without colliding, since task_id is typed as int — a request to /tasks/stats never matches a digits-only path parameter regardless of which route was registered first. PATCH /tasks/3/toggle flips task 3's completion status and returns the updated task; calling it again flips it back. GET /tasks/stats returns {"total": 12, "completed": 5, "pending": 7} for a database holding twelve tasks, five of them completed. GET /tasks/search?query=milk returns every task whose title contains "milk" in any case — "Buy milk", "MILK run", and "almond milk substitute" would all match a search for "milk".
Key Takeaways
Every route built here follows the same shape established from the start: the router parses the request and translates domain errors into HTTP ones, while task_service.py holds every actual database interaction and reports failure through plain ValueError exceptions rather than anything FastAPI-specific. Filtering and pagination combine naturally into a single list_tasks function and a single GET /tasks endpoint, since both are really the same underlying operation — "return some subset of tasks" — rather than two different ones. Extending the API with a toggle, a stats summary, and a search endpoint required no new architectural pattern at all: each one reuses get_task_by_id or select(Task) the same way the original CRUD operations did, which is exactly the payoff of having settled on a consistent router-and-service structure from the beginning.
Found this helpful? Share the knowledge...
... or support this project to keep it alive!
Previous
Reusable Components and Services
Next
Authentication, Authorization and Security
- Wiring CRUD Operations to the Database
- Filtering and Pagination Together
- Handling a Single Task
- Project Structure
- Testing in Swagger UI
- Full Endpoint Recap
- Extending the API: Toggle, Stats, and Search
- Key Takeaways
