H2 Database

<!-- pom.xml — H2 for development (runtime scope) and testing: -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<!-- Runtime scope — available in dev and test but not in production JAR: -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

# ── application.yml — H2 auto-configures with no datasource needed: ───
# Spring Boot detects H2 on the classpath and creates an in-memory DB.
# The DB name is random by default — use a fixed name to share between
# connections (e.g. the H2 console and your application):

spring:
  datasource:
    url: jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
    # DB_CLOSE_DELAY=-1  — keep DB alive as long as JVM runs
    # DB_CLOSE_ON_EXIT=FALSE — don't close when last connection closes
    driver-class-name: org.h2.Driver
    username: sa
    password: ""

  jpa:
    hibernate:
      ddl-auto: create-drop     # create schema on start, drop on stop
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.H2Dialect
        format_sql: true

  h2:
    console:
      enabled: true             # enable browser-based SQL console
      path: /h2-console         # URL: http://localhost:8080/h2-console
      settings:
        trace: false
        web-allow-others: false # allow only localhost (security)

# ── MySQL compatibility mode: ─────────────────────────────────────────
spring:
  datasource:
    url: jdbc:h2:mem:testdb;MODE=MySQL;DB_CLOSE_DELAY=-1
    # Enables: AUTO_INCREMENT, IFNULL, GROUP BY behaviour matching MySQL

# ── PostgreSQL compatibility mode: ────────────────────────────────────
spring:
  datasource:
    url: jdbc:h2:mem:testdb;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DB_CLOSE_DELAY=-1
    # DATABASE_TO_LOWER=TRUE — H2 is case-insensitive by default;
    # PostgreSQL folds identifiers to lowercase — this matches that behaviour

# ── Oracle compatibility mode: ────────────────────────────────────────
spring:
  datasource:
    url: jdbc:h2:mem:testdb;MODE=Oracle;DB_CLOSE_DELAY=-1

# ── SQL Server compatibility mode: ────────────────────────────────────
spring:
  datasource:
    url: jdbc:h2:mem:testdb;MODE=MSSQLServer;DB_CLOSE_DELAY=-1

# ── Compatibility mode limitations: ───────────────────────────────────
# H2 compatibility is not 100% — some advanced features differ:
# - PostgreSQL: arrays, JSONB, LATERAL joins not fully supported
# - MySQL: ON DUPLICATE KEY UPDATE partially supported
# - Oracle: CONNECT BY, MERGE differences
# For comprehensive testing of DB-specific features: use Testcontainers.

# ── File-based H2 — data persists across restarts: ────────────────────
spring:
  datasource:
    url: jdbc:h2:file:./data/testdb;DB_CLOSE_DELAY=-1;AUTO_SERVER=TRUE
    # AUTO_SERVER=TRUE — allows multiple connections to the same file DB
    # Useful when the app and the H2 console connect simultaneously

# ── Enable the H2 console: ────────────────────────────────────────────
spring:
  h2:
    console:
      enabled: true
      path: /h2-console

# ── Access: http://localhost:8080/h2-console ──────────────────────────
# Connection settings in the H2 console login page:
# Driver Class:  org.h2.Driver
# JDBC URL:      jdbc:h2:mem:testdb    (must match spring.datasource.url)
# User Name:     sa
# Password:      (empty)

# ── Spring Security — allow H2 console when security is on: ───────────
@Configuration
@EnableWebSecurity
public class SecurityConfig {

    @Bean
    public SecurityFilterChain filterChain(HttpSecurity http) throws Exception {
        http
            .authorizeHttpRequests(auth -> auth
                .requestMatchers("/h2-console/**").permitAll()  // allow console
                .anyRequest().authenticated()
            )
            // H2 console uses iframes — must disable frameOptions:
            .headers(headers -> headers
                .frameOptions(frame -> frame.sameOrigin()))
            // H2 console submits forms — disable CSRF for console path:
            .csrf(csrf -> csrf
                .ignoringRequestMatchers("/h2-console/**"));
        return http.build();
    }
}

# ── application-dev.yml (dev profile only): ──────────────────────────
spring:
  h2:
    console:
      enabled: true   # never enable in prod — only in dev profile

# ── application.yml — enable SQL initialisation: ─────────────────────
spring:
  sql:
    init:
      mode: always          # always, embedded (default), or never
      schema-locations: classpath:schema.sql
      data-locations: classpath:data.sql
      encoding: UTF-8
  jpa:
    hibernate:
      ddl-auto: none        # let schema.sql manage DDL, not Hibernate

