Spring Boot MySQL Integration: Config, HikariCP, Flyway and Testcontainers

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <scope>runtime</scope>
    </dependency>

    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-core</artifactId>
    </dependency>
    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-mysql</artifactId>
    </dependency>
</dependencies>

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb
    username: ${DB_USER:appuser}
    password: ${DB_PASSWORD}
  jpa:
    open-in-view: false
    hibernate:
      ddl-auto: validate          # Flyway owns the schema; Hibernate only checks it matches
    properties:
      hibernate:
        format_sql: true
        jdbc:
          time_zone: UTC          # read and write timestamps as UTC
          batch_size: 50

---
spring:
  config:
    activate:
      on-profile: dev
  datasource:
    # No TLS to a local container; the public key option is a dev-only convenience.
    url: jdbc:mysql://localhost:3306/mydb?sslMode=DISABLED&allowPublicKeyRetrieval=true

---
spring:
  config:
    activate:
      on-profile: prod
  datasource:
    # One line. Verify the server certificate and hostname.
    url: jdbc:mysql://prod-db.internal:3306/mydb?sslMode=VERIFY_IDENTITY&rewriteBatchedStatements=true

spring:
  datasource:
    hikari:
      pool-name: app-pool
      maximum-pool-size: 10          # minimum-idle unset: fixed-size pool
      connection-timeout: 10000      # fail fast if the pool is exhausted (default 30s)
      max-lifetime: 1500000          # 25 min: below any wait_timeout / proxy idle timeout (default 30 min)
      keepalive-time: 300000         # ping idle connections every 5 min
      leak-detection-threshold: 20000   # logs a stack trace if a connection is held over 20s

management:
  endpoints:
    web:
      exposure:
        include: health,metrics

# Watch these in Actuator or your metrics system:
#   hikaricp.connections.active    connections in use right now
#   hikaricp.connections.pending   threads WAITING for a connection. Sustained values above 0
#                                  mean the pool is too small or queries are too slow.
#   hikaricp.connections.timeout   requests that gave up waiting

-- V1__create_users.sql
CREATE TABLE users (
    id          BIGINT       NOT NULL AUTO_INCREMENT,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) NOT NULL,
    role        VARCHAR(20)  NOT NULL DEFAULT 'USER',
    active      TINYINT(1)   NOT NULL DEFAULT 1,
    created_at  DATETIME(6)  NOT NULL,
    updated_at  DATETIME(6)  NOT NULL,
    PRIMARY KEY (id),
    -- The _ci collation makes this unique key case-insensitive: alice@x.com and ALICE@x.com collide.
    UNIQUE KEY uk_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- V2__add_users_bio.sql  (one DDL statement per migration: DDL cannot be rolled back)
ALTER TABLE users ADD COLUMN bio TEXT NULL;

-- V3__index_users_created_at.sql
ALTER TABLE users
    ADD INDEX idx_users_created_at (created_at),
    ALGORITHM=INPLACE, LOCK=NONE;   -- fails loudly instead of locking a big table

-- Only when adopting Flyway on an existing database, once:
--   spring.flyway.baseline-on-migrate=true
--   spring.flyway.baseline-version=1

-- V4__create_products.sql
CREATE TABLE products (
    id          BIGINT         NOT NULL AUTO_INCREMENT,
    version     BIGINT         NOT NULL DEFAULT 0,
    name        VARCHAR(200)   NOT NULL,
    description TEXT           NULL,
    category    VARCHAR(100)   NULL,
    price       DECIMAL(10,2)  NOT NULL,
    active      TINYINT(1)     NOT NULL DEFAULT 1,
    metadata    JSON           NULL,
    created_at  DATETIME(6)    NOT NULL,
    PRIMARY KEY (id),
    FULLTEXT KEY ft_products (name, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- V5__create_product_views.sql
CREATE TABLE product_views (
    product_id BIGINT NOT NULL,
    view_date  DATE   NOT NULL,
    view_count INT    NOT NULL DEFAULT 0,
    PRIMARY KEY (product_id, view_date)      -- the unique key ON DUPLICATE KEY relies on
) ENGINE=InnoDB;

-- V6__create_user_tags.sql
CREATE TABLE user_tags (
    user_id BIGINT NOT NULL,
    tag_id  BIGINT NOT NULL,
    PRIMARY KEY (user_id, tag_id)
) ENGINE=InnoDB;

@Entity
@Table(name = "products")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)   // AUTO_INCREMENT; disables insert batching
    private Long id;

    @Version
    private long version;                                 // optimistic locking

    @Column(nullable = false, length = 200)
    private String name;

    @Column(columnDefinition = "TEXT")
    private String description;

    private String category;

    @Column(nullable = false, precision = 10, scale = 2)  // DECIMAL(10,2), never double for money
    private BigDecimal price;

    @Column(nullable = false)
    private boolean active = true;

    @JdbcTypeCode(SqlTypes.JSON)                          // native JSON mapping, no converter
    @Column(columnDefinition = "json")
    private Map<String, Object> metadata;

    @CreationTimestamp
    @Column(nullable = false, updatable = false)          // DATETIME(6) holding UTC
    private Instant createdAt;

    protected Product() { }
    // constructor, getters...
}

