A Real Database Behind the Service

Context

A database server is a program of its own that owns the data files and answers SQL from any program that connects to it over a port with a user name and a password. Here PostgreSQL runs in its own container beside the service, the service finds it through a connection string read from an environment variable, and every price the service returns is written as one row: the listing it was sent, the price it answered and the moment it answered.

That table is where every later question about a deployed model starts: which listings it priced yesterday, whether they still look like its training rows, what it said about the listing a seller now disputes. An analyst reads it with psql while the service writes it, a nightly job exports it for retraining, and a replacement container carries on where the last one stopped, because the rows live in the server and in none of its clients.

Lesson 6.3.1's container answers a request and keeps nothing of it, so the first question about a deployed model, what did it say, has no answer. A database adds two failures of its own: the connection string that works from your terminal fails inside the container, where localhost is the container itself and the service refuses to start, and a container started without the variable looks healthy, prices every listing and writes nothing down.

Goal

• Run PostgreSQL 18.6 in its own container on a user-defined Docker network, read its startup log, and query it with psql through docker exec.
• Connect with psycopg from a DATABASE_URL connection string, and name the part of the string behind each connection error.
• Build db.py, which opens one autocommit connection, creates the predictions table with the listing as jsonb, and logs each price, and show what a connection without autocommit loses.
• Log every prediction the service returns, read the rows back with psql, and test the write with a test that skips when no database is set.
• Diagnose the Connection refused that localhost causes inside a container, and fix it with the database container's name on the network.

Prerequisites

None of this is required, and the lesson stands on its own. It is here to see containers find each other by name, or read the PostgreSQL image's settings, first.

• YouTube - "Docker networking is CRAZY!! (you NEED to learn it)" by NetworkChuck. The first two networks of the tour, the default bridge from 3:33 and the user-defined bridge from 10:44, where containers reach each other by name.
• docker-library documentation - "postgres", Environment Variables. POSTGRES_PASSWORD, POSTGRES_USER and POSTGRES_DB, which take effect only on an empty data directory, the trust rule for connections from inside the container, and PGDATA, whose location changed in PostgreSQL 18.

Table of Contents

• PostgreSQL in Its Own Container
• The Connection String and psycopg
• db.py, One Connection and a jsonb Table
• Every Prediction Written
• A Database Test That Skips
• localhost Inside a Container

Theory

Everything runs in ~/dev/listings-service as Lesson 6.3.1 left it, with (bootcamp) deactivated: the Dockerfile that copies the project into python:3.12.8-slim and runs uv sync --locked --no-dev, the champion exported to model/, and app.py loading model by default. The lesson adds psycopg[binary] to pyproject.toml and uv.lock, creates src/listings/db.py and tests/test_db.py and changes src/listings/app.py. Probes live in ~/dev/listings-probes, run as uv run python ../listings-probes/<name>.py, the service runs in Terminal 1 and every other command in Terminal 2. IDs, PIDs, timestamps and timings differ on every run.

PostgreSQL in Its Own Container

What. PostgreSQL is a database server: a process that keeps its data files in a directory it owns and answers SQL from any client that connects over TCP, on port 5432 by default, with a user name and a password. The official image postgres:18.6 runs one such server in a container, and a user-defined Docker network, one you create by name, lets every container attached to it reach the others by their container names. Why. Lesson 2.1.1's SQLite has no server: the engine is a library inside each program that opens the file, which suits one script and one file on one disk. A service container is replaced on every deploy, and its log has to outlive it and be readable by psql, a notebook and the next container while it is written. A server holds the rows in one place, and each of those programs is only a client. When. Whenever more than one process reads or writes the same data at once, or the process that writes it is a container that will be replaced.

A network first, then the server on it, in Terminal 2:

docker network create listings-net
docker run -d --name listings-db --network listings-net -p 5432:5432 -e POSTGRES_USER=listings -e POSTGRES_PASSWORD=listings -e POSTGRES_DB=listings postgres:18.6

Each command prints a long hexadecimal ID, and the first docker run of postgres:18.6 downloads the image, 650 MB on disk. docker network create makes a bridge network, a private network inside Docker that containers join with --network. -e NAME=value sets an environment variable inside the container, and the image reads these three on its first start to create the superuser listings with the password listings and a database listings owned by it. -p 5432:5432 publishes the server's port on your machine, so programs outside Docker reach it at localhost:5432.

The first start initializes an empty data directory, which takes a second or two. Three lines from the end of docker logs listings-db say it is ready:

PostgreSQL init process complete; ready for start up.

2026-11-03 14:19:31.863 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-11-03 14:19:31.893 UTC [1] LOG:  database system is ready to accept connections

Above them, a temporary server created the listings database and printed a ready to accept connections of its own, so the line to wait for is the one after init process complete. The real server listens on 0.0.0.0, every address the container has, as Lesson 6.3.1's Uvicorn did. psql, PostgreSQL's command-line client, ships inside the image, and docker exec runs it there:

docker exec listings-db psql -U listings -d listings -c "SELECT current_user, current_database(), version()"

 current_user | current_database |                                                      version                                                       
--------------+------------------+--------------------------------------------------------------------------------------------------------------------
 listings     | listings         | PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)

-U names the user, -d the database and -c one command to run before psql exits. It asked for no password, because the image trusts every connection made from inside the container itself, and the last rule of its pg_hba.conf, the server's file of who may connect how, host all all all scram-sha-256, demands the password from every other address, your machine and the other containers included.

The data directory is not in the image. docker inspect reads a container's configuration:

docker inspect listings-db --format '{{range .Mounts}}{{.Type}} {{.Destination}}{{end}}'

volume /var/lib/postgresql

