Database Migrations with Flyway in Spring Boot

Database versioning is a crucial aspect of modern application development. Managing schema changes efficiently helps prevent inconsistencies and ensures smooth deployments. In this post, we will explore how to use Flyway for database migrations in a Spring Boot application.

Project Setup

We assume that you already have a Spring Boot project set up with the necessary dependencies:

• Spring Web
• Spring Data JPA
• PostgreSQL
• Flyway

If you haven't set up your project yet, you can generate it using
Spring Initializr.

Create a docker-compose file

To run our database, we are going to use docker. Create a docker compose file like this:

services:
  postgres:
    container_name: postgres_database
    image: postgres:latest
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: postgres
    ports:
      - 5432:5432 
    volumes:
      - postgres-data-migrations:/var/lib/postgresql/data

volumes:
  postgres-data-migrations:

Configuring Flyway

Spring Boot automatically integrates with Flyway when the Flyway dependency is present in pom.xml.

Next, configure the database connection in application.properties:

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

spring.jpa.hibernate.ddl-auto=none

Creating a Migration Script

Flyway looks for migration scripts inside the src/main/resources/db/migration directory by default.
We are going to create a table called person, with the idand the name.
Create a new SQL file, inside the migration directory, following Flyway's naming convention: V1__create_person_table.sql.

Example:

CREATE TABLE person(
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Once the Spring Boot application starts, Flyway will automatically detect and apply this migration.

Running and Verifying Migrations

Start your Spring Boot application, and Flyway will execute the migration.
We are using docker to run our database, so we can verify the changes in our database by the command line.

Enter in the docker container terminal

docker exec -it  psql -U  -d

Change the to the ID or name of your container, and for the user and name you set in the docker compose file.

List all tables from the database

\dt

See columns and types of data from a table

\d person

You will see something like this:

Table "public.person"
 Column |          Type          | Collation | Nullable |              Default               
--------+------------------------+-----------+----------+------------------------------------
 id     | integer                |           | not null | nextval('person_id_seq'::regclass)
 name   | character varying(100) |           | not null |

Adding More Columns

If you're going to write a new migration, remember to stop your Spring Boot application to prevent errors.

To modify the database schema, create a new migration file, e.g., V2__add_age_and_hair_color_columns.sql:

ALTER TABLE person ADD COLUMN age INT;

ALTER TABLE person ADD COLUMN hair_color VARCHAR(50);

Restart the Spring Boot application, and Flyway will apply this new migration automatically.

If we run the command to see our database in the terminal, we will see something like this:

Table "public.person"
   Column   |          Type          | Collation | Nullable |              Default               
------------+------------------------+-----------+----------+------------------------------------
 id         | integer                |           | not null | nextval('person_id_seq'::regclass)
 name       | character varying(100) |           | not null | 
 age        | integer                |           |          | 
 hair_color | character varying(50)  |           |          |

Removing Columns

Para deletar a coluna hair_color da tabela person usando uma migration com Flyway, você deve criar um novo arquivo de migração SQL, seguindo a convenção de nomes, por exemplo:

To delete the column hair_color using a migration with Flyway, you need to create the migration file called V5__remove_hair_color.sql.

ALTER TABLE person DROP COLUMN hair_color;

Precautions when removing a column:

✅ Backup:Before you run the migration, make a backup of the database, in case you need to restore the data after.

✅ Code impact: Check if there are references to hair_color on your code base (queries, DTOs, APIs) to avoid errors.

✅ Safe deploy: If the database is in the production, certify that this column is not being used before execute the migration.

If you need to revert this change in the future, it will be necessary create a new migration to add the column back(Flyway does not permit automatic rollback).

After running this migration our database will look like this:

Table "public.person"
 Column |          Type          | Collation | Nullable |              Default               
--------+------------------------+-----------+----------+------------------------------------
 id     | integer                |           | not null | nextval('person_id_seq'::regclass)
 name   | character varying(100) |           | not null | 
 age    | integer                |           |          |

Rollback Strategies in Flyway

Flyway does not support automatic rollbacks, meaning that once a migration has been successfully applied, it cannot be automatically undone. If a migration fails or you need to revert a change, you must use manual rollback strategies. Here are some effective approaches:

1 - Create a Reversal Migration (Manual Rollback) (Recommended)

The most common approach is to create a new migration to undo the unwanted change rather than modifying or deleting an already applied migration.

Example:

If you removed the hair_color column in migration V3__remove_hair_color.sql:

📌 V4__restore_hair_color.sql (new migration for rollback)

ALTER TABLE person ADD COLUMN hair_color VARCHAR(50);

Why use this approach?

✔️ Keeps the database migration history clean and organized.

✔️ Avoids modifying applied migrations, which could cause inconsistencies.

✔️ Ensures that dependent migrations continue to work correctly.

2 - Use Transactions for Migrations (If Supported by the Database) 🚨 (Limited Use Case)

If your database supports transactions for DDL (such as PostgreSQL), you can ensure that a failed migration does not leave the database in an inconsistent state.

Example of a transactional migration:

BEGIN;

ALTER TABLE person ADD COLUMN age INT;
ALTER TABLE person ADD COLUMN hair_color VARCHAR(50);

COMMIT;

If an error occurs, the ROLLBACK command will be triggered automatically, preventing partial changes.

⚠️ Limitations:

• MySQL does not support transactions for some DDL operations, so this strategy may not always work.
• It only prevents incomplete migrations but does not revert already applied migrations.

3 - Restore a Database Backup (Full Rollback) ⏪ (For Critical Environments)

If you need to revert multiple migrations at once or return to a previous database state, restoring a backup is a reliable approach.

Recommended workflow:

1. Before running critical migrations, create a full database backup.

   • For PostgreSQL:

    pg_dump -U myuser -d mydatabase -F c -f backup.dump
   

• For MySQL:
  

   mysqldump -u myuser -p mydatabase > backup.sql
  

1. If you need to revert, restore the backup:

   • For PostgreSQL:

    pg_restore -U myuser -d mydatabase backup.dump
   

• For MySQL:
  

   mysql -u myuser -p mydatabase < backup.sql
  

✅ The best strategy for production environments, ensuring the database returns to a known, stable state.

⚠️ Restoring a backup may result in data loss if new data was inserted after the backup was created!

4 - Manually Deleting the Migration from Flyway (Not Recommended 🚫)

You can manually revert a migration by removing its entry from the Flyway history and undoing the changes in the database.

📌 Steps:

1. Manually revert the database changes (e.g., recreating deleted columns).
2. Remove the migration entry from Flyway:

DELETE FROM flyway_schema_history WHERE version = '5';

1. Run the corrected migration again.

⚠️ Risks:

• Can corrupt Flyway’s versioning history.
• If the migration has already been applied in other environments, it may cause inconsistencies.
• Only recommended for development/testing environments, never in production.

Which Strategy Should You Use?

Best practice: Always create a new migration to revert changes, as it keeps the migration history clean and avoids versioning issues.

Conclusion

Flyway is a powerful tool for managing database migrations in Spring Boot applications. By following a structured approach to schema changes, you can ensure consistency and reliability across different environments.

📍 Reference

• Flyway docs
• Database migrations with Flyway

💻 Project Repository

• Project Repository on Github

👋 Talk to me

• LinkedIn
• Github
• Portfolio