Customers across industries and verticals deal with relational database code deployment. In most cases, developers rely on database administrators (DBAs) to perform the database code deployment. This works well when the number of databases and the amount of database code changes are low. As organizations scale, however, they deal with different database engines—including Oracle, SQL Server, PostgreSQL, and MySQL—and hundreds or even thousands of databases. This creates a huge load on the DBAs to deploy, track, and perhaps roll back the database changes as and when developers need them to. Moreover, waiting for DBAs to make changes for the developers in an agile environment creates a bottleneck.
In this blog post, I will walk through an example of building a cost effective, database-independent solution in AWS using open source tools Liquibase and Jenkins to solve these database problems. I will not take a dive deep into how Liquibase or Jenkins work; rather, I will focus on how to deploy these open source tools on AWS services Amazon Elastic Compute Cloud (Amazon EC2), AWS CodeCommit, AWS Secrets Manager, and Amazon Simple Email Service (Amazon SES) to achieve the desired goal.
Liquibase is database-independent library for tracking, managing, and applying database schema changes that allows easier tracking of database changes. You can easily define changes in SQL, XML, JSON, or YAML.
Jenkins is an open source automation server that enables developers to reliably build, deploy, and roll back code changes. Jenkins can be installed in any machine with a Java Runtime Environment (JRE) installed.
For the purpose of this walkthrough, you will need the following:
- AWS account
- Amazon Aurora RDS database (PostgreSQL)
- Database user that can connect to the Amazon Aurora RDS database and create/drop table
- Local machine where the Git client is installed and configured to connect to the CodeCommit repository and push the database code
Let’s walk through an example in which I build this solution using an Amazon EC2 instance running on Amazon Linux AMI to deploy database schema changes on an Amazon Aurora PostgreSQL RDS database.
Step 1: Store the database credentials in the AWS Secrets Manager
In the prerequisites section, I mentioned the need for a database user to deploy database changes. This database user will be used to deploy and roll back the schema changes to and from the database. I will use the Secrets Manager to store these database user credentials. A Jenkins job will be configured to retrieve them as and when needed using the AWS Command Line Interface (AWS CLI).
In the example below, I store the credentials of my RDS Aurora database named dbdevopsaurora.
1. In the AWS console navigate to AWS Secrets Manager, Store a new Secret.
2. Select the secret type as Credentials for RDS database and then enter the database username and password.
3. Select Next and fill in the Secret name and Description fields.
4. Select Next and then Enable Automatic rotation (optional, but recommended).
5. Select Next and Save.
Step 2: Set up a CodeCommit repository
To create a CodeCommit repository:
1. Open the CodeCommit console.
2. In the region selector, choose the AWS Region where you want to create the repository: Us-east-1.
3. On the Repositories page, choose Create repository.
4. On the Create repository page, enter a Repository name: DBDevopsDemoRepo.
5. In Description, enter a description for the repository: This repo is for Demo of DB deployment automation.
6. Optional step: Choose Add tag to add one or more repository tags (a custom attribute label that helps you organize and manage your AWS resources) to your repository.
Step 3: Launch the EC2 instance to host Jenkins and Liquibase
For the build environment, I will launch an Amazon EC2 instance running on Amazon Linux AMI to host Liquibase, Jenkins, and other packages needed for this solution.
Install and configure Jenkins, Java, Nginx, jq, Git
Connect to your instance using your private key and switch to the root user. First, let’s update the repositories and install Jenkins, Nginx, Git, Java, and jq.
To install Jenkins on Amazon Linux, we need to add the Jenkins repository and install Jenkins from there:
wget -O /etc/yum.repos.d/jenkins.repo http://pkg.jenkins-ci.org/redhat/jenkins.repo rpm --import http://pkg.jenkins-ci.org/redhat/jenkins-ci.org.key yum install -y jenkins yum install -y java yum install -y nginx1 yum install -y jq yum install -y git-all
As Jenkins typically uses port TCP/8080, we’ll configure Nginx as a proxy. Edit the Nginx config file (/etc/nginx/nginx.conf) and change the server configuration to look like this:
Jenkins typically uses port TCP/8080 open that port in the security group to those machines that will need access.
Start the Jenkins and Nginx services and make sure they are configured to run even after reboot:
service jenkins start service nginx start chkconfig jenkins on chkconfig nginx on
Configure the Git on EC2 instance
Make sure you use your own email address:
sudo -u jenkins git config --global credential.helper '!aws codecommit credential-helper $@‘ sudo -u jenkins git config --global credential.useHttpPath true sudo -u jenkins git config --global user.email "email@example.com" sudo -u jenkins git config --global user.name (http://user.name/) "MyJenkinsServer"
Point your browser to the public DNS name of your EC2 instance (for example, http://ec2-54-221-39-132.compute-1.amazonaws.com/) and you should be able to see the Jenkins home page and the instructions to unlock:
On the Customize Jenkins page, choose Install suggested plugins.
Wait until Jenkins installs all the suggested plugins. When the process completes, check marks will display beside installed plugins.
On the Create First Admin User page, enter a user name, password, full name, and email address for the Jenkins user.
Choose Save and continue, Save and finish, and Start using Jenkins.
Download and install Liquibase on EC2 instance
Download the Liquibase Community version as a .zip folder. Log on to the EC2 instance using Putty or any other SSH tool and move to the Jenkins installation directory cd /var/lib/Jenkins.
Create a folder called liquibase and copy the contents of the Liquibase .zip folder into this folder. The directory should look like this:
Download and install JDBC driver on EC2 instance
Install the JDBC driver for the Liquibase to connect to the RDS database. In this example we are using Aurora PostgreSQL, so we will need a PostgreSQL JDBC driver.
Download the JDBC JAR file and place it in the Liquibase folder. The directory should look like this:
Create Bash shell scripts for deployment and rollback
Jenkins will be calling these shell scripts in the background. These Bash shell scripts call the secrets manager via IAM roles assigned to the EC2 instance to fetch the database credentials, run the Liquibase utility, and deploy and roll back changes.
1. Create the script for deployment. Please note that this script is specific for PostgreSQL. Change the driver and classpath as appropriate for a different database. Use an editor such as vi to create this file.
cd /var/lib/jenkins vi callLiquibaseDemoDeployment.sh export lquser=`aws secretsmanager get-secret-value --secret-id dbdevopsAuroraCreds --region us-east-1 | jq --raw-output .SecretString | jq -r ."username"` export lqpassword=`aws secretsmanager get-secret-value --secret-id dbdevopsAuroraCreds --region us-east-1 | jq --raw-output .SecretString | jq -r ."password"` export hostname=`aws secretsmanager get-secret-value --secret-id dbdevopsAuroraCreds --region us-east-1 | jq --raw-output .SecretString | jq -r ."host"` export portnumber=`aws secretsmanager get-secret-value --secret-id dbdevopsAuroraCreds --region us-east-1 | jq --raw-output .SecretString | jq -r ."port"` bash $JENKINS_HOME/liquibase/liquibase --changeLogFile=$1 --url=jdbc:postgresql://$hostname:$portnumber/devopsdb --username=$lquser --password=$lqpassword --driver=org.postgresql.Driver --classpath=$JENKINS_HOME/liquibase/postgresql-42.2.8.jar update
Then save this script and exit the editor.
2. Create the script for rollback using Count. Please note that this script is specific for PostgreSQL. Change the driver and classpath as appropriate for a different database.
cd /var/lib/jenkins vi callLiquibaseDemoRollback.sh export JENKINS_HOME=/var/lib/jenkins/ export lquser=`aws secretsmanager get-secret-value --secret-id dbdevopsAuroraCreds --region us-east-1 | jq --raw-output .SecretString | jq -r ."username"` export lqpassword=`aws secretsmanager get-secret-value --secret-id dbdevopsAuroraCreds --region us-east-1 | jq --raw-output .SecretString | jq -r ."password"` export hostname=`aws secretsmanager get-secret-value --secret-id dbdevopsAuroraCreds --region us-east-1 | jq --raw-output .SecretString | jq -r ."host"` export portnumber=`aws secretsmanager get-secret-value --secret-id dbdevopsAuroraCreds --region us-east-1 | jq --raw-output .SecretString | jq -r ."port"` bash $JENKINS_HOME/liquibase/liquibase --changeLogFile=$1 --url=jdbc:postgresql://$hostname:$portnumber/devopsdb --username=$lquser --password=$lqpassword --driver=org.postgresql.Driver --classpath=$JENKINS_HOME/liquibase/postgresql-42.2.8.jar rollbackCount $2
Then save this script and exit the editor.
Step 3: Set up IAM role for an EC2 instance
Create an IAM role for an EC2 instance to access the CodeCommit repository and the secrets manager.
1. Create role JenkinsEC2DevopsRole.
2. Attach the AWS policy AWSCodeCommitPowerUser to the role.
3. Create a custom policy SecretsManagerRead to read the secrets from the secrets manager and attach it to the role. The JSON for this custom policy would look like this:
Attach the role JenkinsEC2DevopsRole to the EC2 instance running Jenkins.
Step 4: Set up an SMTP server for Jenkins using SES
1. Log on to the AWS console for SES and select the Region as us-east-1.
2. Navigate to SMTP settings and click on Create My SMTP Credentials. This will download the credentials to the local machine; the next steps require the contents of this file.
3. Log in to Jenkins through a browser and navigate to Manage Jenkins, Configure System.
4. Enter the email address of your choice in the System Admin e-mail address, which will be used for sending Jenkins notifications. This email address should be a verified email address from SES. Refer to the documentation on how to verify an email address in SES.
5. Enter the SMTP server name under Email Notification (for example email-smtp.us-east-1.amazonaws.com from step 4.2).
6. Click the Advanced button and then click the checkbox next to the Use SMTP Authentication option. Then set the following fields:
- Username: Username from the downloaded SMTP credentials (from step 4.2)
- Password: Password from downloaded SMTP credentials (from step 4.2)
- Use SSL: Uncheck this checkbox
- SMTP Port: 587
6. Check the email notification functionality by clicking the checkbox next to the Test configuration by sending Test e-mail recipient option. Enter a valid email id and click the Test configuration button to see if you receive a test message in the configured mail box. Note that the recipient email address should be verified by SES for this to work. Refer to the documentation on how to verify an email address in SES.
Step 5: Push the database code to CodeCommit repository
In the prerequisites, I mentioned a local machine that can connect to the CodeCommit repository through a Git client. I do not want to elaborate on that topic, which is covered in many other blog posts. Refer to the CodeCommit documentation to learn how to push files to a CodeCommit repository.
For Liquibase, the changes can be specified in SQL, XML, and JSON formats. In this example, I will walk through the SQL format. The screenshot below shows a Liquibase-formatted SQL file pushed into the master branch of repository DBDevopsDemoRepo. The file name is changeset.sql. This file is holding the code for deployment into the database.
How this file is formatted for Liquibase
Liquibase-formatted SQL files use comments to provide Liquibase with metadata. Each SQL file must begin with the following comment:
Each changeset in a formatted SQL file begins with the following comment:
changeset.sql you can see the author is public and an arbitrary ID of 3 is used to designate the changeset number. This ID should be unique. These values help with tracking the changes in the database.
Then comes the DDL to deploy, which is:
create table test3 ( id int primary key, name varchar(255) );