Autoverse: Database Migration Automated with Flyway

Article

Automate Database Migrations with Flyway, AWS Lambda and ECR

Building a continuous delivery pipeline for database migrations

Databases are often changing to support the application need, requiring DDL scripts to be executed directly on the database instance. Tools such as Flyway and Liquibase version and manage these DDL changes. These tools provide the capability to consistently migrate (and revert) these changes in SDLC databases.

The problem is these database migrations usually fall out of defined CICD deployment pipelines and source control tools such as AWS CodeCommit. A more consistent and continuous approach would be to adopt serverless architecture and are able to use defined CICD pipelines.

This is were AWS Lambda and AWS ECR come in. We can create an AWS ECR container using the flyway docker image and call this container each time we deploy using an AWS Lambda function.

Technology Stack

Flyway
AWS Lambda
AWS CDK
AWS CodeCommit
AWS CodeBuild
AWS CodePipeline
AWS ECR
AWS IAM
AWS SNS

The Solution

We’ve developed a solution for this in the form of an AWS Lambda function that can be invoked to perform the Flyway migration against a Database.

AWS CodeCommit was used as our source control repository which held our CDK application, Docker image definition , bootstrap scripts for the docker container and all DDL files that are required for migration.

CICD Pipeline Explained

When new code was merged to master it would trigger a AWS CodePipeline which called AWS CodeBuild to:

1. Download Flyway Docker Container image

2. Setup Flyway environment variables in the docker container

3. Copy DDL scripts from AWS CodeCommit to the Flyway Docker Container

4. Register Flyway Docker Container in AWS ECR

5. Create a AWS Lambda function using a custom resource definition which points to the Flyway Docker container in AWS ECR

6. Call AWS lambda function to run "flyway migrate" on the Flyway Docker container

7. Flyway would then run the DDL scripts on the AWS Aurora PostgreSQL instance

Flyway Docker Script

FROM flyway/flyway:7.12
USER root
RUN apt-get update && apt-get -y install jq awscli curl
USER flyway
COPY scripts /flyway/sql
COPY lambda-entrypoint.sh /flyway
COPY entry.sh /
RUN chmod a+x /flyway/flyway
# Execute the command migrate
ENTRYPOINT ["bash", "/entry.sh"]

/entry.sh Script

#!/bin/bash
mkdir -p /aws-lambda-rie
curl -Lo /aws-lambda-rie/aws-lambda-rie https://github.com/aws/aws-lambda-runtime-interface-emulator/releases/latest/download/aws-lambda-rie
chmod +x /aws-lambda-rie/aws-lambda-rie
if [ -z "${AWS_LAMBDA_RUNTIME_API}" ]; then
    exec /aws-lambda-rie/aws-lambda-rie /bin/bash /flyway/lambda-entrypoint.sh $@

else
    /bin/bash /flyway/lambda-entrypoint.sh $@
fi

/entrypoint.sh Script


#!/bin/bash
set -e
function error() {
    ERROR="{\"errorMessage\" : \"$1\", \"errorType\" : \"InvalidFunctionException\"}"
    ERROR_URL=http://${AWS_LAMBDA_RUNTIME_API}/2018-06-01/runtime/init/error
    echo "error url is $ERROR_URL"
    curl -X POST "http://${AWS_LAMBDA_RUNTIME_API}/2018-06-01/runtime/init/error" -d "$ERROR" --header "Lambda-Runtime-Function-Error-Type: Unhandled"
    exit
}
function respond() {
    RESPONSE_URL="http://${AWS_LAMBDA_RUNTIME_API}/2018-06-01/runtime/invocation/$1/response"
    echo "response url is $RESPONSE_URL"
    curl -X POST "$RESPONSE_URL" -d "{}"
}

echo "Retreiving input from http://${AWS_LAMBDA_RUNTIME_API}/2018-06-01/runtime/invocation/next"
INPUT_URL=http://${AWS_LAMBDA_RUNTIME_API}/2018-06-01/runtime/invocation/next
INPUT=$(curl -D /tmp/headers.txt $INPUT_URL)
echo "Received input: $INPUT"
REQUEST_ID=$(cat /tmp/headers.txt | grep 'Lambda-Runtime-Aws-Request-Id' | sed -n 's/^.*: \([[:alnum:]-]*\).*/\1/p')
REQUEST_TYPE=$(echo $INPUT | jq -r '.RequestType')

if [ $REQUEST_TYPE == "Delete" ]
then
    respond $REQUEST_ID
fi
SECRET=$(aws secretsmanager get-secret-value --secret-id $MASTER_SECRET_ARN --version-stage AWSCURRENT --query 'SecretString' --output text --region ca-central-1) || error 'failed to retrieve master secret'
export FLYWAY_USER=$(echo $SECRET | jq -r '.username') || error 'failed to retrieve master secret username'
export FLYWAY_PASSWORD=$(echo $SECRET | jq -r '.password') || error 'failed to retrieve master secret password'
INFO=$(flyway info -X )
error "flyway info before migrate command"
error $INFO 
flyway migrate -X || error "failed to successfully execute 'flyway migrate' command"
INFO=$(flyway info -X )
error "flyway info after migrate command"
 error $INFO 
respond $REQUEST_ID

CDK Lambda Definition Script

const flywayLambda = new lambda.DockerImageFunction(this , 'FlywayFunction', {
        code: dockerImageAsset ,
        environment: {
         "MASTER_SECRET_ARN": props.pgSecretArn,
         "FLYWAY_URL": "jdbc:postgresql://" + secret.sHost + ":" +  secret.sPort.toString() + "/" + secret.sDB,
         "FLYWAY_LOCATIONS": "filesystem:./sql/postgresql",
         "FLYWAY_PLACEHOLDERS_DATABASE_SCHEMA":  "dbo",
         "FLYWAY_PLACEHOLDERS_DATABASE_NAME":  secret.sDB,
         "FLYWAY_PLACEHOLDERS_ENDPOINT":  secret.sHost,
         "FLYWAY_PLACEHOLDERS_PORT": secret.sPort.toString(),
         "FLYWAY_PLACEHOLDERS_REP_USER": secret.sUser , 
         "FLYWAY_PLACEHOLDERS_DSN": secret.sHost+"   port="+ secret.sPort.toString()+"  dbname="+secret.sDB+"  user="+ secret.sUser,
      },
      securityGroups: [securityGroupLambda],
      vpc: pgVpc,
      vpcSubnets: {
      subnets: pgSubnets      
      },
      memorySize: 512,
      timeout: cdk.Duration.minutes(10)
      });