A volume is storage Docker keeps outside the container's own files, mounted at a path inside it. The image declares /var/lib/postgresql as one, so docker run created an anonymous volume, one with a generated name, and PostgreSQL 18 keeps its files in 18/docker under it, the path the variable PGDATA holds. A volume survives docker stop and docker rm and goes with the container only under docker rm -v. PostgreSQL 17 and earlier used /var/lib/postgresql/data, and guides written before 18 still mount a volume there, here a named volume, one called by a name of yours:

docker run --rm -e POSTGRES_PASSWORD=listings -v listings-pgdata:/var/lib/postgresql/data postgres:18.6

Its message opens with Error: in 18+, these Docker images are configured to store database data in a, goes on about major-version-specific directory names, and near its end says:

       The suggested container configuration for 18+ is to place a single mount
       at /var/lib/postgresql which will then place PostgreSQL data in a
       subdirectory, allowing usage of "pg_upgrade --link" without mount point
       boundary issues.

The container exits with code 1 before any server starts, and --rm removes it. -v name:/path mounts a named volume, creating it when missing, and this one stays behind until docker volume rm listings-pgdata.

DON'T:

• DON'T leave --network off either container. On Docker's default bridge network the service, given the database's name, stopped at startup with failed to resolve host 'listings-db': [Errno -2] Name or service not known.
• DON'T mount a volume at /var/lib/postgresql/data for PostgreSQL 18. The server never started, and the error names /var/lib/postgresql as the place for a single mount.
• DON'T take a passwordless psql inside the container as proof the password works. Only a client outside the container is ever asked for it.

The Connection String and psycopg

What. psycopg is the Python client for PostgreSQL. psycopg.connect(url) opens one connection to a server from a connection string, a URL of the form postgresql://user:password@host:port/database, and conn.execute(sql, params) sends one statement and returns a cursor whose fetchone() and fetchall() read the result, the same reading Lesson 2.1.1 did with sqlite3. The string reaches the code in an environment variable, DATABASE_URL, so the same code connects to whichever server the variable names. Why. Connecting to a server is a network conversation with a login, where SQLite's connect only opens a file. Each part of the string can be wrong, and each wrong part fails at connect time with its own message, which tells you which part to fix. When. Every time a Python program talks to PostgreSQL, a service, a test, a probe or a nightly export.

psycopg is not installed yet. The binary extra brings psycopg-binary beside it, a compiled build that carries libpq, PostgreSQL's own client library, inside the wheel:

uv add "psycopg[binary]==3.3.6"

Resolved 100 packages in 3.90s
   Building listings @ file:///home/goku/dev/listings-service
      Built listings @ file:///home/goku/dev/listings-service
Downloading psycopg-binary (5.0MiB)
 Downloaded psycopg-binary
