Connecting Routes to Database Actions: the Architecture

What will we learn?#

Now we connect everything: the server from the backend and FastAPI topic, and the database from the databases topic. This lesson focuses on the architecture, that is, how the parts connect, and implements the first two Endpoints (reading). In the next lesson, the rest.

Why do we need this?#

In the backend and FastAPI topic the server kept students in RAM, and they disappeared on every restart. Now the server will save in the database: data that stays, with constraints and Transactions. This is the basic architecture of almost every Backend in the world: Client → API → Database.

Explanation from the basics#

Two files, two responsibilities#

students-api/
├── main.py     ← the API layer: routes, HTTP, validation, status codes
└── db.py       ← the data layer: SQL, connections, commit (from the databases topic)

MySQL in Docker (kodkod-mysql)  ← where the data lives

This is the “basic code separation between the Route and the database-access code” that the syllabus asks for. And why is it good?

• Every file is small and focused (SRP, the clean code topic).
• You test every layer on its own: we already tested db.py in the previous lesson, without a server.
• You replace one layer without touching the other: moving to PostgreSQL will change only db.py. A different interface (a command-line app) will use the same db.py.

The mapping table#

Every Route does three things: (1) receives and validates input (FastAPI + validation), (2) calls one function in db.py, (3) translates the result into HTTP.

One request, from end to end#

GET /students/2
   │
   ▼  Uvicorn receives the HTTP request (port 8000)
   ▼  FastAPI finds the route, converts "2" → 2 (int), calls get_student(2)
   ▼  main.get_student  →  db.get_student(2)
   ▼  db: open connection → SELECT ... WHERE id = %s (2) → fetchone → dict → close
   ▼  main: dict is not None → return it
   ▼  FastAPI: dict → JSON, status 200
   ▼  Uvicorn sends the HTTP response
{"id": 2, "name": "Avi", "age": 31, "email": "avi@mail.com"}

The whole course in one path: HTTP and Port (the topic on the computer, the operating system and the network), functions and dict and JSON (the Python topics), Classes and Pydantic (the OOP topic), SQL (the databases topic).

When is the table created?#

db.init_db() is called once, when the server starts (at the top level of main.py). Because of IF NOT EXISTS, it is safe on every start: the first time the table is created, and the other times nothing happens.

Syntax#

from fastapi import FastAPI, HTTPException

import db                                   # our data layer (same folder)

app = FastAPI(title="Students API")
db.init_db()                                # create the table once, on startup

@app.get("/students/{student_id}")
def get_student(student_id: int):
    student = db.get_student(student_id)    # 1 call to the data layer
    if student is None:                     # translate "not found" to HTTP
        raise HTTPException(status_code=404, detail="student not found")
    return student                          # dict → JSON (200)

Identical names in two layers: the function in main.py is called get_student, and so is the one in db.py. There is no collision, because you call the database one through the module’s name: db.get_student(...) (remember modules!).

First code example#

Setting up the project (the MySQL container needs to be running — docker start kodkod-mysql):

mkdir ~\dev\kodkod6\week-4\students-api
cd ~\dev\kodkod6\week-4\students-api
python -m venv .venv
.venv\Scripts\Activate.ps1
pip install "fastapi[standard]" mysql-connector-python
git init

.gitignore:

.venv/
__pycache__/
.env

Copy db.py from the databases topic (full CRUD) into the folder. And main.py, at this stage only two Endpoints:

from fastapi import FastAPI, HTTPException

import db

app = FastAPI(title="Students API")
db.init_db()

@app.get("/students")
def list_students():
    return db.get_all_students()

@app.get("/students/{student_id}")
def get_student(student_id: int):
    student = db.get_student(student_id)
    if student is None:
        raise HTTPException(status_code=404, detail="student not found")
    return student

Adding data for testing (the database is empty! There is no POST yet). seed.py:

import db

db.init_db()
for name, age, email in [("Dana", 24, "dana@mail.com"), ("Avi", 31, "avi@mail.com")]:
    try:
        print(db.create_student(name, age, email))
    except ValueError as error:
        print("Skipped:", error)

python seed.py
uvicorn main:app --reload

In the browser:

• http://127.0.0.1:8000/students
• http://127.0.0.1:8000/students/2
• http://127.0.0.1:8000/students/99

Responses:

[{"id":1,"name":"Dana","age":24,"email":"dana@mail.com"},{"id":2,"name":"Avi","age":31,"email":"avi@mail.com"}]

{"id":2,"name":"Avi","age":31,"email":"avi@mail.com"}

{"detail":"student not found"}

Breaking down the code line by line#

And now the important experiment: stop the server (Ctrl + C), and start it again. Refresh /students. The data is there! Unlike in the FastAPI topic, it is in the database — in the MySQL server in the container, which writes it to Storage (the computer topic!). You can also see it in Database Client.

