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

Nathan (Nursultan) Bekenov - Jul 6 - - Dev Community

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

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

Verify that Flyway run

docker ps -a
docker logs <container-id-or-name> --tail 20
Enter fullscreen mode Exit fullscreen mode

Image description

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

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";
}
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 
    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 
    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 
    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 
    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 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.");
        }
    }
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 
plugins {
    id 'java'
    id 'groovy'
    id 'application'
}
Enter fullscreen mode Exit fullscreen mode

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'
}
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 
application {
    mainClass = 'com.example.DatabaseMigrationHandler'
}
Enter fullscreen mode Exit fullscreen mode

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

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

./gradlew build
Enter fullscreen mode Exit fullscreen mode

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

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

Image
Building a RESTful API with Node.js and Express
Image
Introduction to Python for Beginners
Image
Building a Dynamic Website with PHP and MySQL
Image
Mastering JavaScript: From Basics to Advanced Concepts
Image
Web Development Fundamentals: HTML, CSS, and JavaScript
Image
Enhancing User Experiences with AI Voice Assistants

ai, chatgpt, voicebot, voiceui
Image
Understanding Clean Code: Boundaries ⚡

cleancode, softwaredevelopment, softwareengineering, javascript
Image
What is the best diet plan? To lose belly fat, which diet plan is best?
Image
Cargo Skirt: A Combination of Function and Form

cargoskirt, wildskirts
Image
The Power of MVP: Turning Ideas into Reality with Minimum Viable Products

mvp, ideation, webdev, development
Image
Introduction to Python for Beginners
Image
Building a Dynamic Website with PHP and MySQL
Image
Mastering JavaScript: From Basics to Advanced Concepts
Image
Web Development Fundamentals: HTML, CSS, and JavaScript
Image
The Ultimate Guide To Data Engineering
Image
How Artificial Intelligence Works in Computer Vision

ai
Image
Leveraging IRSA with AWS SES To Send Emails from EKS Pods

kubernetes, security, aws, devops
Image
Code as Data

productivity, lowcode
Image
Advice for people getting started with Knowledge Management and Writing

writing, pkm
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player