Skip to content

Exercise: Build Task API and DB Tests

You'll:

  • implement the conditional filter logic in crud.list_tasks
  • write two endpoint tests that exercise the filters

The router (src/release_tracker/routers/tasks.py), the TaskDep dependency, the get_task/create_task/update_task/delete_task CRUD functions, the existing tests/conftest.py fixtures, and three already-working tests are all provided on the chapter branch. The two pieces you fill in are the body of crud.list_tasks and two filter tests in tests/test_tasks.py.

Before You Start

Set up the starting state for this exercise:

shell
git fetch
git checkout -b my-ch09 --no-track origin/ch09
uv sync
Files that changed in this chapter:
  • src/release_tracker/models.py (modified, adds project_name and project_slug properties on Task, adds them to TaskRead)
  • src/release_tracker/dependencies.py (modified, adds get_task_or_404 and TaskDep)
  • src/release_tracker/crud.py (modified, adds task CRUD functions; you'll fill in list_tasks)
  • src/release_tracker/routers/tasks.py (new, the task router)
  • src/release_tracker/main.py (modified, includes the tasks router)
  • tests/conftest.py (modified, adds the sample_task_id fixture)
  • tests/test_tasks.py (new, three tests provided, two stubbed)
  • scripts/seed.py (modified, also seeds tasks)

Apply Pending Migrations

If you skipped the previous migration, apply it now so the tasks table exists:

shell
uv run alembic upgrade head

Step 1: Implement the Filter Logic

Open src/release_tracker/crud.py and find list_tasks. The function signature, the selectinload option, and the return statement are in place. The body has a TODO for each filter:

src/release_tracker/crud.py
def list_tasks(
    session: Session,
    *,
    project_id: int | None = None,
    project_slug: str | None = None,
    task_status: TaskStatus | None = None,
    task_priority: TaskPriority | None = None,
    overdue_only: bool = False,
) -> list[Task]:
    statement = select(Task).options(selectinload(Task.project))  # type: ignore[arg-type]

    # TODO: Apply each filter to `statement` only when its argument was passed.
    #
    # - project_id: where Task.project_id == project_id
    # - project_slug: join Project, where Project.slug == project_slug
    # - task_status: where Task.status == task_status
    # - task_priority: where Task.priority == task_priority
    # - overdue_only: where Task.due_date is not None, Task.due_date < today,
    #   Task.status != TaskStatus.done
    #
    # Use `today = datetime.now(UTC).date()` for the overdue comparison.

    return list(session.exec(statement).all())

Two reminders from the filter composition setup:

  • statement = statement.where(...) (reassign each time) is the composition pattern.
  • The overdue predicate uses Task.due_date != None rather than is not None. The # noqa: E711 comment silences ruff for that one line.

Step 2: Write the Two Filter Tests

Open tests/test_tasks.py. Two test functions are stubbed at the bottom. Each one asks for fixtures (client, sample_project_id) and has a comment describing what to set up and what to assert.

tests/test_tasks.py
def test_list_tasks_filter_by_status(
    client: TestClient, sample_project_id: int
):
    # TODO: Create two tasks (one with status="planned", one with status="done"),
    # then GET /tasks?status=done and assert that only the done task comes back.
    pass


def test_list_tasks_filter_by_project_slug(
    client: TestClient, sample_project_id: int
):
    # TODO: Create a second project, post one task to each project, then
    # GET /tasks?project_slug=<second-project-slug> and assert that only
    # the second project's task comes back.
    pass

Step 3: Run the Test Suite

shell
uv run pytest

All tests should pass: the project tests from earlier chapters, the seven task tests already provided, and the two you wrote.

Step 4: Try the API in /docs

Reseed the database with the updated scripts/seed.py (which also creates a few tasks), start the server, and open the docs UI:

shell
uv run python -m scripts.seed
uv run fastapi dev

python -m runs a module by name

python -m scripts.seed tells Python to find scripts/seed.py as a module and run it.

In the browser at http://localhost:8000/docs, try:

  • GET /tasks with no query parameters: every task in the database.
  • GET /tasks?status=in_progress: only the in-progress task.
  • GET /tasks?overdue_only=true: tasks whose due_date is in the past and aren't done yet. The seed script creates one matching task.
  • GET /tasks?project_slug=frontend-redesign: tasks attached to the frontend project.

Success

If pytest is green and the docs UI returns the expected subsets for each query, the filter chain in list_tasks is composing correctly and the route handler is passing through the parameters faithfully.

Troubleshooting Tips

  • All filters return every task:

    The if ... is not None: guards may be missing. Without them, the conditional clauses get skipped (or worse, applied with the default value).

  • ?project_slug= returns 0 results when tasks exist:

    Confirm the join is in place: statement = statement.join(Project).where(Project.slug == project_slug). Without .join(Project), SQLAlchemy doesn't know how Project.slug relates to Task.

  • ?overdue_only=true returns done tasks:

    The Task.status != TaskStatus.done predicate may be missing. The exercise page's bullet list is the full set of three predicates the overdue filter needs.

Compare with the Solution

shell
git diff solutions/ch09 -- src/ tests/