Overcoming VPC Barriers: GitHub Actions, Flyway, and RDS Database Schema Migrations Made Easy

Published on June 28, 2024 | Written by Andreas

Using Flyway with GitHub Actions for RDS database migrations offers automated, version-controlled database schema updates. This combination ensures consistent and repeatable deployments across different environments, reducing manual errors and improving reliability. It also integrates database changes seamlessly into your CI/CD pipeline, allowing for better tracking and management of database schema evolution alongside your application code.

The problem: GitHub-hosted runners cannot connect to RDS database instances

GitHub Actions typically run on GitHub-hosted runners with internet access. However, this setup can complicate database migrations, particularly for RDS instances. RDS databases are usually placed in a VPC, a private network, making them inaccessible from the public internet. Consequently, executing database migrations directly from GitHub Actions becomes challenging.

What’s a simple and reliable solution to the problem?

The solution: Deploy self-hosted runners into a VPC to access RDS database instances

A straightforward approach to resolve this issue is to set up a self-hosted runner within the same VPC as the RDS database instance. This configuration enables GitHub Actions to communicate with the RDS database through a private network connection, facilitating the execution of database migrations.

But how to deploy a self-hosted runner on AWS?

HyperEnv for GitHub Actions Runner

We’ve crafted a solution to deploy self-hosted runners on AWS with ease. Our approach launches on-demand EC2 instances acting as ephemeral runners for GitHub Actions, a highly scalable and cost-efficient solution.

How to deploy HyperEnv to your AWS account?

For more detailed instructions, please refer to the HyperEnv setup guide.

1. Go to AWS Marketplace and subscribe to HyperEnv for GitHub Actions Runner.
2. Select the Fulfilment Option named Bring Your Own VPC.
3. Use the provided CloudFormation template to set up necessary AWS resources.
4. Configure stack details, including naming the stack (recommended: hyperenv-github-actions).
5. Set required parameters like VpcId, SubnetIds, and KeyPair.
6. Review and acknowledge that CloudFormation might create IAM resources.
7. Wait for the stack creation to complete (status: CREATE_COMPLETE).
8. Install GitHub App, as HyperEnv requires a private GitHub app to receive webhook events and register runners.

The next step is to configure Flyway and execute actions via GitHub.

Execute Flyway data migrations with GitHub Actions

Flyway is a popular open-source database migration tool. It allows developers to version control and manage database schema changes across different environments. Flyway supports various database systems and can be integrated into build processes, making it easier to maintain consistent database structures throughout the development lifecycle.

• The workflow rds-migration-flyway starts when a tag starting with v is pushed.
• The workflow runs on HyperEnv, and therefore on a self-hosted runner in the same VPC as the RDS instance.
• The job first checks out the repository using the actions/checkout@v4 action.
• It then uses the Flyway Docker image (redgate/flyway:10) to execute the migration.
• The migration command is configured with several arguments:
  • The location of migration scripts is set to a specific directory in the repository.
  • The database URL, user, and password are supplied using GitHub secrets for security.

name: rds-migration-flyway
on:
  workflow_dispatch:
  push:
    tags: 'v*'
jobs:
  rds-migration-flyway:
    runs-on: hyperenv
    steps:
    - uses: actions/checkout@v4
    - name: migrate
      uses: docker://redgate/flyway:10
      with:
        args: >-
          migrate
          -locations="filesystem:/github/workspace/migrations/"
          -url="${{ secrets.FLYWAY_URL }}"
          -user="${{ secrets.FLYWAY_USER }}"
          -password="${{ secrets.FLYWAY_PASSWORD }}"          

The SQL scripts for the database migrations should be stored in the repository under /migrations. For example, migrations/V1__create_book_table.sql includes a script to create a new table.

create table BOOK (
    ID int not null,
    NAME varchar(100) not null,
    AUTHOR varchar(100) not null
);

Check out widdix/hyperenv-examples for a working example.

Summary

This blog post discusses the challenge of executing RDS database migrations using GitHub Actions due to VPC restrictions, and presents HyperEnv as a solution. HyperEnv deploys self-hosted runners within the same VPC as the RDS instance, enabling secure database migrations through GitHub Actions. The post outlines the setup process for HyperEnv and provides a sample GitHub workflow using Flyway for database migrations. By integrating Flyway with GitHub Actions and HyperEnv, teams can achieve automated, version-controlled database schema updates as part of their CI/CD pipeline. Try HyperEnv today to streamline your RDS database migrations and enhance your DevOps workflow!