public interface PriceRankView {
    Long getId();
    String getName();
    String getCategory();
    BigDecimal getPrice();
    Long getPriceRank();
}

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    // Upsert. Needs the primary/unique key (product_id, view_date) from V5.
    @Modifying
    @Query(value = """
            INSERT INTO product_views (product_id, view_date, view_count)
            VALUES (:productId, CURDATE(), 1)
            ON DUPLICATE KEY UPDATE view_count = view_count + 1
            """, nativeQuery = true)
    void incrementDailyView(@Param("productId") Long productId);

    // Full-text search. Needs the FULLTEXT index ft_products from V4.
    @Query(value = """
            SELECT * FROM products
            WHERE MATCH(name, description) AGAINST (:query IN NATURAL LANGUAGE MODE)
              AND active = true
            """,
           countQuery = """
            SELECT COUNT(*) FROM products
            WHERE MATCH(name, description) AGAINST (:query IN NATURAL LANGUAGE MODE)
              AND active = true
            """,
           nativeQuery = true)
    Page<Product> fullTextSearch(@Param("query") String query, Pageable pageable);

    // JSON field filter. ->> unquotes the value, so compare it with a string.
    @Query(value = "SELECT * FROM products WHERE metadata ->> '$.featured' = 'true'",
           nativeQuery = true)
    List<Product> findFeatured();

    // Window function (MySQL 8.0+), mapped to a projection by column alias.
    @Query(value = """
            SELECT p.id AS id, p.name AS name, p.category AS category, p.price AS price,
                   RANK() OVER (PARTITION BY p.category ORDER BY p.price ASC) AS priceRank
            FROM products p
            WHERE p.active = true
            """, nativeQuery = true)
    List<PriceRankView> findWithPriceRank();
}

// In a repository for user_tags (needs the primary key (user_id, tag_id) from V6):
//
//   @Modifying
//   @Query(value = """
//           INSERT INTO user_tags (user_id, tag_id) VALUES (:userId, :tagId)
//           ON DUPLICATE KEY UPDATE user_id = user_id
//           """, nativeQuery = true)
//   void addTag(@Param("userId") Long userId, @Param("tagId") Long tagId);

services:
  mysql:
    image: mysql:8.4
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD:-rootpassword}   # local development only
      MYSQL_DATABASE: mydb
      MYSQL_USER: appuser
      MYSQL_PASSWORD: ${MYSQL_PASSWORD:-apppassword}
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql
      - ./init:/docker-entrypoint-initdb.d    # .sql files run only on first start of an empty volume
    command:
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_unicode_ci
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-uroot", "-p$$MYSQL_ROOT_PASSWORD"]
      interval: 10s
      timeout: 5s
      retries: 10

volumes:
  mysql_data:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-testcontainers</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>junit-jupiter</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>mysql</artifactId>   <!-- testcontainers-mysql on Testcontainers 2.x -->
    <scope>test</scope>
</dependency>

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)   // keep the real datasource
@Testcontainers
class UserRepositoryMySqlTest {

    @Container
    @ServiceConnection
    static MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.4");

    @Autowired
    private UserRepository userRepository;

    @Test
    void savesAndFindsByEmail() {
        userRepository.saveAndFlush(new User("Alice", "alice@example.com"));

        assertThat(userRepository.findByEmail("alice@example.com")).isPresent();
    }

    @Test
    void emailUniquenessIgnoresCase() {
        userRepository.saveAndFlush(new User("Alice", "alice@example.com"));

        assertThatThrownBy(() ->
            userRepository.saveAndFlush(new User("Impostor", "ALICE@example.com")))
            .isInstanceOf(DataIntegrityViolationException.class);
    }
}

SHOW VARIABLES LIKE 'max_connections';       -- default 151: ALL application pools together must fit
SHOW VARIABLES LIKE 'wait_timeout';          -- default 28800 (8h); keep Hikari max-lifetime below this
SHOW VARIABLES LIKE 'character_set_server';  -- expect utf8mb4
SELECT @@transaction_isolation;              -- REPEATABLE-READ by default
SELECT @@sql_mode;                           -- should include STRICT_TRANS_TABLES
SHOW ENGINE INNODB STATUS;                   -- the LATEST DETECTED DEADLOCK section shows the last one