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)



└── src
    ├── 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



/docker
├── .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 SCHEMA IF NOT EXISTS myschema;

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

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

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

cd docker
docker-compose up -d


Verify that Flyway run

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

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

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";

}

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

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;
}

Constructor: Initializes the instance variables with the default values defined above.


- Test Connection Method

private boolean testConnection() {
    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

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();
    }
}

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

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;
}

}

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

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.");
    }
}

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

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

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

- Dependencies Section

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'

}

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

- Application Section

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.

./gradlew clean

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

./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.

./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)

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