Add Flyway migrations for DB schema

[axymthr]

Right now, the DB is initialized through JPA annotations across dev, test, and prod profiles. Here's the desired behavior:

• Keep dev profile with JPA annotations (or add a schema.sql file if necessary/appropriate)
• Use Flyway for integration tests with Testcontainers
• Use Flyway for prod

Summary of Changes and How it Works:

1. Default Run (Local Dev):
   • No profile specified, so application.properties is active.
   • spring.jpa.hibernate.ddl-auto=create-drop takes effect.
   • spring.flyway.enabled=false, so Flyway doesn't run.
   • Connects to Docker Compose Postgres. Schema generated by Hibernate.
2. Integration Test Run:
   • @ActiveProfiles("test") activates the test profile.
   • application.properties is loaded first, then application-test.properties overrides properties.
   • spring.flyway.enabled becomes true (from application-test.properties).
   • spring.jpa.hibernate.ddl-auto becomes validate (from application-test.properties).
   • @DynamicPropertySource overrides the datasource URL/user/pass to point to the Testcontainer.
   • Flyway runs its migrations against the Testcontainer DB. Hibernate validates entities against the migrated schema.
3. Production Run (--spring.profiles.active=prod):
   • The prod profile is active.
   • application.properties is loaded first, then application-prod.properties overrides properties.
   • spring.flyway.enabled becomes true (from application-prod.properties).
   • spring.jpa.hibernate.ddl-auto becomes validate (from application-prod.properties).
   • Datasource properties are overridden by placeholders, which must be resolved by the external config file.
   • Flyway runs its migrations against the Production DB. Hibernate validates entities against the migrated schema.

1. Project Structure:

Here's a typical Maven/Gradle project structure:

my-spring-app/
├── pom.xml 
├── compose.yml        # For local Postgres dev using Docker
├── src/
│   ├── main/
│   │   ├── java/
│   │   │   └── com/example/myapp/
│   │   │       └── MySpringBootApplication.java # Main application class
│   │   ├── resources/
│   │   │   ├── application.properties         # Default profile (implicitly 'dev')
│   │   │   ├── application-prod.properties    # Production profile specifics
│   │   │   └── db/
│   │   │       └── migration/
│   │   │           └── V1__Initial_schema.sql # Example Flyway migration
│   └── test/
│       ├── java/
│       │   └── com/example/myapp/
│       │       └── MyServiceIntegrationTest.java # Integration test using Testcontainers
│       └── resources/
│           └── application-test.properties        # Optional: Test-specific properties
│                                             # (often empty if main props are suitable)
└── external_config/             # <<-- IMPORTANT: This directory lives OUTSIDE your Git repo on the VM
    └── application-prod.properties # External prod properties file with secrets
    └── certs/
        └── keystore.p12           # External PKCS12 keystore file

2. Dependencies (pom.xml):

Ensure you have these key dependencies:

• Spring Boot Starter Web (spring-boot-starter-web)
• Spring Boot Starter Data JPA (spring-boot-starter-data-jpa)
• PostgreSQL Driver (org.postgresql:postgresql)
• Flyway Core (org.flywaydb:flyway-core)
• Testcontainers (org.testcontainers:postgresql, org.testcontainers:junit-jupiter - for tests)

1. Project Structure:

Here's a typical Maven/Gradle project structure:

my-spring-app/
├── pom.xml                   # Or build.gradle
├── docker-compose.yml        # For local Postgres dev using Docker
├── .gitignore                # To exclude target/, secrets, etc.
├── src/
│   ├── main/
│   │   ├── java/
│   │   │   └── com/example/myapp/
│   │   │       └── MySpringBootApplication.java # Main application class
│   │   │       └── config/
│   │   │           └── MandatoryPropertiesConfig.java # Optional: For explicit checks
│   │   ├── resources/
│   │   │   ├── application.properties         # Default profile (implicitly 'dev')
│   │   │   ├── application-prod.properties    # Production profile specifics
│   │   │   └── db/
│   │   │       └── migration/
│   │   │           └── V1__Initial_schema.sql # Example Flyway migration
│   └── test/
│       ├── java/
│       │   └── com/example/myapp/
│       │       └── MyServiceIntegrationTest.java # Integration test using Testcontainers
│       └── resources/
│           └── application.properties        # Optional: Test-specific properties
│                                             # (often empty if main props are suitable)
└── external_config/             # <<-- IMPORTANT: This directory lives OUTSIDE your Git repo on the VM
    └── application-prod.properties # External prod properties file with secrets
    └── certs/
        └── keystore.p12           # External PKCS12 keystore file

