Chapter 1: Datasource Configuration

Introduction

Whenever your Spring Boot application interacts with the database, it uses a connection. But where do these connections come from? How are they managed? This is the role of the Datasource.

---

🎯 Chapter Objectives

By the end of this chapter, you will know:

1. ✅ What a Datasource is and why it's important
2. ✅ How Spring Boot automatically configures connections
3. ✅ Configure HikariCP (the default pool)
4. ✅ Optimize settings for production
5. ✅ Configure multiple datasources

---

1. What is a Datasource?

Simple Definition

A Datasource is an object that provides connections to the database.

The Problem Without Connection Pooling

// ❌ BAD PRACTICE: New connection for each request
public void getUser(Long id) {
    Connection conn = DriverManager.getConnection(url, user, password);
    // ... use the connection
    conn.close();
}

Problems:

• Creating a connection = 500ms to 1s (very slow!)
• 100 simultaneous users = 100 connections = DB saturated
• No reuse = resource waste

The Solution: Connection Pooling

// ✅ GOOD PRACTICE: Reuse connections
public void getUser(Long id) {
    Connection conn = dataSource.getConnection();  // ← Connection from pool (< 1ms)
    // ... use the connection
    conn.close();  // ← Returns to pool (not really closed)
}

Advantages:

• Pre-created connections = instant response
• Reuse = less load on the DB
• Limits the max number of connections

---

2. HikariCP: The Default Pool

Why HikariCP?

Spring Boot uses HikariCP (光 = "light" in Japanese) because it is:

• 🚀 The fastest (proven benchmarks)
• 💪 The most reliable (used by large companies)
• 🔧 Simple to configure

Spring Boot Auto-Configuration

When you add a database dependency, Spring Boot automatically configures HikariCP:

<!-- pom.xml -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
    <!-- HikariCP is included automatically! -->
</dependency>

---

3. Basic Configuration

Minimal Configuration (H2)

# application.properties
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

PostgreSQL Configuration

# application.properties
spring.datasource.url=jdbc:postgresql://localhost:5432/mydb
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.username=postgres
spring.datasource.password=secret

MySQL Configuration

# application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=secret

---

4. Advanced HikariCP Configuration

Essential Settings

# application.properties

# Pool size
spring.datasource.hikari.minimum-idle=5          # Minimum maintained connections
spring.datasource.hikari.maximum-pool-size=10    # Maximum connections

# Timeouts
spring.datasource.hikari.connection-timeout=30000   # Max wait to get a connection (30s)
spring.datasource.hikari.idle-timeout=600000        # Time before closing an idle connection (10min)
spring.datasource.hikari.max-lifetime=1800000       # Max lifetime of a connection (30min)

# Pool name (for logs)
spring.datasource.hikari.pool-name=MyAppPool

Pool Visualization

┌─────────────────────────────────────────────────────┐
│                    HikariCP Pool                    │
│                                                     │
│  minimum-idle=5      maximum-pool-size=10           │
│  ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐          │
│  │Conn1│ │Conn2│ │Conn3│ │Conn4│ │Conn5│ (idle)   │
│  └─────┘ └─────┘ └─────┘ └─────┘ └─────┘          │
│                                                     │
│  + 5 additional connections if needed               │
│                                                     │
│  connection-timeout=30s (max wait if pool full)     │
└─────────────────────────────────────────────────────┘

YAML Format (Alternative)

# application.yml
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: postgres
    password: secret
    hikari:
      minimum-idle: 5
      maximum-pool-size: 10
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      pool-name: MyAppPool

---

5. Environment Recommendations

Development (H2)

# application-dev.properties
spring.datasource.url=jdbc:h2:mem:devdb
spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.maximum-pool-size=5

# H2 Console enabled
spring.h2.console.enabled=true

Production (PostgreSQL)

# application-prod.properties
spring.datasource.url=jdbc:postgresql://db.server.com:5432/proddb
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.maximum-pool-size=50

# No H2 console!
spring.h2.console.enabled=false

# Connection validation
spring.datasource.hikari.connection-test-query=SELECT 1

Formula for maximum-pool-size

Empirical rule:
maximum-pool-size = (number of CPUs * 2) + number of disks

Example:
- Server with 4 CPUs and 1 SSD disk
- Pool size = (4 * 2) + 1 = 9 ≈ 10

