Usually there is a need to run SQL database updates: update table columns, add new rows, create a new schema etc. Often developer teams are using Flyway It is an open-source database SQL deployment tool. In Flyway, all DDL and DML changes to the database are called migrations. Migrations can be versioned or repeatable.
If RDS cluster is in private subnet how then you are going to automate these DB migrations?
One of the solutions is to use AWS Lambda in the same VPC that will have flyway run against DB
Here is what we are going to do:
Part 1 – Create local setup
Initialize project
Docker image for PostgreSQL and Flyway so we can test our code
Write Java class that will run Flyway Migrations in our docker container
Part 2 – Deploy in AWS
Create AWS Lambda using Terraform
Update Java class and deploy code in Lambda
Configure access from Lambda to RDS (no DB password is needed)
Make some conclusions
Initialize project
create new java project using gradle init
you src folder should like this (Example https://github.com/nbekenov/flyway-lambda/tree/local-setup)
├── main
├── java
│ └── com
│ └── example
│ └── DatabaseMigrationHandler.java
└── resources
└── db
└── migration
└── V1__Create_table.sql
our SQL migration scripts will be stored in src/resources/db/migration folder
our main java class will be in DatabaseMigrationHandler.java (you can name you package the way you want – I named it com.example)
Docker Compose Setup for Local Development
In this setup, we are using Docker Compose to create a local environment for testing database migrations using Flyway and PostgreSQL. If you want you can skip explanation and get to git repo with the code
├── .env.pg_admin
├── README.md
├── docker-compose.yml
└── init
└── create_schemas.sql
Create docker folder.
Create init folder inside docker folder
In init folder create new file create_schemas.sql. This file will be used for initialization and creating our DB schema.
Create new file .env.pg_admin inside docker folder – this file contains values for env variables for one of the docker containers
PGADMIN_DEFAULT_PASSWORD=mysecretpassword
And finally create docker-compose.yml inside docker folder
services:
db:
image: postgres
restart: always
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: mysecretpassword
volumes:
– ./local-data:/var/lib/postgresql/data
– ./init:/docker-entrypoint-initdb.d # init scripts are executed upon DB container startup
ports:
– 5432:5432
flyway:
image: flyway/flyway
depends_on:
– db
volumes:
– ../src/main/resources/db/migration:/flyway/sql
command: -url=jdbc:postgresql://db:5432/postgres -schemas=myschema -user=postgres -password=mysecretpassword -connectRetries=60 migrate
pg_admin:
image: dpage/pgadmin4
depends_on:
– db
env_file:
– .env.pg_admin
ports:
– 80:80
volumes:
local-data:
external: false
We define three services: db, flyway, and pg_admin.
Database Service (db)
Environment Variables: Sets the PostgreSQL user and password.
Volumes:
./local-data:/var/lib/postgresql/data: Maps a local directory to the PostgreSQL data directory to persist data.
./init:/docker-entrypoint-initdb.d: Maps a local directory to the directory where PostgreSQL looks for initialization scripts.
Flyway Service (flyway)
Depends_on: Ensures that the db service starts before the Flyway service.
Volumes: Maps the local directory containing SQL migration scripts to Flyway’s expected location.
Command: Provides Flyway with the necessary parameters to connect to the database and run the migrations:
-schemas=myschema: Specifies the schema to migrate.
-user=postgres and -password=mysecretpassword: Database credentials.
-connectRetries=60: Retries the connection for up to 60 seconds if the database is not immediately available.
migrate: Command to run the migrations.
pgAdmin Service (pg_admin)
Depends_on: Ensures the db service starts before pgAdmin.
Env_file: Loads environment variables from a .env.pg_admin file to configure pgAdmin.
Ports: Maps port 80 on the host to port 80 in the container to access pgAdmin through a web browser.
Start containers
docker-compose up -d
Verify that Flyway run
docker logs <container-id-or-name> –tail 20
Write Java class
In this section, we’ll dive into the Java class DatabaseMigrationHandler that is designed to run Flyway migrations against a local PostgreSQL database set up in a Docker container. This class encapsulates all the necessary logic to establish a database connection, test the connection, and execute the migrations.
If you want you can skip explanation and get to git repo with the code
Package and Imports
import org.flywaydb.core.Flyway;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Objects;
import software.amazon.jdbc.PropertyDefinition;
import software.amazon.jdbc.ds.AwsWrapperDataSource;
Package Declaration: The class is part of the com.example package.
Imports: Necessary classes from the Flyway library, Java SQL package, and AWS JDBC wrapper for handling database connections are imported
Class and Instance Variables
// instance vars
private final String dbHost;
private final String dbPort;
private final String dbName;
private final String dbSchema;
private final String dbUser;
private final String dbPassword;
private static final String DB_HOST = “localhost”;
private static final String DB_PORT = “5432”;
private static final String DB_NAME = “postgres”;
private static final String DB_SCHEMA = “myschema”;
private static final String DB_USER = “postgres”;
private static final String DB_PASSWORD = “mysecretpassword”;
}
Instance Variables: These store the database connection details such as host, port, name, schema, user, and password.
Static Constants: Default values for the database connection details are defined as static constants.
Constructor
this.dbHost = DB_HOST;
this.dbPort = DB_PORT;
this.dbName = DB_NAME;
this.dbSchema = DB_SCHEMA;
this.dbUser = DB_USER;
this.dbPassword = DB_PASSWORD;
}
Constructor: Initializes the instance variables with the default values defined above.
Test Connection Method
try (Connection connection = getDataSource().getConnection()) {
return connection != null;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
testConnection Method: Attempts to establish a connection to the database. Returns true if successful, otherwise logs the exception and returns false.
Run Migrations Method
try{
Flyway flyway = Flyway.configure()
.dataSource(getDataSource())
.schemas(this.dbSchema. )
.load();
flyway.migrate();
System.out.println(“Completed Database migration!”);
} catch (Exception e) {
System.out.println(“Database migration failed!”);
e.printStackTrace();
}
}
runMigrations Method: Configures and runs Flyway migrations. It uses the Flyway class to set up the data source and schema, then initiates the migration process.
Data Source Configuration
Properties targetDataSourceProps = new Properties();
targetDataSourceProps.setProperty(“ssl”, “false”);
targetDataSourceProps.setProperty(“password”, this.dbPassword);
AwsWrapperDataSource ds = new AwsWrapperDataSource();
ds.setJdbcProtocol(“jdbc:postgresql:”);
ds.setTargetDataSourceClassName(“org.postgresql.ds.PGSimpleDataSource”);
ds.setServerName(this.dbHost);
ds.setDatabase(this.dbName);
ds.setServerPort(this.dbPort);
ds.setUser(this.dbUser);
ds.setTargetDataSourceProperties(targetDataSourceProps);
return ds;
}
}
getDataSource Method: Configures the data source using AwsWrapperDataSource to connect to the PostgreSQL database. It sets the necessary properties such as server name, database name, port, user, and password.
Main method
DatabaseMigrationHandler handler = new DatabaseMigrationHandler();
if (handler.testConnection()) {
System.out.println(“Database connection successful!”);
handler.runMigrations();
} else {
System.out.println(“Failed to connect to the database.”);
}
}
main Method: The entry point of the application. It creates an instance of DatabaseMigrationHandler, tests the database connection, and runs the migrations if the connection is successful.
Explanation of the build.gradle
In this section, we’ll go through the build.gradle file, which is used to configure the build process for your Java project. We’ll also cover some useful Gradle commands for building and running your project.
Plugins Section
id ‘java’
id ‘groovy’
id ‘application’
}
application Plugin: Facilitates the creation of Java applications and provides tasks for running the application
Dependencies Section
implementation ‘org.flywaydb:flyway-core:9.22.3’
implementation ‘org.postgresql:postgresql:42.7.2’
implementation ‘software.amazon.jdbc:aws-advanced-jdbc-wrapper:2.3.0’
testImplementation platform(‘org.junit:junit-bom:5.10.0’)
testImplementation ‘org.junit.jupiter:junit-jupiter’
}
implementation: Declares dependencies required to compile and run the application. Here, flyway-core, postgresql, and aws-advanced-jdbc-wrapper are included.
Application Section
mainClass = ‘com.example.DatabaseMigrationHandler’
}
mainClass: Specifies the main class of the application, which is com.example.DatabaseMigrationHandler. This is the entry point when running the application.
Once you have your build.gradle file set up, you can use several Gradle commands to manage your project. These commands are executed from the command line.
clean: Deletes the build directory, effectively cleaning the project. This is useful for ensuring a fresh build environment.
build: Compiles the source code, runs tests, and packages the project into a JAR file. This command performs all the necessary steps to create a build artifact.
run: Executes the main class specified in the application section. In this case, it will run com.example.DatabaseMigrationHandler, which handles the Flyway migrations.
In the logs you should see that connection to DB was established and DB migrations run successfully.