logo
Menu
CI/CD: Tenant DB Onboarding with AWS & Flyway

CI/CD: Tenant DB Onboarding with AWS & Flyway

Explore the essentials of automating database CI/CD for tenant onboarding with AWS Codebuild and Flyway.

Published Dec 29, 2023

Introduction

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.

Database CI/CD
Database CI/CD

Prerequisites for Automated Database CI/CD

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
  • Step 1: Upload the Migration folder.
  • Step 2: Upload the necessary scripts.
Migration Folder (migrations):
Migrations
Migrations Folder
V1.0__initial_schema_setup.sql (Content)
1
2
3
4
5
CREATE TABLE IF NOT EXISTS master.client_types
(
id serial constraint client_types_pk primary key,
name text NOT NULL
);
V1.2__test_schema_update.sql (Content)
1
2
3
4
5
CREATE TABLE IF NOT EXISTS master.test
(
id serial constraint test_pk primary key,
name text NOT NULL
);
Migration S3
Migration Bucket
The provided sample code represents a CDK stack that takes Props as demonstrated in the following example:
  • environment: 'dev'
  • deploymentType: 'pool'
  • deploymentId: 'demo-deployment'
  • vpc: 'vpc-123456'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
import { Construct } from 'constructs';
import {

Stack,
StackProps,
aws_iam as iam,
aws_s3 as s3,
aws_ec2 as ec2,
aws_codebuild as codebuild,
} from 'aws-cdk-lib';

// example of the Props that pass to the ControlPlaneStackProps

//environment = 'dev'
//deploymentType = 'pool'
//deploymentId = 'demo-deployment'
//vpc = 'vpc-123456'

interface ControlPlaneStackProps extends StackProps {
readonly environment: string;
readonly deploymentId: string;
readonly deploymentType: string;
readonly vpc: ec2.IVpc;
}

