Database Migration with Flyway and Micronaut Data JDBC

Micronaut Data

Micronaut Data is a database access toolkit that uses Ahead of Time (AoT) compilation to pre-compute queries for repository interfaces that are then executed by a thin, lightweight runtime layer.

Micronaut Data is inspired by GORM and Spring Data, however improves on those solutions in the following ways:

No runtime model - Both GORM and Spring Data maintain a runtime metamodel that uses reflection to model relationships between entities. This model consumes significant memory and memory requirements grow as your application size grows. The problem is worse when combined with Hibernate which maintains its own metamodel as you end up with duplicate meta-models.

No query translation - Both GORM and Spring Data use regular expressions and pattern matching in combination with runtime generated proxies to translate a method definition on a Java interface into a query at runtime. No such runtime translation exists in Micronaut Data and this work is carried out by the Micronaut compiler at compilation time.

No Reflection or Runtime Proxies - Micronaut Data uses no reflection or runtime proxies, resulting in better performance, smaller stack traces and reduced memory consumption due to a complete lack of reflection caches (Note that the backing implementation, for example Hibernate, may use reflection).

Type Safety - Micronaut Data will actively check at compile time that a repository method can be implemented and fail compilation if it cannot.

Flyway

Database migrations are a important part of the software development process, especially in environments where continuous integration and delivery are practiced. As applications evolve, so does the structure and schema of the databases they rely on. Managing these changes manually can be error-prone and time-consuming. This is where Flyway comes in. Flyway is an open-source tool designed to simplify database migrations. It provides version control for your database so you can migrate your database schema safely and reliably. This article will guide you through automating database migrations in Java applications using Flyway, making sure your database changes are manageable and less prone to errors.

Key Features of Flyway

Flyway offers several features that make it an attractive choice for managing database migrations:

Version Control for Databases: Flyway keeps track of which migrations have been applied, ensuring that only the necessary changes are applied to each environment. This version control mechanism helps in maintaining consistency across development, testing, and production environments.

Support for Multiple Databases: Flyway supports a wide range of databases, making it a versatile tool for projects that involve different database technologies.

Ease of Use: With Flyway, migrations can be written in SQL, the native language of databases, or in Java for more complex migrations. This flexibility allows developers to use the most appropriate language for each migration task.

Integration with Build Tools: Flyway integrates seamlessly with popular build tools like Maven and Gradle, making it easy to incorporate database migrations into the project’s build process.
Community and Enterprise Support: Flyway offers both a Community version, which is open-source and free to use, and an Enterprise version, which provides additional features and support for larger, more complex projects.

Creating project

To generate a professional and production-ready Micronaut application tailored to your needs, you can leverage the Micronaut Launch tool.

Micronaut Launch simplifies project setup by allowing you to configure dependencies, select build tools, and choose your preferred programming language and application settings. It ensures a seamless start to your development process with a well-structured, optimized project skeleton that aligns with best practices.

mn create-app --build=gradle_kotlin --jdk=21 --lang=java --test=junit --features=lombok,data-jdbc,swagger-ui,openapi,serialization-jackson,flyway,micronaut-aop,graalvm,postgres schoolstaff.perk

After creating the project, we can integrate the Flyway plugin to handle database migrations manually. This approach ensures that migrations are not automatically executed during application startup, providing greater control over the migration process.

To achieve this, include the Flyway plugin in your project as follows:

buildscript {
    dependencies {
        classpath("org.flywaydb:flyway-database-postgresql:11.1.0") // This is required for the flyway plugin to work on the migration, otherwise it will throw an error as No Database found
    }
}
plugins {
    //.. Others
    id("org.flywaydb.flyway") version "11.0.1"
}

In the application.properties file of the Micronaut application, configure the following property to false to prevent Flyway migrations from being automatically applied during application startup:

# Run migrations manually (setting false)
flyway.datasources.default.enabled=false

Ensure the following dependencies are included in your project to enable Flyway to work with PostgreSQL:

dependencies {
    annotationProcessor("io.micronaut.data:micronaut-data-processor")

    implementation("io.micronaut.data:micronaut-data-jdbc")
    implementation("org.flywaydb:flyway-core:11.1.0")
    implementation("io.micronaut.flyway:micronaut-flyway")

    compileOnly("jakarta.persistence:jakarta.persistence-api")

    runtimeOnly("ch.qos.logback:logback-classic")
    runtimeOnly("org.postgresql:postgresql")
    runtimeOnly("io.micronaut.sql:micronaut-jdbc-hikari")
    runtimeOnly("org.flywaydb:flyway-database-postgresql") // This is required for micronuat test resources for postgresql, otherwise it will throw an error Postgresql version not supported

    testImplementation("io.micronaut:micronaut-http-client")
    testRuntimeOnly("org.postgresql:postgresql")
}

Configuring Flyway

After adding Flyway as a dependency, the next step is to configure it for your specific database. Flyway offers various configuration options, but at a minimum, we need to specify the details of our database connection.

// Place this code at the top of the file
import java.util.Properties

// Function to load properties based on the environment
fun loadProperties(env: String): Properties {
    val properties = Properties()
    val propsFile = file("src/main/resources/application-$env.properties")

    if (propsFile.exists()) {
        propsFile.inputStream().use { properties.load(it) }
    } else {
        throw GradleException("Properties file for environment '$env' not found: ${propsFile.absolutePath}")
    }

    return properties
}

// Set the environment (default is 'dev' if no argument is passed)
val env = project.findProperty("env")?.toString() ?: "dev"

// Load properties for the chosen environment
val dbProps = loadProperties(env)

flyway {
    url = dbProps["datasources.default.url"] as String
    user = dbProps["datasources.default.username"] as String
    password = dbProps["datasources.default.password"] as String
    locations = arrayOf("classpath:db/migration")
    baselineOnMigrate = true // Optional: Set to true to create a baseline on first migration
}

Lets define an Entity

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Entity
public final class Perk{
    @Id
    @AutoPopulated
    private UUID id;

    @NonNull
    @NotBlank
    @Column(unique = true, length = 255, name = "name")
    private String name;

    @Column(columnDefinition = "TEXT")
    private String description;
}

Migrations in Flyway are typically SQL files placed in a directory that Flyway scans, such as src/main/resources/db/migration.

We can utilize JPA Buddy to automatically generate the schema for the entity. However, for the time being, we will create the schema manually.

Migration Naming Convention:

Flyway requires migration scripts to follow a specific naming convention to determine the order of execution. The convention is as follows:

Versioned Migrations: These are the most common types of migrations and are applied only once. The naming format is V.sql. For example, V1Create_users_table.sql.

Repeatable Migrations: Applied every time their checksum changes, useful for managing views or stored procedures. They start with R__ followed by a description, like R_Refresh_all_views.sql.
The must be a unique number or a sequence (like 1, 2, 3 or 1.1, 1.2). The double underscores _ separate the version from the description, and the description should succinctly convey the script's purpose.

Create a SQL file within this directory, following Flyway’s naming convention, such as V1__Initial_tables.sql, with contents like:

CREATE TABLE perk
(
    id                 UUID NOT NULL,
    name               VARCHAR(255),
    description        TEXT,
    CONSTRAINT pk_perk PRIMARY KEY (id)
);

ALTER TABLE perk
    ADD CONSTRAINT uc_perk_name UNIQUE (name);

Flyway command to run migration

./gradlew flywayMigrate

The migration should be applied on the respective database.