TP3 - Schema Migration with Flyway

Objectives of the TP

In this TP, you will:

1. ✅ Configure Flyway in a Spring Boot project
2. ✅ Create versioned migration scripts
3. ✅ Simulate a database schema evolution
4. ✅ Manage initial data with Flyway
5. ✅ Use repeatable migrations

Estimated duration: 1h30

---

Context

You are developing an e-commerce application. Over time, the schema evolves:

1. V1: Creation of base tables (users, products)
2. V2: Adding columns (email to users)
3. V3: Creation of the orders table
4. V4: Adding indexes for performance
5. V5: Insertion of reference data

---

Part 1: Project Configuration

1.1 Create the Project

Create a new Spring Boot project with:

• Artifact: flyway-demo
• Dependencies:
  • Spring Web
  • Spring Data JPA
  • H2 Database
  • PostgreSQL Driver
  • Flyway Migration
  • Lombok

1.2 Maven Dependencies

<!-- pom.xml - Ensure Flyway is present -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

<!-- For PostgreSQL (optional) -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-postgresql</artifactId>
</dependency>

1.3 Folder Structure

src/main/resources/
├── application.properties
├── application-dev.properties
├── application-prod.properties
└── db/
    └── migration/
        ├── V1__create_users_table.sql
        ├── V2__add_email_to_users.sql
        ├── V3__create_products_table.sql
        ├── V4__create_orders_table.sql
        ├── V5__add_indexes.sql
        ├── V6__insert_reference_data.sql
        └── R__create_views.sql

1.4 Configuration

# application.properties
spring.profiles.active=dev

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

# Logging
logging.level.org.flywaydb=DEBUG

# application-dev.properties
spring.datasource.url=jdbc:h2:mem:flywaydb;DB_CLOSE_DELAY=-1
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

# JPA does NOT manage the schema - Flyway does!
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.show-sql=true

---

Part 2: First Migration - Users

2.1 V1 Script

Create the file src/main/resources/db/migration/V1__create_users_table.sql:

-- V1__create_users_table.sql
-- Creation of the users table

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(100) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT uk_users_username UNIQUE (username)
);

-- Comment for documentation
COMMENT ON TABLE users IS 'Application users table';

2.2 Corresponding Entity

// src/main/java/com/example/flywaydemo/model/User.java
package com.example.flywaydemo.model;

import jakarta.persistence.*;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.time.LocalDateTime;

@Entity
@Table(name = "users")
@Data
@NoArgsConstructor
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true, length = 50)
    private String username;

    @Column(nullable = false, length = 100)
    private String password;

    @Column(name = "first_name", length = 50)
    private String firstName;

    @Column(name = "last_name", length = 50)
    private String lastName;

    @Column(name = "created_at")
    private LocalDateTime createdAt = LocalDateTime.now();

    @Column(name = "updated_at")
    private LocalDateTime updatedAt = LocalDateTime.now();
}

2.3 Test

# Start the application
mvn spring-boot:run

# Check the logs - you should see:
# Flyway Community Edition
# Successfully validated 1 migration
# Migrating schema "PUBLIC" to version "1 - create users table"
# Successfully applied 1 migration

H2 Verification:

1. Open http://localhost:8080/h2-console
2. URL: jdbc:h2:mem:flywaydb
3. Verify that the users table exists
4. Verify that the flyway_schema_history table exists

SELECT * FROM flyway_schema_history;

---

Part 3: Schema Evolution

3.1 V2 - Add a Column

-- V2__add_email_to_users.sql
-- Adding the email column

ALTER TABLE users ADD COLUMN email VARCHAR(100);

-- Create an index on the email
CREATE UNIQUE INDEX idx_users_email ON users(email);

Entity Update:

// Add in User.java
@Column(unique = true, length = 100)
private String email;

3.2 V3 - New Products Table

-- V3__create_products_table.sql
-- Products table

CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    category VARCHAR(50),
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_price_positive CHECK (price >= 0),
    CONSTRAINT chk_stock_positive CHECK (stock_quantity >= 0)
);

// Product.java
package com.example.flywaydemo.model;

import jakarta.persistence.*;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;
import java.time.LocalDateTime;

