Building a Backend

CS 374, Fall 2026

Warm-Up: What Status Code?

With your table, decide the status code for each request:

  1. GET /courses/625
  2. POST /courses with a new course, CS 375
  3. POST /courses with CS 374, which already exists
  4. POST /courses with no title
  5. DELETE /courses/625 for a course nobody refers to
  6. DELETE /courses/99999
  7. PATCH /courses/625 with {"credits": "four"}
  8. GET /courses?subject=XYZ
  9. GET /courses/625, but your code divides by zero

Choices: 200, 201, 204, 404, 409, 422, 500

CRUD, Four Ways

Operation HTTP SQL SQLModel Success
Create POST /courses INSERT session.add(course), then commit() 201
Read GET /courses/625 SELECT session.get(Course, 625) or exec(select(...)) 200
Update PATCH /courses/625 UPDATE change attributes, then commit() 200
Delete DELETE /courses/625 DELETE session.delete(course), then commit() 204
  • Every write ends with session.commit(), and that is where the database checks its constraints
  • After a create, session.refresh(course) loads the id the database chose

One Table, Several Models

Create: POST

class CourseCreate(SQLModel):            # not a table: the shape of the request body
    subject_code: str = Field(max_length=4)
    number: str = Field(max_length=5)
    title: str = Field(min_length=1)
    credits: Decimal = Field(default=Decimal("3.0"), ge=0, max_digits=3, decimal_places=1)


@app.post("/courses", status_code=201)
def create_course(data: CourseCreate, session: SessionDep) -> CoursePublic:
    course = Course.model_validate(data)  # request model -> table model
    session.add(course)
    commit_or_409(session, "That course already exists, or its subject does not")
    session.refresh(course)               # load the course_id the database chose
    return course                         # table model -> response model
  • A parameter whose type is a model class comes from the request body, as JSON
  • Field(max_length=..., ge=...) rules are checked before your function runs: 422

The Request Body on /docs

  • The example and the Schema tab come from CourseCreate: no course_id, a default for credits

Update: PATCH and exclude_unset

@app.patch("/courses/{course_id}")
def update_course(course_id: int, data: CourseUpdate, session: SessionDep) -> CoursePublic:
    course = get_course(session, course_id)                  # 404 if missing
    course.sqlmodel_update(data.model_dump(exclude_unset=True))
    commit_or_409(session, "The change breaks a constraint")
    session.refresh(course)
    return course

The client sends {"title": "Database Systems I"}:

Call Result Effect on the row
data.model_dump() {"title": "Database Systems I", "credits": None, "is_active": None} erases credits: wrong
data.model_dump(exclude_unset=True) {"title": "Database Systems I"} changes only the title

Delete: DELETE

@app.delete("/courses/{course_id}", status_code=204)
def delete_course(course_id: int, session: SessionDep) -> None:
    session.delete(get_course(session, course_id))
    commit_or_409(session, "Other rows refer to this course")
  • 204 means success with no body, so the function returns nothing
  • DELETE /courses/625 gets a 409: other teams' tables have FK's to CS 374
  • The database protects itself; your job is to turn its refusal into a clear answer
  • In HW6, Problem 7: delete an appointment's service_at_appt rows first

Where Each Error Comes From

Catching a Constraint Violation

def commit_or_409(session: SessionDep, detail: str) -> None:
    """Commit, or roll back and stop the request with a 409 if a constraint fails."""
    try:
        session.commit()
    except IntegrityError:
        session.rollback()
        raise HTTPException(status_code=409, detail=detail) from None
  • IntegrityError comes from sqlalchemy.exc: UNIQUE, FOREIGN KEY, NOT NULL, or CHECK
  • After a failed commit the session refuses to do anything until you roll back
  • Without the try, the client gets a 500 and learns nothing; the traceback is only in your terminal
  • Catch the specific exception, never a bare except: (ruff flags it)

What the Client Sees

POST /courses with CS 374, which already exists:

  • Undocumented means the decorator does not list 409; responses={409: {...}} would add it to /docs

Your Turn: HW6, Part 2

  1. Download hw6.py, vet.py, and vet.db from the HW6 page, outside your planner clone
  2. With your planner venv active: fastapi dev hw6.py
  3. Finish Problems 1--3 if you have not yet
  4. Start Problems 4--8: request and response models, POST, PATCH, DELETE

Each problem lists the requests the autograder sends and what it expects.
backend_demo.py in today's notes has every pattern from these slides, on the course table.

Coming Up: GP5

  • Your team's endpoints go in src/planner/<area>/router.py, as an APIRouter
  • Every path starts with your area: /advising/..., /plans/...
  • One app, planner.main, includes all six routers: one /docs page for the whole project
  • Your GP4 query functions become read endpoints; add writes for your own tables
  • Branches are cut Monday, Nov 16, after the GP4 merges; due Monday, Nov 30
  • Today, with your team: list endpoints your GP1 use cases need; who writes which?

Warm-Up: Answers

# Request Code Why
1 GET /courses/625 200 it worked
2 POST a new course 201 a row was created
3 POST CS 374 again 409 breaks the UNIQUE (subject_code, number) constraint
4 POST with no title 422 the body is missing a required field
5 DELETE /courses/625 204 it worked, and there is nothing to send back
6 DELETE /courses/99999 404 the path names a course that does not exist
7 PATCH with "four" 422 wrong type: not a decimal
8 GET ?subject=XYZ 200 an empty list is still an answer
9 divides by zero 500 an exception nobody caught