Overview
This guide is inspired by an article that I read a few years ago that describes the concept of evolutionary database design. The article can be found here. In the article, reference was made to a number of database migration tools. Flyway was one of the tools mentioned. As it turns out, it is Flyway that I adopted in the end. Why Flyway? Because it is powerful yet easy to learn and use.
In this guide, I am going to explain how to use Flyway by providing a working example using tools such as Docker, Docker-Compose, PostgreSQL, pgAdmin, and of course Flyway.
According to the official Flyway documentation, Flyway is an open-source database migration tool that strongly favors simplicity and convention over configuration.
The key concept in the above definition is "database migration". Within the context of discussing Flyway, a "database migration" is a group of one or more database changes. In order to keep track of migrations, Flyway creates a table (per schema) that maintains a history of all migrations for a specific schema.
Flyway supports 2 forms of migration namely versioned and repeatable migrations.
-
Versioned
- Has a version number, checksum, and description. The version number ensures that migrations are unique. The checksum helps prevent accidental changes from being migrated. The description provides metadata about the migration.
- Versioned migrations are applied in order and only once
- An "undo migration" having the same version number can be provided
-
Repeatable
- Has a description and checksum but a version number is not required.
- Repeatable migrations can be re-applied everytime their checksum changes.
- Always applied after versioned migrations.
Migrations can be written in Java and SQL. However, in this guide I will be focusing on the SQL migrations. SQL migrations offer the most flexibility due to them being agnostic of any particular development platform. I will also be focusing on versioned migrations.
In this getting started guide, the simplest scenario is to begin with a new database. This guide uses Postgres to illustrate migrations. To make it simpler to view the changes in the database, pgAdmin4 will be used to connect to and inspect database changes. All migrations that will be created in this guide are available in this repo.
Technology Used
The technology used to produce this guide is summarised as follows:
Visual Studio Code is a source code editor developed by Microsoft for Windows, Linux and macOS. It includes support for debugging, embedded Git control, syntax highlighting, intelligent code completion, snippets, and code refactoring.
Docker is a computer program that performs operating-system-level virtualization also known as containerization. It is developed by Docker, Inc.
Compose is a tool for defining and running multi-container Docker applications.
PostgreSQL is an object-relational database management system.
Open Source administration and development platform for PostgreSQL
Flyway is an open source database migration tool.
Prerequisites
In order to follow this guide, a basic understanding of the following technologies is required:
- Git
Git is a free and open source distributed version control system. Although not strictly mandatory, it is suggested to have Git installed on your local machine to make it simpler to retrieve the accompanying example repository. However, a zip file may also be downloaded.
- Docker
For this guide, I primarily use software hosted within Docker containers. Although I provide all the instructions to follow this guide, it is still recommended that one familiarise oneself with Docker. I have written a Docker guide that can be found here. As part of the Docker guide, there is also a getting started section.
In addition to Docker related technologies, the tool docker-compose will also be used to run the containers. More specifically, docker-compose is a tool for defining and running multi-container Docker applications.
- Postgres
PostgreSQL is a free and open source object-relational database management system. In this guide, PostgreSQL will be run from a Docker container. For more information on PostgreSQL on Docker, please visit the official PostgreSQL Docker registry.
- pgAdmin
The tool pgAdmin, is an open source administration and development platform for PostgreSQL. In this guide, pgAdmin will be run from a Docker container. For more information on pgAdmin on Docker, please visit the official pgAdmin Docker registry.
Environment Setup
At this point you should have Docker and Docker-Compose installed. This can be verified by running the following commands from the command line.
- To verify Docker:
docker version
docker info
docker run hello-world
- To verify Docker-Compose:
docker-compose --version
If all is well, the above commands should have run flawlessly.
Next up, let's get the repository for this guide.
Get Repository
There are 3 ways to get the repository for this guide:
- Clone Repo Using HTTPS
git clone https://github.com/drminnaar/flyway.git
- Clone Repo Using SSH
git clone git@github.com:drminnaar/flyway.git
- Download Zip File
wget https://github.com/drminnaar/flyway/archive/master.zip
unzip ./master.zip
Initialise Environment
Navigate to the 'flyway' directory using the command line.
Once inside the flyway directory, you will notice a file called 'docker-compose.yml'. This file contains all the instructions required to initialise our environment with all the required containerised software. In our case, the docker-compose.yml files holds the instructions to run postgresql and pgadmin containers.
Type the following command to initialise environment to run Flyway code migrations:
docker-compose up
- Type the following command to verify that there are 2 containers running. One container will be our PostgreSQL server. The second container will be our pgAdmin web application.
docker-compose ps
The above command should display the running containers as specified in the docker-compose file.
Create Database
The first thing to be aware of when creating a migration, is that migrations do not create databases. Migrations only apply within the context of a database and do not create the database itself. Therefore, for my demonstration I will create an empty database from scratch and then create migrations for that database.
In this example, I create a database called "heroes". It is a database that stores data related to, you guessed it, heroes.
- At this point, you should have a running PostgreSQL container instance. To verify this, run the following command:
docker-compose ps
- List available databases by running the following command:
docker exec -it $(docker container ls -qf name=pg-dev) psql -U postgres -c '\l'
Currently, there is no heroes database.
- Type the following command to create a heroes database:
docker exec -it $(docker container ls -qf name=pg-dev) psql -U postgres -c 'CREATE DATABASE heroes OWNER postgres'
List available databases by running the following command:
docker exec -it $(docker container ls -qf name=pg-dev) psql -U postgres -c '\l'
Create Migrations
For clarity sake, please take note that a migration is nothing more than a SQL file consisting of various SQL operations to be performed on the database.
Understanding The Migrations
The heroes database now exists. We are now ready to run our migrations. Please take note of the migrations folder that is part of the repo for this example. The migrations folder consists of 7 migrations that are briefly described as follows:
- V1_1_Create_hero_schema.sql - Creates a new _hero_data schema
CREATE SCHEMA hero_data AUTHORIZATION postgres;
- V1_2_Create_hero_table.sql - Create a new _hero table in the hero_data schema
CREATE TABLE hero_data.hero
(
id BIGSERIAL NOT NULL,
name VARCHAR(250) NOT NULL,
description TEXT NOT NULL,
debut_year INT NOT NULL,
appearances INT NOT NULL,
special_powers INT NOT NULL,
cunning INT NOT NULL,
strength INT NOT NULL,
technology INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
updated_at TIMESTAMPTZ NOT NULL
);
ALTER TABLE hero_data.hero ADD CONSTRAINT pk_hero_id PRIMARY KEY (id);
- V1_3_Add_Destroyer_hero.sql - Inserts our first hero into _hero table
INSERT INTO hero_data.hero (
name,
description,
debut_year,
appearances,
special_powers,
cunning,
strength,
technology,
created_at,
updated_at) VALUES (
'Destroyer',
'Created by Odin, locked in temple, brought to life by Loki',
1965,
137,
15,
1,
19,
80,
now(),
now());
- V1_4_Create_user_schema.sql - Create a _user_data schema
CREATE SCHEMA user_data AUTHORIZATION postgres;
- V1_5_Create_user_table.sql - Create a new _user table in the user_data schema
CREATE TABLE user_data.user
(
id BIGSERIAL NOT NULL,
first_name VARCHAR(250) NOT NULL,
last_name VARCHAR(250) NOT NULL,
email VARCHAR(250) NOT NULL,
alias VARCHAR(250) NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
updated_at TIMESTAMPTZ NOT NULL
);
ALTER TABLE user_data.user ADD CONSTRAINT pk_user_id PRIMARY KEY (id);
- V1_6_Add_unique_hero_name_contraint.sql - Alter _hero table by adding a unique name constraint
ALTER TABLE hero_data.hero ADD CONSTRAINT uk_hero_name UNIQUE (name);
- V1_7_Add_unique_user_email_constraint.sql - Alter _user table by adding a unique email constraint
ALTER TABLE user_data.user ADD CONSTRAINT uk_user_email UNIQUE (email);
You will have noticed the strange naming convention. The way we name a migrations is as follows:
According to the official Flyway documentation, the file name consists of the following parts:
- Prefix: V for versioned migrations, U for undo migrations, R for repeatable migrations
- Version: Underscores (automatically replaced by dots at runtime) separate as many parts as you like (Not for repeatable migrations)
- Separator: __ (two underscores)
- Description: Underscores (automatically replaced by spaces at runtime) separate the words
Run Migrations
Finally we get to run our migrations. To run the migrations, we will execute the Flyway Docker container.
Before running the migration, we need to obtain the IP address of the postgres container as follows:
docker container inspect -f "{{ .NetworkSettings.Networks.flyway_skynet.IPAddress}}" flyway_pg-dev_1
We plug the obtained IP address from above into the command below. In my case, my IP address is 172.18.0.2
docker run --rm --network docker_skynet -v $PWD/migrations:/flyway/sql boxfuse/flyway -url=jdbc:postgresql://172.18.0.2:5432/heroes -user=postgres -password=password migrate
You should see an output similar to the following output:
As can be seen from output above, all 7 migrations ran successfully.
Run the following command to see a list of tables in the heroes database:
docker exec -it $(docker container ls -qf name=pg-dev) psql -U postgres -d heroes -c "SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema NOT IN ('pg_catalog', 'information_schema')"
You should see a list of tables as follows:
| table_schema | table_name |
|---|---|
| public | flyway_schema_history |
| hero_data | hero |
| user_data | user |
The database table flyway_schema_history contains all the records for the database migrations that took place.
Lastly, log into pgAdmin to view the flyway_schema_history table.
- Login
Navigate to http://localhost:8080 in your browser
- email/username: iamhero@heroes.com
- password: password
If you're wondering where the pgadmin credentials come from, you can find them specified in the docker-compose.yml file. They're passed in as environment variables.
- Once logged in, you can connect to the PostgreSQL server by adding a connection as follows:
- Open the flyway_schema_history table that is located in the public schema of the heroes database.
Conclusion
This has been a basic introduction into using the tool Flyway for performing database migrations. Although basic, it doesn't really get much more complicated than this.
I did not cover how to use Flyway with an existing database, however, this is also supported by using the baseline command with Flyway. I will cover this in more detail in a next part to this guide.
Top comments (1)
That was really helpful! How do I expose the postgres, so that I can connect to it from IDE on host OS?