export class ControlPlaneStack extends Stack {
constructor(scope: Construct, id: string, props: ControlPlaneStackProps) {
super(scope, id, props);
const stateMachineName = `${props.environment}-${props.deploymentId}-tenant-onboarding`;

const vpcId = ec2.Volume

// from vpc
const vpc = ec2.Vpc.fromLookup(this, 'vpc', {
vpcId: props.vpc.vpcId,
})

//Migration bucket that store migrations scripts.
const migrationBucket = new s3.Bucket(this, 'db-migrations-bucket-demo12345', {
versioned: true,
});

// create code build project
const codeBuildRole = new iam.Role(this, 'db-codebuild-role', {
assumedBy: new iam.ServicePrincipal('codebuild.amazonaws.com'),
roleName: `${props.environment}-${props.deploymentType}-${props.deploymentId}-db-codebuild-role`,
});

codeBuildRole.addToPolicy(
new iam.PolicyStatement({
effect: iam.Effect.ALLOW,
actions: ['codebuild:*'],
resources: [`*`],
})
);

const fnSg = new ec2.SecurityGroup(this, 'db-codebuild-sg', {
vpc: props.vpc,
allowAllOutbound: true,
securityGroupName: `${props.environment}-${props.deploymentType}-${props.deploymentId}-db-codebuild-sg`,
description: `${props.environment}-${props.deploymentType}-${props.deploymentId}-db-codebuild-sg`,
});

const project = new codebuild.Project(this, 'db-project', {
projectName: `${props.environment}-${props.deploymentType}-${props.deploymentId}-db-codebuild`,
cache: codebuild.Cache.local(codebuild.LocalCacheMode.DOCKER_LAYER),
vpc: props.vpc,
subnetSelection: {
subnetType: ec2.SubnetType.PRIVATE_WITH_EGRESS,
},
securityGroups: [fnSg],
environmentVariables: {
RDS_ENDPOINT: {
value: `${props.environment}-${props.deploymentType}-${props.deploymentId}-postgres-credentials:host`,
type: codebuild.BuildEnvironmentVariableType.SECRETS_MANAGER,
},
DB_NAME: {
value: 'you_can_override_me',
type: codebuild.BuildEnvironmentVariableType.PLAINTEXT,
},
DB_USER: {
value: 'you_can_override_me',
type: codebuild.BuildEnvironmentVariableType.PLAINTEXT,
},
DB_PASSWORD: {
value: 'you_can_override_me',
type: codebuild.BuildEnvironmentVariableType.PARAMETER_STORE,
},
DB_PORT: {
value: `${props.environment}-${props.deploymentType}-${props.deploymentId}-postgres-credentials:port`,
type: codebuild.BuildEnvironmentVariableType.SECRETS_MANAGER,
},
},

environment: {
buildImage: codebuild.LinuxBuildImage.STANDARD_7_0,
privileged: true,
},

buildSpec: codebuild.BuildSpec.fromObject({
version: '0.2',
env: {
variables: {
RDS_ENDPOINT: 'you_can_override_me',
DB_NAME: 'you_can_override_me',
DB_USER: 'you_can_override_me',
DB_PASSWORD: 'you_can_override_me',
DB_PORT: 'you_can_override_me',
},
},
phases: {
install: {
commands: [
'nohup /usr/local/bin/dockerd --host=unix:///var/run/docker.sock --host=tcp://127.0.0.1:2375 --storage-driver=overlay2 &',
"timeout 15 sh -c 'until docker info; do echo .; sleep 1; done'",
],
},
build: {
commands: [
'printenv',
'mkdir -p flyway/sql',
'ls -la',
'aws s3 ls',
`aws s3 ls s3://${migrationBucket.bucketName}`,
`aws s3 sync s3://${migrationBucket.bucketName}/migrations/ /flyway/sql/`,
'cd /flyway/sql/',
'pwd',
'ls -la',
'docker pull flyway/flyway',
'docker run -v $PWD:/flyway/sql flyway/flyway -url=jdbc:postgresql://$RDS_ENDPOINT:$DB_PORT/$DB_NAME -user=$DB_USER -password=$DB_PASSWORD -schemas=master -X -locations=filesystem:/flyway/sql -connectRetries=5 -outOfOrder=true -baselineOnMigrate=true migrate',
],
},
},
}),
});

project.addToRolePolicy(
new iam.PolicyStatement({
actions: ['ec2:DescribeRegions'],
effect: iam.Effect.ALLOW,
resources: ['*'],
})
);
// Allow provision project to get AWS regions.
// This is required for deployment information validation.
project.addToRolePolicy(
new iam.PolicyStatement({
actions: ['ec2:DescribeRegions'],
effect: iam.Effect.ALLOW,
resources: ['*'],
})
);

// Allow provision project to query ssm parameters.
project.addToRolePolicy(
new iam.PolicyStatement({
actions: ['ssm:GetParameter'],
effect: iam.Effect.ALLOW,
resources: [`arn:aws:ssm:us-west-2:111122223333:parameter/<paramnemes>/*`],
})
);
// Allow provision project to query ssm parameters.
project.addToRolePolicy(
new iam.PolicyStatement({
actions: [
'secretsmanager:GetResourcePolicy',
'secretsmanager:GetSecretValue',
'secretsmanager:DescribeSecret',
'secretsmanager:ListSecretVersionIds',
],
effect: iam.Effect.ALLOW,
resources: ['arn:aws:secretsmanager:us-west-2:111122223333:secret:aes128-1a2b3c'],
})
);
project.addToRolePolicy(
new iam.PolicyStatement({
actions: ['secretsmanager:ListSecrets'],
effect: iam.Effect.ALLOW,
resources: ['*'],
})
);
project.addToRolePolicy(
new iam.PolicyStatement({
actions: ['s3:ListBucket'],
effect: iam.Effect.ALLOW,
resources: ['arn:aws:us-west-2:111122223333{migrationBucket}'],
})
);
project.addToRolePolicy(
new iam.PolicyStatement({
actions: ['s3:*Object'],
effect: iam.Effect.ALLOW,
resources: ['arn:aws:us-west-2:111122223333{migrationBucket}'],
})
);

// Add other nessary permisons for codebuild if needed.
}
}

Execution Options for Code Build in Tenant On-Boarding Process

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
CodeBuild Env
CodeBuild Env
Code build log build output logs
1
2
3
4
5
[Container] 2023/12/24 01:11:52.987606 Phase complete: BUILD State: SUCCEEDED
[Container] 2023/12/24 01:11:52.987707 Phase context status code: Message:
[Container] 2023/12/24 01:11:53.024523 Entering phase POST_BUILD
[Container] 2023/12/24 01:11:53.026499 Phase complete: POST_BUILD State: SUCCEEDED
[Container] 2023/12/24 01:11:53.026514 Phase context status code: Message:
After the code build is run the demo database is populated with new two tables
DB
db
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! šŸš€šŸ‘

Comments