tip

Do not oversize! A pool too large can degrade performance as the DB must handle too many simultaneous connections.

---

6. Multi-Datasource Configuration

Use Case

Sometimes, an application needs to connect to multiple databases:

Configuration

# application.properties

# Primary datasource (PostgreSQL)
spring.datasource.primary.url=jdbc:postgresql://localhost:5432/usersdb
spring.datasource.primary.username=postgres
spring.datasource.primary.password=secret
spring.datasource.primary.driver-class-name=org.postgresql.Driver

# Secondary datasource (MySQL)
spring.datasource.secondary.url=jdbc:mysql://localhost:3306/productsdb
spring.datasource.secondary.username=root
spring.datasource.secondary.password=secret
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver

Java Configuration

@Configuration
public class DataSourceConfig {

    // Primary datasource
    @Primary
    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    // Secondary datasource
    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }
}

Usage with JPA

// EntityManager Configuration for Primary
@Configuration
@EnableJpaRepositories(
    basePackages = "com.example.repository.users",
    entityManagerFactoryRef = "primaryEntityManager",
    transactionManagerRef = "primaryTransactionManager"
)
public class PrimaryDatabaseConfig {
    // ... configuration
}

// EntityManager Configuration for Secondary
@Configuration
@EnableJpaRepositories(
    basePackages = "com.example.repository.products",
    entityManagerFactoryRef = "secondaryEntityManager",
    transactionManagerRef = "secondaryTransactionManager"
)
public class SecondaryDatabaseConfig {
    // ... configuration
}

---

7. Pool Monitoring

Via Actuator

Add Spring Boot Actuator to monitor the pool:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-actuator</artifactId>
</dependency>

# application.properties
management.endpoints.web.exposure.include=health,metrics
management.endpoint.health.show-details=always

Available Metrics

# Check the pool
GET /actuator/metrics/hikaricp.connections

# Active connections
GET /actuator/metrics/hikaricp.connections.active

# Pending connections
GET /actuator/metrics/hikaricp.connections.pending

# Timeouts
GET /actuator/metrics/hikaricp.connections.timeout

Example Response

{
  "name": "hikaricp.connections.active",
  "measurements": [
    {
      "statistic": "VALUE",
      "value": 3.0
    }
  ],
  "availableTags": [
    {
      "tag": "pool",
      "values": ["MyAppPool"]
    }
  ]
}

---

8. Common Issues and Solutions

❌ Connection Timeout

HikariPool-1 - Connection is not available, request timed out after 30000ms.

Causes:

• Pool too small
• Unclosed connections (leak)
• Requests too slow

Solutions:

# Increase the pool
spring.datasource.hikari.maximum-pool-size=20

# Increase the timeout
spring.datasource.hikari.connection-timeout=60000

# Enable leak logs
spring.datasource.hikari.leak-detection-threshold=60000

❌ Too Many Connections

PostgreSQL: FATAL: too many connections for role "postgres"

Cause: Pool too large or multiple instances

Solution:

# Reduce the pool
spring.datasource.hikari.maximum-pool-size=5

# Check PostgreSQL max_connections
# SHOW max_connections;

❌ Connection Leak

HikariPool-1 - Connection leak detection triggered

Cause: Connection never returned to the pool

Solution:

# Enable leak detection
spring.datasource.hikari.leak-detection-threshold=30000

Then find and fix the faulty code (unclosed connection).

---

9. Summary

Key Points

Concept	Description
Datasource	Provides connections to the DB
HikariCP	Default connection pool (the fastest)
minimum-idle	Minimum maintained connections
maximum-pool-size	Maximum connections
connection-timeout	Max time to get a connection

Configuration Checklist

✅ Define spring.datasource.url
✅ Define username/password
✅ Configure minimum-idle and maximum-pool-size
✅ Adjust timeouts according to the environment
✅ Monitor with Actuator

Recommended Configuration

# Production
spring.datasource.url=jdbc:postgresql://localhost:5432/mydb
spring.datasource.username=myuser
spring.datasource.password=${DB_PASSWORD}

spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.pool-name=MyAppPool

---

🎯 Next Step

Now that you understand how connections are managed, it's time to learn how to protect your data integrity with transactions.

➡️ Chapter 2: Transaction Management (@Transactional)

---

⬅️ Back to Module 7