TheLazy.Dev
Portfolio GitHub

Blog

devops |aws |java |database

Run Flyway DB migrations with AWS Lambda and RDS - Part 1

nbekenov

Nathan (Nursultan) Bekenov

Posted on July 6, 2024

Run Flyway DB migrations with AWS Lambda and RDS - Part 1

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

Image description

Here is what we are going to do:

Part 1 - Create local setup

  1. Initialize project
  2. Docker image for PostgreSQL and Flyway so we can test our code
  3. Write Java class that will run Flyway Migrations in our docker container

Part 2 - Deploy in AWS

  1. Create AWS Lambda using Terraform
  2. Update Java class and deploy code in Lambda
  3. Configure access from Lambda to RDS (no DB password is needed)
  4. Make some conclusions

Initialize project



└── src
    ├── main
        ├── java
        │   └── com
        │       └── example
        │           └── DatabaseMigrationHandler.java
        └── resources
            └── db
                └── migration
                    └── V1__Create_table.sql
Enter fullscreen mode Exit fullscreen mode
  • 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 



/docker
├── .env.pg_admin
├── README.md
├── docker-compose.yml
└── init
    └── create_schemas.sql
Enter fullscreen mode Exit fullscreen mode

  • 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 SCHEMA IF NOT EXISTS myschema;
Enter fullscreen mode Exit fullscreen mode
  • Create new file .env.pg_admin inside docker folder - this file contains values for env variables for one of the docker containers


PGADMIN_DEFAULT_EMAIL=user@domain.com
PGADMIN_DEFAULT_PASSWORD=mysecretpassword
Enter fullscreen mode Exit fullscreen mode
  • And finally create docker-compose.yml inside docker folder


version: '3.1'

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
Enter fullscreen mode Exit fullscreen mode

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: ```

-url=jdbc:postgresql://db:5432/postgres: JDBC URL to connect to the PostgreSQL database.
-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
Enter fullscreen mode Exit fullscreen mode

cd docker
docker-compose up -d


Verify that Flyway run
Enter fullscreen mode Exit fullscreen mode

docker ps -a
docker logs --tail 20


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zthp2pk2xcwgahfca4ad.png)


---

**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](https://github.com/nbekenov/flyway-lambda/blob/local-setup/src/main/java/com/example/DatabaseMigrationHandler.java)

- Package and Imports
Enter fullscreen mode Exit fullscreen mode

package com.example;

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
Enter fullscreen mode Exit fullscreen mode

public class DatabaseMigrationHandler {
// 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";
Enter fullscreen mode Exit fullscreen mode

}

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
Enter fullscreen mode Exit fullscreen mode 
public DatabaseMigrationHandler() {
    this.dbHost = DB_HOST;
    this.dbPort = DB_PORT;
    this.dbName = DB_NAME;
    this.dbSchema = DB_SCHEMA;
    this.dbUser = DB_USER;
    this.dbPassword = DB_PASSWORD;
}
Enter fullscreen mode Exit fullscreen mode 
Constructor: Initializes the instance variables with the default values defined above.


- Test Connection Method
Enter fullscreen mode Exit fullscreen mode 
private boolean testConnection() {
    try (Connection connection = getDataSource().getConnection()) {
        return connection != null;
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }
}
Enter fullscreen mode Exit fullscreen mode 
testConnection Method: Attempts to establish a connection to the database. Returns true if successful, otherwise logs the exception and returns false.


- Run Migrations Method
Enter fullscreen mode Exit fullscreen mode 
private void runMigrations() {
    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();
    }
}
Enter fullscreen mode Exit fullscreen mode 
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
Enter fullscreen mode Exit fullscreen mode 
private AwsWrapperDataSource getDataSource() {
    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;
}
Enter fullscreen mode Exit fullscreen mode

}

getDataSource Method: Configures the data source using [AwsWrapperDataSource](https://github.com/aws/aws-advanced-jdbc-wrapper/blob/main/docs/using-the-jdbc-driver/DataSource.md) to connect to the PostgreSQL database. It sets the necessary properties such as server name, database name, port, user, and password.

- Main method
Enter fullscreen mode Exit fullscreen mode 
public static void main(String[] args) {
    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.");
    }
}
Enter fullscreen mode Exit fullscreen mode 
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
Enter fullscreen mode Exit fullscreen mode

plugins {
id 'java'
id 'groovy'
id 'application'
}

application Plugin: Facilitates the creation of Java applications and provides tasks for running the application

- Dependencies Section
Enter fullscreen mode Exit fullscreen mode

dependencies {
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'
Enter fullscreen mode Exit fullscreen mode

}

implementation: Declares dependencies required to compile and run the application. Here, flyway-core, postgresql, and aws-advanced-jdbc-wrapper are included.

- Application Section
Enter fullscreen mode Exit fullscreen mode

application {
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.
Enter fullscreen mode Exit fullscreen mode

./gradlew clean

clean: Deletes the build directory, effectively cleaning the project. This is useful for ensuring a fresh build environment.
Enter fullscreen mode Exit fullscreen mode

./gradlew build

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.
Enter fullscreen mode Exit fullscreen mode

./gradlew run

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r0gowkqdscmc6x8ek5lg.png)
Enter fullscreen mode Exit fullscreen mode
💖 💪 🙅 🚩
nbekenov
Nathan (Nursultan) Bekenov

Posted on July 6, 2024

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

Run Flyway DB migrations with AWS Lambda and RDS - Part 1
devops Run Flyway DB migrations with AWS Lambda and RDS - Part 1

July 6, 2024