This is a repository that consists of a collection of examples that illustrate how to use Flyway in terms of SQL migrations.

• Visual Studio Code

  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

  Docker is a computer program that performs operating-system-level virtualization also known as containerization. It is developed by Docker, Inc.

• Docker-Compose

  Compose is a tool for defining and running multi-container Docker applications.

• PostgreSQL

  PostgreSQL is an object-relational database management system.

• pgAdmin4

  Open Source administration and development platform for PostgreSQL

• Flyway

  Flyway is an open source database migration tool.

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.

There are 3 ways to get the repository for this guide:

1. Clone Repo Using HTTPS

   git clone https://github.com/drminnaar/flyway.git

2. Clone Repo Using SSH

   git clone [email protected]:drminnaar/flyway.git

3. Download Zip File

   wget https://github.com/drminnaar/flyway/archive/master.zip
   unzip ./master.zip

• 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.

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'

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.

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

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:https://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:

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:https://localhost:8080 in your browser

  • email/username: [email protected]
  • 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.

  

I use SemVer for versioning. For the versions available, see the tags on this repository.

• Douglas Minnaar - Initial work - drminnaar