Implement Flyway for database migrations

[nanotaboada]

Problem

The project currently lacks a structured approach to database schema versioning and migrations. This leads to:

• Manual SQL script execution for schema changes
• Inconsistent database states across environments (dev, test, prod)
• Difficulty tracking which migrations have been applied
• Risk of human error when deploying schema changes
• No rollback strategy for failed migrations
• Challenges onboarding new developers who need to set up local databases

Without a proper migration tool, database evolution becomes error-prone and difficult to manage as the application grows.

Required by: #286 (Add PostgreSQL support with unified migration-based initialization)

Proposed Solution

Integrate Flyway as the database migration tool for the Spring Boot project. This will:

• Provide version control for database schemas
• Enable automated, repeatable migrations across all environments
• Track migration history in the database
• Integrate seamlessly with Spring Boot auto-configuration
• Allow easy rollback and repair of failed migrations
• Ensure consistency between application code and database schema
• Lay the groundwork for PostgreSQL support (Add PostgreSQL support with unified migration-based initialization #286) — migrations must use SQL compatible with both SQLite and PostgreSQL

Suggested Approach

1. Add Flyway dependencies

Update pom.xml:

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

<!-- Flyway Database PostgreSQL (required for PostgreSQL support in Flyway 10+) -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-postgresql</artifactId>
</dependency>

2. Configure Flyway in Spring Boot

Update application.properties:

spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.jpa.hibernate.ddl-auto=validate

Remove spring.datasource.initialization-mode, schema.sql, and data.sql if present — Flyway takes over all schema management.

3. Create migration directory structure

src/main/resources/
└── db/
    └── migration/
        ├── V1__Create_players_table.sql
        ├── V2__Seed_starting11.sql
        └── V3__Seed_substitutes.sql

Educational note: Splitting seed data across two migrations illustrates the incremental, composable nature of the migration system. Rolling back V3 removes only the substitutes, leaving the starting 11 intact.

4. Migration naming convention

Follow Flyway's versioned migration pattern:

V{version}__{description}.sql

Examples:

• V1__Create_players_table.sql
• V2__Seed_starting11.sql
• V3__Seed_substitutes.sql

5. Write cross-dialect compatible SQL

Migrations must work with both SQLite (local dev/test) and PostgreSQL (Docker/production, see #286). Avoid provider-specific syntax:

Avoid (SQLite-only)	Avoid (PostgreSQL-only)	Use instead
AUTOINCREMENT	SERIAL / BIGSERIAL	INTEGER PRIMARY KEY (SQLite auto-assigns; use identity columns in PG via Hibernate)
TEXT for all strings	VARCHAR(n) enforced	VARCHAR(n) works in both
INTEGER for booleans	BOOLEAN	INTEGER (Hibernate maps it correctly for both)

Hibernate/JPA with ddl-auto=validate will handle the ORM layer; Flyway handles the DDL. Keep migration SQL as standard as possible.

V1__Create_players_table.sql (example):

CREATE TABLE IF NOT EXISTS players (
    id          INTEGER PRIMARY KEY,
    firstName   VARCHAR(100),
    middleName  VARCHAR(100),
    lastName    VARCHAR(100),
    dateOfBirth VARCHAR(20),
    squadNumber INTEGER,
    position    VARCHAR(50),
    abbrPosition VARCHAR(10),
    team        VARCHAR(100),
    league      VARCHAR(100),
    starting11  INTEGER
);

V2__Seed_starting11.sql (example):

INSERT INTO players (firstName, middleName, lastName, dateOfBirth, squadNumber, position, abbrPosition, team, league, starting11)
VALUES
    ('Emiliano', 'Viviano', 'Martínez', '1992-08-02', 23, 'Goalkeeper', 'GK', 'Aston Villa', 'Premier League', 1),
    -- ... 10 more Starting XI players (starting11 = 1)
;

V3__Seed_substitutes.sql (example):

INSERT INTO players (firstName, middleName, lastName, dateOfBirth, squadNumber, position, abbrPosition, team, league, starting11)
VALUES
    ('Gerónimo', NULL, 'Rulli', '1992-05-07', 12, 'Goalkeeper', 'GK', 'Marseille', 'Ligue 1', 0),
    -- ... 13 more Substitute players (starting11 = 0)
;

6. Update database initialization logic

• Remove any manual schema.sql / data.sql files
• Remove spring.sql.init.* properties
• Let Flyway handle all schema and seed data management

7. Update documentation

README.md:

• Document migration workflow (how to create and apply new migrations)
• Explain that the database auto-initializes via Flyway on startup
• Add database reset workflow: delete the .db file and re-run the app

Acceptance Criteria

• flyway-core and flyway-database-postgresql added to pom.xml
• Flyway configured in application.properties (spring.flyway.enabled=true)
• spring.jpa.hibernate.ddl-auto=validate (Flyway owns DDL, not Hibernate)
• Migration directory created at src/main/resources/db/migration/
• V1__Create_players_table.sql created with cross-dialect compatible SQL
• V2__Seed_starting11.sql seeds the 11 Starting XI players (starting11 = 1)
• V3__Seed_substitutes.sql seeds the 14 Substitute players (starting11 = 0)
• Rolling back V3 removes only substitutes; V2 removes only Starting XI
• Application starts successfully and Flyway applies migrations automatically
• flyway_schema_history table is created and populated correctly
• Manual schema.sql / data.sql files removed (if present)
• All existing tests pass
• Application can start from a clean database and build schema via migrations
• Migration SQL is verified to be compatible with both SQLite and PostgreSQL
• README.md updated with migration workflow

References

• Flyway Official Documentation
• Spring Boot Flyway Integration
• Flyway Naming Patterns
• PostgreSQL Docker Official Images
• Required by: Add PostgreSQL support with unified migration-based initialization #286 (Add PostgreSQL support with unified migration-based initialization)

[coderabbitai]

🤖 CodeRabbit Planning

Planning is in progress. This will take a moment...

[coderabbitai]

Coding Plan

Approach

Integrate Flyway into the Spring Boot project to provide version-controlled database migrations, replacing the current pre-seeded database approach with automated, repeatable schema management across all environments.

Observations

This is a Spring Boot 4.0.0 application using SQLite for production and H2 for testing. The project has a single JPA entity (Book) with no relationships, uses a custom UnixTimestampConverter for storing dates as Unix timestamps, and currently relies on a pre-seeded SQLite database file that's copied into Docker containers at startup. Configuration is managed through application.properties files, with Hibernate's ddl-auto set to "none" in production and "create-drop" in tests.

Assumptions

Assumption 1: Test environment database initialization approach

Options Considered:

• Enable Flyway for tests with H2-compatible migrations (consistent but requires maintaining cross-database SQL)
• Keep tests using Hibernate's ddl-auto=create-drop (simpler, faster tests, but doesn't validate migrations)

Chosen Option: Keep tests using Hibernate's ddl-auto=create-drop initially

Rationale: This maintains the fast test execution developers currently enjoy, avoids the complexity of writing cross-database SQL, and focuses the migration effort on production/development environments where it matters most. Tests can be migrated to Flyway in a future enhancement if migration validation becomes critical.

Assumption 2: Handling existing pre-seeded databases

Options Considered:

• Remove pre-seeded database entirely and rely only on Flyway (clean but breaking change)
• Use baseline-on-migrate to mark existing databases as "already at V1" (backwards compatible)

Chosen Option: Use baseline-on-migrate=true to gracefully handle existing databases

Rationale: This allows Flyway to recognize pre-existing schemas as already migrated to V1, while new installations will run V1 from scratch. This provides the best developer experience by avoiding disruption to existing environments while establishing proper version control going forward.

💡 User Tips

Regenerate the plan with different choices with @coderabbitai <feedback>.

Implementation Steps

Phase 1: Add Flyway Dependency and Basic Configuration

Set up Flyway as a managed dependency and configure its basic integration with Spring Boot, ensuring it activates only in production environments while preserving the current test setup.

Task 1: Add Flyway dependency to Maven

Add the Flyway core dependency to the project build configuration.

• Add org.flywaydb:flyway-core to pom.xml dependencies section (no version needed - Spring Boot 4.0.0 manages Flyway 11.14.1)
• Place the dependency in the main dependencies block alongside other Spring Boot starters

Task 2: Configure Flyway in production application properties

Configure Flyway to work with SQLite and handle existing databases gracefully.

• Update src/main/resources/application.properties to add Flyway configuration properties
• Enable Flyway with spring.flyway.enabled=true
• Set baseline-on-migrate=true to handle existing pre-seeded databases
• Configure migration location as spring.flyway.locations=classpath:db/migration
• Set baseline-version=1 to align with the initial migration version
• Maintain the existing SQLite datasource configuration unchanged

Task 3: Ensure test environment bypasses Flyway

Preserve the fast test execution by keeping H2's auto-schema generation for tests.

• Update src/test/resources/application.properties to disable Flyway with spring.flyway.enabled=false
• Keep the existing spring.jpa.hibernate.ddl-auto=create-drop setting for test environment
• Verify no Flyway configuration conflicts with test H2 database

🤖 Prompt for AI agents

Set up Flyway dependency and configuration for production while preserving the
current test setup.

Add Flyway core dependency to pom.xml (Spring Boot will manage the version)

In src/main/resources/application.properties:
- Enable Flyway
- Set baseline-on-migrate=true for existing databases
- Configure migration location to classpath:db/migration
- Set baseline-version=1
- Keep existing SQLite datasource configuration

In src/test/resources/application.properties:
- Disable Flyway
- Keep existing ddl-auto=create-drop for H2

Phase 2: Create Migration Infrastructure and Initial Schema

Establish the migration directory structure and create the baseline migration that captures the current database schema.

Task 1: Create migration directory structure

Set up the standard Flyway directory layout for versioned migrations.

• Create directory structure: src/main/resources/db/migration/
• Ensure the directory is tracked in Git (add .gitkeep if needed)

Task 2: Create initial baseline migration

Write the V1 migration that defines the complete current schema.

• Create V1__Create_books_table.sql in src/main/resources/db/migration/
• Define CREATE TABLE statement for books table with columns: isbn (TEXT PRIMARY KEY), title (TEXT NOT NULL), subtitle (TEXT), author (TEXT), publisher (TEXT), published (INTEGER, for Unix timestamp), pages (INTEGER), description (TEXT), website (TEXT)
• Include PRAGMA foreign_keys = ON statement at the top (SQLite best practice, even though no foreign keys exist yet)
• Ensure the schema matches the Book entity structure, particularly the INTEGER type for published column (used by UnixTimestampConverter)

🤖 Prompt for AI agents

Create Flyway migration infrastructure and baseline schema.

Create directory: src/main/resources/db/migration/

Create V1__Create_books_table.sql with:
- PRAGMA foreign_keys = ON at the top
- CREATE TABLE books with columns: isbn (TEXT PRIMARY KEY), title (TEXT NOT
NULL), subtitle (TEXT), author (TEXT), publisher (TEXT), published (INTEGER),
pages (INTEGER), description (TEXT), website (TEXT)
- Ensure schema matches the Book entity, especially INTEGER type for published
column

Phase 3: Update Docker and Deployment Configuration

Modify the containerization setup to work seamlessly with Flyway while maintaining backwards compatibility.

Task 1: Update Docker entrypoint script

Adjust the container initialization to work with Flyway's baseline approach.

• Update scripts/entrypoint.sh to keep the database copy logic for backwards compatibility
• Ensure the script doesn't interfere with Flyway's schema_history table
• The entrypoint should copy the pre-seeded database if it doesn't exist (for existing installations) but Flyway will baseline it on first connection
• Keep the existing error handling and logging

Task 2: Verify CI/CD pipeline compatibility

Ensure GitHub Actions workflows handle Flyway migrations correctly.

• Review .github/workflows/maven.yml to confirm no changes needed (Flyway runs automatically at application startup)
• Verify test job still works with Flyway disabled in test properties
• Confirm Docker container build includes the db/migration directory in the image

🤖 Prompt for AI agents

Update Docker configuration for Flyway compatibility.

In scripts/entrypoint.sh:
- Keep the database copy logic for backwards compatibility
- Ensure script doesn't interfere with Flyway's schema_history table
- Copy pre-seeded database only if it doesn't exist
- Maintain existing error handling and logging

Verify .github/workflows/maven.yml:
- Confirm Flyway runs automatically at startup (no changes needed)
- Ensure tests work with Flyway disabled
- Verify db/migration directory is included in Docker image

Phase 4: Documentation and Developer Guidance

Update project documentation to explain the new migration-based workflow and provide clear instructions for common operations.

Task 1: Document Flyway workflow in README

Add comprehensive migration documentation to the main project README.

• Update README.md to add a new "Database Migrations" section
• Explain that Flyway manages schema versioning automatically
• Document how existing databases are baselined (no action needed)
• Provide instructions for creating new migrations: naming convention (V{number}__{description}.sql), location (src/main/resources/db/migration/), and execution timing (automatic on application startup)
• Document the baseline-on-migrate behavior for existing installations
• Add a note that tests continue using H2 with auto-schema generation

Task 2: Add migration examples and troubleshooting

Provide practical guidance for working with migrations.

• Add example of creating a new migration file (e.g., V2__Add_rating_column.sql)
• Document how to view migration history (query flyway_schema_history table)
• Add troubleshooting section for common issues: baseline already applied, checksum mismatches, manual repair procedures
• Include note about SQLite-specific considerations (no schemas, PRAGMA statements, INTEGER for dates)
• Reference Flyway official documentation for advanced scenarios

🤖 Prompt for AI agents

Add comprehensive Flyway documentation to README.md.

Add "Database Migrations" section covering:
- Flyway manages schema versioning automatically
- Existing databases are baselined automatically (no action needed)
- Creating new migrations: naming (V{number}__{description}.sql), location
(src/main/resources/db/migration/), automatic execution
- Baseline-on-migrate behavior
- Tests use H2 with auto-schema generation

Add examples and troubleshooting:
- Example migration file (V2__Add_rating_column.sql)
- View migration history (query flyway_schema_history table)
- Common issues: baseline already applied, checksum mismatches, manual repair
- SQLite considerations: no schemas, PRAGMA statements, INTEGER for dates
- Reference to Flyway documentation

---

🚀 Next Steps

🤖 All AI agent prompts combined

Task: 1

Set up Flyway dependency and configuration for production while preserving the
current test setup.

Add Flyway core dependency to pom.xml (Spring Boot will manage the version)

In src/main/resources/application.properties:
- Enable Flyway
- Set baseline-on-migrate=true for existing databases
- Configure migration location to classpath:db/migration
- Set baseline-version=1
- Keep existing SQLite datasource configuration

In src/test/resources/application.properties:
- Disable Flyway
- Keep existing ddl-auto=create-drop for H2
===============================================================================

Task: 2

Create Flyway migration infrastructure and baseline schema.

Create directory: src/main/resources/db/migration/

Create V1__Create_books_table.sql with:
- PRAGMA foreign_keys = ON at the top
- CREATE TABLE books with columns: isbn (TEXT PRIMARY KEY), title (TEXT NOT
NULL), subtitle (TEXT), author (TEXT), publisher (TEXT), published (INTEGER),
pages (INTEGER), description (TEXT), website (TEXT)
- Ensure schema matches the Book entity, especially INTEGER type for published
column
===============================================================================

Task: 3

Update Docker configuration for Flyway compatibility.

In scripts/entrypoint.sh:
- Keep the database copy logic for backwards compatibility
- Ensure script doesn't interfere with Flyway's schema_history table
- Copy pre-seeded database only if it doesn't exist
- Maintain existing error handling and logging

Verify .github/workflows/maven.yml:
- Confirm Flyway runs automatically at startup (no changes needed)
- Ensure tests work with Flyway disabled
- Verify db/migration directory is included in Docker image
===============================================================================

Task: 4

Add comprehensive Flyway documentation to README.md.

Add "Database Migrations" section covering:
- Flyway manages schema versioning automatically
- Existing databases are baselined automatically (no action needed)
- Creating new migrations: naming (V{number}__{description}.sql), location
(src/main/resources/db/migration/), automatic execution
- Baseline-on-migrate behavior
- Tests use H2 with auto-schema generation

Add examples and troubleshooting:
- Example migration file (V2__Add_rating_column.sql)
- View migration history (query flyway_schema_history table)
- Common issues: baseline already applied, checksum mismatches, manual repair
- SQLite considerations: no schemas, PRAGMA statements, INTEGER for dates
- Reference to Flyway documentation

💡 Iterate on the plan with: @coderabbitai <feedback>

Example Feedback
- @coderabbitai You can skip phase 3. Add a simple unit test case for phase 2.
- @coderabbitai For assumption 1 go ahead with option 3 and replan.

---

💬 Have feedback or questions? Drop into our discord!