0
# Connection Pooling
1

2
HikariCP-based connection pool implementation with comprehensive configuration options, performance monitoring, and lifecycle management.
3

4
## Capabilities
5

6
### ConnectionPool Interface
7

8
Abstract interface for connection pool implementations.
9

10
```scala { .api }
11
trait ConnectionPool {
12
  /** Create a data source with the given configuration */
13
  def create(name: String, dbConfig: DatabaseConfig, configuration: Config): DataSource
14
  /** Close the given data source */
15
  def close(dataSource: DataSource): Unit
16
}
17
```
18

19
### ConnectionPool Factory Methods
20

21
Factory methods for creating connection pool instances.
22

23
```scala { .api }
24
object ConnectionPool {
25
  /** Load a connection pool from a configured connection pool with Injector support */
26
  def fromConfig(
27
    config: String,
28
    injector: Injector,
29
    environment: Environment, 
30
    default: ConnectionPool
31
  ): ConnectionPool
32
  
33
  /** Load a connection pool from configuration for compile-time DI */
34
  def fromConfig(
35
    config: String, 
36
    environment: Environment, 
37
    default: ConnectionPool
38
  ): ConnectionPool
39
  
40
  /** Extract and process database URL, supporting shortcut formats */
41
  def extractUrl(maybeUrl: Option[String], mode: Mode): (Option[String], Option[(String, String)])
42
  
43
  /** Wrap a data source for SQL logging if logSql is enabled */
44
  private[db] def wrapToLogSql(dataSource: DataSource, configuration: Config): DataSource
45
  
46
  /** Unwrap a data source if it has been wrapped for SQL logging */
47
  private[db] def unwrap(dataSource: DataSource): DataSource
48
}
49
```
50

51
**Usage Examples:**
52

53
```scala
54
import play.api.db._
55
import play.api.Environment
56

57
// Create connection pool for compile-time DI
58
val environment = Environment.simple()
59
val defaultPool = new HikariCPConnectionPool(environment)
60
val pool = ConnectionPool.fromConfig("hikaricp", environment, defaultPool)
61

62
// URL extraction with credential parsing
63
val (extractedUrl, credentials) = ConnectionPool.extractUrl(
64
  Some("postgres://user:pass@localhost:5432/mydb"), 
65
  play.api.Mode.Dev
66
)
67
// extractedUrl: Some("jdbc:postgresql://localhost:5432/mydb")
68
// credentials: Some(("user", "pass"))
69
```
70

71
### HikariCP Implementation
72

73
HikariCP-based implementation of the ConnectionPool interface.
74

75
```scala { .api }
76
@Singleton
77
class HikariCPConnectionPool @Inject()(environment: Environment) extends ConnectionPool {
78
  /** Create a HikariCP data source with the given configuration */
79
  override def create(name: String, dbConfig: DatabaseConfig, configuration: Config): DataSource
80
  /** Close the HikariCP data source */
81
  override def close(dataSource: DataSource): Unit
82
}
83
```
84

85
**Usage Examples:**
86

87
```scala
88
import play.api.db._
89
import play.api.{Configuration, Environment}
90
import com.typesafe.config.ConfigFactory
91

92
val environment = Environment.simple()
93
val pool = new HikariCPConnectionPool(environment)
94

95
val dbConfig = DatabaseConfig(
96
  driver = Some("org.postgresql.Driver"),
97
  url = Some("jdbc:postgresql://localhost/myapp"),
98
  username = Some("dbuser"), 
99
  password = Some("dbpass"),
100
  jndiName = None
101
)
102

103
val config = ConfigFactory.parseString("""
104
  hikaricp {
105
    maximumPoolSize = 20
106
    minimumIdle = 5
107
    connectionTimeout = 30 seconds
108
    idleTimeout = 10 minutes
109
    maxLifetime = 30 minutes
110
  }
111
""")
112

113
val dataSource = pool.create("myapp", dbConfig, config)
114
try {
115
  val connection = dataSource.getConnection()
116
  // Use connection...
117
  connection.close()
118
} finally {
119
  pool.close(dataSource)
120
}
121
```
122

