A Practical Guide to Database Migration with Flyway in Java

In modern software development, the database schema is as much a part of the application code as the Java classes themselves. Manually managing SQL scripts, tracking which ones have been run, and ensuring consistency across development, testing, and production environments is a recipe for error and frustration. This is where database migration tools come in, and Flyway is one of the most popular and developer-friendly choices for the Java ecosystem.

Flyway empowers you to version your database alongside your code, ensuring that everyone on your team can easily reproduce the same database state.

Table of Contents

What is Flyway?

Flyway is an open-source database migration tool that simplifies the process of managing and applying incremental, version-controlled changes to your database. It follows a simple principle: versioned migrations.

Think of it as Git for your database schema. You create SQL scripts (or Java-based migrations) that are numbered sequentially. Flyway keeps track of which scripts have already been executed in a special metadata table (flyway_schema_history by default) and automatically applies the new ones in the correct order.

Core Concepts of Flyway

Migrations: These are the scripts that change your database schema or data. They can be versioned (the main migrations), undo (for rolling back versioned migrations), or repeatable (scripts that are re-applied when they change, like views or stored procedures).
Versioned Migrations: The most common type. They have a unique version and a description (e.g., V1__Create_user_table.sql). They are applied exactly once.
The Schema History Table: Flyway's "brain." This table, created automatically in your database, tracks which migrations have been run, their checksums, and their execution time.

Hands-On Tutorial: Integrating Flyway with a Java Application

Let's build a simple Spring Boot application to see Flyway in action.

Step 1: Set Up a Spring Boot Project

Use Spring Initializr to create a new project with the following dependencies:

Spring Web (for the basic web setup)
Spring Data JPA (for data access)
H2 Database (an in-memory database for easy testing)
Flyway Migration (this is the key!)

Step 2: Review the Dependencies

Your pom.xml should include Flyway and the H2 driver automatically.

<!-- Flyway will be included by the Spring Boot starter -->
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
</dependency>
<!-- H2 Database -->
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>

Step 3: Configure the Application

Add the following to your src/main/resources/application.properties file. We'll use an in-memory H2 database for simplicity.

# H2 Database Configuration
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
# Show SQL queries in the console (optional, for debugging)
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
# This tells Flyway to run automatically on startup (it's the default)
spring.flyway.enabled=true

Step 4: Create Your First Database Migration

Flyway looks for migration scripts in the src/main/resources/db/migration directory by default.

Create this folder structure in your project.
Inside the migration folder, create a new SQL file named:
V1__Create_user_table.sql

The naming convention is crucial:

V - Prefix for a versioned migration.
1 - The version number (can also be 1.1, 2, 2024.09.17.01).
__ - A double underscore separator.
Create_user_table - The description, using underscores instead of spaces.
.sql - The file extension.

Add the following SQL to V1__Create_user_table.sql:

CREATE TABLE user_entity (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 5: Create a Second Migration

Now, let's add another change. Create a second file: V2__Add_last_login_to_user.sql

ALTER TABLE user_entity
ADD COLUMN last_login TIMESTAMP;

Step 6: Create a JPA Entity and Repository

To test our setup, let's create a simple JPA entity that maps to the table we just created.

src/main/java/com/example/demo/UserEntity.java

import jakarta.persistence.*;
import java.time.LocalDateTime;
@Entity
@Table(name = "user_entity")
public class UserEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(unique = true, nullable = false)
private String username;
@Column(nullable = false)
private String email;
private LocalDateTime createdAt;
private LocalDateTime lastLogin;
// Constructors, Getters, and Setters
public UserEntity() {}
// ... (generate getters and setters)
}

src/main/java/com/example/demo/UserRepository.java

import org.springframework.data.jpa.repository.JpaRepository;
public interface UserRepository extends JpaRepository<UserEntity, Long> {
}

Step 7: Run the Application!

When you start your Spring Boot application, Flyway will automatically kick in:

It checks the db/migration directory for any SQL files.
It checks the flyway_schema_history table in the H2 database (which doesn't exist yet).
It creates the flyway_schema_history table.
It applies V1__Create_user_table.sql, then V2__Add_last_login_to_user.sql in order.
It records the success of each migration in the history table.

You can verify this by checking the application logs. You should see lines like:

Successfully validated 2 migrations (execution time 00:00.015ms)
Creating Schema History table "PUBLIC"."flyway_schema_history" ...
Current version of schema "PUBLIC": << Empty Schema >>
Migrating schema "PUBLIC" to version "1 - Create user table"
Migrating schema "PUBLIC" to version "2 - Add last login to user"
Successfully applied 2 migrations to schema "PUBLIC" (execution time 00:00.035ms)

Step 8: Check the H2 Console (Optional)

To visually confirm, enable the H2 console in your application.properties:

# Enable H2 Console
spring.h2.console.enabled=true

Restart the app, go to http://localhost:8080/h2-console, and connect to the JDBC URL jdbc:h2:mem:testdb. You can run SELECT * FROM flyway_schema_history; to see the log of applied migrations and SELECT * FROM user_entity; to see the empty table.

Advanced: Java-Based Migrations

Sometimes, SQL isn't powerful enough. You might need to transform data using complex Java logic. Flyway supports this with Java-based migrations.

Create a Java class in src/main/java/com/example/demo/db/migration called V3__InsertDefaultUsers.java.
Implement the FlywayMigration interface (for standalone Flyway) or, more commonly in Spring, use the JdbcTemplate.

Example using JdbcTemplate:

package com.example.demo.db.migration;
import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;
import org.springframework.stereotype.Component;
// Note: For newer versions, BaseJavaMigration is common.
public class V3__InsertDefaultUsers extends BaseJavaMigration {
@Override
public void migrate(Context context) throws Exception {
JdbcTemplate jdbcTemplate = new JdbcTemplate(
new SingleConnectionDataSource(context.getConnection(), true)
);
// Your complex Java logic here
jdbcTemplate.update("INSERT INTO user_entity (username, email) VALUES (?, ?)", "admin", "[email protected]");
jdbcTemplate.update("INSERT INTO user_entity (username, email) VALUES (?, ?)", "user1", "[email protected]");
System.out.println("Inserted default users via Java migration!");
}
}

When you run the app again, Flyway will detect this Java class and execute its migrate method, seamlessly integrating it with your SQL migrations.

Conclusion

Flyway brings order and reliability to the often chaotic process of database evolution. By integrating it into your Java (especially Spring Boot) projects, you can:

Eliminate manual database updates.
Ensure consistency across all environments (Dev, Staging, Prod).
Automate your CI/CD pipeline by having Flyway run migrations automatically on deployment.
Collaborate effectively with your team by treating database changes as version-controlled artifacts.

Start using Flyway in your next project, and you'll never want to manage SQL scripts by hand again.