Database Migrations

When working on an application, the database tables and columns -- that is, the schema -- evolve over time. As an example, on this course platform, we've done over 35 changes to the database schema over the last three years. To keep track of the changes and to ensure that the changes are applied in a consistent fashion, there is a need for a tool that helps with the process -- this holds especially when there are multiple developers working on the application.

This is what database migration tools are for -- here, we look at database migrations using Flyway.

In a nutshell, the term database migrations or schema migrations refer to the incremental process of modifying the database schema over the lifetime of an application.

As we are working with Docker, it is meaningful to the tools responsible for database migrations as a separate container that are run when the project is run. This way, database changes are made whenever the project is started. Before starting, let's clean the existing PostgreSQL database by running the command docker compose down. Running the command removes the containers and the volumes associated with the containers -- in the case of PostgreSQL, the volumes also contain the database data.

Adding Flyway

First, we add a service for Flyway to our docker-compose.yml -- let's call the service database-migrations. For the service, we use an existing Flyway docker image called flyway/flyway:10.0.0-alpine (available at https://hub.docker.com/u/flyway) . We also specify the file containing environmental variables using env_file; similarly to the earlier services, we use project.env. Furthermore, we state that the service depends on the service database. These steps of the service configuration look as follows.

  database-migrations:
    image: flyway/flyway:10.0.0-alpine
    env_file:
      - project.env
    depends_on:
      - database

In addition to the above, we also need to define a location for our migration scripts. We do this by adding a volume to the service configuration. The volume is defined as follows -- this states that the folder flyway/sql in the project folder is mounted to the folder /flyway/sql in the container. The folder does not yet exist in our project, but we'll create it soon.

    volumes:
      - ./flyway/sql:/flyway/sql

Finally, we need to also add a command that is run when when the container is started. The command is as follows.

    command: -connectRetries=60 -baselineOnMigrate=true migrate

The above command states that Flyway should be run with the command migrate and that Flyway should wait for the database to be available for 60 seconds before giving up on migration attempts (i.e. if the database takes plenty of time to start up). Furthermore, we state that Flyway should create a baseline for the database migrations if such a table does not yet exist. The baseline is used to keep track of the migrations that have been applied to the database.

As a whole, the service configuration looks as follows.

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

At this point, the docker-compose.yml should look as follows.

services:
  api:
    build: api
    restart: unless-stopped
    volumes:
      - ./api:/app
    ports:
      - 8000:8000
    depends_on:
      - database
    env_file:
      - project.env
    
  database:
    container_name: postgresql_database
    image: postgres:16.1
    restart: unless-stopped
    env_file:
      - project.env

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

Finally, before trying the database migration image out, we need to also add Flyway-specific environmental variables to the project.env file. Flyway expects three environmental 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 environmental variables are as follows.

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

Together, at this point, the project.env file looks as follows.

POSTGRES_USER=username
POSTGRES_PASSWORD=password
POSTGRES_DB=database

PGUSER=username
PGPASSWORD=password
PGDATABASE=database

PGHOST=postgresql_database
PGPORT=5432

FLYWAY_USER=username
FLYWAY_PASSWORD=password
FLYWAY_URL=jdbc:postgresql://postgresql_database:5432/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 is up to date. This is because we have not yet added any migration files.

docker compose up --build
database-migrations-1  | Flyway OSS Edition 10.0.0 by Redgate
..
database-migrations-1  | Database: jdbc:postgresql://postgresql_database:5432/database (PostgreSQL 16.1)
database-migrations-1  | WARNING: Flyway upgrade recommended: PostgreSQL 16.1 is newer than this version of Flyway and support has not been tested. The latest supported version of PostgreSQL is 15.
database-migrations-1  | Schema history table "public"."flyway_schema_history" does not exist yet
database-migrations-1  | Successfully validated 0 migrations (execution time 00:00.010s)
database-migrations-1  | WARNING: No migrations found. Are your locations set up correctly?