123
### HikariCP Configuration
124

125
Comprehensive configuration options for HikariCP connection pools.
126

127
```scala { .api }
128
private[db] class HikariCPConfig(dbConfig: DatabaseConfig, configuration: Configuration) {
129
  def toHikariConfig: HikariConfig
130
}
131
```
132

133
**Configuration Properties:**
134

135
Essential configurations:
136
- `dataSourceClassName` - Data source class name (alternative to driver/url)
137
- `jdbcUrl` - JDBC URL (from dbConfig.url)
138
- `driverClassName` - JDBC driver class (from dbConfig.driver)
139
- `username` - Database username (from dbConfig.username)
140
- `password` - Database password (from dbConfig.password)
141

142
Frequently used:
143
- `autoCommit` - Auto-commit behavior (default: true)
144
- `connectionTimeout` - Maximum wait time for connection (default: 30 seconds)
145
- `idleTimeout` - Maximum idle time before connection is retired (default: 10 minutes)
146
- `maxLifetime` - Maximum lifetime of a connection (default: 30 minutes)
147
- `connectionTestQuery` - Query to validate connections
148
- `minimumIdle` - Minimum number of idle connections
149
- `maximumPoolSize` - Maximum number of connections in pool (default: 10)
150
- `poolName` - User-defined name for the connection pool
151

152
Infrequently used:
153
- `initializationFailTimeout` - Pool initialization failure timeout
154
- `isolateInternalQueries` - Isolate internal pool queries
155
- `allowPoolSuspension` - Allow pool suspension
156
- `readOnly` - Default read-only mode for connections
157
- `registerMbeans` - Register JMX management beans
158
- `connectionInitSql` - SQL executed after connection creation
159
- `catalog` - Default catalog name
160
- `transactionIsolation` - Default transaction isolation level
161
- `validationTimeout` - Maximum time for connection validation
162
- `leakDetectionThreshold` - Connection leak detection threshold
163

164
**Configuration Example:**
165

166
```hocon
167
db.default {
168
  driver = "org.postgresql.Driver"
169
  url = "jdbc:postgresql://localhost/myapp"
170
  username = "dbuser"
171
  password = "dbpass"
172
  
173
  # HikariCP specific settings
174
  hikaricp {
175
    # Pool sizing
176
    minimumIdle = 5
177
    maximumPoolSize = 20
178
    
179
    # Connection timeouts
180
    connectionTimeout = 30 seconds
181
    idleTimeout = 10 minutes
182
    maxLifetime = 30 minutes
183
    
184
    # Connection testing
185
    connectionTestQuery = "SELECT 1"
186
    
187
    # Pool behavior
188
    autoCommit = true
189
    readOnly = false
190
    
191
    # Monitoring
192
    registerMbeans = true
193
    leakDetectionThreshold = 60 seconds
194
    
195
    # Data source properties
196
    dataSource {
197
      cachePrepStmts = true
198
      prepStmtCacheSize = 250
199
      prepStmtCacheSqlLimit = 2048
200
      useServerPrepStmts = true
201
    }
202
  }
203
  
204
  # SQL logging
205
  logSql = false
206
}
207
```
208

209
### Database URL Parsing
210

211
Support for shortcut URL formats with automatic credential extraction.
212

213
**Supported URL Formats:**
214

215
PostgreSQL shortcut:
216
```
217
postgres://username:password@host/database
218
```
219
Converts to:
220
```
221
jdbc:postgresql://host/database
222
```
223

224
MySQL shortcut:
225
```
226
mysql://username:password@host/database
227
```
228
Converts to:
229
```
230
jdbc:mysql://host/database?useUnicode=yes&characterEncoding=UTF-8&connectionCollation=utf8_general_ci
231
```
232

