Flyway Migration Exercises

Setup

git clone postgres-persistence
big setup and postgres container running in docker-machine
run sbt test and verify you are on green bar
find the migrations in the library project in the banno-all-migrations package
review the com.banno.persistence.postgres.SchemaMigrationsTests class

Clean Up Between Scenarios and After All Scenarios are Complete

run the following to restore the database to a good state

big destroy postgres
big up -d postgres

revert any changes made to postgres-persistence and delete any new files
run sbt "test-only *SchemaMigrationsTests" and verify you are back to green bar

Assumptions

At the time of writing this there were migrations V1 through V8 in the project
The next version for adding new migration would be V9

Exercise 1 - Adding a new migration

add a new migration called V9__create_foo_table.sql
- add the following SQL to the new migration
```
CREATE TABLE foo (
   bar character varying(36) NOT NULL
);
```
- run sbt "test-only *SchemaMigrationsTests"
- connect to banno_all database jdbc:postgresql://dev.banno.com:5432/banno_all
- find that the table foo was created
- inspect the schema_version table with SELECT version, script, checksum, installed_by, success FROM schema_version;
  - output should look like "9";"V9__create_foo_table.sql";1529165610;"banno_db";t
- congratulations, you know how to add a new SQL migration

Exercise 2 - Flyway does not allow editing an existing migration

run sbt "test-only *SchemaMigrationsTests" so all migrations get ran
alter migration 6, adding a new column my_new_fancy_foo so it looks like the following:

create table payment_cards(
   card_id varchar(36) primary key not null,
   account_discriminator varchar(40) not null,
   card_discriminator varchar(40) not null,
   user_id varchar(36) not null,
   card_name varchar(128) not null,
   masked_card_number varchar(15) not null,
   encrypted_card_number bytea not null,
   card_suffix_number smallint,
   card_status smallint not null,
   created_at timestamp not null,
   updated_at timestamp not null,
   deleted_at timestamp,
   vendor_json text,
   my_new_fancy_foo text not null
);

run sbt "test-only *SchemaMigrationsTests" again
verify you get an error like the following:

[error]  FlywayException: : Validate failed. Migration Checksum mismatch for migration 6
[error] -> Applied to database : 1915742862
[error] -> Resolved locally    : -513184501  (Flyway.java:1108)

You can not change an existing migration once it has been released
- Once applied you will get a validation error
- This is a safety check because Flyway does not apply a migration more than once
- You will instead need to add a new migration with a new version to alter the table to add the column

Exercise 2 - Migrations must not have duplicate versions

add a migration V9__create_foo_table.sql with the following content:

CREATE TABLE foo (
  bar character varying(36) NOT NULL
);

add a migration V9__create_bar_table.sql with the following content:

CREATE TABLE bar (
  biff character varying(36) NOT NULL
);

run sbt "test-only *SchemaMigrationsTests"
verify you get an error like the following:

[info] ! Schema Migrations should be run
[error]  FlywayException: : Found more than one migration with version 9
[error] Offenders:
[error] -> /.../banno-all-migrations/V9__create_foo_table.sql (SQL)
[error] -> /.../banno-all-migrations/V9__create_bar_table.sql (SQL)

duplicate versions make no sense as migrations need to be ordered
this typically happens on merging as two people added a different migration with the same version
- resolve this by renumbering migrations which have not been released or applied to a persistent database yet

Exercise 3 (Advanced) - Simulating Baselining an existing database already at Version 1

SchemaMigrationsForTests assumes you want to start with an empty version of the database and apply all the migrations How would you handle applying only migrations 2 on up to an existing database which was already at version 1?

HINT: The com.banno.persistence.postgres.Migrator is hard coded with a baseLineVersion of 0

Lets simulate a database which hasn’t had any baseline ran or migrations ran but is at Version 1

start with fresh postgres big destroy postgres and big up -d postgres
revert any changes you’ve made to postgres-persistence
delete all SQL migrations other than V1__Initial.sql
run sbt "test-only *SchemaMigrationsTests"
verify the contents of the schema_version table has only V1 migration applied
manually drop the schema_version table drop table schema_version;

Now lets tell our Migrator the database is at Version #1 so it can baseline to that version and apply the other scripts

undo the changes to the postgres-persistence project so you have all migrations now
alter SchemaMigrationsForTests and pass in baseLineVersion = "1" to the Migrator
run sbt "test-only *SchemaMigrationsTests"
query the contents of the schema_version table
verify you did not run V1__Initial.sql (there will be no row for this migration in the table)
verify the baseline version looks like this: "1";"<< Flyway Baseline >>";;"banno_db";t
- this is because we told Flyway the database is at version 1
verify SQL migrations version 2 through 8 ran
- Flyway only applies migrations past the current version of the database once
- Since we told it we are at version 1 of the database it started at migration version 2
- Now that the database is at version 8 it will never re-apply those migrations
  - Adding migrations will only run migrations past the current version of the database as tracked in the schema_version table

Terminology

green bar - tests are passing
persistent database - a database which is not brought up from an empty state (eg Staging, UAT, Production)