Flyway for Database Migrations

1. The Problem: Manual and Automatic Schema Changes

In a team environment, or even when working alone across different machines, how do you ensure the database schema is always correct?

• Manual SQL scripts? You could write SQL scripts and run them manually, but this is error-prone. What if you forget to run a script on the production server? What if you run the same script twice?
• JPA's ddl-auto? You've likely seen the spring.jpa.hibernate.ddl-auto property. Setting it to update or create-drop seems convenient during early development. However, this approach is unreliable and dangerous for any real application.

To understand why, let's look at the limitations of relying on Hibernate for schema management.

The key takeaway is that ddl-auto lacks the control, versioning, and reliability needed for production systems. It cannot handle complex changes like data transformations, renaming columns, or providing a clear audit trail. This is where database migration tools come in.

2. Introduction to Flyway

Flyway is an open-source tool that automates database schema migrations. The core idea is simple and should be familiar to you as a developer: treat your database schema changes like source code. You write your changes in SQL files, add them to your project, and Flyway applies them in a deterministic, versioned order.

How It Works

1. Migration Scripts: You create plain SQL files containing your CREATE TABLE, ALTER TABLE, etc., statements. Each file follows a specific naming convention.
2. flyway_schema_history Table: On its first run, Flyway creates a special table in your database called flyway_schema_history. This table acts as a ledger.
3. Migration Process: When your application starts, Flyway scans for migration scripts. It compares the scripts in your project with the records in the flyway_schema_history table. Any scripts that have not yet been applied are executed in order. After a script is successfully executed, a new row is added to the history table, recording the version, description, and a checksum.

The Naming Convention

The most common naming convention for Flyway scripts is crucial to understand:

V<VERSION>__<DESCRIPTION>.sql

• V: The prefix for a Versioned migration.
• <VERSION>: The version number (e.g., 1, 1.1, 202411211030). Versions are sorted numerically.
• __: Two underscores act as a separator.
• <DESCRIPTION>: A human-readable description of the change (e.g., Create_users_table).
• .sql: The file extension.

Example: V1__Create_bookmarks_table.sql

Flyway uses a checksum for each applied migration file to ensure its integrity. If you change a migration file that has already been applied, the checksum will no longer match the one stored in the history table, and Flyway will fail to start, preventing accidental changes.

3. Implementing Flyway in a Spring Boot Application

Let's walk through adding Flyway to a Spring Boot project.

Step 1: Add the Flyway Dependency

When creating a new project with Spring Initializr, you can add the "Flyway Migration" dependency. If you have an existing project, add this to your pom.xml (for Maven) or build.gradle (for Gradle).

For Maven:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<!-- If using a database not automatically supported, like PostgreSQL -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-postgresql</artifactId>
</dependency>

Spring Boot manages the versions for you.

Step 2: Configure the Database and Disable ddl-auto

In your application.properties file, ensure your database connection is configured and, importantly, set ddl-auto to validate or none. This hands over schema management duties to Flyway.

spring.datasource.url=jdbc:postgresql://localhost:5432/mydatabase
spring.datasource.username=user
spring.datasource.password=password

# Let Flyway manage the schema
spring.jpa.hibernate.ddl-auto=validate

Step 3: Create Your First Migration

By default, Spring Boot tells Flyway to look for migration scripts in src/main/resources/db/migration.

1. Create this directory structure in your project.
2. Inside db/migration, create your first SQL migration file. Let's name it V1__create_bookmarks_table.sql.

-- V1__create_bookmarks_table.sql
CREATE TABLE bookmarks (
    id          BIGINT          GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title       VARCHAR(255)    NOT NULL,
    url         VARCHAR(255)    NOT NULL,
    created_at  TIMESTAMP       NOT NULL,
    updated_at  TIMESTAMP
);

The following article provides a detailed walkthrough of this setup process.

Now, when you run your Spring Boot application, Flyway will automatically:

1. Detect there is no flyway_schema_history table and create it.
2. Scan the db/migration folder and find V1__create_bookmarks_table.sql.
3. Execute the SQL script, creating the bookmarks table.
4. Record the successful migration in the flyway_schema_history table.

Step 4: Evolving the Schema

Your application requirements have changed, and you now need to add a status column to the bookmarks table. Do not edit V1__create_bookmarks_table.sql!

Instead, create a new migration file with a higher version number.

Create src/main/resources/db/migration/V2__add_status_to_bookmarks.sql:

-- V2__add_status_to_bookmarks.sql
ALTER TABLE bookmarks
ADD COLUMN status VARCHAR(50) DEFAULT 'DRAFT' NOT NULL;

When you restart your application:

1. Flyway will scan the migrations and see that V1 is already applied.
2. It will find V2__add_status_to_bookmarks.sql as a new, pending migration.
3. It will execute the ALTER TABLE statement.
4. It will record the V2 migration in the history table.

This process ensures that any database, no matter its current state, can be brought up to the latest version just by running the application.

4. Important Concepts and Best Practices

Understanding a few core principles will help you avoid common problems.

Based on that reading and industry best practices, here are the golden rules:

1. Versioned Migrations are Immutable: Once a versioned migration (V*) has been applied to any database (especially production), never, ever change it. To make further changes, create a new migration file with a new version number. Changing an applied migration causes the checksum mismatch error you read about.
2. Use flyway clean with Extreme Caution: Flyway has a clean command that drops all objects in the configured schemas. This is useful for resetting your local development database, but it is extremely dangerous in production. It's best practice to disable it in production environments via spring.flyway.clean-disabled=true.
3. Consider Repeatable Migrations for Certain Objects: Flyway also supports repeatable migrations (e.g., R__create_or_update_view.sql). Unlike versioned migrations, these are re-applied whenever their checksum changes. They are ideal for objects that are managed as a whole, like views or stored procedures, but they must be written to be idempotent (safe to run multiple times).

Conclusion

You have now learned how to manage your database schema's evolution in a structured and automated way. By incorporating Flyway into your workflow, you bring the same discipline of version control to your database that you already apply to your application code.

Key Takeaways:

• Relying on hibernate.ddl-auto=update is risky and unsuitable for production.
• Flyway automates schema changes using versioned SQL scripts.
• It tracks applied migrations in a dedicated flyway_schema_history table.
• The naming convention V<VERSION>__<DESCRIPTION>.sql is fundamental, as Flyway applies scripts in version order.
• The Golden Rule: Never modify a versioned migration script after it has been applied. To make changes, create a new script with a higher version number.
• Spring Boot provides seamless integration with Flyway; you just need to add the dependency and create your scripts in src/main/resources/db/migration.

What's Next?

We've now tackled transaction management, efficient data querying, and schema versioning. The next topic in our module addresses a common and subtle performance trap. In the next lesson, we will explain the N+1 query problem and show how to solve it using fetch joins. This will further refine your ability to build high-performance, data-driven applications.