How to Configure Connection Pooling for MySQL in dat_project.yaml

DAT delegates MySQL connection pooling to the JDBC driver entirely through the db.configuration.url field in 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 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. 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:

• 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:

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:

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:

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)

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

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:

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: 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.

• 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.

• project_yaml_template.jinja: Template file that generates the initial 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.
• 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, 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 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}