Above, we notice that Flyway does not yet officially support PostgreSQL 16.1 (but let's not worry about it!). We also notice that Flyway states that no migrations were found. This is because we haven't added any yet -- let's add those now.

Adding migration files

Migration files are placed under the folder flyway/sql in the project folder, which is mapped to the folder /flyway/sql within the container. This is the location from where the container will read the migration files.

Let's create a file called V1__todos.sql to a folder called sql under the folder flyway. The migration files are essentially SQL files that have a versioning prefix that helps Flyway execute them in the correct order.

The versioning prefix is of the form V<version>__<description>.sql where <version> is a number and <description> is a description of the migration. The versioning prefix is followed by the SQL statements that are executed as part of the migration.

Place the following create table statement to the file.

CREATE TABLE todos (
  id SERIAL PRIMARY KEY,
  todo TEXT NOT NULL
);

At this point, the folder structure should be as follows.

tree --dirsfirst
.
├── api
│   ├── app.js
│   ├── app-run.js
│   ├── deps.js
│   └── Dockerfile
├── flyway
│   └── sql
│       └── V1__todos.sql
├── docker-compose.yml
└── project.env

3 directories, 7 files

Now, when we launch the project using docker compose, we observe that the applications starts up as expected. In addition, we see from the container logs that flyway has executed and migrated the schema to version 1.

database-migrations-1  | Successfully validated 1 migration (execution time 00:00.031s)
database-migrations-1  | Creating Schema History table "public"."flyway_schema_history" ...
database-migrations-1  | Current version of schema "public": << Empty Schema >>
migrations-1  | Migrating schema "public" to version "1 - todos"

With the project running, we can 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 (16.1 (Debian 16.1-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=#

We can take a peek into both tables. The flyway_schema_history table contains information about the migrations that have been applied to the database -- at this point, there's one only row. The todos table is empty as we have not yet added any data to it.

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

database=# select * from flyway_schema_history;
 installed_rank | version | description | type |    script     |  checksum  | ..
----------------+---------+-------------+------+---------------+------------+ ..
              1 | 1       | todos       | SQL  | V1__todos.sql | 1745730117 | ..
(1 row)

database=# select * from todos;
 id | todo 
----+------
(0 rows)

database=#

Now that the above works, let's add a few more migration files. Let's add a file called V2__users.sql to the folder flyway/sql and place the following content to it. The following creates a database table users with three columns -- id, email, and password_hash, and adds a case-insensitive uniqueness constraint to email.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  password_hash TEXT NOT NULL
);

CREATE UNIQUE INDEX ON users(lower(email));

With the project running (i.e., we haven't pressed ctrl+C or run docker compose stop in the project folder), we can run the database migrations by running a specific container. This is done by providing the docker compose up command the name of the container that we wish to run -- that is, if we wish to run the container database-migrations, we run the command docker compose up database-migrations.

docker compose up database-migrations
[+] Running 2/0
 ✔ Container postgresql_database                   Running                                                                      0.0s 
 ✔ Container database-migrations-1  Created                                                                      0.0s 
Attaching to database-migrations-1
..
database-migrations-1  | Successfully validated 2 migrations (execution time 00:00.044s)
database-migrations-1  | Current version of schema "public": 1
database-migrations-1  | Migrating schema "public" to version "2 - users"
database-migrations-1  | Successfully applied 1 migration to schema "public", now at version v2 (execution time 00:00.027s)
database-migrations-1 exited with code 0

As we can see from the above output, Flyway first notices that the current version in the database is 1, after which it migrates the schema to version 2. Now, the database has three tables -- flyway_schema_history, todos, and users.

docker exec -it postgresql_database psql -U username database
psql (16.1 (Debian 16.1-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
 public | users                 | table | username
(3 rows)

database=#

Finally, let's create one more database migration file, this time adding a table called addresses to the database. Place the following create table statement to a file called V3__addresses.sql and place the file to the folder flyway/sql.

CREATE TABLE addresses (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  address TEXT NOT NULL
);

To run the migration file, run the command docker compose up database-migrations again. After running the command, the database has four tables and three migrations have been run.

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

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

database=# select * from flyway_schema_history;
 installed_rank | version | description | type |      script       |  checksum   | ..
----------------+---------+-------------+------+-------------------+-------------+ ..
              1 | 1       | todos       | SQL  | V1__todos.sql     |  1745730117 | ..
              2 | 2       | users       | SQL  | V2__users.sql     | -1816354722 | ..
              3 | 3       | addresses   | SQL  | V3__addresses.sql |  1861485335 | ..
(3 rows)

database=# 

At this point, the project structure should be as follows.

tree --dirsfirst
.
├── api
│   ├── app.js
│   ├── app-run.js
│   ├── deps.js
│   └── Dockerfile
├── flyway
│   └── sql
│       ├── V1__todos.sql
│       ├── V2__users.sql
│       └── V3__addresses.sql
├── docker-compose.yml
└── project.env

3 directories, 9 files

Finally, let's add one more migration file, V4__books_and_ratings.sql, which is used to add two tables: books and ratings. The table books contains information about books, whereas the table ratings contains information about ratings given to books. The tables are related to each other using a foreign key constraint. Use the following as the contents of the migration file.

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  pages INTEGER NOT NULL,
  isbn TEXT NOT NULL
);

CREATE TABLE book_ratings (
  id SERIAL PRIMARY KEY,
  book_id INTEGER NOT NULL REFERENCES books(id),
  rating INTEGER NOT NULL,
  feedback TEXT
);

Finally, at this point, the project structure should be as follows.

tree --dirsfirst
.
├── api
│   ├── app.js
│   ├── app-run.js
│   ├── deps.js
│   └── Dockerfile
├── flyway
│   └── sql
│       ├── V1__todos.sql
│       ├── V2__users.sql
│       ├── V3__addresses.sql
│       └── V4__books_and_ratings.sql
├── docker-compose.yml
└── project.env

3 directories, 10 files