Skip to content

Exercise: Refactoring to CRUD

Bloated route handlers mix HTTP concerns with database logic.

In this exercise, you'll implement a new crud.py holding the database operations and review how to call those methods from main.py. The result: main.py handles HTTP routing and parameter injection only; crud.py handles all the database interactions.

Before You Start

Set up the starting state for this exercise:

shell
git fetch
git checkout -b my-ch06 --no-track origin/ch06
uv sync
Files that changed in this chapter:
  • src/release_tracker/crud.py (new, you'll fill in TODOs in create_project and update_project)
  • src/release_tracker/main.py (modified, refactored to call crud functions and use SessionDep)
  • tests/conftest.py (new, sets up an in-memory SQLite database for tests)
  • tests/test_api.py (new, test suite for CRUD operations)

Postgres still running?

If you want to test against the dev server while you refactor, confirm Postgres is up first:

shell
docker compose ps

The db service should show as healthy. If it isn't, start it with docker compose up -d.

Guidelines

  • slugify lives in crud.py.

  • CRUD functions own session.add(), session.commit(), and session.refresh().

    Routes don't touch transaction boundaries on the success path.

  • Routes own HTTP concerns: status codes and HTTPException.

  • Don't change API behavior.

    • Same status codes, same error messages, same response bodies.

Implement the CRUD Functions

Open src/release_tracker/crud.py. The slugify helper is provided. The five CRUD functions (list_projects, get_project, create_project, update_project, delete_project) all have TODO comments waiting for you to fill in.

src/release_tracker/crud.py
from sqlmodel import Session, select

from .models import Project, ProjectCreate, ProjectUpdate


def slugify(value: str) -> str:
    cleaned = "".join(c for c in value.lower() if c.isalnum() or c == " ")
    return "-".join(cleaned.split()) or "project"


def list_projects(session: Session) -> list[Project]:
    # TODO: Build a select statement ordered by Project.name and return the list of all projects.
    pass


def get_project(session: Session, project_id: int) -> Project | None:
    # TODO: Return the Project for `project_id`, or None if it doesn't exist.
    pass


def create_project(session: Session, payload: ProjectCreate) -> Project:
    # TODO: Build a Project from the payload, derive its slug, add/commit/refresh, return it.
    pass


def update_project(
    session: Session, project: Project, payload: ProjectUpdate
) -> Project:
    # TODO: Apply payload to project (use exclude_unset=True so missing fields stay missing),
    # re-slugify if the name was set, then add/commit/refresh.
    pass


def delete_project(session: Session, project: Project) -> None:
    # TODO: Delete the project and commit.
    pass

How main.py Calls into crud.py

src/release_tracker/main.py is already refactored to use the CRUD layer. Each route handler parses the request, delegates to crud, and returns the result. For example, the POST /projects handler:

src/release_tracker/main.py
@app.post(
    "/projects", response_model=ProjectRead, status_code=status.HTTP_201_CREATED
)
def create_project(
    payload: ProjectCreate,
    session: SessionDep,
):
    return crud.create_project(session, payload)

The other handlers do the same thing for their HTTP verbs: pull the parameters, call into crud, return what crud returns. Open main.py to read all five.

Testing Your Refactor

Two test files ship in your branch:

  • tests/conftest.py sets up an in-memory SQLite database and overrides get_session so the routes use the test database instead of Postgres. The SQLite-specific arguments (check_same_thread=False and StaticPool) are required for in-memory databases shared across TestClient's thread pool. Running tests against Postgres is out of scope for this exercise.
  • tests/test_api.py contains the tests that exercise the endpoints: create, get, list, update, delete.

Running the Tests

Once you've finished implementing the two TODOs in crud.py, run the tests to verify your implementation preserves the API's behavior:

shell
uv run pytest
Issues running pytest?

pytest should already be in your dev dependencies.

Confirm with uv tree --dev.

If for some reason it's missing, add it: uv add --dev pytest.

Success

If all five tests pass, your refactor preserves the API's behavior end-to-end: create, read, update, and delete all work as before.

Compare with the Solution

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