# How to Configure Connection Pooling for MySQL in dat_project.yaml

> Learn to configure MySQL connection pooling in dat_project.yaml. Set pool parameters like maxPoolSize directly in the db.configuration.url connection string for efficient database management.

- Repository: [Junjie.M/dat](https://github.com/junjiem/dat)
- Tags: how-to-guide
- Published: 2026-03-05

---

**DAT delegates MySQL connection pooling to the JDBC driver entirely through the `db.configuration.url` field in [`dat_project.yaml`](https://github.com/junjiem/dat/blob/main/dat_project.yaml), allowing you to set pool parameters like `maxPoolSize` directly in the connection string.**

The DAT (Data Analytics Toolkit) framework uses the standard MySQL JDBC driver (`com.mysql.cj.jdbc.MysqlDataSource`) to manage database connections. Unlike frameworks that require separate pooling libraries, DAT forwards your configuration directly to the driver via the JDBC URL. This means you can configure connection pooling in [`dat_project.yaml`](https://github.com/junjiem/dat/blob/main/dat_project.yaml) without writing additional Java code or importing external dependencies.

## How DAT Handles MySQL Connections

DAT's MySQL adapter creates connections through the `MySqlDatabaseAdapterFactory` class located at [`src/main/java/ai/dat/adapter/mysql/MySqlDatabaseAdapterFactory.java`](https://github.com/junjiem/dat/blob/main/src/main/java/ai/dat/adapter/mysql/MySqlDatabaseAdapterFactory.java). According to the source code, the factory constructs a `MysqlDataSource` instance and populates it with four configuration options from your YAML file:

- **`url`**: The JDBC connection string containing pooling parameters
- **`username`**: Database authentication user
- **`password`**: Database authentication password  
- **`timeout`**: Optional connection timeout duration passed to `MysqlDataSource#setConnectTimeout()`

The adapter passes the URL string directly to `MysqlDataSource#setURL()`, which means any pooling directives embedded in the URL query string are interpreted by the MySQL driver itself.

## JDBC URL Parameters for Connection Pooling

Since DAT does not implement its own connection pool, you configure pooling behavior using standard MySQL Connector/J URL parameters. Append these to your JDBC URL in [`dat_project.yaml`](https://github.com/junjiem/dat/blob/main/dat_project.yaml):

- **`maxPoolSize`**: Limits the number of active connections in the pool (e.g., `maxPoolSize=20`)
- **`autoReconnect=true`**: Automatically re-establishes lost connections
- **`maxReconnects`**: Sets the upper limit for reconnection attempts (requires `autoReconnect=true`)
- **`cachePrepStmts=true`**: Enables caching of prepared statements
- **`prepStmtCacheSize`**: Defines the size of the prepared statement cache (default 25, suggested 250-500)
- **`prepStmtCacheSqlLimit`**: Maximum length of SQL statements eligible for caching
- **`useServerPrepStmts=true`**: Forces server-side prepared statements for better performance
- **`connectTimeout`**: Connection acquisition timeout in milliseconds
- **`socketTimeout`**: Socket read timeout in milliseconds

## Step-by-Step Configuration

### 1. Define the Base Connection URL

Start with the standard MySQL JDBC URL format under `db.configuration.url`. Ensure all pooling parameters are appended as query string arguments:

```yaml
db:
  provider: mysql
  configuration:
    url: jdbc:mysql://localhost:3306/mydb?maxPoolSize=20&autoReconnect=true
    username: my_user
    password: my_password

```

### 2. Add Performance Optimizations

For high-throughput workloads, include prepared statement caching and reconnection logic:

```yaml
db:
  provider: mysql
  configuration:
    url: jdbc:mysql://localhost:3306/mydb?maxPoolSize=50&autoReconnect=true&maxReconnects=5&cachePrepStmts=true&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048&useServerPrepStmts=true
    username: my_user
    password: my_password

```

### 3. Configure Connection Timeouts

Set the optional `timeout` field to control the driver-level connection timeout. This value maps to `MysqlDataSource#setConnectTimeout()` and accepts duration strings like `30 sec` or `1 min`:

```yaml
db:
  provider: mysql
  configuration:
    url: jdbc:mysql://localhost:3306/mydb?maxPoolSize=20&connectTimeout=10000
    username: my_user
    password: my_password
    timeout: 30 sec

```

If both `timeout` and `connectTimeout` are specified, the `timeout` field overrides the URL parameter when passed to the driver's `setConnectTimeout()` method.

## Complete Configuration Examples

### Minimal Configuration (No Explicit Pooling)

```yaml
version: 1
name: my-dat-project

db:
  provider: mysql
  configuration:
    url: jdbc:mysql://localhost:3306/mydb
    username: user
    password: pass
    timeout: 15 sec

```

### Standard Pool Size Configuration

```yaml
db:
  provider: mysql
  configuration:
    url: jdbc:mysql://localhost:3306/mydb?maxPoolSize=30&autoReconnect=true
    username: user
    password: pass
    timeout: 20 sec

```

### Advanced Production Configuration

Use YAML's folded block scalar (`>`) for multi-line readability:

```yaml
db:
  provider: mysql
  configuration:
    url: >-
      jdbc:mysql://dbhost:3306/analytics
      ?autoReconnect=true
      &maxReconnects=3
      &cachePrepStmts=true
      &prepStmtCacheSize=500
      &prepStmtCacheSqlLimit=4096
      &useServerPrepStmts=true
      &socketTimeout=60000
      &connectTimeout=15000
      &maxPoolSize=50
    username: analytics_user
    password: ${MYSQL_PASSWORD}
    timeout: 45 sec

```

## Key Implementation Files

The MySQL pooling behavior is implemented in the following source files:

- **[`MySqlDatabaseAdapterFactory.java`](https://github.com/junjiem/dat/blob/main/MySqlDatabaseAdapterFactory.java)**: Constructs the `MysqlDataSource` from YAML configuration and sets connection properties. Located at [`dat-adapters/dat-adapter-mysql/src/main/java/ai/dat/adapter/mysql/MySqlDatabaseAdapterFactory.java`](https://github.com/junjiem/dat/blob/main/dat-adapters/dat-adapter-mysql/src/main/java/ai/dat/adapter/mysql/MySqlDatabaseAdapterFactory.java).

- **[`MySqlDatabaseAdapter.java`](https://github.com/junjiem/dat/blob/main/MySqlDatabaseAdapter.java)**: Wrapper class that encapsulates the `DataSource` used by DAT's execution engine. Located at [`dat-adapters/dat-adapter-mysql/src/main/java/ai/dat/adapter/mysql/MySqlDatabaseAdapter.java`](https://github.com/junjiem/dat/blob/main/dat-adapters/dat-adapter-mysql/src/main/java/ai/dat/adapter/mysql/MySqlDatabaseAdapter.java).

- **`project_yaml_template.jinja`**: Template file that generates the initial [`dat_project.yaml`](https://github.com/junjiem/dat/blob/main/dat_project.yaml) structure, including the `db` configuration section. Located at `dat-sdk/src/main/resources/templates/project_yaml_template.jinja`.

## Summary

- DAT does not implement a custom connection pool; it relies on the MySQL JDBC driver's internal pooling mechanism configured via URL parameters.
- Configure connection pooling by appending parameters like `maxPoolSize`, `autoReconnect`, and `cachePrepStmts` to `db.configuration.url` in [`dat_project.yaml`](https://github.com/junjiem/dat/blob/main/dat_project.yaml).
- Use the `timeout` field to set the driver-level connection timeout, which is passed to `MysqlDataSource#setConnectTimeout()`.
- No Java code changes or additional dependencies are required to enable connection pooling in DAT.

## Frequently Asked Questions

### Does DAT support external pooling libraries like HikariCP?

No. According to the source code in [`MySqlDatabaseAdapterFactory.java`](https://github.com/junjiem/dat/blob/main/MySqlDatabaseAdapterFactory.java), DAT instantiates `com.mysql.cj.jdbc.MysqlDataSource` directly and does not integrate third-party pooling libraries. The MySQL driver's native pooling is sufficient for most use cases when configured through URL parameters.

### What happens if I omit the maxPoolSize parameter?

If you do not specify `maxPoolSize` in the JDBC URL, the MySQL Connector/J driver uses its default configuration, which typically allows unlimited or server-limited connections depending on your driver version. Always set `maxPoolSize` explicitly to prevent resource exhaustion in production environments.

### Can I use environment variables in the JDBC URL?

Yes. DAT processes [`dat_project.yaml`](https://github.com/junjiem/dat/blob/main/dat_project.yaml) with variable substitution, allowing you to use syntax like `${MYSQL_URL}` or `${DB_PASSWORD}` within the configuration fields, including inside the JDBC URL string.

### How do I tune the pool for high-concurrency workloads?

Increase `maxPoolSize` to match your application's thread count, enable `cachePrepStmts` with a `prepStmtCacheSize` between 250-500, and set `useServerPrepStmts=true` to reduce parsing overhead. Also configure `socketTimeout` and `connectTimeout` to fail fast during network issues, preventing thread pool saturation.