Setting up a Walking Skeleton

PostgreSQL Database and Migrations

---

In this chapter, we will add a PostgreSQL database to the walking skeleton, which allows persisting data between server restarts.

Environment file

First, create a file called project.env and place it to the same folder with the compose.yaml file. With the file in place, the folder structure of the project is as follows.

tree --dirsfirst
.
// ..
├── compose.yaml
└── project.env

Copy the following contents to the project.env file.

POSTGRES_USER=username
POSTGRES_PASSWORD=password
POSTGRES_DB=database

The file defines environment variables that are used to configure the database. PostgreSQL expects three variables: POSTGRES_USER, POSTGRES_PASSWORD, and POSTGRES_DB. Perhaps unsurprisingly, these are used to define the username, the password, and the database name.

In production, we would use more secure credentials.

Adding the database

As PostgreSQL is available as a Docker image, it can be added easily to the project. Let’s use database as the service name, and add a configuration container_name that allows us to easily find the container — we’ll call the container postgresql_database. For the database service, we define the image as postgres:17.5, and add a policy to restart the service unless it has been stopped. Finally, we also define an environment file — using env_file for the project. This definition looks as follows.

  database:
    container_name: postgresql_database
    image: postgres:17.5
    restart: unless-stopped
    env_file:
      - project.env

When using a PostgreSQL image, there are a wide variety of options to choose from, as outlined at https://hub.docker.com/_/postgres. In our case, we use the version 17.5.

PostgreSQL Docker images follow a naming postgres:version, where version is the version number. There are also additional image types, but we do not care about these.

With the above configuration, we want to also adjust our server service so that it depends on the database. This is done by adding depends_on configuration for the server service. The depends_on configuration is given the service that is being depended on as a value — in this case, the value is database, as our database service is called database.

With this in place, the server service configuration looks as follows.

  server:
    build: server
    restart: unless-stopped
    volumes:
      - ./server:/app
    ports:
      - 8000:8000
    depends_on:
      - database

And the whole compose.yaml file is as follows.

services:
  client:
    build: client
    restart: unless-stopped
    volumes:
      - ./client/src:/app/src
    ports:
      - 5173:5173
    depends_on:
      - server

  server:
    build: server
    restart: unless-stopped
    volumes:
      - ./server:/app
    ports:
      - 8000:8000
    depends_on:
      - database

  database:
    container_name: postgresql_database
    image: postgres:17.5
    restart: unless-stopped
    env_file:
      - project.env

Loading Exercise...

Accessing the database from the terminal

Now that the database has been added to the compose.yaml file, we can launch the project with the docker compose up --build command. This builds all the services and also downloads the PostgreSQL image. When we run the command, we see plenty of log information, which highlights — at the end — that the database is also running.

The database running in a container can be accessed with Docker using the command docker exec -it postgresql_database psql -U username database. This means that we connect to the docker container postgresql_database and execute the psql command in the container, providing the username and the database name as parameters, asking for an interactive terminal (-it).

When we run the command on the command line, we are connected to the database.

$ docker exec -it postgresql_database psql -U username database
psql (17.5 (Debian 17.5-1.pgdg120+1))
Type "help" for help.

database=# \dt
Did not find any relations.
database=#

As we can see, there are no tables in the database yet. Let’s next use a migration tool to create a table to the database.

Loading Exercise...

Database migrations

For database migrations, we use a tool called Flyway. Flyway is a database migration tool that allows us to manage database migrations using SQL files. Flyway is available as a Docker image, and we can add it to our project by adding a service to the compose.yaml file. First, add a service called database-migrations to the compose.yaml file, copying the following configuration to it.

  database-migrations:
    image: flyway/flyway:11.10
    env_file:
      - project.env
    depends_on:
      - database
    volumes:
      - ./database-migrations:/flyway/db/migration
    command: -connectRetries=60 -baselineOnMigrate=true migrate

The above configuration states that we have a service called database-migrations that uses the image flyway/flyway:11.0. The service uses the environment variables defined in the project.env file, depends on the database service, and mounts the folder database-migrations to the folder /flyway/db/migration in the container. The service is run with the command migrate, which migrates the database to the latest version.

In the above configuration, we mapped a folder called database-migrations to the folder /flyway/db/migration in the container. This is where Flyway looks for the migration files. Create the folder database-migrations to the root of the project, and create a file called V1__todos.sql to the folder. Place the following create table statement to the file.

