Understanding Flyway Migration Naming and Versioning

Flyway is a powerful tool for managing database schema changes, making it easier to version and track every alteration efficiently. A key element of Flyway’s approach is the naming and versioning of migration scripts, which dictate the order and application of schema changes in your database. Additionally, Flyway supports repeatable migrations for dynamic database operations.

This article provides an in-depth guide to Flyway migration naming conventions, versioning systems, the role of repeatable migrations, and the step-by-step process Flyway uses to apply scripts.

Table of Contents

1. Migration Naming Conventions
2. Version Ordering and Control
3. Importance of Repeatable Migrations
4. How Flyway Applies Scripts
5. Demo Example with Spring Boot
6. External Resources for Further Learning
7. Final Thoughts

---

Migration Naming Conventions

Flyway relies on naming conventions to determine the order and type of migrations to apply. Naming your migration scripts correctly ensures that Flyway identifies and runs them in a predictable manner.

Naming Format

The standard naming convention for migration files is as follows:

V{version_number}__{description}.sql

• V indicates a versioned migration.
• {version_number} specifies the migration version.
• __ (double underscore) separates the version number from the descriptive filename.
• {description} is a brief, lowercase description of the migration’s purpose.

Examples

1. V1__init_schema.sql: Initializes the database schema.
2. V2__add_users_table.sql: Adds a users table to the schema.
3. V3__update_email_column.sql: Modifies the email column properties.

Rules for Naming:

• Always start with V1 for your first migration.
• Never reuse a version number once it’s applied.
• Stick to lowercase descriptions for better readability.

Flyway ignores filenames that do not conform to this pattern, so consistency is critical.

---

Version Ordering and Control

Flyway determines the order of migrations by the version number in the filename. If versions are applied out-of-order or missed, schema inconsistencies can occur.

Version Number Rules

1. Sequential Numbering
   Each migration version must be unique and increment sequentially (e.g., V1, V2, V3).
2. Major and Minor Versions
   Flyway supports complex versioning schemes like V1.1, V2.0, and V3.4.2, enabling finer control over migration sequences in multi-team environments.
3. Gaps and Overlaps
   • Gaps between versions (e.g., missing a V3) are allowed.
   • Overlapping or duplicate versions are not permitted.

Demo Example

Assume you have the following migrations:

• V1__create_schema.sql
• V2__add_employee_table.sql
• V3.1__add_department_table.sql

Flyway will run these scripts in the exact order based on their version numbers.

Pro Tip: Use consistent version ranges (e.g., V1, V2 for major releases) to avoid confusion in larger teams.

---

Importance of Repeatable Migrations

What Are Repeatable Migrations?

Repeatable migrations have filenames starting with R instead of V:

R__{description}.sql

Unlike versioned migrations, repeatable migrations do not have a version number. They are reapplied whenever their content changes.

Use Cases

1. Dynamic Database Changes
   For example, the creation of materialized views or stored procedures often requires periodic updates.
2. Seed Data Initialization
   Use repeatable migrations to populate or update seed data in development or test environments.

Example Repeatable Migration

File: src/main/resources/db/migration/R__refresh_materialized_views.sql

REFRESH MATERIALIZED VIEW user_activity_view;

Flyway detects changes to the checksum of the repeatable script and reapplies it as necessary.

---

How Flyway Applies Scripts

Flyway applies migration scripts in sequential order, ensuring the database state matches the defined schema. The process involves the following steps:

Step-by-Step Workflow

1. Scanning for Scripts
   Flyway scans the configured db/migration directory at startup for migration scripts.
2. Checking Metadata Table
   Flyway maintains a flyway_schema_history table in the database. This table:
   • Tracks applied scripts.
   • Records the state (success/failure) of each migration.
3. Executing New Migrations
   Scripts that are not yet applied are executed sequentially.
   • Versioned migrations are executed in ascending order.
   • Repeatable migrations are executed if their checksums differ.
4. Integrity Check
   Flyway checks for conflicting scripts (e.g., reordering or modifying applied scripts). An error will be thrown if conflicts arise.

Application Logs Example

Flyway Community Edition 9.0.0 by Redgate
Database: jdbc:mysql://localhost/flyway_test (MySQL 8.0)
Successfully validated 3 migrations (execution time 00:00.012s)
Current version of schema "flyway_test": 2
Migrating schema "flyway_test" to version "3 - add_department_table"
Successfully applied 1 migration to schema "flyway_test" (execution time 00:00.045s)

---

Demo Example with Spring Boot

Project Setup

Configure your Spring Boot project with Flyway:

1. Add the Flyway Maven/Gradle dependency.
2. Define database connection properties in application.properties.

Flyway-Specific Configuration:

spring.flyway.locations=classpath:/custom_migration_directory
spring.flyway.out-of-order=true

• locations sets a custom folder for migrations.
• out-of-order enables Flyway to apply a missed migration out of sequence.

Sample Migrations

Versioned Migration Example (V1):

-- db/migration/V1__create_user_table.sql
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(100) NOT NULL
);

Repeatable Migration Example (R):

-- db/migration/R__refresh_user_view.sql
REFRESH MATERIALIZED VIEW user_view;

Validating Migrations

1. Start the Spring Boot application.
2. Verify that migrations are applied by querying the flyway_schema_history table.

---

External Resources for Further Learning

• Flyway Official Documentation
• Spring Boot Flyway Integration
• Schema Migration on Wikipedia

---

Final Thoughts

Understanding Flyway’s migration naming and versioning conventions is essential for effective database management. By adhering to best practices for script naming, version ordering, and leveraging repeatable migrations, you can streamline schema updates and avoid conflicts or errors in development and production.

Flyway integrates seamlessly with Spring Boot, enabling automated schema migrations with minimal setup. Use this guide as a reference to ensure your database stays in sync with your codebase, fostering a more efficient and collaborative development process.

Start managing your database migrations confidently with Flyway today! Bookmark this guide for future projects.

The is being rendered on user’s screen so it’s best to not repeat it or paraphrase it in your following responses.

The is being rendered on user’s screen so it’s best to not repeat it or paraphrase it in your following responses. Your detailed article on “Understanding Flyway Migration Naming and Versioning” is ready, complete with naming conventions, versioning insights, and Spring Boot examples