On this page

Build a Task Manager with FastAPI: Persistence & Pagination

Published on August 11, 2026

Enrico Hochkoeppler Profile

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!

Buy Me a Coffee

Previous

Reusable Components and Services

Next

Authentication, Authorization and Security