@Entity
@Table(name = "products")
@Data
@NoArgsConstructor
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, length = 100)
    private String name;

    @Column(columnDefinition = "TEXT")
    private String description;

    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal price;

    @Column(name = "stock_quantity", nullable = false)
    private Integer stockQuantity = 0;

    @Column(length = 50)
    private String category;

    @Column(nullable = false)
    private Boolean active = true;

    @Column(name = "created_at")
    private LocalDateTime createdAt = LocalDateTime.now();
}

3.3 V4 - Orders Table with FK

-- V4__create_orders_table.sql
-- Orders table with relation to users

CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'PENDING',
    total_amount DECIMAL(12, 2) NOT NULL,
    shipping_address TEXT,
    
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) 
        REFERENCES users(id) ON DELETE CASCADE
);

-- Orders-products link table
CREATE TABLE order_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    
    CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) 
        REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_items_product FOREIGN KEY (product_id) 
        REFERENCES products(id)
);

3.4 V5 - Index for Performance

-- V5__add_indexes.sql
-- Adding indexes to improve performance

-- Index on order date
CREATE INDEX idx_orders_date ON orders(order_date);

-- Index on status
CREATE INDEX idx_orders_status ON orders(status);

-- Index on user
CREATE INDEX idx_orders_user ON orders(user_id);

-- Index on product category
CREATE INDEX idx_products_category ON products(category);

-- Index on active products
CREATE INDEX idx_products_active ON products(active) WHERE active = TRUE;

3.5 V6 - Reference Data

-- V6__insert_reference_data.sql
-- Insertion of reference data

-- Admin user
INSERT INTO users (username, password, email, first_name, last_name) 
VALUES ('admin', '$2a$10$hashedpassword', 'admin@example.com', 'Admin', 'System');

-- Product categories (via demo products)
INSERT INTO products (name, description, price, stock_quantity, category) VALUES
('Laptop Pro', 'High-performance laptop', 1299.99, 50, 'ELECTRONICS'),
('Smartphone X', 'Latest generation phone', 899.99, 100, 'ELECTRONICS'),
('T-Shirt Basic', '100% cotton T-shirt', 19.99, 500, 'CLOTHING'),
('Jeans Classic', 'Straight cut jeans', 49.99, 200, 'CLOTHING'),
('Java Book', 'Complete Java guide', 39.99, 75, 'BOOKS');

---

Part 4: Repeatable Migrations

4.1 Create a View

Repeatable migrations are re-executed if the file changes.

-- R__create_views.sql
-- View of active products with stock

CREATE OR REPLACE VIEW v_available_products AS
SELECT 
    id,
    name,
    price,
    stock_quantity,
    category
FROM products
WHERE active = TRUE 
  AND stock_quantity > 0;

-- View of statistics by category
CREATE OR REPLACE VIEW v_category_stats AS
SELECT 
    category,
    COUNT(*) as product_count,
    AVG(price) as avg_price,
    SUM(stock_quantity) as total_stock
FROM products
WHERE active = TRUE
GROUP BY category;

4.2 Test

Modify the view and restart:

-- Modify R__create_views.sql
CREATE OR REPLACE VIEW v_available_products AS
SELECT 
    id,
    name,
    price,
    stock_quantity,
    category,
    'IN_STOCK' as availability  -- New column!
FROM products
WHERE active = TRUE 
  AND stock_quantity > 0;

Upon restart, Flyway detects the checksum change and re-executes the script.

---

Part 5: Verification and Commands

5.1 Verify Migration State

Open the H2 console and execute:

-- Complete migration history
SELECT 
    installed_rank,
    version,
    description,
    type,
    installed_on,
    execution_time,
    success
FROM flyway_schema_history
ORDER BY installed_rank;

Expected result:

installed_rank	version	description	type	success
1	1	create users table	SQL	true
2	2	add email to users	SQL	true
3	3	create products table	SQL	true
4	4	create orders table	SQL	true
5	5	add indexes	SQL	true
6	6	insert reference data	SQL	true
7	NULL	create views	SQL	true

5.2 Maven Commands (Optional)

Add the Flyway plugin in pom.xml:

<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <configuration>
        <url>jdbc:h2:file:./target/flywaydb</url>
        <user>sa</user>
        <password></password>
    </configuration>
</plugin>

# View state
mvn flyway:info

# Apply migrations
mvn flyway:migrate

# Validate
mvn flyway:validate