2. Dependencies (pom.xml or build.gradle):

Ensure you have these key dependencies:

• Spring Boot Starter Web (spring-boot-starter-web)
• Spring Boot Starter Data JPA (spring-boot-starter-data-jpa)
• Spring Boot Starter Actuator (spring-boot-starter-actuator) (Recommended for health checks etc.)
• PostgreSQL Driver (org.postgresql:postgresql)
• Flyway Core (org.flywaydb:flyway-core)
• Testcontainers (org.testcontainers:postgresql, org.testcontainers:junit-jupiter - for tests)

3. Configuration Files:

a) src/main/resources/application.properties (Default/Dev Profile)

Properties

# --- Default Application Settings (implicitly 'dev' profile) ---


# JPA/Hibernate Settings (Example)
spring.jpa.hibernate.ddl-auto=none # IMPORTANT: Let Flyway manage the schema
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.default_schema=public # Or your desired schema

# --- Flyway Configuration ---
spring.flyway.enabled=true
# Flyway will automatically use the configured spring.datasource
# Location of migration scripts (default is classpath:db/migration)
# spring.flyway.locations=classpath:db/migration
# Schema Flyway manages (defaults to default schema of the datasource user)
# spring.flyway.schemas=public

b) src/main/resources/application-prod.properties (Production Profile Placeholders)

Properties

# --- Flyway Configuration (Prod) ---
# Defaults from application.properties are usually fine, but you can override if needed.
# e.g., spring.flyway.validate-on-migrate=true (default is true)

5. Integration Test (MyServiceIntegrationTest.java)

Java

import javax.sql.DataSource;
import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.NONE) // Adjust if you need a web server for tests
@Testcontainers // Enable Testcontainers support for JUnit 5
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) // Disable default H2 replacement
// @ActiveProfiles("test") // Optional: activate a specific test profile if needed
public class MyServiceIntegrationTest {

    // Define the PostgreSQL container
    @Container
    private static final PostgreSQLContainer<?> postgresContainer =
            new PostgreSQLContainer<>(DockerImageName.parse("postgres:15"))
                    .withDatabaseName("testdb")
                    .withUsername("testuser")
                    .withPassword("testsecret");
                    // .withInitScript("db/init_test.sql"); // Optional: Run extra SQL only for tests AFTER Flyway

    // Dynamically set Spring properties based on the running container
    @DynamicPropertySource
    static void postgresqlProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgresContainer::getJdbcUrl);
        registry.add("spring.datasource.username", postgresContainer::getUsername);
        registry.add("spring.datasource.password", postgresContainer::getPassword);
        // Flyway will automatically use these properties and run migrations
        // registry.add("spring.flyway.enabled", () -> "true"); // Usually not needed, enabled by default
    }

    @Autowired
    private ApplicationContext applicationContext;

    @Autowired
    private DataSource dataSource; // Inject DataSource to verify connection

    // Example Test
    @Test
    void contextLoads() {
        assertThat(applicationContext).isNotNull();
        System.out.println("Test Datasource URL: " + dataSource.toString()); // Check it's the Testcontainers URL
    }

    @Test
    void databaseIsRunningAndAccessible() throws Exception {
         assertThat(postgresContainer.isRunning()).isTrue();
         // You can perform DB operations here using JPA repositories or JdbcTemplate
         // Flyway migrations should have run successfully before this test executes.
    }

    // Add your actual integration tests here...
}

6. Flyway Migrations (src/main/resources/db/migration)

Create SQL files following the naming convention V<VERSION>__<Description>.sql.

