# Connecting Routes to Database Actions: the Architecture<no value>

## 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

```text
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
```

| | `main.py` | `db.py` |
|---|---|---|
| Knows about | HTTP, Requests, Status Codes, JSON, Pydantic | SQL, mysql.connector, tables, commit |
| Does **not** know about | SQL, connections | HTTP, 404, FastAPI |
| The language of errors | `HTTPException(404)` | `None`, `False`, `ValueError` |

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

| Endpoint | Function in db.py | SQL | Success | Failures |
|---|---|---|---|---|
| `GET /students` | `get_all_students()` | SELECT | 200 + a list | — |
| `GET /students/{id}` | `get_student(id)` | SELECT WHERE id | 200 + a dict | `None` → **404** |
| `POST /students` | `create_student(...)` | INSERT | **201** + a dict | `ValueError` → **409**. Validation → **400** / **422** |
| `PUT /students/{id}` | `update_student(id, ...)` | UPDATE WHERE id | 200 + a dict | `None` → **404**. `ValueError` → **409** |
| `DELETE /students/{id}` | `delete_student(id)` | DELETE WHERE id | **204** | `False` → **404** |

**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

```text
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).

{{< box "analogy" "Analogy: a restaurant, again" >}}
**`main.py`** = the waiter: takes orders from the diners, checks that they make sense ("we don't have pizza on the menu"), passes them to the kitchen, and serves. **`db.py`** = the storekeeper in the kitchen: knows where every ingredient is, takes out and puts in. The waiter does **not** go into the storeroom, and the storekeeper does **not** talk to the diners. Each one in their own role.
{{< /box >}}

### 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

```python
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`):

```powershell
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`:

```text
.venv/
__pycache__/
.env
```

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

```python
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`:

```python
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)
```

```powershell
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:**

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

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

```json
{"detail":"student not found"}
```

## Breaking down the code line by line

| Code | Explanation |
|---|---|
| `import db` | Importing the data layer. `main.py` and `db.py` are in the same folder (the modules lesson). |
| `db.init_db()` | Runs once when Uvicorn loads `main.py`: the table is ready before the first request. |
| `def list_students(): return db.get_all_students()` | **A Route in one line.** The list (of dicts) becomes JSON. |
| `student_id: int` | FastAPI converts and checks (`/students/abc` → 422, the FastAPI topic). |
| `db.get_student(student_id)` | **One call** to the data layer. |
| `if student is None: raise HTTPException(404)` | **Translation**: the "not found" of `db.py` → the 404 of HTTP. |
| `seed.py` | **Seed** = starting data. A separate script, which **uses the same `db.py`**. Proof that the layer does not depend on the server. |

**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`:

```python
@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.

```text
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.**

```python
@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.**

```python
@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:

```text
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.

<!-- exercises:start — generated by scripts/intro6/sync_site_exercises.py; edit scripts/intro6/exercises/ -->
## 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)`

{{< solution >}}
1. **db.py** — SQL.
2. **main.py** — an HTTP code.
3. **main.py** — a Route.
4. **db.py** — the connection details for the database.
5. **main.py** — the server calls the data layer.

The rule: **main.py speaks HTTP, db.py speaks SQL**. Neither of them knows the details of the other.
{{< /solution >}}

### 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"`.

{{< solution >}}
**main.py**

```py
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
```

`db.get_student` returns `None` when there is no student — and **main.py translates** that into a 404. That is the whole role of the HTTP layer.
{{< /solution >}}

### 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

{{< solution >}}
The order: **(b), (d), (a), (e), (c)**

```text
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
```
{{< /solution >}}

### 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?

{{< solution >}}
**main.py** (in addition to the previous Routes)

```py
@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)
```

- `?name=av` → 200 with the list. `?name=a` → **400**. Without `name` → **422** (a required Query, because it has no default).
- Define `/students/search` **before** `/students/{student_id}` — otherwise FastAPI will try to convert `"search"` to an int and return 422.
{{< /solution >}}

### Exercise 5: What is wrong?

```text
@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.

{{< solution >}}
1. **SQL inside the Route** — mixing layers. If we change the table's structure, we will have to look for SQL in all the Routes. Its place is in `db.py`.
2. **The connection is not closed** — there is no `close()`, and certainly not in `finally`. On a server that runs all the time, this adds up.
3. **A student that does not exist comes back as 200 with `null`** — you need to check for `None` and return 404. And also, without `dictionary=True`, the student comes back as a list without field names, instead of a dictionary.

The fix: `return db.get_student(student_id)` with a check for `None` — as in the second exercise.
{{< /solution >}}

<!-- exercises:end -->

## 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.

<!-- docs:start — generated by scripts/intro6/site_docs.py; edit the links there -->

## Official documentation

- [FastAPI — Bigger Applications](https://fastapi.tiangolo.com/tutorial/bigger-applications/)
- [FastAPI — Handling Errors](https://fastapi.tiangolo.com/tutorial/handling-errors/)

<!-- docs:end -->