Another example#

Another Endpoint, which uses db.py with a Query Parameter: searching. Add to db.py the search_students from the previous lesson’s practice, and to main.py:

@app.get("/students/search")
def search_students(name: str):
    if len(name.strip()) < 2:
        raise HTTPException(status_code=400, detail="search text must have at least 2 characters")
    return db.search_students(name)

Where in the file? /students/search is a fixed path, so it must come before /students/{student_id} (the FastAPI topic, mistake 3!). Otherwise search will be caught as a student_id → 422.

GET /students/search?name=av   → 200 [{"id": 2, "name": "Avi", ...}]
GET /students/search?name=a    → 400 {"detail": "search text must have at least 2 characters"}
GET /students/search           → 422 (name is required)

Notice the division of the work: the validation (at least 2 characters) is in main.py, because it is a rule of the API. The SQL (LIKE, lower) is in db.py.

Common mistake#

Mistake 1: SQL inside the Route.

@app.get("/students/{student_id}")
def get_student(student_id: int):
    connection = mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM students WHERE id = %s", (student_id,))
    ...

It “works”, but now there is SQL in two places, connection details in two places, and a Route that does everything. Route = input + a call to db + translation.

Mistake 2: returning None from the Route.

@app.get("/students/{student_id}")
def get_student(student_id: int):
    return db.get_student(student_id)

For a student that does not exist, 200 is returned with null. The Client will think it succeeded. “Not found” = 404.

Mistake 3: the server does not start — the MySQL container is not running. db.init_db() runs when Uvicorn loads main.py, so the server crashes already on startup:

mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'localhost:3306' ...

Check with docker ps, and start it: docker start kodkod-mysql (the databases topic!).

Mistake 4: ModuleNotFoundError: No module named 'db'. db.py is not in the same folder as main.py, or you run uvicorn from another folder. Run it from the project folder.

Practice#

The exercises use only what was taught in this lesson and the lessons before it. Try on your own first, and only then open the solution.

Exercise 1: Two layers#

For each line of code — does it belong to main.py (the HTTP layer) or to db.py (the data layer)?

1. cursor.execute("SELECT * FROM students WHERE id = %s", (student_id,))
2. raise HTTPException(status_code=404, detail="student not found")
3. @app.get("/students/{student_id}")
4. mysql.connector.connect(host="localhost", user="root", ...)
5. return db.get_student(student_id)

Exercise 2: Routes for reading#

You have db.py from the previous lesson (with init_db, get_all_students and get_student). Write a main.py with:

• db.init_db() when the server starts;
• GET /students — all the students;
• GET /students/{student_id} — one student, or 404 with "student not found".

from fastapi import FastAPI, HTTPException

import db

app = FastAPI(title="Students API")
db.init_db()


@app.get("/students")
def list_students():
    return db.get_all_students()


@app.get("/students/{student_id}")
def get_student(student_id: int):
    student = db.get_student(student_id)
    if student is None:
        raise HTTPException(status_code=404, detail="student not found")
    return student

Exercise 3: One request — from end to end#

Put in order what happens in the request GET /students/2:

• (a) db.get_student(2) runs SELECT ... WHERE id = %s with (2,)
• (b) FastAPI converts the "2" from the path to an int
• (c) The response comes back as JSON with 200
• (d) main.get_student(2) is called
• (e) The row comes back as a dictionary

GET /students/2
  -> FastAPI: student_id = 2 (int)
  -> main.get_student(2)
  -> db.get_student(2)
  -> SELECT ... WHERE id = %s   (2,)
  -> {'id': 2, 'name': 'Avi', ...}
  -> JSON, 200

Exercise 4: A search Endpoint#

Add to main.py the GET /students/search?name=..., which uses db.search_students. A name shorter than 2 characters → 400. Where should the Route be defined, and why?

@app.get("/students/search")
def search_students(name: str):
    if len(name.strip()) < 2:
        raise HTTPException(status_code=400, detail="too short")
    return db.search_students(name)

Exercise 5: What is wrong?#

@app.get("/students/{student_id}")
def get_student(student_id: int):
    connection = mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM students WHERE id = %s", (student_id,))
    return cursor.fetchone()

Find three problems in this code.

Summary#

• main.py = the API layer: Routes, validation and status codes. db.py = the data layer: SQL, connections and commit. No SQL in the Routes.
• Mapping: GET→SELECT, POST→INSERT (201), PUT→UPDATE, DELETE→DELETE (204). None/False → 404. ValueError → 409.
• Route = input → one call to db → translation into HTTP.
• db.init_db() once when the server starts (IF NOT EXISTS).
• Fixed paths (/students/search) before /students/{student_id}.
• The data survives a restart of the server, because it is in the database.

Official documentation#

• FastAPI — Bigger Applications
• FastAPI — Handling Errors