Skip to content

Read Endpoints

With our read/write schemas defined for Project and our database migrated, we can rip out the mock database in main.py and replace it with real database queries.

To query the database inside a FastAPI route handler, we need a database Session. We'll use FastAPI's dependency injection system to provide that session to our routes.

Injecting the Session

We already have a get_session() function in database.py. We can use FastAPI's Depends to inject it into our route handlers.

However, modern FastAPI strongly encourages using typing.Annotated to attach this dependency metadata directly to the type hint. This keeps our function signatures clean and makes the dependency highly reusable. Let's define a SessionDep type alias at the top of main.py.

While we're rewriting the top of the file, we'll also add a description to FastAPI(...)which will show up at the top of the /docs page.

src/release_tracker/main.py
from typing import Annotated

from fastapi import Depends, FastAPI, HTTPException, status
from sqlmodel import Session, select

from .database import get_session
from .models import Project, ProjectRead

app = FastAPI(
    title="Release Tracker API",
    description="An API for tracking project milestones and developer tasks.",
)

# Note: The mock_database has been deleted!

SessionDep = Annotated[Session, Depends(get_session)]

Warning

The mock-database tests target the mock_database we just removed, so they no longer pass. Delete tests/test_main.py now; we'll add a fresh test suite in during the exercise.

Implementing GET /projects

Our list_projects route needs to fetch all projects from the database. We use SQLModel's select statement and execute it via the injected session.

src/release_tracker/main.py
@app.get("/projects", response_model=list[ProjectRead])
def list_projects(session: SessionDep):
    statement = select(Project).order_by(Project.name)
    projects = session.exec(statement).all()
    return list(projects)

By specifying response_model=list[ProjectRead], FastAPI automatically takes the Project database models returned by session.exec(), validates them against our ProjectRead schema, and serializes them to JSON. We don't have to map the fields ourselves.

Implementing GET /projects/{project_id}

session.get(Model, primary_key) returns the matching row, or None if no row matches. We use that None return to raise an HTTP 404 in the route.

src/release_tracker/main.py
@app.get("/projects/{project_id}", response_model=ProjectRead)
def get_project(project_id: int, session: SessionDep):
    project = session.get(Project, project_id)
    if not project:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND, detail="Project not found"
        )
    return project

Queries That Throw Exceptions

While session.get(Model, pk) returns None, other query methods can potentially raise an Exception.

As your queries grow more complex, you may need to use select() and execute it with .one():

Python
project = session.exec(select(Project).where(Project.slug == slug)).one()

.one() raises sqlalchemy.exc.NoResultFound if the query returns zero rows, and sqlalchemy.exc.MultipleResultsFound if it returns more than one.

When using these methods, write unit tests to ensure your handler catches them and returns the right HTTP status code.

Try It Out

Before we hit the new endpoints, confirm Postgres is up and the table has seed data.

Run docker compose ps and verify the db service is healthy. If it isn't, start it with docker compose up -d.

Then visit http://127.0.0.1:8000/docs and try GET /projects.

With seed data in the database you'll see three rows. If the response is [], re-run scripts/seed.py with uv run python scripts/seed.py.

GET /projects/9999 returns a 404.

Our read endpoints are wired up to the database, now let's build the write endpoints.