# ── src/main/resources/schema.sql: ────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
    id          BIGINT          NOT NULL AUTO_INCREMENT,
    name        VARCHAR(100)    NOT NULL,
    email       VARCHAR(255)    NOT NULL UNIQUE,
    role        VARCHAR(20)     NOT NULL DEFAULT 'USER',
    active      BOOLEAN         NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS orders (
    id          BIGINT          NOT NULL AUTO_INCREMENT,
    user_id     BIGINT          NOT NULL,
    reference   VARCHAR(50)     NOT NULL UNIQUE,
    total       DECIMAL(10,2)   NOT NULL,
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

# ── src/main/resources/data.sql: ──────────────────────────────────────
INSERT INTO users (name, email, role) VALUES
    ('Alice Smith',   'alice@example.com',   'ADMIN'),
    ('Bob Jones',     'bob@example.com',     'USER'),
    ('Carol White',   'carol@example.com',   'USER');

INSERT INTO orders (user_id, reference, total) VALUES
    (1, 'ORD-001', 99.99),
    (1, 'ORD-002', 149.50),
    (2, 'ORD-003', 29.99);

# ── With Flyway and H2 — use Flyway scripts instead: ─────────────────
spring:
  flyway:
    enabled: true
    locations: classpath:db/migration  # same migrations as production
  sql:
    init:
      mode: never   # Flyway handles schema — disable Spring's init

// ── @DataJpaTest — lightweight JPA slice test with H2: ───────────────
@DataJpaTest   // configures H2, JPA, repositories; excludes web layer
class UserRepositoryTest {

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private TestEntityManager entityManager;   // for arranging test data

    @Test
    void findByEmail_returnsUser_whenExists() {
        // Arrange — persist using TestEntityManager:
        User user = new User("Alice", "alice@example.com");
        entityManager.persistAndFlush(user);

        // Act:
        Optional<User> found = userRepository.findByEmail("alice@example.com");

        // Assert:
        assertThat(found).isPresent();
        assertThat(found.get().getName()).isEqualTo("Alice");
    }

    @Test
    void existsByEmail_returnsTrue_whenExists() {
        entityManager.persistAndFlush(new User("Bob", "bob@example.com"));
        assertThat(userRepository.existsByEmail("bob@example.com")).isTrue();
        assertThat(userRepository.existsByEmail("nobody@example.com")).isFalse();
    }

    @Test
    @Sql("/test-data/users.sql")   // load from SQL script before test:
    void findByRole_returnsAllAdmins() {
        List<User> admins = userRepository.findByRole(User.Role.ADMIN);
        assertThat(admins).hasSize(2);
    }
}

# ── test/resources/application.yml — test H2 configuration: ──────────
spring:
  datasource:
    url: jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;MODE=MySQL
  jpa:
    hibernate:
      ddl-auto: create-drop
  h2:
    console:
      enabled: false

# ── @Sql — run SQL scripts in a specific test: ────────────────────────
@Test
@Sql(scripts = "/test-data/users.sql",
     executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
@Sql(scripts = "/test-data/cleanup.sql",
     executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
void myTest() { ... }

# ── Separate H2 config for tests only: ───────────────────────────────
# src/test/resources/application.yml overrides main config for tests.
# @DataJpaTest uses an embedded database automatically —
# no datasource URL needed unless you want a specific H2 mode.

# ── Use H2 when: ─────────────────────────────────────────────────────
# - Testing repository logic that uses only standard SQL
# - Unit and slice tests (@DataJpaTest) where speed matters
# - JPQL queries, derived query methods, Spring Data features
# - CI pipelines where Docker is not available
# - Local development quick feedback loop

# Example — @DataJpaTest with H2 (fast, ~500ms startup):
@DataJpaTest
class UserRepositoryTest {
    @Autowired UserRepository userRepository;
    // Tests standard CRUD and JPQL — H2 is sufficient
}

# ── Use Testcontainers when: ──────────────────────────────────────────
# - Queries use database-specific syntax (ON CONFLICT, MERGE, JSON, arrays)
# - Native queries that H2 cannot parse
# - Testing Flyway migrations against the real database engine
# - Full-text search (PostgreSQL tsvector, MySQL FULLTEXT)
# - Stored procedures, functions, triggers
# - Verifying database constraints, indexes, and data types
# - Integration tests that must match production behaviour exactly

# Example — @SpringBootTest with Testcontainers (slower, ~10-30s startup):
@SpringBootTest
@Testcontainers
class ProductRepositoryIntegrationTest {

    @Container
    static PostgreSQLContainer<?> postgres =
        new PostgreSQLContainer<>("postgres:16-alpine");

    @DynamicPropertySource
    static void configureProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
    }

    @Autowired ProductRepository productRepository;

    @Test
    void fullTextSearch_returnsMatchingProducts() {
        // Tests PostgreSQL-specific tsvector full-text search —
        // this would fail with H2
        List<Product> results = productRepository.fullTextSearch("widget");
        assertThat(results).isNotEmpty();
    }
}

# ── Recommended test strategy: ────────────────────────────────────────
# @DataJpaTest + H2        → repository unit tests (fast, many)
# @SpringBootTest + TC     → integration tests (slower, fewer, CI only)
# The two complement each other — neither replaces the other.