Using Databases from Applications

The Walking Skeleton Setup

Learning Objectives

You can explain what the walking skeleton gives you at a practical level.
You can describe why tools such as Docker and Docker Compose are used in the course project.
You can explain why a new database-backed feature often begins with a new migration file.

The walking skeleton that we created in the first project checkpoint is a minimal working version of the project. It gives you a small web application, a PostgreSQL database, and a repeatable way to run them together.

Keep building on top of the same study tracker project throughout this part. The chapter examples add new functionality to the same broader project instead of starting from a blank application.

Project Setup

At a high level, the setup looks like this:

Figure 1 — The browser talks to the app, the app talks to PostgreSQL, and the project files define how those services behave.

There are two main running services in the setup: the FastAPI app and the PostgreSQL database.

The browser sends requests to the app, the app runs routes and SQL queries, the database stores the persistent data, and the project files define the app behavior and the starting database state.

Docker and Docker Compose

We use Docker and Docker Compose in the project setup.

Docker helps package each service into a repeatable environment, while Docker Compose helps start those services together with the expected configuration. Docker Compose also creates the network context that lets the application and database talk to each other.

The Docker documentation on Docker Compose and Compose networking gives more background on the functionality.

You can think of Docker and Docker Compose as tools that help run containers. A container is a lightweight, isolated environment that can run one service. The project uses two containers: one for the FastAPI app and one for the PostgreSQL database.

This is useful in a project because:

everyone can start from roughly the same setup,
the database and application use the expected versions and configuration,
and it is easier to focus on the database-backed feature instead of local machine differences.

You do not need deep container knowledge for this course. It is enough to be able to run things, understand what is running, which service does what, and where your code changes take effect.

Loading Exercise...

Project Files

When you work on this part, there are clear responsibilities for the files:

app/main.py contains the routes, which define the app’s behavior and how it responds to requests,
app/db.py contains the database connection helper, which defines how the app connects to the database and runs queries,
app/templates/ contains the HTML templates, which define how the app renders responses,
and migrations/ contains the SQL scripts that define the starting database state.

When you change a route, a template, or a SQL query, you are changing one piece of the running setup; these changes go to the specific files.

Adding the Tags Table

In this part, we will work on a table called tags that can later be used to label study content. The table is added to the existing project through one new migration file. In other words, we extend the current project state instead of replacing it.

To have the tags table available in the database, a new migration file is added in migrations/. That way, the later chapters can focus on routes, templates, and request flow instead of also introducing missing database state.

Create a file called 003_tags.sql in the migrations directory with the following content:

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO tags (name)
VALUES
  ('sql'),
  ('web'),
  ('docker');

The migration file (1) creates the tags table and (2) inserts three rows into it.

Reading the CREATE TABLE

The schema-defining part of the migration uses a few new pieces of syntax that we will study more carefully later in the course. For now, a short ordinary-language reading is enough:

CREATE TABLE tags ( ... ) defines a new table called tags.
id SERIAL PRIMARY KEY says that id is an integer column whose values are generated by the database, and that each id value uniquely identifies one row.
name TEXT NOT NULL says that name is a text column and that every row must have a value for it.
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP says that created_at is a timestamp column and that, if no value is provided, the database fills it in with the current time.

The INSERT statements then follow the pattern from the previous part: they name the target columns and provide values for them.

When you start the application using docker compose up --build, the database is created with the tags table and the three seeded rows. Migration files run in order, so the first two migrations create the decks table and seed it, and the third migration then creates the tags table and seeds it.

You can stop the application with Ctrl+C and start it again with docker compose up --build. If you want to reset the database state, you can run docker compose down -v to stop the application and remove the volumes, which deletes the database data.

Check Your Understanding

Which two services are the main running services in the walking skeleton?
Why is Docker Compose used in this project?
Which files should you focus on when adding a database-backed feature?

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

Selected style: choose a diagram style

Programming Exercise

The programming exercise of this chapter expects you to add the migration file and to add a small text to the page that shows the decks.

Loading Exercise...

← Overview

Using Databases from Web Applications →