Skip to main content

Chapter 3: Schema Migration with Flyway

Introduction​

Have you ever experienced this situation?

"Wait, which SQL script did you run in production?"
"The one with the new columns... I think?"
"But there were 3 versions! V1, V2 or V2_final_final?"
💀

Flyway solves this problem by versioning your database schema just like you version your code.

🎯 Chapter Objectives​

By the end of this chapter, you will know:

  1. ✅ Why Flyway is essential for production
  2. ✅ Install and configure Flyway with Spring Boot
  3. ✅ Create migration scripts
  4. ✅ Understand the naming convention
  5. ✅ Manage repeatable migrations
  6. ✅ Solve common issues

1. Why Version the Schema?​

The Problem​

Problems:

  • You don't know which script was executed
  • No trace of history
  • Risk of forgetting a migration
  • Impossible to reproduce the environment

The Solution: Flyway​

Advantages:

  • ✅ Complete history of migrations
  • ✅ Automatic execution at startup
  • ✅ Identical across all environments
  • ✅ Impossible to forget a migration

2. Installation​

Maven Dependency​

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

<!-- For PostgreSQL, also add: -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-postgresql</artifactId>
</dependency>

With Spring Boot​

That's it! Spring Boot auto-configures Flyway automatically.

# application.properties
spring.flyway.enabled=true  # Default: true if Flyway is in the classpath

3. File Structure​

Default Location​

src/
└── main/
    └── resources/
        └── db/
            └── migration/
                ├── V1__create_users_table.sql
                ├── V2__add_email_column.sql
                ├── V3__create_orders_table.sql
                └── V4__add_index_on_email.sql

Naming Convention​

V{version}__{description}.sql
 │    │          │
 │    │          └── Description (underscores = spaces)
 │    └── Version number (1, 2, 2.1, etc.)
 └── Prefix "V" (Versioned migration)

Valid Examples:

V1__create_users.sql
V2__add_email_to_users.sql
V2.1__add_phone_number.sql
V10__create_products.sql
V100__final_cleanup.sql

Invalid Examples:

V1_create_users.sql       ❌ Single underscore
v1__create_users.sql      ❌ Lowercase
1__create_users.sql       ❌ No prefix V
V1__create users.sql      ❌ Spaces in the name

4. Create Migrations​

Migration 1: Create a Table​

-- V1__create_users_table.sql

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Migration 2: Add a Column​

-- V2__add_email_to_users.sql

ALTER TABLE users
ADD COLUMN email VARCHAR(100) UNIQUE;

Migration 3: Create Another Table with FK​

-- V3__create_orders_table.sql

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'PENDING',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_orders_user_id ON orders(user_id);

Migration 4: Insert Data​

-- V4__insert_initial_data.sql

INSERT INTO users (username, password, email) VALUES
('admin', '$2a$10$hashedpassword', 'admin@example.com'),
('user1', '$2a$10$hashedpassword', 'user1@example.com');

5. How Flyway Works​

The flyway_schema_history Table​

Flyway automatically creates a table to track migrations:

SELECT * FROM flyway_schema_history;
installed_rankversiondescriptiontypescriptchecksuminstalled_onexecution_timesuccess
11create users tableSQLV1__create_users_table.sql-1234562025-01-15 10:0045true
22add email to usersSQLV2__add_email_to_users.sql7890122025-01-15 10:0112true
33create orders tableSQLV3__create_orders_table.sql3456782025-01-16 09:0067true

Execution Flow​

6. Spring Boot Configuration​

Basic Configuration​

# application.properties

# Enable Flyway
spring.flyway.enabled=true

# Script location (default: classpath:db/migration)
spring.flyway.locations=classpath:db/migration

# Baseline (for existing DBs)
spring.flyway.baseline-on-migrate=false
spring.flyway.baseline-version=1

Advanced Configuration​

# Target schema
spring.flyway.schemas=public

# Cleaning (DANGEROUS in prod!)
spring.flyway.clean-disabled=true

# Validation
spring.flyway.validate-on-migrate=true

# Placeholder substitution
spring.flyway.placeholder-replacement=true
spring.flyway.placeholders.table_prefix=app_

YAML Configuration​

spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: false
    clean-disabled: true
    validate-on-migrate: true

7. Repeatable Migrations​

Use Case​

Some objects need to be recreated with each change:

  • Views (CREATE OR REPLACE VIEW)
  • Stored procedures
  • Functions
  • Reference data

Convention​

R__{description}.sql
│
└── Prefix "R" (Repeatable)

Example​

-- R__create_active_users_view.sql

CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE last_login > NOW() - INTERVAL '30 days';

Behavior:

  • Flyway calculates a checksum of the file
  • If the file changes → re-executed
  • Executed AFTER all versioned migrations

8. Environment Management​

Multi-Environment Structure​