Prepared 3 packages in 2.28s
Uninstalled 1 package in 0.24ms
Installed 3 packages in 1ms
 ~ listings==0.1.0 (from file:///home/goku/dev/listings-service)
 + psycopg==3.3.6
 + psycopg-binary==3.3.6

The quotes keep the shell from reading the square brackets as a file pattern. The lock grows from 98 packages to 100, and pyproject.toml gains "psycopg[binary]==3.3.6" among its dependencies. A connection string of the server you just started, as a probe, ~/dev/listings-probes/connect.py:

import os

import psycopg

url = os.environ["DATABASE_URL"]
with psycopg.connect(url) as conn:
    print(conn.info.host, conn.info.port, conn.info.dbname, conn.info.user)
    print(conn.execute("SELECT version()").fetchone()[0])
    print(conn.execute("SELECT %s + %s, now()", (2, 3)).fetchone())

DATABASE_URL=postgresql://listings:listings@localhost:5432/listings uv run python ../listings-probes/connect.py

localhost 5432 listings listings
PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(5, datetime.datetime(2026, 11, 3, 14, 21, 59, 967970, tzinfo=zoneinfo.ZoneInfo(key='Etc/UTC')))

Read the URL left to right: the scheme, the user listings, a colon and the password, an @, the host localhost, a colon and the port 5432, and after the slash the database. os.environ["DATABASE_URL"] raises KeyError when the variable is unset, right for a probe that cannot work without it, and conn.info reports what the connection reached. psycopg's placeholder is %s where sqlite3 used ?, the values travelling beside the SQL as in Lesson 2.1.1, and now() comes back as a datetime in the server's time zone, UTC. with psycopg.connect(url) as conn: commits what the block changed and closes the connection, where Lesson 2.1.1's with con: committed and left it open.

Now three strings, each wrong in one part, in ~/dev/listings-probes/bad_urls.py:

import psycopg

for url in (
    "postgresql://listings:wrong@localhost:5432/listings",
    "postgresql://listings:listings@localhost:5432/listngs",
    "postgresql://listings:listings@localhost:5433/listings",
):
    try:
        psycopg.connect(url)
    except psycopg.OperationalError as err:
        print(err)

uv run python ../listings-probes/bad_urls.py

connection failed: connection to server at "127.0.0.1", port 5432 failed: FATAL:  password authentication failed for user "listings"
connection failed: connection to server at "127.0.0.1", port 5432 failed: FATAL:  database "listngs" does not exist
connection failed: connection to server at "127.0.0.1", port 5433 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?

psycopg.OperationalError is what psycopg raises when it cannot reach or log in to a server. The first two reached the server, and its answer starts with FATAL:, a refusal from PostgreSQL itself: the password, then the database name. The third reached no server at all, because nothing listens on 5433, and Connection refused comes from your machine's network stack, not from PostgreSQL. A misspelled database is a refusal here, where Lesson 2.1.1's sqlite3.connect("housng.db") created a new, empty file and failed only at the first query, with no such table.

DON'T:

• DON'T install psycopg without the binary extra on a machine without libpq. In a fresh environment on such a machine, import psycopg alone raised ImportError: no pq wrapper available, and the image's python:3.12.8-slim has no libpq either.
• DON'T write the URL into the code. The same db.py connects from your terminal and from a container, and the two need different hosts, which only the variable can give them.
• DON'T bring ? over from sqlite3. conn.execute("SELECT ? + ?", (2, 3)) raised ProgrammingError: the query has 0 placeholders but 2 parameters were passed.

db.py, One Connection and a jsonb Table

What. db.py is the service's whole contact with PostgreSQL: connect(url) opens one connection in autocommit mode, where every statement is committed the moment it succeeds, and makes sure the predictions table exists, and log_prediction(conn, listing, price) inserts one row and returns its id. The listing goes into one jsonb column, PostgreSQL's binary JSON type, as the dictionary the schema validated. Why. A service that writes one row per request needs each row to be durable the instant the request is answered, with no commit to forget, and a table that exists before the first request arrives. Storing the listing whole keeps exactly what the model was sent, nine fields with their blanks, in a form any later reader can take apart. When. Whenever a program logs events one at a time, each to survive on its own.

Save src/listings/db.py:

"""Every prediction the service returns, written to PostgreSQL."""

import psycopg
from psycopg.types.json import Jsonb

SCHEMA = """
CREATE TABLE IF NOT EXISTS predictions (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    listing jsonb NOT NULL,
    price double precision NOT NULL
)
"""


def connect(url):
    conn = psycopg.connect(url, autocommit=True)
    conn.execute(SCHEMA)
    return conn


def log_prediction(conn, listing, price):
    row = conn.execute(
        "INSERT INTO predictions (listing, price) VALUES (%s, %s) RETURNING id",
        (Jsonb(listing), price),
    ).fetchone()
    return row[0]

CREATE TABLE IF NOT EXISTS creates the table the first time and does nothing after, so connect can run at every start. id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY is a 64-bit integer the server assigns from its own counter and refuses from the client, an INSERT that names id failing with cannot insert a non-DEFAULT value into column "id", and PRIMARY KEY makes it unique and indexed. timestamptz is a timestamp with a time zone, filled by DEFAULT now() from the server's clock. jsonb stores JSON parsed into a binary form PostgreSQL can take apart field by field, and double precision is Python's float.

autocommit=True matters most. By default psycopg opens a transaction with the first statement and keeps it open until commit(), so what a connection writes stays invisible to every other connection and is thrown away if it closes first, and a service holding one connection for its whole life would never commit. Jsonb(listing) wraps the dictionary so psycopg sends it as JSON, and RETURNING id makes the INSERT hand back the id the server assigned, which fetchone()[0] reads.

DON'T:

• DON'T drop autocommit=True from a connection the service keeps open. A connection opened without it saw its own new row while psql saw none, and the row was gone once it closed, Lesson 2.1.1's forgotten commit() at the scale of a service.
• DON'T pass the dictionary bare. conn.execute(insert, ({"rooms": 3}, 1.0)) raised ProgrammingError: cannot adapt type 'dict' using placeholder '%s' (format: AUTO), and Jsonb is the wrapper that adapts it.
• DON'T split the listing into nine columns while Listing still changes. A field added to the schema arrives in jsonb as one more key, with no change to the table.

Every Prediction Written

What. The service opens the connection in its lifespan when DATABASE_URL is set, keeps it on app.state.db beside the model, and the route writes one row for every price it returns. Without the variable the service runs as Lesson 6.3.1 built it and writes nothing. Why. The connection is opened once, at startup, for the reason Lesson 6.2.4 loaded the model there: an unreachable server stops the service before it pretends to be up, and no request pays for a connection of its own. Making the database optional keeps the app's tests and a quick local run free of a server. When. For every prediction a service returns to a client, written before the response leaves.

src/listings/app.py becomes:

"""The price service: one listing in, one price out, every price logged."""

import os
from contextlib import asynccontextmanager

import mlflow
from fastapi import FastAPI, Request

from listings import db
from listings.data import to_frame
from listings.schema import Listing, Quote

DEFAULT_MODEL_URI = "model"


@asynccontextmanager
async def lifespan(app):
    uri = os.environ.get("MODEL_URI", DEFAULT_MODEL_URI)
    app.state.model = mlflow.pyfunc.load_model(uri)
    url = os.environ.get("DATABASE_URL")
    app.state.db = db.connect(url) if url else None
    yield
    if app.state.db is not None:
        app.state.db.close()


app = FastAPI(title="listings-service", lifespan=lifespan)


@app.get("/health")
def health():
    return {"status": "ok"}


@app.post("/predict")
def predict(listing: Listing, request: Request) -> Quote:
    row = listing.model_dump()
    price = round(request.app.state.model.predict(to_frame([row]))[0])
    if request.app.state.db is not None:
        db.log_prediction(request.app.state.db, row, price)
    return Quote(price=price)

The lifespan reads DATABASE_URL with os.environ.get, which returns None when it is unset, connects when it is set and stores None otherwise, and after yield, at shutdown, closes the connection. The route keeps the validated dictionary as row, rounds the price once, and logs both, so the table holds exactly the number the client received. Every request shares the one connection: FastAPI runs a plain def route in a pool of threads, psycopg's documentation calls a connection thread-safe, usable by several threads through a cursor each, which conn.execute makes, and fifty requests sent at once all came back 200 and wrote fifty rows.

Start it in Terminal 1, with the variable set for that one command:

DATABASE_URL=postgresql://listings:listings@localhost:5432/listings uv run uvicorn listings.app:app

INFO:     Started server process [64792]
INFO:     Waiting for application startup.
INFO:     Application startup complete.
INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)

The lifespan connected and created the table before Application startup complete.. In Terminal 2, Lesson 6.2.4's reference listing, its blank version, and zero rooms:

curl -X POST http://127.0.0.1:8000/predict -H "Content-Type: application/json" -d '{"area_sqm": 85.0, "year_built": 1998, "distance_km": 3.0, "rooms": 3, "bathrooms": 1, "neighborhood": "Riverside", "property_type": "apartment", "energy_rating": "C", "condition": "good"}'; echo
curl -X POST http://127.0.0.1:8000/predict -H "Content-Type: application/json" -d '{"distance_km": 3.0, "rooms": 3, "bathrooms": 1, "neighborhood": "Riverside", "property_type": "apartment", "condition": "good"}'; echo
curl -s -o /dev/null -w "%{http_code}\n" -X POST http://127.0.0.1:8000/predict -H "Content-Type: application/json" -d '{"rooms": 0}'

{"price":384235}
{"price":434348}
422

; echo adds the newline the body lacks, and -s -o /dev/null -w "%{http_code}\n" discards the body and prints the status. The prices are Lesson 6.2.4's. The table, read by psql while the service holds its connection:

docker exec listings-db psql -U listings -d listings -c "SELECT id, created_at, price FROM predictions"

 id |          created_at           | price  
----+-------------------------------+--------
  1 | 2026-11-03 14:22:26.205484+00 | 384235
  2 | 2026-11-03 14:22:26.22404+00  | 434348
(2 rows)

Three requests, two rows: the 422 was refused by validation before predict ran, so it reached neither the model nor the table. psql prints a whole double precision without its .0. The listing of row 2, as stored:

docker exec listings-db psql -U listings -d listings -c "SELECT listing FROM predictions WHERE id = 2"

                                                                                            listing                                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"rooms": 3, "area_sqm": null, "bathrooms": 1, "condition": "good", "year_built": null, "distance_km": 3.0, "neighborhood": "Riverside", "energy_rating": null, "property_type": "apartment"}
(1 row)

The request left out three fields, and the row holds all nine, because model_dump() gave the schema's defaults, None, which JSON writes as null. jsonb keeps no order of keys, and this server stored them shortest first, rooms before area_sqm. listing->>'area_sqm' takes one field out as text, and a JSON null comes out as SQL's NULL, which psql prints as nothing. Stop the service with Ctrl+C in Terminal 1, and the lifespan's shutdown closes its connection.

DON'T:

• DON'T count requests by counting rows. The 422 wrote nothing, so three requests left two rows.
• DON'T expect the request's key order back. jsonb stored rooms first, and a reader needing the training order builds it from the column names.
• DON'T compare ->> results as numbers. pg_typeof(listing->>'area_sqm') is text, and ->, with one >, keeps the value as jsonb.

A Database Test That Skips

What. tests/test_db.py writes one prediction through db.py, reads it back, deletes it and checks it came back unchanged. @pytest.mark.skipif(condition, reason=...) is a mark that skips a test when its condition is true, and here the condition is that DATABASE_URL is unset, so the suite runs on any machine and the database test runs wherever a database is named. Why. The write is the one piece of db.py the rest of the suite cannot see, because the app's tests run with no DATABASE_URL. A test that needs a server should say so by skipping, with its reason in the report, and never fail on a laptop with no PostgreSQL running. When. For every test that needs something outside the project, a server, a credential, a GPU, which some machines that run the suite will not have.

Save tests/test_db.py:

import os

import pytest

from listings import db

URL = os.environ.get("DATABASE_URL")
BLANK = {
    "area_sqm": None, "year_built": None, "distance_km": 3.0, "rooms": 3,
    "bathrooms": 1, "neighborhood": "Riverside", "property_type": "apartment",
    "energy_rating": None, "condition": "good",
}


@pytest.mark.skipif(URL is None, reason="DATABASE_URL is not set")
def test_prediction_is_stored():
    conn = db.connect(URL)
    row_id = db.log_prediction(conn, BLANK, 434348)
    stored = conn.execute(
        "SELECT listing, price FROM predictions WHERE id = %s", (row_id,)
    ).fetchone()
    conn.execute("DELETE FROM predictions WHERE id = %s", (row_id,))
    conn.close()
    assert stored == (BLANK, 434348.0)

URL is read once, when pytest imports the file, and the mark's condition is decided then. BLANK is the blank listing as the service logs it, and the test deletes its row before asserting, so a failed comparison still leaves the table as it found it. psycopg reads jsonb back as a dictionary and double precision as a float, and two dictionaries holding the same pairs are equal in any order, as 434348 == 434348.0 is True. Without the variable, -rs prints one line per skipped test with its reason, Lesson 6.1.3's -r report option with s for skips:

uv run pytest -rs

The collection, the per-file lines and the report's last three lines:

collected 27 items

tests/test_app.py ........                                               [ 29%]
tests/test_data.py ....                                                  [ 44%]
tests/test_db.py s                                                       [ 48%]
tests/test_features.py .....                                             [ 66%]
tests/test_model.py .........                                            [100%]

=========================== short test summary info ============================
SKIPPED [1] tests/test_db.py:15: DATABASE_URL is not set
================== 26 passed, 1 skipped, 2 warnings in 1.82s ===================

The s is the skip, and the summary names the file, the line of the mark and the reason. The two warnings are Lesson 6.2.4's. With the variable, the database test on its own, -v printing one line per test, and the end of its report:

DATABASE_URL=postgresql://listings:listings@localhost:5432/listings uv run pytest tests/test_db.py -v

tests/test_db.py::test_prediction_is_stored PASSED                       [100%]

============================== 1 passed in 0.07s ===============================

A psql query in the last subtitle's form, SELECT id, price FROM predictions ORDER BY id, still shows ids 1 and 2 alone. The test's row took id 3 and was deleted, and the identity never hands out a number twice, so the next row will be 4. Now the whole suite with the variable set, and its last line:

