Databases and Data Validation

Database Migrations

---

When working on an application, the database tables and columns — that is, the schema — evolve over time. As an example, at the time of the writing of these materials, this course platform has had 57 versions of the database schema.

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. In this course, we are briefly looking into 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 run database migrations as a separate container that is run when the project is run. This way, database changes are made whenever the project is started. Towards the end of setting up the walking skeleton, in the chapter on PostgreSQL Database and Migrations, we set up a PostgreSQL database and Flyway to the project, also introducing the first database migration file.

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 migration files

In our walking skeleton, the database migration files are placed in the folder database-migrations. The folder is mapped to the folder /flyway/sql in the Flyway container, which is the location that Flyway reads the migration files when it is started.

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.

There are two underscores between the version and the description.

Todos

At this point, the folder database-migrations contains one migration file, V1__todos.sql, which creates a table called todos. If the contents of the file does not match the following, modify the file to match the following.

CREATE TABLE todos (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  done BOOLEAN NOT NULL DEFAULT false
);

After running docker compose down, the database is empty and the schema is not yet created. To run the project, including the database migrations, run the command docker compose up --build. The command starts the database and the database migrations container, which runs the migrations.

We see from the container logs that flyway has executed and migrated the schema to version 1.

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 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=#

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 (17.0 (Debian 17.0-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 | ...        | ..
(1 row)

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

database=#

Users

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 database-migrations 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 (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
 public | users                 | table | username
(3 rows)

database=#

Addresses

Let’s again 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 (17.0 (Debian 17.0-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     |  ...        | ..
              2 | 2       | users       | SQL  | V2__users.sql     |  ...        | ..
              3 | 3       | addresses   | SQL  | V3__addresses.sql |  ...        | ..
(3 rows)

database=#

Books and ratings

Again, 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,
  title TEXT,
  year INT
);

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

After running the database migrations, the database has five tables: flyway_schema_history, todos, users, addresses, books, and book_ratings.

Dropping addresses

Although our examples so far include only creating tables, database migrations can also be used to drop tables. Let’s create a migration file V5__drop_addresses.sql that drops the table addresses. The contents of the file are as follows.

DROP TABLE addresses;

After running the database migrations, the table addresses is no longer in the database.

Adding a constraint

Similarly, database migrations can be used to modify tables. Let’s create a migration file V6__book_ratings_constraint.sql that adds a constraint to the rating field.

ALTER TABLE book_ratings ADD CONSTRAINT rating_range CHECK (rating >= 1 AND rating <= 5);

Now, when the user tries to add a rating, any ratings that are not between 1 and 5 will cause an error.

Loading Exercise...