src/main/resources/
├── db/
│   └── migration/
│       ├── V1__create_tables.sql     # All environments
│       ├── V2__add_columns.sql
│       └── V3__create_indexes.sql
│
├── db-dev/
│   └── migration/
│       └── V1000__dev_test_data.sql  # Test data (dev only)
│
└── db-prod/
    └── migration/
        └── V1001__prod_config.sql    # Production config

Configuration by Profile​

# application-dev.properties
spring.flyway.locations=classpath:db/migration,classpath:db-dev/migration

# application-prod.properties  
spring.flyway.locations=classpath:db/migration,classpath:db-prod/migration

9. Integration with JPA/Hibernate​

Execution Order​

# IMPORTANT: Flyway runs BEFORE Hibernate
spring.jpa.hibernate.ddl-auto=validate  # Do not generate schema

# Or for dev:
spring.jpa.hibernate.ddl-auto=none      # Flyway manages everything
tip

In development: Use ddl-auto=validate to ensure your entities match the schema.

In production: Use ddl-auto=none and let Flyway handle everything.

10. Flyway Commands​

Via Maven Plugin​

<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <configuration>
        <url>jdbc:postgresql://localhost:5432/mydb</url>
        <user>postgres</user>
        <password>secret</password>
    </configuration>
</plugin>
# View applied migrations
mvn flyway:info

# Apply migrations
mvn flyway:migrate

# Validate migrations
mvn flyway:validate

# Repair history (caution!)
mvn flyway:repair

# Clean the DB (DANGEROUS!)
mvn flyway:clean

Example flyway:info Output​

+-----------+---------+----------------------+------+---------------------+---------+
| Category  | Version | Description          | Type | Installed On        | State   |
+-----------+---------+----------------------+------+---------------------+---------+
| Versioned | 1       | create users table   | SQL  | 2025-01-15 10:00:00 | Success |
| Versioned | 2       | add email to users   | SQL  | 2025-01-15 10:01:00 | Success |
| Versioned | 3       | create orders table  | SQL  | 2025-01-16 09:00:00 | Success |
| Versioned | 4       | add indexes          | SQL  |                     | Pending |
+-----------+---------+----------------------+------+---------------------+---------+

11. Common Issues and Solutions​

❌ Checksum Mismatch​

FlywayException: Validate failed: Migration checksum mismatch for migration version 2
-> Applied to database : -123456
-> Resolved locally    : 789012

Cause: The migration file was modified after execution.

Solutions:

  1. Never modify an already executed migration!
  2. If in dev: mvn flyway:repair
  3. Create a new migration to fix

❌ Migration Not Found​

FlywayException: Detected resolved migration not applied to database: 3

Cause: A migration exists in the DB but not in the files.

Solution: Restore the missing migration file.

❌ Out of Order Migration​

FlywayException: Detected resolved migration not applied to database: 2
(Applied migrations are: 1, 3)

Cause: Migration V2 added after V3.

Solution:

spring.flyway.out-of-order=true  # Allow out-of-order migrations

❌ Failed Migration​

FlywayException: Migration V4__add_index.sql failed

Solution:

  1. Fix the SQL in the file
  2. Run mvn flyway:repair (marks as resolved)
  3. Run mvn flyway:migrate

12. Best Practices​

✅ Golden Rules​

1. NEVER modify an already executed migration
   → Create a new migration to fix

2. NEVER delete an executed migration
   → History must remain consistent

3. ALWAYS test migrations on a test environment
   → Before deploying to production

4. ALWAYS have a backup before production migration
   → Just in case

5. Keep migrations small and focused
   → One migration = one logical change

✅ Clear Naming​

-- ✅ GOOD
V1__create_users_table.sql
V2__add_email_column_to_users.sql
V3__create_orders_with_foreign_keys.sql

-- ❌ BAD
V1__changes.sql
V2__update.sql
V3__fix.sql

✅ Idempotent Scripts​

-- ✅ GOOD: Check before creating
CREATE TABLE IF NOT EXISTS users (...);

-- ✅ GOOD: Check before adding a column (PostgreSQL)
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'users' AND column_name = 'phone') THEN
        ALTER TABLE users ADD COLUMN phone VARCHAR(20);
    END IF;
END $$;

13. Summary​

Key Points​

ConceptDescription
FlywayVersioned schema migration tool
V{n}__Prefix for versioned migrations
R__Prefix for repeatable migrations
flyway_schema_historyTable that tracks migrations
checksumFile hash to detect changes

Typical Workflow​

1. Create a V{n}__description.sql file
2. Write the SQL
3. Start the application (or mvn flyway:migrate)
4. Flyway executes automatically
5. Check with mvn flyway:info

Minimal Configuration​

# Spring Boot + Flyway
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.jpa.hibernate.ddl-auto=validate

🎯 Next Step​

Flyway is the most popular tool, but Liquibase offers an alternative with different features. Let's see when to choose one or the other.

➡️ Chapter 4: Migration with Liquibase

⬅️ Back to Module 7