DATABASE_URL=postgresql://listings:listings@localhost:5432/listings uv run pytest

======================== 27 passed, 2 warnings in 1.60s ========================

The same query after it:

 id | price  
----+--------
  1 | 384235
  2 | 434348
  4 | 384235
  5 | 434348
(4 rows)

Rows 4 and 5 are not the database test's, which took 6 and deleted it. They are test_predict and test_blank_fields_are_priced from test_app.py: the variable was set for the whole run, the client fixture's lifespan read it like any other start of the app, connected, and the route logged both prices into the real table.

DON'T:

• DON'T read a green run as proof the database test ran. Only the s and -rs showed it never did.
• DON'T run the whole suite with DATABASE_URL set to a database whose rows matter. The app's tests wrote rows 4 and 5 into the service's table.
• DON'T expect a skip when the variable names a server that is down. Pointed at port 5433, the suite read 1 failed, 18 passed, 2 warnings, 8 errors, every app test erroring in its fixture with Connection refused.

localhost Inside a Container

What. A container has a network stack of its own, so localhost inside it is the container itself, not your machine and not any other container. On a user-defined network, Docker answers each container's name with that container's address, so the service in one container reaches the database in another at listings-db:5432. Why. Your terminal's string names localhost:5432 because -p published the database's port on your machine, and inside the service's container nothing listens there. The fix is in the variable, never in the code. When. Every time one container connects to another, a service to its database, a job to a service, a test runner to a server.

Port 8000 must be free, the port Lesson 6.3.1 found a server on your machine holding, so stop Terminal 1's service first if it still runs. The image needs the new code and psycopg, and COPY . . sits before uv sync in the Dockerfile, so the changed files make Docker run the whole sync again:

docker build -t listings-service .
docker images listings-service

IMAGE                     ID             DISK USAGE   CONTENT SIZE   EXTRA
listings-service:latest   11f05ec4b5b5       1.15GB          258MB        

The sync installed 90 packages, psycopg's two among them, in a build of about two and a half minutes here, and the image grew from Lesson 6.3.1's 1.12 GB and 253 MB to 1.15 GB and 258 MB. Run it on the network, in the foreground, with the URL that worked from your terminal:

docker run --rm --name listings-service --network listings-net -p 8000:8000 -e DATABASE_URL=postgresql://listings:listings@localhost:5432/listings listings-service; echo $?

INFO:     Started server process [1]
INFO:     Waiting for application startup.
ERROR:    Traceback (most recent call last):
  File "/app/.venv/lib/python3.12/site-packages/starlette/routing.py", line 656, in lifespan
    async with self.lifespan_context(app) as maybe_state:
               ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/contextlib.py", line 210, in __aenter__
    return await anext(self.gen)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/app/src/listings/app.py", line 21, in lifespan
    app.state.db = db.connect(url) if url else None
                   ^^^^^^^^^^^^^^^
  File "/app/src/listings/db.py", line 17, in connect
    conn = psycopg.connect(url, autocommit=True)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/app/.venv/lib/python3.12/site-packages/psycopg/connection.py", line 134, in connect
    raise new_ex.with_traceback(None)
psycopg.OperationalError: connection failed: connection to server at "127.0.0.1", port 5432 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
Multiple connection attempts failed. All failures were:
- host: 'localhost', port: '5432', hostaddr: '::1': connection failed: connection to server at "::1", port 5432 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
- host: 'localhost', port: '5432', hostaddr: '127.0.0.1': connection failed: connection to server at "127.0.0.1", port 5432 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?

ERROR:    Application startup failed. Exiting.
3

The paths are the container's. Line 21 of app.py called db.connect, psycopg tried both of localhost's addresses, 127.0.0.1, Lesson 0.2.3's loopback to the machine itself, and ::1, the same loopback in IPv6, and both were refused: the machine is now the container, and no PostgreSQL runs in it. The lifespan failed, uvicorn exited with code 3, and --rm removed the container. It is the refusal Lesson 6.2.4 showed for a missing alias, and right for the same reason: the service does not come up without its database. Inside listings-net, getent hosts listings-db run in a container printed the database's address, 172.18.0.2 in one run of this lesson and 172.22.0.2 in a later one on a network created again, so the URL names the container and Docker supplies the address. Run the service for real, in the background:

docker run -d --name listings-service --network listings-net -p 8000:8000 -e DATABASE_URL=postgresql://listings:listings@listings-db:5432/listings listings-service

The port inside the URL is 5432, the server's own port on the network, whatever -p published on your machine. /health answered 8 seconds after this command here, once docker logs listings-service showed Uvicorn running on http://0.0.0.0:8000. The reference listing and a five-room house in Hillcrest:

curl -X POST http://127.0.0.1:8000/predict -H "Content-Type: application/json" -d '{"area_sqm": 85.0, "year_built": 1998, "distance_km": 3.0, "rooms": 3, "bathrooms": 1, "neighborhood": "Riverside", "property_type": "apartment", "energy_rating": "C", "condition": "good"}'; echo
curl -X POST http://127.0.0.1:8000/predict -H "Content-Type: application/json" -d '{"area_sqm": 120.0, "year_built": 1975, "distance_km": 8.5, "rooms": 5, "bathrooms": 2, "neighborhood": "Hillcrest", "property_type": "house", "energy_rating": "E", "condition": "fair"}'; echo
docker exec listings-db psql -U listings -d listings -c "SELECT id, created_at, listing->>'neighborhood' AS neighborhood, price FROM predictions ORDER BY id"

{"price":384235}
{"price":277253}
 id |          created_at           | neighborhood | price  