CREATE TABLE todos (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

At this point, the compose.yaml should look as follows.

services:
  client:
    build: client
    restart: unless-stopped
    volumes:
      - ./client/src:/app/src
    ports:
      - 5173:5173
    depends_on:
      - server

  server:
    build: server
    restart: unless-stopped
    volumes:
      - ./server:/app
    ports:
      - 8000:8000
    depends_on:
      - database

  database:
    container_name: postgresql_database
    image: postgres:17.5
    restart: unless-stopped
    env_file:
      - project.env

  database-migrations:
    image: flyway/flyway:11.10
    env_file:
      - project.env
    depends_on:
      - database
    volumes:
      - ./database-migrations:/flyway/db/migration
    command: -connectRetries=60 -baselineOnMigrate=true migrate

Next, we need to also add Flyway-specific environment variables to the project.env file. Flyway expects three environment variables to be defined — FLYWAY_USER, FLYWAY_PASSWORD, and FLYWAY_URL.

The variables are defined as follows. The first one is the username for the database, the second is the password for the database, and the third is a Java Database Connectivity (JDBC) URL for the database (Flyway is written in Java). The JDBC URL is of the form jdbc:postgresql://<host>:<port>/<database>.

In our case, the host is postgresql_database, the port is 5432, and the database is database. The values for the environment variables are as follows.

FLYWAY_USER=username
FLYWAY_PASSWORD=password
FLYWAY_URL=jdbc:postgresql://postgresql_database:5432/database

At this point, the project.env file should look as follows.

FLYWAY_USER=username
FLYWAY_PASSWORD=password
FLYWAY_URL=jdbc:postgresql://postgresql_database:5432/database

POSTGRES_USER=username
POSTGRES_PASSWORD=password
POSTGRES_DB=database

Now, when we run docker compose up --build — having run docker compose down beforehand, we see that the image is downloaded and that the service starts up. There are a few messages from the service, but the most important one is the one that states that the database migration has been successfully applied. The output contains lines similar to the following.

database-migrations-1  | Database: jdbc:postgresql://postgresql_database:5432/database (PostgreSQL 17.5)
database-migrations-1  | Schema history table "public"."flyway_schema_history" does not exist yet
database-migrations-1  | Successfully validated 1 migration (execution time 00:00.032s)
database-migrations-1  | All configured schemas are empty; baseline operation skipped. A baseline or migration script with a lower version than the baseline version may execute if available. Check the Schemas parameter if this is not intended.
database-migrations-1  | Creating Schema History table "public"."flyway_schema_history" ...
database-migrations-1  | Current version of schema "public": << Empty Schema >>
database-migrations-1  | Migrating schema "public" to version "1 - todos"
database-migrations-1  | Successfully applied 1 migration to schema "public", now at version v1 (execution time 00:00.021s)

With the project running, we can also verify that the database has been created by connecting to the database. When we connect to the database and list the tables, we notice that there are two tables — a table called todos that we created in the migration file and a table called flyway_schema_history that flyway uses to keep track of the migrations.

$ docker exec -it postgresql_database psql -U username database
psql (17.5 (Debian 17.5-1.pgdg120+1))
Type "help" for help.

database=# \dt
                 List of relations
 Schema |         Name          | Type  |  Owner
--------+-----------------------+-------+----------
 public | flyway_schema_history | table | username
 public | todos                 | table | username
(2 rows)

database=# \quit

Loading Exercise...

Summary

In summary:

• We added a PostgreSQL database to the project.
• We created a table to the database using Flyway migrations.
• We verified that the table was created by connecting to the database.

At this point, you should have a folder called walking-skeleton with three folders, one called client, one called server, and one called database-migrations. The structure of the project should be similar to the following (folders like node_modules and files like deno.lock are omitted).

$ tree --dirsfirst
.
├── client
│   ├── src
│   │   ├── lib
│   │   │   └── index.js
│   │   ├── routes
│   │   │   └── +page.svelte
│   │   └── app.html
│   ├── static
│   │   └── favicon.png
│   ├── package.json
│   ├── Dockerfile
│   ├── README.md
│   ├── svelte.config.js
│   └── vite.config.js
├── database-migrations
│   └── V1__todos.sql
├── server
│   ├── app.js
│   ├── app-run.js
│   └── deno.json
├── compose.yaml
└── project.env