Database as Code on Cloud Foundry with Flyway

January 15, 2019

Database Migrations on Cloud Foundry with Flyway

I took the PCF Cloud Native Developer four day course to prepare for the Pivotal Certified Java Developer exam. This was my first in-depth exposure to PCF and Spring (Boot, MVC, Cloud), so there was a lot to learn with little experience. I learned a lot and enjoyed the course.

In my last article, I shared my notes on Spring MVC CRUD operations. This article will cover database migrations using Flyway and MySQL, deploying to Cloud Foundry.

Provision and Bind MySQL in Cloud Foundry

Use cf marketplace to find the appropriate service and provisioning options for MySQL, then use cf create-service to provision the service:

$ cf marketplace
Getting services from marketplace in org some-org / space develop as [email protected]
OK

service               plans                  description
# ...
p-mysql               100mb, 1gb             A DBaaS
# ...

In the example above, the p-mysql service is avaialbe. Provision it with cf create-service:

cf create-service p-mysql 100mb pal-tracker-db

The cf create-service call will return immedietly, before the service is provisioned. To check the status use cf service:

$ cf service pal-tracker-db

Service instance: pal-tracker-db
Service: p-mysql
Plan: 100mb
Description: MySQL databases on demand
Documentation url:
Dashboard: https://p-mysql.example.com/manage/instances/abcd-ef12-3456

Last Operation
Status: create succeeded
Message:
Started: 2019-01-15T22:59:07Z
Updated: 2019-01-15T22:59:26Z

Once the service is ready, bind it to the pal-tracker application:

cf bind-service pal-tracker pal-tracker-db # Service info added to VCAP_SERVICES env var

Once the service is bound, the VCAP_SERVICES environment variable will contain details about the service:

$ cf env my-app
Getting env variables for app pal-tracker in org some-org / space some-space as
you...
OK
System-Provided:

{
    "VCAP_SERVICES": {
        "p.mysql": [{
            "label": "p.mysql",
            "name": "pal-tracker-db",
            "plan": "db-small",
            "provider": null,
            "syslog_drain_url": null,
            "tags": [ "mysql" ],
            "credentials": {
            "hostname": "10.0.0.20",
            "jdbcUrl": "jdbc:mysql://10.0.0.20:3306/service_instance_db?user=fefcbe8360854a18a7994b870e7b0bf5\u0026password=z9z6eskdbs1rhtxt",
            "name": "service_instance_db",
            "password": "z9z6eskdbs1rhtxt",
            "port": 3306,
            "uri": "mysql://fefcbe8360854a18a7994b870e7b0bf5:[email protected]:3306/service_instance_db?reconnect=true",
            "username": "fefcbe8360854a18a7994b870e7b0bf5"
            },
            "volume_mounts": []
        }
    }
    ...
}

Add the Database “as code” to the Project

Continuing with the PAL Tracker application, create a databases/tracker folder in the application root then create a file called create_databases.sql there:

mkdir -p database/tracker
touch database/tracker/create_databases.sql

Edit the create_databases.sql file to create tracker_dev and tracker_test databases:

-- Delete DBs if they exist
DROP DATABASE IF EXISTS tracker_dev;
DROP DATABASE IF EXISTS tracker_test;

-- Create the databases
CREATE DATABASE tracker_dev;
CREATE DATABASE tracker_test;

-- Add a user with full access to both DBs
CREATE USER IF NOT EXISTS 'tracker'@'localhost'
  IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON tracker_dev.* TO 'tracker' @'localhost';
GRANT ALL PRIVILEGES ON tracker_test.* TO 'tracker' @'localhost';

Add the first Migration

Add a file called V1__initial_schema.sql to a folder called migrations in the database/tracker folder, then add a CREATE TABLE statement to store TimeEntry objects created in the Spring MVC CRUD operations article:

CREATE TABLE time_entries (
  id         BIGINT(20) NOT NULL AUTO_INCREMENT,
  project_id BIGINT(20), -- projectId on TimeEntry
  user_id    BIGINT(20), -- userId on TimeEntry
  date       DATE,
  hours      INT,

  PRIMARY KEY (id)
)
  ENGINE = innodb
  DEFAULT CHARSET = utf8;

Create and Migrate a Local MySQL Databases

To create the test and development databases, simply pass the create_databases.sql file to MySQL on the command line:

mysql -uroot < databases/tracker/create_databases.sql

Add the time_entries table to both databases using the Flyway CLI:

flyway -url="jdbc:mysql://localhost:3306/tracker_dev" -locations=filesystem:databases/tracker clean migrate
flyway -url="jdbc:mysql://localhost:3306/tracker_test" -locations=filesystem:databases/tracker clean migrate

The databases can now be inspected with MySQL.

Open MySQL on the CLI:

mysql -u tracker

Inspect the database:

use tracker_dev;
describe time_entries;

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| project_id | bigint(20)   | YES  |     | NULL    |                |
| user_id    | bigint(20)   | YES  |     | NULL    |                |
| date       | date         | YES  |     | NULL    |                |
| hours      | int(11)      | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Your local databases are ready to go!

Create and Migrate the Database on Cloud Foundry

The database provided by Cloud Foundry is behind a Firewall, so it’s necessary to open an SSH tunnel. The following is a fully re-usable script for running the migration as part of a CI process:

#!/usr/bin/env bash

# Fail on error
set -e

# Get the GUID of the app - pass pal-tracker as first arg to script
app_guid=`cf app $1 --guid`
credentials=`cf curl /v2/apps/$app_guid/env | jq '.system_env_json.VCAP_SERVICES | .[] | .[] | select(.instance_name=="pal-tracker-db") | .credentials'`

# Get MySQL Connection info
ip_address=`echo $credentials | jq -r '.hostname'`
db_name=`echo $credentials | jq -r '.name'`
db_username=`echo $credentials | jq -r '.username'`
db_password=`echo $credentials | jq -r '.password'`

# Open SSH tunnel
echo "Opening ssh tunnel to $ip_address"
cf ssh -N -L 63306:$ip_address:3306 pal-tracker &
cf_ssh_pid=$!

echo "Waiting for tunnel"
sleep 5

# Run migrations
flyway-*/flyway -url="jdbc:mysql://127.0.0.1:63306/$db_name" -locations=filesystem:$2/databases/tracker -user=$db_username -password=$db_password migrate

kill -STOP $cf_ssh_pid

To run the script, pass the application name and root directory to the script:

./scripts/migrate-databases.sh pal-tracker .

Once the script has completed, the Cloud Foundry database is ready to go!