----+-------------------------------+--------------+--------
  1 | 2026-11-03 14:22:26.205484+00 | Riverside    | 384235
  2 | 2026-11-03 14:22:26.22404+00  | Riverside    | 434348
  4 | 2026-11-03 14:22:58.482802+00 | Riverside    | 384235
  5 | 2026-11-03 14:22:58.577823+00 | Riverside    | 434348
  7 | 2026-11-03 14:26:47.167227+00 | Riverside    | 384235
  8 | 2026-11-03 14:26:47.189647+00 | Hillcrest    | 277253
(6 rows)

Rows 7 and 8 came from the container, after the uvicorn on your machine wrote 1 and 2 and the tests 4 and 5, all into one table: the rows belong to the server, and the service, the tests and psql are its clients. The container ran the same db.py as your terminal, and only the variable changed. Commit the lesson's five files:

git add pyproject.toml uv.lock src/listings/db.py src/listings/app.py tests/test_db.py
git commit -m "feat: log every prediction to PostgreSQL"

[main b7ca7be] feat: log every prediction to PostgreSQL
 5 files changed, 134 insertions(+), 4 deletions(-)
 create mode 100644 src/listings/db.py
 create mode 100644 tests/test_db.py

The hash is your commit's own. Then remove the two containers and the network, -v taking the database's anonymous volume with it:

docker rm -f listings-service
docker rm -f -v listings-db
docker network rm listings-net

Each prints the name it removed. The labs and the Assignment start what they need again.

DON'T:

• DON'T use localhost in a URL a container reads, for anything outside that container. The service stopped at startup with Connection refused on ::1 and 127.0.0.1, its own loopback.
• DON'T put the published host port into the container's URL. A variant given listings-db with another port than 5432 stopped with Connection refused at the database's own address, because -p opens a port on your machine and nothing on the network.
• DON'T remove the database with docker rm -f alone. Its anonymous volume stays behind under its generated name, used by nothing, until docker volume rm removes it.

Lab Exercises

Lab Exercise 1: Where the Rows Live

Save ~/dev/listings-probes/rows.py, which connects with db.connect(os.environ["DATABASE_URL"]), logs the reference listing at 384235 with db.log_prediction, and prints ids and the list of every id in predictions in order, or, when the connection fails with psycopg.OperationalError, prints no server: and the error's first line. Start the database with the Theory's docker run without --network, and run the probe once, then after each of these steps, waiting a second after each start:

1. docker stop listings-db
2. docker start listings-db
3. docker rm -f listings-db, then the same docker run, and then docker volume ls --filter dangling=true to list volumes no container uses
4. docker rm -f -v listings-db and docker volume rm of the old volume, then the same docker run with -v listings-pgdata:/var/lib/postgresql added
5. docker rm -f listings-db, then step 4's docker run, and docker logs listings-db searched for not properly shut down

Remove the container and listings-pgdata at the end. Say in a comment what survived each step, and why the last id is not the next number.

Lab Exercise 2: A Second Reader of the Log

Start the network, the database and the service container as the last subtitle did, and send four listings, the reference listing, its blank version, the Hillcrest house and the reference listing with zero rooms, printing each status after its body with -w " %{http_code}\n". Then write ~/dev/listings-probes/reprice.py, which reads every row's id, listing and price, prices the stored listings again in one call with mlflow.pyfunc.load_model("model") and Lesson 6.2.2's to_frame, and prints each id, the stored price, the new price and whether they are equal, then whether the first stored listing's keys are in FEATURES order, with its first three keys. Run it with your terminal's URL, then with the service's, listings-db as the host. Say in a comment why the prices match, why the key order does not matter, and why the second URL fails from your machine.

import os

import psycopg

from listings import db

LISTING = {
    "area_sqm": 85.0, "year_built": 1998, "distance_km": 3.0, "rooms": 3,
    "bathrooms": 1, "neighborhood": "Riverside", "property_type": "apartment",
    "energy_rating": "C", "condition": "good",
}

try:
    conn = db.connect(os.environ["DATABASE_URL"])
except psycopg.OperationalError as err:
    print("no server:", str(err).splitlines()[0])
else:
    db.log_prediction(conn, LISTING, 384235)
    ids = [row[0] for row in conn.execute("SELECT id FROM predictions ORDER BY id")]
    print("ids", ids)
    conn.close()
# stop and start: the container and its volume both stayed, ids [1, 2].
# rm -f and run: a new container got a new anonymous volume, ids [1],
# and the old volume was left dangling. The named volume outlived rm,
# but rm -f killed the server, and after recovery the identity resumed
# past the values it had logged ahead: 1, then 34.

docker run -d --name listings-db -p 5432:5432 -e POSTGRES_USER=listings -e POSTGRES_PASSWORD=listings -e POSTGRES_DB=listings postgres:18.6
docker stop listings-db
docker start listings-db
docker rm -f listings-db
docker run -d --name listings-db -p 5432:5432 -e POSTGRES_USER=listings -e POSTGRES_PASSWORD=listings -e POSTGRES_DB=listings postgres:18.6
docker volume ls --filter dangling=true

ids [1]
no server: connection failed: connection to server at "127.0.0.1", port 5432 failed: Connection refused
ids [1, 2]
ids [1]
DRIVER    VOLUME NAME
local     4c704c8a026c99050a7d592744fd8dbd751e0698fb76afb410281e780a19c299

docker rm -f -v listings-db
docker volume rm 4c704c8a026c99050a7d592744fd8dbd751e0698fb76afb410281e780a19c299
docker run -d --name listings-db -p 5432:5432 -e POSTGRES_USER=listings -e POSTGRES_PASSWORD=listings -e POSTGRES_DB=listings -v listings-pgdata:/var/lib/postgresql postgres:18.6
docker rm -f listings-db
docker run -d --name listings-db -p 5432:5432 -e POSTGRES_USER=listings -e POSTGRES_PASSWORD=listings -e POSTGRES_DB=listings -v listings-pgdata:/var/lib/postgresql postgres:18.6
docker logs listings-db 2>&1 | grep "not properly shut down"