233
H2 development mode:
234
```
235
jdbc:h2:mem:testdb
236
```
237
Automatically adds `DB_CLOSE_DELAY=-1` in development mode.
238

239
**Usage Examples:**
240

241
```scala
242
import play.api.db.ConnectionPool
243
import play.api.Mode
244

245
// PostgreSQL shortcut URL
246
val (pgUrl, pgCreds) = ConnectionPool.extractUrl(
247
  Some("postgres://myuser:mypass@db.example.com/production"),
248
  Mode.Prod
249
)
250
// pgUrl: Some("jdbc:postgresql://db.example.com/production")
251
// pgCreds: Some(("myuser", "mypass"))
252

253
// MySQL shortcut URL  
254
val (mysqlUrl, mysqlCreds) = ConnectionPool.extractUrl(
255
  Some("mysql://app:secret@mysql.example.com/appdb"),
256
  Mode.Prod  
257
)
258
// mysqlUrl: Some("jdbc:mysql://mysql.example.com/appdb?useUnicode=yes&characterEncoding=UTF-8&connectionCollation=utf8_general_ci")
259
// mysqlCreds: Some(("app", "secret"))
260

261
// H2 in development
262
val (h2Url, h2Creds) = ConnectionPool.extractUrl(
263
  Some("jdbc:h2:mem:testdb"),
264
  Mode.Dev
265
)
266
// h2Url: Some("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1")
267
// h2Creds: None
268
```
269

270
### SQL Logging
271

272
Optional SQL logging capability using jdbcdslog proxy.
273

274
```scala { .api }
275
private[db] def wrapToLogSql(dataSource: DataSource, configuration: Config): DataSource
276
private[db] def unwrap(dataSource: DataSource): DataSource
277
```
278

279
**Configuration:**
280

281
```hocon
282
db.default {
283
  # Enable SQL logging
284
  logSql = true
285
}
286
```
287

288
When enabled, all SQL statements and their parameters are logged to the console, useful for development and debugging.
289

290
### JNDI Support
291

292
Support for binding data sources to JNDI names.
293

294
**Configuration:**
295

296
```hocon
297
db.default {
298
  driver = "org.postgresql.Driver"
299
  url = "jdbc:postgresql://localhost/myapp"
300
  jndiName = "java:comp/env/jdbc/MyAppDS"
301
}
302
```
303

304
The data source will be automatically bound to the specified JNDI name during creation.
305

306
## Performance Tuning
307

308
### Connection Pool Sizing
309

310
- **minimumIdle**: Should be same as maximumPoolSize for stable workloads
311
- **maximumPoolSize**: Formula: `((core_count * 2) + disk_count)` for typical OLTP workloads
312
- **connectionTimeout**: Should be longer than typical connection acquisition time
313
- **idleTimeout**: Should be shorter than database server connection timeout
314

315
### Prepared Statement Caching
316

317
```hocon
318
db.default {
319
  hikaricp {
320
    dataSource {
321
      cachePrepStmts = true
322
      prepStmtCacheSize = 250
323
      prepStmtCacheSqlLimit = 2048
324
      useServerPrepStmts = true
325
    }
326
  }
327
}
328
```
329

330
### Connection Leak Detection
331

332
```hocon
333
db.default {
334
  hikaricp {
335
    leakDetectionThreshold = 60 seconds
336
    registerMbeans = true
337
  }
338
}
339
```
340

341
## Error Handling
342

343
- **SQLException**: Thrown by underlying database operations
344
- **Connection timeouts**: Thrown when connection acquisition exceeds `connectionTimeout`
345
- **Pool exhaustion**: Thrown when no connections are available within timeout period
346
- **Configuration errors**: Thrown for invalid HikariCP configuration parameters
347

348
## Monitoring
349

350
- **JMX MBeans**: Enable with `registerMbeans = true` for pool metrics
351
- **Connection leak detection**: Configure `leakDetectionThreshold` to detect leaked connections
352
- **SQL logging**: Enable `logSql = true` for development debugging