---

Part 6: Error Handling

6.1 Simulate an Error

Create a script with an error:

-- V7__broken_migration.sql
-- Script with syntax error

CREATE TABLE broken_table (
    id BIGINT PRIMARY KEY
    name VARCHAR(50)  -- Missing comma!
);

Restart the application → Error!

6.2 Fix

1. Option A: Fix the script and use repair (dev only)

# Mark as resolved
mvn flyway:repair

# Reapply
mvn flyway:migrate

2. Option B: Delete the V7 file and the line in flyway_schema_history

3. Option C (Production): Create V8 to fix

-- V8__fix_broken_table.sql
-- Fixing the V7 error

DROP TABLE IF EXISTS broken_table;

CREATE TABLE broken_table (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50)
);

6.3 Golden Rule

danger

NEVER modify a migration script already executed in production!

Always create a NEW script to fix issues.

---

Part 7: PostgreSQL Migration

7.1 Production Configuration

# application-prod.properties
spring.datasource.url=jdbc:postgresql://localhost:5432/flywaydb
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.username=postgres
spring.datasource.password=secret

spring.h2.console.enabled=false

spring.jpa.hibernate.ddl-auto=validate
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

# Flyway
spring.flyway.enabled=true
spring.flyway.clean-disabled=true

7.2 Adapt Scripts (if necessary)

Some syntax differs between H2 and PostgreSQL:

-- H2
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ...
);

-- PostgreSQL
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    ...
);

Solution: Use environment-specific scripts:

db/migration/
├── h2/
│   └── V1__create_tables.sql
└── postgresql/
    └── V1__create_tables.sql

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

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

---

Part 8: Verification Endpoint

8.1 Controller to Verify Migrations

// src/main/java/com/example/flywaydemo/controller/MigrationController.java
package com.example.flywaydemo.controller;

import org.flywaydb.core.Flyway;
import org.flywaydb.core.api.MigrationInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@RestController
@RequestMapping("/api/migrations")
public class MigrationController {

    @Autowired
    private Flyway flyway;

    @GetMapping
    public List<Map<String, Object>> getMigrations() {
        return Arrays.stream(flyway.info().all())
            .map(this::toMap)
            .collect(Collectors.toList());
    }

    @GetMapping("/pending")
    public List<Map<String, Object>> getPendingMigrations() {
        return Arrays.stream(flyway.info().pending())
            .map(this::toMap)
            .collect(Collectors.toList());
    }

    private Map<String, Object> toMap(MigrationInfo info) {
        return Map.of(
            "version", info.getVersion() != null ? info.getVersion().toString() : "N/A",
            "description", info.getDescription(),
            "type", info.getType().name(),
            "state", info.getState().name(),
            "installedOn", info.getInstalledOn() != null ? info.getInstalledOn().toString() : "N/A"
        );
    }
}

Test:

curl http://localhost:8080/api/migrations

---

Summary

Files Created

File	Description
V1__create_users_table.sql	Base users table
V2__add_email_to_users.sql	Add email column
V3__create_products_table.sql	Products table
V4__create_orders_table.sql	Orders and order_items tables
V5__add_indexes.sql	Performance indexes
V6__insert_reference_data.sql	Initial data
R__create_views.sql	Views (repeatable)

Key Commands

Command	Action
mvn flyway:info	View migration state
mvn flyway:migrate	Apply migrations
mvn flyway:validate	Validate consistency
mvn flyway:repair	Repair history
mvn flyway:clean	Clean the DB (DANGEROUS!)

What You Have Learned

1. ✅ Configure Flyway with Spring Boot
2. ✅ Properly name scripts (V1__, V2__, R__)
3. ✅ Create incremental migrations
4. ✅ Manage reference data
5. ✅ Use repeatable migrations for views
6. ✅ Fix migration errors

---

🎯 Module Completed!

Congratulations! You have completed Module 7 on databases and transactions.

Module Recap:

• Chapter 1: Datasource Configuration (HikariCP)
• Chapter 2: Transaction Management (@Transactional)
• Chapter 3: Migration with Flyway
• Chapter 4: Migration with Liquibase
• TP1: Datasource Configuration
• TP2: Transactions and Rollback
• TP3: Flyway Migration (this TP)

➡️ Back to Module 7

---

⬅️ Back to Course Outline