CI/CD: Tenant DB Onboarding with AWS & Flyway

Introduction to the Control Plane Initiative
I recently worked on a project to create a SaaS solution by developing a Control Plane. This Control Plane plays a critical role in facilitating the onboarding of tenants as part of the pool deployment phase, where the tenants share some RDS instances with other tenants. This article covers just a segment of the tenant onboarding process that I will demonstrate.

Diverse Onboarding Steps Aligned with Business Logic
The process of onboarding tenants in this Control Plane is multifaceted, tailored to the unique requirements of different applications and underlying business logic. A common thread across most applications is the integration of a database, which necessitates the automated and secure creation of database entities for each new tenant.

Tackling the Challenge of Database Schema Automation
One of the key challenges faced in this project is the automation of database schema deployment. This step is essential for ensuring the database remains consistent and up-to-date throughout the application's development and lifecycle. There are several tools available, like Flyway and Liquibase, that help in addressing this challenge.

Guiding Through Database CI/CD Automation
This article is dedicated to walking you through the steps of automating database CI/CD, emphasizing the utilization of Flyway and AWS Codebuild, specifically in scenarios where Postgres is used on RDS.

Discover More About Flyway
For detailed information about Flyway, please refer to this link.

Creating a Dynamic Codebuild Project
I will also show how to create a Codebuild project that dynamically incorporates the connection string to the RDS instance. This includes handling database credentials and migration SQL scripts, ensuring the Codebuild project is effectively deployed in a VPC with access to the RDS instance.

Utilizing Flyway Docker in Build Specification
An important aspect of this setup is using the official Flyway docker image in the build spec.yaml file.

Visualizing the Process: The Architecture Diagram
An Architecture diagram will be included to provide a clearer understanding, illustrating the entire setup.

RDS Deployment: Ensure that RDS is deployed within a VPC.

Create Database: CREATE DATBASE demo;

Migration Scripts Storage: The SQL migration scripts can be stored in either Codebuild, GitHub, or optionally in an S3 bucket. If using an S3 bucket, the scripts are downloaded to Codebuild at runtime.

Codebuild Project Setup: The Codebuild project should be deployed in a VPC with access to the RDS instance. In this guide, I use AWS Typescript CDK for deployment and will showcase CDK code for building a Codebuild project.

Triggering Codebuild: You can trigger the Codebuild process through a source in CodeBuild or GitHub or by uploading migration script artifacts to an S3 bucket. For this article, the source of the migration scripts is an S3 bucket named db-migrations-bucket-demo12345. that has been uploaded for demonstration of the concept
Uploading Process: Migration Folder and Scripts

Migration Folder (migrations):

V1.0__initial_schema_setup.sql (Content)

V1.2__test_schema_update.sql (Content)

The provided sample code represents a CDK stack that takes Props as demonstrated in the following example:

AWS State Machine Integration: Implement an AWS State Machine to initiate the Codebuild process automatically during the initial onboarding of a tenant.

Codebuild and Codepipeline Utilization: Set up Codebuild in conjunction with Codepipeline to execute migration scripts each time new code is added to the database migration folder.

Environment Variables as Defined in the CodeBuild Setup

Code build log build output logs

After the code build is run the demo database is populated with new two tables

To wrap up, we've walked through automating database CI/CD with AWS Codebuild and Flyway, aiming to make tenant onboarding smoother. I hope you found this guide helpful! I’d love to hear your thoughts or any feedback you might have. Feel free to share your experiences or suggestions in the comments – let's keep the conversation going! 🚀👍