Using Databases from Applications

Organizing a Small Application

Learning Objectives

You can describe a small, maintainable structure for a web application.
You can explain when a shared base template becomes useful.
You can explain when a small query-helper module becomes useful.

After create, delete, and update all work, the next natural question is: how do we keep the application readable as it grows?

For small applications, a small amount of structure is enough.

A Lightweight Starting Structure

At the start, the structure can stay small:

app/
  main.py
  db.py
  templates/
    tags.html
    newest_tag.html
    tag_edit.html
migrations/
  001_schema.sql
  002_seed.sql
  003_tags.sql

That is a sensible starting point while the application is still small:

main.py holds the routes,
db.py holds the connection helper,
templates/ holds the HTML views,
and migrations/ holds schema and seed scripts.

A Shared Base Template

Once the application has more than one visible page, the first thing that usually becomes repetitive is the HTML boilerplate. Each page so far has carried its own <!doctype html>, <head>, and top-level <h1>Study Tracker</h1>.

At that point, it often helps to move the common page shell into a shared base template.

For example, app/templates/base.html might look like this:

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

    <nav>
      <a href="/tags">Tags</a>
      <a href="/tags/newest">Newest tag</a>
    </nav>

    {% block content %}{% endblock %}
  </body>
</html>

This is a complete template file, and it now owns the shared HTML boilerplate.

Then a page such as app/templates/tags.html can focus only on its own content:

{% extends "base.html" %}

{% block content %}
  <h2>Tags</h2>
  <ul>
    {% for tag in tags %}
      <li>
        <strong>{{ tag.name }}</strong>
        <a href="/tags/{{ tag.id }}/edit">Edit</a>
        <form method="post" action="/tags/{{ tag.id }}/delete">
          <button type="submit">Delete</button>
        </form>
      </li>
    {% endfor %}
  </ul>

  <h2>Create a Tag</h2>
  <form method="post" action="/tags">
    <label for="name">Tag Name</label>
    <input id="name" name="name" type="text" />

    <button type="submit">Create</button>
  </form>
{% endblock %}

This child template is intentionally not a full HTML document. The shared boilerplate now lives in base.html.

The same idea works for newest_tag.html and tag_edit.html: each of them becomes a short file that extends base.html and fills in only its own content block.

The {% extends "base.html" %} line tells Jinja to start from the shared layout, and the {% block content %} section fills in the page-specific area defined in base.html.

The Jinja documentation on template inheritance gives more background on extends and blocks.

This keeps navigation and the common HTML shell in one place. If the heading or navigation later changes, only base.html needs to be updated.

The navigation should still stay practical. Stable top-level pages such as /tags and /tags/newest belong there more naturally than action pages such as /tags/3/edit or delete endpoints. Context-specific pages can use local links such as “Back to tags” instead.

Loading Exercise...

A Small Query Helper

After the shared template is in place, the next question is whether every route should still contain all of its SQL directly.

At first, keeping SQL in the route is fine. But as the file grows, one large main.py becomes harder to scan.

That is the moment to extract clearly related query code into a small helper module. For example:

app/
  main.py
  db.py
  queries/
    tags.py
  templates/
    base.html
    tags.html
    newest_tag.html
    tag_edit.html

In a small course project, a good pattern is:

the route still owns the request and the response,
the route still opens the database connection,
and a helper function only contains the SQL that should be run.

That keeps the request flow visible.

For example, a route might begin like this:

@app.get("/tags")
def list_tags(request: Request):
    with get_connection() as conn:
        tags = conn.execute(
            "SELECT id, name FROM tags ORDER BY name"
        ).fetchall()

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

After a small reorganization, the same idea could look like this:

# app/queries/tags.py
def fetch_all_tags(conn):
    return conn.execute(
        "SELECT id, name FROM tags ORDER BY name"
    ).fetchall()

In app/main.py, the helper is then imported from that file:

from .queries.tags import fetch_all_tags

Because both files are inside the same app package, main.py can import the helper from app/queries/tags.py in this way.

# app/main.py
@app.get("/tags")
def list_tags(request: Request):
    with get_connection() as conn:
        tags = fetch_all_tags(conn)

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

This is a useful compromise:

the route still shows when the query happens,
the SQL still stays visible,
and the repeated query details have one clear home.

For this course, that is usually a better fit than hiding the whole connection-and-query flow inside one large helper. Keeping the connection scope in the route makes the request flow easier to follow.

The important idea is that the application behavior did not change. Only the organization changed.

Loading Exercise...

Check Your Understanding

Why is a shared base.html useful once the application has several visible pages?
Why is it often useful for the route to keep owning the database connection even if a query helper is added?
Why is visible SQL still helpful in a small course project?

Programming Exercise

This chapter’s programming exercise builds on the earlier chapters. It asks you to add a shared layout and a query-helper module for the tags, and then to adjust the existing templates and routes to use those new files.

Loading Exercise...

← Updating Data in Applications

Project Checkpoint →