ids [1]
ids [1, 34]
2026-11-03 14:47:36.665 UTC [32] LOG:  database system was not properly shut down; automatic recovery in progress

docker network create listings-net
docker run -d --name listings-db --network listings-net -p 5432:5432 -e POSTGRES_USER=listings -e POSTGRES_PASSWORD=listings -e POSTGRES_DB=listings postgres:18.6
docker run -d --name listings-service --network listings-net -p 8000:8000 -e DATABASE_URL=postgresql://listings:listings@listings-db:5432/listings listings-service

for body in '{"area_sqm": 85.0, "year_built": 1998, "distance_km": 3.0, "rooms": 3, "bathrooms": 1, "neighborhood": "Riverside", "property_type": "apartment", "energy_rating": "C", "condition": "good"}' '{"distance_km": 3.0, "rooms": 3, "bathrooms": 1, "neighborhood": "Riverside", "property_type": "apartment", "condition": "good"}' '{"area_sqm": 120.0, "year_built": 1975, "distance_km": 8.5, "rooms": 5, "bathrooms": 2, "neighborhood": "Hillcrest", "property_type": "house", "energy_rating": "E", "condition": "fair"}' '{"area_sqm": 85.0, "distance_km": 3.0, "rooms": 0, "bathrooms": 1, "neighborhood": "Riverside", "property_type": "apartment", "condition": "good"}'; do curl -s -X POST http://127.0.0.1:8000/predict -H "Content-Type: application/json" -d "$body" -w " %{http_code}\n"; done

{"price":384235} 200
{"price":434348} 200
{"price":277253} 200
{"detail":[{"type":"greater_than_equal","loc":["body","rooms"],"msg":"Input should be greater than or equal to 1","input":0,"ctx":{"ge":1}}]} 422

import os

import mlflow
import psycopg

from listings.data import FEATURES, to_frame

with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    rows = conn.execute("SELECT id, listing, price FROM predictions ORDER BY id").fetchall()

model = mlflow.pyfunc.load_model("model")
ids, listings, stored = zip(*rows)
again = model.predict(to_frame(list(listings)))
for row_id, price, fresh in zip(ids, stored, again):
    print(row_id, price, round(fresh), price == round(fresh))
print(list(listings[0]) == FEATURES, list(listings[0])[:3])
# The prices match: the host's model/ is the folder the image copied, and
# to_frame builds the frame by FEATURES, so jsonb's key order never
# reaches the model. listings-db is a name only Docker's network answers.

DATABASE_URL=postgresql://listings:listings@localhost:5432/listings uv run python ../listings-probes/reprice.py

1 384235.0 384235 True
2 434348.0 434348 True
3 277253.0 277253 True
False ['rooms', 'area_sqm', 'bathrooms']

DATABASE_URL=postgresql://listings:listings@listings-db:5432/listings uv run python ../listings-probes/reprice.py

psycopg.OperationalError: failed to resolve host 'listings-db': [Errno -2] Name or service not known

Theory Assessment

Q1: Two Misspelled Databases

A script calls sqlite3.connect("listngs.db"), and a second calls psycopg.connect("postgresql://listings:listings@localhost:5432/listngs"), both meaning a database called listings. Say what each call does, where each one's data lives, and which program in each case holds the data while the script runs.

Hint: One engine is a library inside the script and creates what it does not find. The other is a process the script logs in to.

Q2: A Laptop That Already Runs PostgreSQL

A teammate's laptop already runs a PostgreSQL of its own on port 5432, so they start listings-db with -p 5433:5432 and change nothing else. Write the DATABASE_URL for the tests run on the laptop and the one for the service container, and say what the service does if it is given listings-db:5433, and what it does if it is given localhost:5433.

Hint: -p host:container opens the first port on the laptop and forwards it to the second inside the container. Ask which side of that mapping each client is on.

Q3: The Missing Ids

After the Theory's runs the table held ids 1, 2, 4, 5, 7 and 8. Say what took ids 3 and 6, why they are missing, why rows 4 and 5 exist at all, and why test_prediction_is_stored passed although the stored listing's keys came back in another order and its price as 434348.0.

Hint: An identity hands out each number once, whatever happens to the row. Ask which tests read DATABASE_URL, directly or through the app's lifespan.

Q4: Healthy, Priced and Silent

A teammate starts the service container and forgets -e DATABASE_URL=.... Say what /health and /predict answer, what the table holds afterwards, which line of app.py decides it, why this is worse than the localhost mistake, and why uv run pytest passes either way.

Hint: os.environ.get returns None for an unset variable, and the lifespan treats None as a decision.

Assignment

Assignment: The Test Set, Logged and Exported

A retraining job wants the log as a file it reads like listings.csv. Write ~/dev/listings-probes/export_log.py, run against the listings-db Lab Exercise 2 left running, which logs the 600 test listings of split(load()) exactly as the service would, exports the rows it logged to ~/dev/listings-probes/logged.csv with the nine features as columns, and checks the file against the test set.

First log one test listing with a blank area_sqm straight from X_test.to_dict("records"), catch psycopg.errors.InvalidTextRepresentation and print the first two lines of its message. Then pass each test listing through Listing, leaving out its blank keys as Lesson 6.2.4's first lab did, price all 600 in one predict call on to_frame, and log each with db.log_prediction, timing the inserts. Remember the largest id before logging, so the export takes only this run's rows.

Two things the lesson did not use do the export. jsonb_to_record(listing) AS r(area_sqm float8, ...) in a query's FROM turns each row's jsonb into a record with the columns and types you declare, so SELECT id, r.*, price FROM predictions, jsonb_to_record(listing) AS r(...) returns one column per field. COPY (query) TO STDOUT WITH (FORMAT csv, HEADER) makes the server stream a query's result as CSV, which psycopg reads with cursor.copy(sql, params), a context manager whose iteration yields blocks of bytes.

