Database Migrations with Flyway in Spring Boot and Kotlin

Database migrations are a crucial part of any production application. They help you manage database schema changes in a version-controlled, reliable way. In this guide, we'll explore how to implement and manage database migrations in a Spring Boot application using Flyway.

Key Takeaways

• Understanding database migrations and why they're essential
• Setting up Flyway in a Spring Boot project
• Creating and organizing migration scripts
• Handling common migration scenarios
• Best practices for production environments

What are Database Migrations?

Database migrations are a way to evolve your database schema over time. Instead of manually applying SQL scripts or modifying the database directly, migrations provide a systematic approach to track and apply database changes. This is especially important when:

• Working in a team where multiple developers modify the database
• Deploying your application across different environments
• Maintaining a history of database changes
• Ensuring consistent database states across all installations

Setting Up Flyway

First, let's add Flyway to your Spring Boot project. If you're using Gradle, add this to your build.gradle.kts:

dependencies {
    implementation("org.flywaydb:flyway-core")
    // If you're using MySQL
    implementation("org.flywaydb:flyway-mysql")
}

Configure Flyway in your application.yml:

spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true
    baseline-version: 0

Creating Your First Migration

Flyway follows a specific naming convention for migration files:

V{VERSION}__{DESCRIPTION}.sql

Create your first migration in src/main/resources/db/migration/V1__init_schema.sql:

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

CREATE TABLE posts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Migration Versioning Strategy

A good versioning strategy is crucial. Here's a recommended approach:

• Use timestamps as versions (e.g., V20250128001__init_schema.sql)
• Use double underscores to separate version from description
• Keep descriptions short but meaningful
• Use lowercase and underscores for descriptions

Example structure:

db/migration/
├── V20250128001__init_schema.sql
├── V20250128002__add_user_status.sql
├── V20250129001__create_comments_table.sql
└── V20250129002__add_post_categories.sql

Common Migration Scenarios

Adding a New Column

-- V20250128002__add_user_status.sql
ALTER TABLE users
ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE';

Creating a Join Table

-- V20250129001__create_post_tags.sql
CREATE TABLE tags (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE post_tags (
    post_id BIGINT NOT NULL,
    tag_id BIGINT NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id) REFERENCES tags(id)
);

Handling Migration in Code

You can also programmatically check migration status using the Flyway API:

@Component
class MigrationCheck(private val flyway: Flyway) {
    
    fun checkMigrationStatus(): MigrationInfo {
        val current = flyway.info().current()
        val pending = flyway.info().pending()
        
        return MigrationInfo(
            currentVersion = current?.version?.version ?: "No migrations",
            pendingMigrations = pending.size
        )
    }
}

data class MigrationInfo(
    val currentVersion: String,
    val pendingMigrations: Int
)

Best Practices

1. Never Modify Existing Migrations
   • Once a migration is committed and deployed, treat it as immutable
   • Create new migrations for changes instead of modifying existing ones
2. Use Meaningful Names
   • Migration descriptions should clearly indicate what changes are being made
   • Keep names concise but descriptive
3. Include Rollback Scripts
   • For critical environments, create rollback scripts (prefixed with 'U')
   • Test rollback scripts in development
4. Transaction Management
   • Each migration runs in its own transaction by default
   • Use @Transaction if you need custom transaction handling
5. Testing Migrations
   • Test migrations against the same database type as production
   • Use TestContainers for integration tests

Example test:

@SpringBootTest
@Testcontainers
class MigrationTests {
    
    companion object {
        @Container
        val mysql = MySQLContainer<Nothing>("mysql:8.0")
    }
    
    @Test
    fun `migrations should apply successfully`() {
        val flyway = Flyway.configure()
            .dataSource(mysql.jdbcUrl, mysql.username, mysql.password)
            .load()
            
        val result = flyway.migrate()
        
        assertThat(result.success).isTrue()
    }
}

Common Issues and Solutions

Schema History Table Issues

If you encounter issues with the schema history table:

@Bean
fun cleanupHistoryTable(flyway: Flyway) = CommandLineRunner {
    flyway.repair()
}

Handling Large Datasets

For tables with large datasets, consider batching:

-- Instead of one large ALTER TABLE
ALTER TABLE large_table
ADD COLUMN new_column VARCHAR(100);

-- Use batched updates
UPDATE large_table
SET new_column = 'default'
WHERE id BETWEEN 1 AND 10000;

UPDATE large_table
SET new_column = 'default'
WHERE id BETWEEN 10001 AND 20000;

Monitoring and Maintenance

Consider adding monitoring for migration execution:

@Component
class MigrationListener : FlywayCallback {
    
    private val logger = LoggerFactory.getLogger(javaClass)
    
    override fun beforeMigrate(context: Context?) {
        logger.info("Starting database migration")
    }
    
    override fun afterMigrate(context: Context?) {
        logger.info("Database migration completed")
    }
}

Conclusion

Database migrations are a critical part of application maintenance and evolution. With Flyway and Spring Boot, you can manage your database schema changes effectively and reliably. Remember to:

• Version your migrations appropriately
• Test migrations thoroughly before production deployment
• Follow best practices for naming and organization
• Include rollback strategies for critical changes
• Monitor migration execution in production

By following these guidelines, you'll have a robust foundation for managing database changes in your Spring Boot application.