Case — a web API with a database

and why a 500 instead of a 409 is a bug

What it is

A continuation of the first case. The bot from article 14.1 acquires neighbours: a website and a mobile application. Keeping the city list in three places won't do, which means we need one API that everyone reads from.

The idea in one phrase: a service that stores a user's cities and serves them over HTTP.

Step 1. The mini-spec

The questions are the same (article 13.6), so here is only what differs from the bot:

From the "programs use it" row follows the main requirement: the responses have to be predictable. A program can't guess what "something went wrong" means — it needs a response code that tells it what to do.

Step 2. The request

An API on FastAPI, Python 3.12, storage in SQLite.

Endpoints:
GET    /cities        the list of cities
POST   /cities        add {name, temp}
DELETE /cities/{name} remove

Requirements:
- request and response schemas with pydantic, city name 1–60 characters
- all database access in one place
- response codes: 201 on creation, 204 on deletion,
  404 if there's nothing to delete, 409 if the city already exists
- no ORM, the queries are written by hand

The list of files and what's in each first. Code after "yes".

The line about response codes is the important one. Without it you get code that answers 200 to everything and reports errors as text inside the response.

Step 3. What arrived

The structure came out right, the schemas are in place, the queries are parameterized (that matters — substituting a string opens an SQL injection, article 7.8). The add handler:

@app.post("/cities", response_model=CityOut, status_code=201)
def add_city(city: CityIn):
    with closing(db()) as conn:
        conn.execute("INSERT INTO cities VALUES (?, ?)", (city.name, city.temp))
        conn.commit()
    return city

Start it and check by hand — exactly as article 4.7 advises: not only the happy path.

$ curl -X POST localhost:8102/cities -d '{"name":"Oslo","temp":4}'
201

$ curl -X POST localhost:8102/cities -d '{"name":"Oslo","temp":4}'
Internal Server Error
HTTP 500

The second time, a 500. The reason is in the logs:

sqlite3.IntegrityError: UNIQUE constraint failed: cities.name

What happened: in the table's schema name is declared the primary key — and rightly so, there shouldn't be duplicates (the lesson from article 14.1). But the handler doesn't know that, the exception flies out, and FastAPI turns it into a 500.

The fix is four lines:

        try:
            conn.execute("INSERT INTO cities VALUES (?, ?)", (city.name, city.temp))
        except sqlite3.IntegrityError:
            raise HTTPException(409, f"city {city.name} is already added")

Step 4. Acceptance

Check every case from the mini-spec one after another — the happy ones and the unhappy ones:

first POST  : 201
second POST : {"detail":"city Oslo is already added"} | HTTP 409
GET         : [{"name":"Oslo","temp":4.0}]
DELETE Oslo : 204
DELETE again: 404

And the data validation nobody wrote — it came from the schema:

$ curl -X POST localhost:8103/cities -d '{"name":"","temp":4}'
422

{"detail":[{"type":"string_too_short","loc":["body","name"],
 "msg":"String should have at least 1 character","ctx":{"min_length":1}}]}

The response names the field, names the rule, names the violation. That is the point of the schemas from article 7.3: the checks are written once, next to the description of the data, rather than in every handler.

The five lines of output above are five checks, each matching a line of the mini-spec. Such a run takes a minute and replaces the question "but does it actually work?".

Step 5. What is deliberately absent

No authentication. Anyone who knows the address can add a city. For a service on your own machine that is a conscious decision; for a service on the internet it is a hole. The decision is written in the mini-spec rather than forgotten, and that is the whole difference.

No pagination. There are dozens of cities. When there are thousands it will appear, and the sign was named in advance (article 13.3).

No ORM. There are five queries and all of them are simple. An ORM is another library, another layer and another source of baffling errors; at five queries it doesn't pay for itself. At fifty, the answer would be different.

Cheat sheet