• V1__Initial_schema.sql:

  SQL

  CREATE TABLE employee (
      id SERIAL PRIMARY KEY,
      username VARCHAR(100) NOT NULL UNIQUE,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
  );
  
  CREATE TABLE visitor (
      id SERIAL PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
  );
  

---

• Local Dev (Docker Compose): Schema initialized by JPA (ddl-auto). Flyway disabled.
• Integration Tests (Testcontainers): Schema initialized by Flyway. JPA validation enabled.
• Production: Schema initialized by Flyway. JPA validation enabled.

We can achieve this by using Spring Profiles more distinctly.

1. Introduce a 'test' Profile:

We'll create a specific configuration file for the test environment (application-test.properties) and activate this profile during tests.

2. Updated Configuration Files:

a) src/main/resources/application.properties (Default/Dev Profile)

Properties

# --- JPA/Hibernate Settings (Dev) ---
# *** Initialize schema directly from entities for local Docker Compose dev ***
spring.jpa.hibernate.ddl-auto=create-drop # Or 'update'
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.default_schema=public

# --- Flyway Configuration (Dev) ---
# *** DISABLED for default/dev profile ***
spring.flyway.enabled=false

b) src/main/resources/application-prod.properties (Production Profile)

Properties

# --- JPA/Hibernate Production Settings ---
# *** Let Flyway manage the schema, validate entity mappings ***
spring.jpa.hibernate.ddl-auto=validate # Or 'none'
spring.jpa.show-sql=false
spring.jpa.properties.hibernate.format_sql=false

# --- Flyway Configuration (Prod) ---
# *** ENABLED for prod profile - manages the schema ***
spring.flyway.enabled=true
# spring.flyway.locations=classpath:db/migration # Default is usually fine
# spring.flyway.schemas=public # Default is usually fine

c) src/main/resources/application-test.properties (NEW - Test Profile)

Create this new file. It will be activated specifically during tests.

Properties

# --- Test Environment Overrides ---
# This file is activated when the 'test' profile is active (e.g., via @ActiveProfiles("test"))

# --- JPA/Hibernate Test Settings ---
# *** Let Flyway manage the schema during tests, validate entity mappings ***
spring.jpa.hibernate.ddl-auto=validate # Or 'none'
spring.jpa.show-sql=true # Often useful to see SQL in tests
spring.jpa.properties.hibernate.format_sql=true

# --- Flyway Configuration (Test) ---
# *** ENABLED for test profile - runs migrations against Testcontainers DB ***
spring.flyway.enabled=true

# --- Other Test Specific Settings (if any) ---
# e.g., disable external service calls, mock settings
myapp.external.service.mocked=true

3. Update Integration Test Class:

Add the @ActiveProfiles("test") annotation to your integration test class to ensure application-test.properties is loaded.

src/test/java/com/example/myapp/MyServiceIntegrationTest.java

Java

// ... other imports
import org.springframework.test.context.ActiveProfiles; // Import this

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.NONE)
@Testcontainers
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@ActiveProfiles("test") // *** Activate the 'test' profile ***
public class MyServiceIntegrationTest {

    @Container
    private static final PostgreSQLContainer<?> postgresContainer =
            new PostgreSQLContainer<>(DockerImageName.parse("postgres:15"))
                    // ... rest of container configuration
                    .withDatabaseName("testdb")
                    .withUsername("testuser")
                    .withPassword("testsecret");

    @DynamicPropertySource
    static void postgresqlProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgresContainer::getJdbcUrl);
        registry.add("spring.datasource.username", postgresContainer::getUsername);
        registry.add("spring.datasource.password", postgresContainer::getPassword);
        // No need to explicitly set flyway.enabled here, it's handled by the 'test' profile
    }

    // ... rest of your test class (Autowired fields, @Test methods)
    @Autowired
    private ApplicationContext applicationContext;

    @Test
    void contextLoadsAndFlywayRan() {
        assertThat(applicationContext).isNotNull();
        // You can potentially add checks here to verify Flyway migrations ran if needed,
        // though the fact that the context loads with ddl-auto=validate is a good sign.
        // For example, query the flyway_schema_history table via JdbcTemplate.
    }
}