Creating Repeatable Migrations with Flyway

Flyway simplifies database schema management with two types of migrations: versioned and repeatable. While versioned migrations (e.g., V1__init.sql) handle one-time changes to your database schema, repeatable migrations (R__*.sql) allow for script re-execution whenever their content changes. This mechanism is perfect for maintaining database objects like views, stored procedures, or seed data.

This article explores the concept of repeatable migrations in Flyway, their common use cases, how Flyway detects changes, and practical best practices for using them with Spring Boot.

Table of Contents

1. What is a Repeatable Migration?
2. Use Cases for Repeatable Migrations
   • Views
   • Functions and Procedures
   • Seed Data
3. How Flyway Detects Changes in Repeatable Migrations
4. Best Practices with Spring Boot
5. Example with Repeatable Migrations in Spring Boot
6. External Resources for Further Learning
7. Final Thoughts

---

What is a Repeatable Migration?

A repeatable migration in Flyway is a script that can be reapplied whenever its content changes, unlike versioned migrations that run only once. Repeatable migrations are most commonly used for database objects that might need frequent updates, such as views or stored procedures.

Naming Convention

Repeatable migration files must begin with R__ and include a descriptive name. For example:

R__refresh_user_view.sql
R__update_stored_procedures.sql

Flyway manages these migrations based on their checksum (a hash of the script content). If the checksum of a repeatable migration changes, Flyway re-applies the script during the next migration phase.

Note: Repeatable migrations are executed after all versioned migrations for the applicable run.

---

Use Cases for Repeatable Migrations

1. Views

Database views are logical, reusable objects created from complex queries. If a view’s query needs to be modified or refined, repeatable migrations ensure the view remains up-to-date.

Example Script for a View:

-- R__create_user_activity_view.sql
CREATE OR REPLACE VIEW user_activity AS
SELECT 
u.id AS user_id,
u.username,
o.order_date,
SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, o.order_date;

Any subsequent changes to this script will trigger Flyway to reapply it.

2. Functions and Procedures

Stored functions and procedures encapsulate reusable business logic within the database. Repeatable migrations are ideal for ensuring these objects are updated anytime changes occur.

Example Script for a Procedure:

-- R__update_total_sales_procedure.sql
CREATE OR REPLACE PROCEDURE update_total_sales()
BEGIN
UPDATE sales_summary
SET total_sales = (
SELECT SUM(amount) FROM orders
);
END;

3. Seed Data

Repeatable migrations can also handle data initialization or updates to reference tables. This ensures environments (e.g., development, staging) always have consistent seed data.

Example Script for Seed Data:

-- R__populate_seed_data.sql
INSERT INTO payment_methods (id, name) VALUES
(1, 'Credit Card'),
(2, 'PayPal'),
(3, 'Bank Transfer')
ON DUPLICATE KEY UPDATE name = VALUES(name);

This script inserts default values into the payment_methods table and ensures existing rows are updated if changes occur.

---

How Flyway Detects Changes in Repeatable Migrations

Checksum Mechanism

Instead of relying on version numbers, Flyway tracks repeatable migrations using checksums.

1. Checksum Calculation
   Flyway calculates a hash of the migration file’s content during execution.
2. Storing the Checksum
   This checksum is stored in the flyway_schema_history table, alongside the migration name and execution timestamp.
3. Detecting Changes
   For subsequent Flyway runs:
   • If the checksum of the repeatable script remains unchanged, Flyway skips it.
   • If the checksum changes, Flyway re-executes the migration.

Execution Order

• Versioned migrations run first, ensuring the database schema is up-to-date.
• Repeatable migrations execute after all versioned scripts, allowing them to reference existing tables or objects.

Example Flyway Schema History:

Installed Rank	Version	Description	Script	Type	Checksum	Success
1	1	Create users table	V1__create_users_table.sql	SQL	1234567890	Yes
2		Refresh user activity	R__refresh_user_activity.sql	REPEATABLE	9876543210	Yes

---

Best Practices with Spring Boot

To use repeatable migrations effectively in Spring Boot projects, follow these best practices:

Place Repeatable Scripts in db/migration

By default, Flyway scans the db/migration folder for migration scripts. Place both versioned and repeatable migrations there for seamless execution:

src/main/resources/db/migration/
├── V1__create_users_table.sql
├── V2__create_orders_table.sql
├── R__create_user_activity_view.sql

Use SQL Comments for Documentation

Add comments to clarify the purpose of your repeatable migrations. For example:

-- This migration refreshes the user_activity view to calculate total spending.
CREATE OR REPLACE VIEW user_activity AS
SELECT ...;

Leverage Flyway Properties

Configure Flyway behavior using Spring Boot’s application.properties:

spring.flyway.locations=classpath:/db/migration
spring.flyway.enabled=true
spring.flyway.baseline-on-migrate=true

Test Modifications in Non-Production Environments

Before migrating production databases, test repeatable migrations in development or staging environments to validate correctness. Tools like Docker-based databases or Testcontainers can simplify this process.

---

Example with Repeatable Migrations in Spring Boot

Project Setup

1. Add Dependencies
   Add Flyway’s dependency to your pom.xml or build.gradle: <dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-core</artifactId> </dependency>
2. Create Migration Scripts
   Add the following scripts to your src/main/resources/db/migration/ folder:
   • V1__init_schema.sql: CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL );
   • R__populate_seed_data.sql: INSERT INTO users (id, username) VALUES (1, 'user1'), (2, 'user2') ON DUPLICATE KEY UPDATE username = VALUES(username);
3. Configure Application Properties
   Ensure Flyway is enabled in application.properties: spring.datasource.url=jdbc:mysql://localhost/flyway_demo spring.datasource.username=root spring.datasource.password=password spring.flyway.enabled=true
4. Run the Application
   Start the Spring Boot application, and Flyway will automatically apply both versioned and repeatable scripts.

Validation

Check the flyway_schema_history table to see the repeatable migration’s execution details:

SELECT * FROM flyway_schema_history WHERE description LIKE 'populate%';

---

External Resources for Further Learning

• Flyway Official Documentation
• Spring Boot + Flyway Integration
• Wikipedia on Database Migration

---

Final Thoughts

Repeatable migrations are a powerful feature in Flyway that ensure database objects like views, stored procedures, and seed data remain synchronized with your application. Flyway’s checksum mechanism ensures updates are applied efficiently and reliably whenever changes occur.

By following best practices and leveraging Spring Boot’s seamless integration, you can simplify and streamline schema management for dynamic database objects. Use this guide as a reference to confidently implement repeatable migrations in your projects!