Read the file back with pandas and print its shape, first three column names and the starting id, the blank counts of its features beside those of X_test, whether its nine features equals X_test with the index reset, and the mean absolute error of the logged prices against y_test. Say in a comment what the NaN failure means and why the file reproduces the test set.

Hints:

• conn.cursor().copy(sql, params) takes %s placeholders like execute, and writing each block to a file opened in "wb" mode builds the CSV.
• pd.isna(r["area_sqm"]) finds a blank area in a record, and {k: v for k, v in r.items() if pd.notna(v)} drops a record's blanks.

import os
import time

import mlflow
import pandas as pd
import psycopg

from listings import db
from listings.data import FEATURES, load, split, to_frame
from listings.schema import Listing

COLUMNS = ("area_sqm float8, year_built int, distance_km float8, rooms int, "
           "bathrooms int, neighborhood text, property_type text, "
           "energy_rating text, condition text")
EXPORT = f"""
    COPY (SELECT id, r.*, price
          FROM predictions, jsonb_to_record(listing) AS r({COLUMNS})
          WHERE id > %s ORDER BY id)
    TO STDOUT WITH (FORMAT csv, HEADER)
"""

_, X_test, _, y_test = split(load())
model = mlflow.pyfunc.load_model("model")
conn = db.connect(os.environ["DATABASE_URL"])

records = X_test.to_dict("records")
blank = next(r for r in records if pd.isna(r["area_sqm"]))
try:
    db.log_prediction(conn, blank, 1.0)
except psycopg.errors.InvalidTextRepresentation as err:
    print(str(err).splitlines()[:2])

start = conn.execute("SELECT coalesce(max(id), 0) FROM predictions").fetchone()[0]
rows = [Listing(**{k: v for k, v in r.items() if pd.notna(v)}).model_dump()
        for r in records]
prices = model.predict(to_frame(rows))
began = time.perf_counter()
for row, price in zip(rows, prices):
    db.log_prediction(conn, row, round(price))
print(f"{len(rows)} rows logged in {time.perf_counter() - began:.2f} s")

with open("../listings-probes/logged.csv", "wb") as f:
    with conn.cursor().copy(EXPORT, (start,)) as copy:
        for block in copy:
            f.write(block)
conn.close()

logged = pd.read_csv("../listings-probes/logged.csv")
print(logged.shape, list(logged.columns[:3]), start)
print(logged[FEATURES].isna().sum()[lambda s: s > 0].to_dict())
print(X_test.isna().sum()[lambda s: s > 0].to_dict())
print(logged[FEATURES].equals(X_test.reset_index(drop=True)))
print(f"{(logged['price'] - y_test.to_numpy()).abs().mean():,.0f}")
# NaN is pandas' blank and no JSON value, so the server refused the row.
# Through Listing a blank becomes None, JSON null, and comes back out of
# the CSV as NaN: the file holds the test set value for value, and its
# prices give the model's own test MAE.

DATABASE_URL=postgresql://listings:listings@localhost:5432/listings uv run python ../listings-probes/export_log.py

['invalid input syntax for type json', 'DETAIL:  Token "NaN" is invalid.']
600 rows logged in 2.00 s
(600, 11) ['id', 'area_sqm', 'year_built'] 3
{'area_sqm': 35, 'year_built': 30, 'energy_rating': 52}
{'area_sqm': 35, 'year_built': 30, 'energy_rating': 52}
True
56,562

Additional Resources

Optional, and nothing later depends on them. Worth reading to query logged listings inside PostgreSQL, or to group writes into one transaction.

• PostgreSQL documentation - "8.14.3. jsonb Containment and Existence". The @> operator, which asks whether one jsonb value contains another, and ?, which asks whether a key exists, for filtering a column of logged listings, with the GIN indexes the page's next section describes, which make them fast.
• psycopg documentation - "Transactions management", Transaction contexts. with conn.transaction(): on an autocommit connection, which wraps a group of statements in one transaction that commits when the block ends and rolls back on an exception.

Summary

The service gained one small file and a few lines, and with them a memory. Until now every price was gone the moment the response left. Now each answer is a row with a time on it, held by a process that belongs to none of the programs around it: the uvicorn on your machine, the tests, the container and psql all wrote or read one table, and the table did not care which of them it was talking to.

That independence is the reason for a server, and also where every failure in this lesson came from. Two processes that do not share a machine have to find each other, and the address right from one place is wrong from the next. A variable set for one test reached the whole suite, and a variable left out produced a service that looked perfect: mistakes about where something ran, not about what the code said.

The log is still only a record. It knows what the model said and nothing about whether it was right, and the rows a forgotten variable never wrote leave no gap to notice. Whatever watches the model from here reads this table, so its gaps and the names it is reached by are now part of what the service is.

Cheat Sheet

Part A - Reference Table

Part B - Compact Code Block

def connect(url):
    conn = psycopg.connect(url, autocommit=True)
    conn.execute(SCHEMA)          # CREATE TABLE IF NOT EXISTS
    return conn


def log_prediction(conn, listing, price):
    row = conn.execute(
        "INSERT INTO predictions (listing, price) VALUES (%s, %s) RETURNING id",
        (Jsonb(listing), price),  # dict to jsonb
    ).fetchone()
    return row[0]                 # the server's id

url = os.environ.get("DATABASE_URL")
# unset: the service runs and logs nothing
app.state.db = db.connect(url) if url else None

URL = os.environ.get("DATABASE_URL")
@pytest.mark.skipif(URL is None, reason="DATABASE_URL is not set")
def test_prediction_is_stored(): ...