flyway-cover

Flyway Guide

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

Toolchain
Environment Setup
Getting Started
Example 1

Toolchain

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.

Environment Setup

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.

Getting Started

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/refs/heads/main.zip
unzip ./main.zip

Example 1

Everything that is required to run this example is managed via docker-compose.

The docker-compose.yml file defines a stack that provides the following:

Postgres 12 Database
Entry point script that creates the heroes database
pgAdmin UI to view/manage database

There are 3 additional docker-compose files that are used to manage the Flyway migrations. They are listed as follows:

docker-compose-info - Runs Flyway to obtain information regarding migrations
docker-compose-validate - Runs Flyway to verify validity of migrations
docker-compose-migrate - Runs Flyway to migrate migrations

Start Stack

Type the following command to initialise environment:

docker-compose -f ./example1/docker-compose.yml up --detach

# output
Creating network "flywaynet" with driver "bridge"
Creating volume "flyway-pg-data" with default driver
Creating flyway-pgadmin ... done
Creating flyway-pg      ... done

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 -f ./example1/docker-compose.yml ps

# output
     Name                   Command              State               Ports
--------------------------------------------------------------------------------------
flyway-pg        docker-entrypoint.sh postgres   Up      0.0.0.0:5432->5432/tcp
flyway-pgadmin   /entrypoint.sh                  Up      443/tcp, 0.0.0.0:8080->80/tcp

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

The heroes database now exists and we are 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:

flyway-naming-convention

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

Manage Migrations

Finally we get to run our migrations. To run the migrations, we will use Docker and the official Flyway Docker Image

Get Migrations Info

Before running the migration, lets see what migrations we have. We can do that by running the following command:

# run docker-compose-info.yml stack
docker-compose -f ./example1/docker-compose-info.yml up

# output

Flyway Community Edition 7.6.0 by Redgate
Database: jdbc:postgresql://flyway-pg/heroes (PostgreSQL 12.5)
Schema version: << Empty Schema >>
+------------+---------+----------------------------------+------+--------------+---------+
| Category   | Version | Description                      | Type | Installed On | State   |
+------------+---------+----------------------------------+------+--------------+---------+
| Versioned  | 1.1     | Create hero schema               | SQL  |              | Pending |
| Versioned  | 1.2     | Create hero table                | SQL  |              | Pending |
| Versioned  | 1.3     | Add Destroyer hero               | SQL  |              | Pending |
| Versioned  | 1.4     | Create user schema               | SQL  |              | Pending |
| Versioned  | 1.5     | Create user table                | SQL  |              | Pending |
| Versioned  | 1.6     | Add unique hero name contraint   | SQL  |              | Pending |
| Versioned  | 1.7     | Add unique user email constraint | SQL  |              | Pending |
| Repeatable |         | 001 Install extensions           | SQL  |              | Pending |
+------------+---------+----------------------------------+------+--------------+---------+

Validate Migrations

We can validate our migrations to determine anythin that should be fixed before running migrations.

# run docker-compose-validate.yml
docker-compose -f ./example1/docker-compose-validate.yml up

# output
Flyway Community Edition 7.6.0 by Redgate
Database: jdbc:postgresql://flyway-pg/heroes (PostgreSQL 12.5)
ERROR: Validate failed: Migrations have failed validation
Detected resolved migration not applied to database: 1.1. To fix this error, either run migrate, or set -ignorePendingMigrations=true.
Detected resolved migration not applied to database: 1.2. To fix this error, either run migrate, or set -ignorePendingMigrations=true.
Detected resolved migration not applied to database: 1.3. To fix this error, either run migrate, or set -ignorePendingMigrations=true.
Detected resolved migration not applied to database: 1.4. To fix this error, either run migrate, or set -ignorePendingMigrations=true.
Detected resolved migration not applied to database: 1.5. To fix this error, either run migrate, or set -ignorePendingMigrations=true.
Detected resolved migration not applied to database: 1.6. To fix this error, either run migrate, or set -ignorePendingMigrat

Flyway

Install / Use

README