Database Migrations - Blackjack API

Overview

The Blackjack API uses Flyway for database schema migrations. Flyway provides version-controlled, repeatable database migrations that ensure your MySQL schema is always in a consistent state.

Why Flyway?

Flyway is used alongside R2DBC because:

R2DBC limitation: R2DBC doesn’t support DDL operations (schema creation)
JDBC for migrations: Flyway uses JDBC for reliable schema management
R2DBC for queries: Application uses R2DBC for reactive data access
Separation of concerns: Schema management separate from data access

Dependencies

Flyway is configured in pom.xml:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-mysql</artifactId>
</dependency>
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

Flyway Configuration

Local Development

Configure Flyway in application-local.yml:

spring:
  flyway:
    enabled: true
    url: jdbc:mysql://localhost:3307/blackjack
    user: blackjack
    password: blackjack
    locations: classpath:db/migration

Docker Environment

For Docker deployments, use service names in application-docker.yml:

spring:
  flyway:
    enabled: true
    url: jdbc:mysql://mysql:3306/blackjack
    user: blackjack
    password: blackjack
    locations: classpath:db/migration

Production Environment

For production, use environment variables in application-prod.yml:

spring:
  flyway:
    enabled: ${SPRING_FLYWAY_ENABLED:true}
    url: ${SPRING_FLYWAY_URL}
    user: ${SPRING_FLYWAY_USER}
    password: ${SPRING_FLYWAY_PASSWORD}
    locations: classpath:db/migration

Configuration Properties

Property	Description	Example
`spring.flyway.enabled`	Enable/disable Flyway	`true`
`spring.flyway.url`	JDBC connection URL (not R2DBC)	`jdbc:mysql://localhost:3307/blackjack`
`spring.flyway.user`	Database username	`blackjack`
`spring.flyway.password`	Database password	`blackjack`
`spring.flyway.locations`	Migration scripts location	`classpath:db/migration`

