Setting up a Walking Skeleton

PostgreSQL Database and Migrations

---

Next, we add PostgreSQL database to the walking skeleton after which we add database migrations to the project. We use Docker to run the database and Flyway to manage the migrations.

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 define environment variables that are used to configure the database. PostgreSQL expects three variables: POSTGRES_USER, POSTGRES_PASSWORD, and POSTGRES_DB. Perhaps unsuprisingly, 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.0, 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.0
    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.0.

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 api 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

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

  e2e-tests:
    entrypoint: "/bin/true"
    build: e2e-tests
    network_mode: host
    depends_on:
      - client
    volumes:
      - ./e2e-tests/tests:/app/tests

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

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.0 (Debian 17.0-1.pgdg120+1))
Type "help" for help.

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

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.

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:10
    env_file:
      - project.env
    depends_on:
      - database
    volumes:
      - ./database-migrations:/flyway/sql
    command: -connectRetries=60 -baselineOnMigrate=true migrate

The above configuration states that we have a service called database-migrations that uses the image flyway/flyway:10. 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/sql 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/sql 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 SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  done BOOLEAN NOT NULL DEFAULT false
);

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

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

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

  e2e-tests:
    entrypoint: "/bin/true"
    build: e2e-tests
    network_mode: host
    depends_on:
      - client
    volumes:
      - ./e2e-tests/tests:/app/tests

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

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 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 tot he following.

database-migrations-1  | Database: jdbc:postgresql://postgresql_database:5432/database (PostgreSQL 17.0)
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.027s)
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.018s)

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.0 (Debian 17.0-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

Accessing database programmatically

When working with databases, we need a database client that takes care of the communication with the database. We use Postgres.js. To take it into use, modify deno.json to match the following.

{
  "imports": {
    "@hono/hono": "jsr:@hono/hono@4.6.5",
    "postgres": "https://deno.land/x/postgresjs@v3.4.4/mod.js"
  }
}

Modify the project.env to match the following.

POSTGRES_USER=username
POSTGRES_PASSWORD=password
POSTGRES_DB=database

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

PGUSER=username
PGPASSWORD=password
PGDATABASE=database
PGHOST=postgresql_database
PGPORT=5432

And modify the compose.yaml configuration of the “server” service to match the following.

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

With these changes, we can now access the database from the server-side application. Let’s modify the server-side application to read all the todos from the database and return them as a JSON object. Modify the app.js to match the following.

import { Hono } from "@hono/hono";
import { cors } from "@hono/hono/cors";
import { logger } from "@hono/hono/logger";
import postgres from "postgres";

const app = new Hono();
const sql = postgres();

app.use("/*", cors());
app.use("/*", logger());

app.get("/", (c) => c.json({ message: "Hello world!" }));
app.get("/todos", async (c) => {
  const todos = await sql`SELECT * FROM todos`;
  return c.json(todos);
});

export default app;

Now, when we run docker compose up --build, we see that the project starts up. In addition, we can access the todos from the database by making a request to the server.

curl localhost:8000/todos
[]%

As there are no todos in the database, the response is an empty array. If you wish, you may access the database through Docker and add a row using SQL. After that, making a query to the server should return with an array that contains the data that you added.

Loading Exercise...