Using Databases from Applications

Using Databases from Web Applications

---

A database is rarely used directly by end users. Instead, an application that uses the database through the database management system, such as a web application, sits between the user and the database. Most of the interaction happens through the application — through pages, forms, and links, if it’s a web application. In this chapter, we look at how one request becomes one database query and then one rendered response.

One Request, Many Small Steps

When a request arrives, it is handled by a route function. That route function may run one or more SQL queries against the database, and then it returns a response. The response is often created by rendering a template, which turns the query result into HTML.

At a high level, the request flow looks like this:

Figure 1 — A server-rendered database-backed request flows from the browser to the route, then to PostgreSQL, and back through a template into HTML.

This is the basic pattern for many server-rendered database-backed pages.

HTTP Requests and FastAPI

The term “request” in web applications usually refers to an HTTP request, and terms such as GET or POST refer to the HTTP method of that request. HTTP is the standard web protocol that browsers and web applications use to send requests and responses.

HTTP supports several request methods. In this course, we use two of them:

• GET is used to request a page or some content from the server.
• POST is used to send submitted data to the server so that something can be created, updated, or otherwise processed.

So when the browser asks for /tags, that is usually a GET request. Later, when a form is submitted to create or edit a tag, that is usually a POST request.

It is useful to notice that the browser and the route do not work at exactly the same level. The browser sends and receives raw HTTP messages. FastAPI sits in between and translates those messages into Python-friendly values on the way in, and back into HTTP responses on the way out.

Figure 2 — FastAPI translates HTTP-level requests and responses into Python-friendly route inputs and return values.

For example:

• a GET /tags request is matched to @app.get("/tags"),
• submitted form fields can become Form(...) parameters,
• a path such as /tags/3/edit can provide tag_id=3,
• and a TemplateResponse or RedirectResponse becomes an HTTP response that the browser understands.

You do not need to know the HTTP protocol in detail for this course. The important idea is that frameworks such as FastAPI remove a lot of repetitive parsing and formatting work, so routes can focus on the application logic.

Loading Exercise...

The Shape of a Route

Each route has a shape, which is a common structure that appears again and again in different features. It has a line that defines the mapping, a block that runs one or more queries, and a return statement that creates a response.

For example, a route for listing tags might look like this (the SQL is not important here, only the shape):

@app.get("/tags")
def list_tags(request: Request):
    with get_connection() as conn:
        tags = conn.execute("...").fetchall()

    return render(request, "tags.html", tags=tags)

The @app.get("/tags") line tells FastAPI that this function should handle GET requests to /tags. When such a request arrives, FastAPI calls this function and provides the Request object that the route needs.

The exact SQL matters, but the first idea to notice is the flow:

• the route receives the request,
• the route asks the database for rows,
• and the template turns those rows into HTML.

The final line uses two helpers that are already part of the project from the walking skeleton: get_connection() and render(). The rest of this chapter looks at what those helpers do and why they are kept small.

Loading Exercise...

Dividing Responsibility

Each route usually has at least three responsibilities:

• receive the request,
• decide what data is needed,
• return a response.

The route is not, however, responsible for every detail of those steps. For example, the route does not need to know how to connect to the database or how to render HTML. Instead, it can call a helper function to get a database connection and call a template to render the response.

A helpful way to think about routes is that they coordinate the feature rather than doing every job themselves.

The responsibility for connecting to the database is hidden in get_connection(), defined in app/db.py. The HTML templates used when rendering the responses live in app/templates/. The small render() helper that wraps the common template-response pattern is already defined in app/main.py and is used from every route in this part.

Database connection helper

The database connection helper in app/db.py looks as follows:

import os

import psycopg
from psycopg.rows import dict_row

def get_connection():
    return psycopg.connect(
        os.environ["DATABASE_URL"],
        row_factory=dict_row,
    )

The helper hides the repetitive work of opening a PostgreSQL connection with the correct environment variable and row format. The psycopg library abstracts away details of the PostgreSQL protocol and provides a Python API for running SQL queries.

The row_factory=dict_row part tells psycopg to return rows with column names as keys. This way, templates can access values such as tag.name instead of relying on indexes.

The psycopg documentation on row factories explains this in more detail.

HTML templates

The folder app/templates/ contains HTML templates, which define how the application renders responses. We use a template engine called Jinja. It allows the templates to include dynamic values such as {{ tag.name }} and control structures such as {% for tag in tags %}.

The values correspond to the context that the route passes into the template. For example, if the route calls render(request, "tags.html", tags=tags), then the template can use tags as a variable that contains the query result.

Loading Exercise...

A file called tags.html in app/templates/ might look like this:

<!doctype html>
<html lang="en">
  <head>
    <meta charset="utf-8" />
    <title>Study Tracker</title>
  </head>
  <body>
    <h1>Study Tracker</h1>
    <h2>Tags</h2>

    <ul>
      {% for tag in tags %}
        <li>{{ tag.name }}</li>
      {% endfor %}
    </ul>
  </body>
</html>

This is a full template file. It renders a list of tags by looping over the tags variable and showing the name of each tag in a list item. The template does not know how the tags variable was created or what SQL query produced it. It only focuses on presentation, not on database logic.

The FastAPI documentation on templates is also useful here because FastAPI uses Jinja templates through that helper layer.

Loading Exercise...

The render() helper

The walking skeleton already defines a small helper in app/main.py:

def render(request: Request, template_name: str, **context):
    return templates.TemplateResponse(
        request=request,
        name=template_name,
        context=context,
    )

render() wraps the TemplateResponse pattern so that routes can stay short. Instead of writing:

return templates.TemplateResponse(
    request=request,
    name="tags.html",
    context={"tags": tags},
)

a route can simply write:

return render(request, "tags.html", tags=tags)

The two versions do the same thing. Using render() consistently keeps the routes easier to scan, and the rest of this part uses it everywhere.

With these helpers in place, the full GET /tags route can stay short:

@app.get("/tags")
def list_tags(request: Request):
    with get_connection() as conn:
        tags = conn.execute("...").fetchall()

    return render(request, "tags.html", tags=tags)

The next chapter turns this same pattern into one complete read-only page.

Check Your Understanding

1. In the route shape above, which line actually talks to PostgreSQL?
2. Why is get_connection() useful even though it does not hide SQL?
3. Why is it useful that the template receives ready-made rows instead of opening the database itself?

AI Study Aid

Create a chapter diagram

We're looking into to what extent AI-generated diagrams could help with studying.

Use this study aid to generate an AI-generated visual summary of the material you just studied. Each diagram style emphasizes the content in a different way, so you can choose the focus that feels most useful.

Using the diagram generator is voluntary and does not affect your course progress. We encourage you to try it out and see if it helps, but it's totally up to you! Your answers help us understand how to make better study aids in the future.

Diagrams tried: 0

Generate diagram

Selected style: choose a diagram style

Programming Exercise

This chapter’s programming exercise is the first real /tags page in the guided tags practice track. It starts from the completed walking skeleton from the previous chapter and asks you to add a route and template that list the tags from the database with the newest tag first, based on the creation time.

Loading Exercise...