Important: Flyway uses JDBC URLs (jdbc:mysql://), not R2DBC URLs (r2dbc:mysql://).

Migration Scripts

Migration scripts are stored in src/main/resources/db/migration/.

Naming Convention

Flyway follows a strict naming convention:

V{version}__{description}.sql

V: Version prefix (required)
: Version number (e.g., 1, 2, 1.1, 2.5)
__: Double underscore separator (required)
: Human-readable description
.sql: File extension

Example Migrations

V1__create_players.sql

CREATE TABLE players (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  external_id VARCHAR(36) NOT NULL UNIQUE,
  name VARCHAR(30) NOT NULL UNIQUE,
  wins INT NOT NULL,
  losses INT NOT NULL
);

This migration creates the initial players table with:

Auto-incrementing primary key
UUID-based external ID for API references
Unique player names (1-30 characters)
Win and loss counters

Migration Lifecycle

Application Startup

Flyway initializes: Checks for flyway_schema_history table
Detects pending migrations: Compares filesystem with database
Executes migrations: Runs pending migrations in version order
Records success: Updates flyway_schema_history table
Application starts: Only after migrations succeed

Migration States

State	Description
Pending	Migration exists but hasn’t run
Success	Migration completed successfully
Failed	Migration failed (blocks startup)
Ignored	Migration skipped (e.g., missing)

Creating New Migrations

Step 1: Create Migration File

Create a new SQL file in src/main/resources/db/migration/:

touch src/main/resources/db/migration/V2__add_player_email.sql

Step 2: Write Migration SQL

ALTER TABLE players
ADD COLUMN email VARCHAR(255) UNIQUE;

Step 3: Test Migration

Restart the application. Flyway will automatically detect and run the new migration.

Step 4: Verify Migration

Check the flyway_schema_history table:

SELECT * FROM flyway_schema_history ORDER BY installed_rank;

Schema History Table

Flyway maintains a flyway_schema_history table:

CREATE TABLE flyway_schema_history (
  installed_rank INT NOT NULL,
  version VARCHAR(50),
  description VARCHAR(200),
  type VARCHAR(20),
  script VARCHAR(1000),
  checksum INT,
  installed_by VARCHAR(100),
  installed_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  execution_time INT,
  success BOOLEAN,
  PRIMARY KEY (installed_rank)
);

This table tracks:

Which migrations have run
When they ran
Whether they succeeded
Checksums to detect changes

Best Practices

1. Never Modify Executed Migrations

Once a migration has been applied to any environment, never modify it. Create a new migration instead.

Bad:

-- Modifying V1__create_players.sql after it's been deployed
ALTER TABLE players ADD COLUMN email VARCHAR(255);

Good:

-- Creating V2__add_player_email.sql
ALTER TABLE players ADD COLUMN email VARCHAR(255);

2. Keep Migrations Small

Create focused migrations that do one thing:

-- V2__add_player_email.sql
ALTER TABLE players ADD COLUMN email VARCHAR(255) UNIQUE;

-- V3__add_player_timestamps.sql  
ALTER TABLE players ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE players ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

3. Use Descriptive Names

Migration names should clearly describe what they do:

V1__create_players.sql (good)
V1__initial.sql (bad - not descriptive)
V2__add_player_email_column.sql (good)
V2__update.sql (bad - too vague)

4. Test Migrations Locally

Always test migrations in local environment before deploying:

Run migration locally
Verify schema changes
Test application functionality
Check rollback strategy (if needed)

5. Include Indexes

Create indexes for frequently queried columns:

-- V1__create_players.sql
CREATE TABLE players (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  external_id VARCHAR(36) NOT NULL UNIQUE,
  name VARCHAR(30) NOT NULL UNIQUE,
  wins INT NOT NULL,
  losses INT NOT NULL,
  INDEX idx_wins (wins),
  INDEX idx_losses (losses)
);

6. Document Complex Migrations

Add comments for complex schema changes:

-- V4__optimize_player_ranking.sql
-- Add composite index to optimize ranking query
-- See: MySqlPlayerRepositoryAdapter.findRanking()
CREATE INDEX idx_player_ranking 
ON players (wins DESC, losses ASC, name ASC);

Rollback Strategies

Flyway supports rollbacks in paid versions. For community edition:

Manual Rollback

Create compensating migrations:

-- V3__remove_player_email.sql
ALTER TABLE players DROP COLUMN email;

Backup and Restore

For critical migrations, backup before applying:

# Backup database
mysqldump -u blackjack -p blackjack > backup_before_v3.sql

# Apply migration
# (Flyway runs automatically on startup)

# Restore if needed
mysql -u blackjack -p blackjack < backup_before_v3.sql

Troubleshooting

Migration Failed

If a migration fails:

Check logs: Review error message in application logs
Fix SQL: Correct the SQL syntax or logic error

Clean failed migration:

DELETE FROM flyway_schema_history WHERE success = 0;

Retry: Restart application to re-run migration

Checksum Mismatch

If Flyway detects a modified migration:

Migration checksum mismatch for migration version 1

Solution: Never modify executed migrations. Create a new migration instead.

Out of Order Migrations

If migrations are executed out of order:

spring:
  flyway:
    out-of-order: true  # Allow out-of-order migrations

Only enable out-of-order in development. Keep disabled in production.

Flyway Commands

While Flyway runs automatically on startup, you can also use Maven plugin:

<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>10.0.0</version>
</plugin>

Common commands:

# Check migration status
mvn flyway:info

# Apply migrations
mvn flyway:migrate

# Validate migrations
mvn flyway:validate

# Repair metadata (use with caution)
mvn flyway:repair

Production Considerations

1. Disable Flyway for Some Deployments

For environments where schema is managed separately:

spring:
  flyway:
    enabled: false

2. Separate Migration Credentials

Use different credentials for migrations vs. application:

spring:
  flyway:
    user: flyway_admin  # Admin user for schema changes
    password: ${FLYWAY_PASSWORD}
  
  r2dbc:
    username: app_user  # Limited user for data access
    password: ${R2DBC_PASSWORD}

3. Migration Timeouts

For long-running migrations:

spring:
  flyway:
    connect-retries: 3
    lock-retry-count: 10

Get Started

Architecture

Game Operations

Player Management

Database

Deployment

Development

​Overview

​Why Flyway?

​Dependencies

​Flyway Configuration

​Local Development

​Docker Environment

​Production Environment

​Configuration Properties

​Migration Scripts

​Naming Convention

​Example Migrations

​Migration Lifecycle

​Application Startup

​Migration States

​Creating New Migrations

​Step 1: Create Migration File

​Step 2: Write Migration SQL

​Step 3: Test Migration

​Step 4: Verify Migration

​Schema History Table

​Best Practices

​1. Never Modify Executed Migrations

​2. Keep Migrations Small

​3. Use Descriptive Names

​4. Test Migrations Locally

​5. Include Indexes

​6. Document Complex Migrations

​Rollback Strategies

​Manual Rollback

​Backup and Restore

​Troubleshooting

​Migration Failed

​Checksum Mismatch

​Out of Order Migrations

​Flyway Commands

​Production Considerations

​1. Disable Flyway for Some Deployments

​2. Separate Migration Credentials

​3. Migration Timeouts

​Related Documentation

Build docs developers (and LLMs) love

Overview

Why Flyway?

Dependencies

Flyway Configuration

Local Development

Docker Environment

Production Environment

Configuration Properties

Migration Scripts

Naming Convention

Example Migrations

Migration Lifecycle

Application Startup

Migration States

Creating New Migrations

Step 1: Create Migration File

Step 2: Write Migration SQL

Step 3: Test Migration

Step 4: Verify Migration

Schema History Table

Best Practices

1. Never Modify Executed Migrations

2. Keep Migrations Small

3. Use Descriptive Names

4. Test Migrations Locally

5. Include Indexes

6. Document Complex Migrations

Rollback Strategies

Manual Rollback

Backup and Restore

Troubleshooting

Migration Failed

Checksum Mismatch

Out of Order Migrations

Flyway Commands

Production Considerations

1. Disable Flyway for Some Deployments

2. Separate Migration Credentials

3. Migration Timeouts

Related Documentation