# How Chat2DB's SQL Execution Service Manages Connection Pooling

> Discover how Chat2DB's SQL execution service efficiently manages connection pooling with a two-level map, background cleanup, and generation-based invalidation for optimal performance.

- Repository: [OtterMind/Chat2DB](https://github.com/OtterMind/Chat2DB)
- Tags: internals
- Published: 2026-07-26

---

**Chat2DB implements a lightweight, home-grown connection pool in the `ConnectionPool` class that maintains a two-level map of datasource IDs to connection queues, with a maximum of 2 connections per queue, background cleanup every minute, and generation-based invalidation to handle configuration changes.**

Chat2DB is an open-source database management tool that provides SQL execution capabilities through a custom connection pooling mechanism. Unlike third-party pooling libraries, the SQL execution service uses a purpose-built `ConnectionPool` class located in [`chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/sql/ConnectionPool.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/sql/ConnectionPool.java) to manage JDBC connections efficiently. This implementation balances resource constraints with performance by limiting concurrent connections per datasource while ensuring stale connections are automatically purged.

## Core Architecture Components

The pooling architecture consists of four primary components that work together to manage connection lifecycle and execution flow.

### ConnectionPool

The **`ConnectionPool`** class serves as the central pool manager. It maintains a two-level concurrent map structure: `datasourceId → (connectionKey → queue)`, where each queue is a `LinkedBlockingQueue` storing reusable `ConnectInfo` objects.

Key implementation details include:
- **Maximum connections per queue**: Set to **2** via the `MAX_CONNECTIONS` constant to prevent database server overload.
- **Validation strategy**: Uses a probe SQL statement (`SELECT 1` or `SELECT 1 FROM DUAL` for Oracle) to verify connection health. To reduce latency, validation is skipped if the connection was used within the last 30 seconds (`SKIP_VALIDATION_IF_RECENTLY_USED_MS`).
- **Background maintenance**: A daemon thread invokes `cleanupConnections` every 60 seconds to purge idle or dead connections.

### ConnectInfo

The **`ConnectInfo`** class, found in [`chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/model/datasource/ConnectInfo.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/model/datasource/ConnectInfo.java), wraps a JDBC `Connection` together with essential metadata including datasource ID, connection key, last-access timestamp, pool generation number, and in-use status.

Thread-safe state management is provided through `trySetInUse()` and `releaseInUse()` methods, which prevent concurrent usage leaks. The `poolGeneration` field enables the pool to identify and discard stale connections when datasource configurations change.

### Chat2DBContext

**`Chat2DBContext`** in [`chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/sql/Chat2DBContext.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/sql/Chat2DBContext.java) acts as the entry point for SQL execution. It delegates connection acquisition to `ConnectionPool.getConnection(ConnectInfo)`, reusing existing connections when available. If `tryBorrowConnection` returns null (indicating an empty pool or failed validation), the context automatically creates a new physical connection via `createNewConnection`.

### DefaultSQLExecutor

The **`DefaultSQLExecutor`** executes actual SQL statements using connections supplied by the context. All higher-level services—including metadata introspection, query execution, and data export—route through this executor, ensuring every operation benefits from the underlying pooling logic.

## Connection Lifecycle Management

The pool manages connections through four distinct phases: creation, borrowing, returning, and cleanup.

### Creation

When a datasource is first accessed, `ConnectionPool.getOrCreateConnectionQueue` lazily initializes a `LinkedBlockingQueue` with a capacity of **2** (the `MAX_CONNECTIONS` value). This queue stores `ConnectInfo` objects for the specific datasource ID and connection key combination.

### Borrowing

The `tryBorrowConnection` method pulls a `ConnectInfo` object from the appropriate queue. It validates the wrapped connection using `checkConnectionIsActive` or the probe SQL. If validation fails or the connection exceeds age limits, it is physically closed and the method returns `null`, forcing `Chat2DBContext` to create a fresh connection.

### Returning

After SQL execution, callers invoke `ConnectionPool.close(ConnectInfo)`. For most database types, this does **not** terminate the physical connection. Instead, the method checks the current generation for the datasource; if the `ConnectInfo` generation matches, it returns the object to the queue via `offerOrClose`. If the generation has advanced or the queue is full, the connection is physically closed to prevent leaks.

### Cleanup

A dedicated background thread runs `cleanupConnections` every minute. This method iterates over all connection queues, removing idle connections older than 30 minutes and closing connections that fail validation probes, ensuring the pool does not retain dead or leaked resources.

## Generation-Based Invalidation

Chat2DB uses a generation counter mechanism to handle configuration changes safely. When `ConnectionPool.removeConnection(datasourceId)` is invoked—such as when a datasource is deleted or its credentials are updated—the method increments the generation counter for that datasource ID in the `CONNECTION_GENERATIONS` concurrent map.

Subsequent borrow attempts compare the `poolGeneration` stored in each `ConnectInfo` against the current generation. Mismatches cause immediate rejection, forcing the pool to discard stale connections and create new ones that reflect the updated configuration.

## Implementation Examples

The following examples demonstrate proper interaction with the pooling system:

```java
// Acquire a connection through Chat2DBContext (recommended approach)
ConnectInfo ci = new ConnectInfo();
ci.setDataSourceId(42L);
ci.setKey("my_user@my_db");

// Delegates to ConnectionPool.getConnection()
Connection conn = Chat2DBContext.getConnection(ci);

// Execute SQL using the pooled connection
ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM users");

// Return connection to pool - do NOT call conn.close() manually
Chat2DBContext.close(ci);

```

To force a pool reset after configuration changes:

```java
// Increment generation and discard existing connections for datasource 42
ConnectionPool.removeConnection(42L);

```

For advanced scenarios requiring direct pool interaction:

```java
// Access the underlying queue directly
LinkedBlockingQueue<ConnectInfo> queue = 
    ConnectionPool.getOrCreateConnectionQueue(42L, "my_user@my_db");

// Borrow from queue
ConnectInfo pooled = queue.poll();
if (pooled != null && pooled.trySetInUse()) {
    Connection conn = pooled.getConnection();
    // ... use connection ...
    pooled.releaseInUse();
    ConnectionPool.offerOrClose(queue, pooled);
}

```

## Summary

- Chat2DB implements a custom **`ConnectionPool`** class with a two-level map structure limiting each datasource to **2** concurrent connections per unique key.
- **`ConnectInfo`** wrappers manage JDBC connections with thread-safe in-use flags and generation tracking to prevent leaks and stale usage.
- The pool validates connections using `SELECT 1` probes but skips validation for recently used connections (30-second window).
- Background cleanup runs every minute to remove idle connections older than 30 minutes.
- **Generation counters** invalidate pooled connections when datasource configurations change, ensuring fresh connections use updated credentials and parameters.

## Frequently Asked Questions

### How does Chat2DB validate connections before returning them to the application?

The `ConnectionPool` validates connections using the `checkConnectionIsActive` method, which executes a probe SQL statement (`SELECT 1` or `SELECT 1 FROM DUAL` for Oracle databases). To minimize overhead, the pool skips validation if the connection was accessed within the last 30 seconds, as defined by the `SKIP_VALIDATION_IF_RECENTLY_USED_MS` constant.

### What happens when a datasource configuration changes in Chat2DB?

When a datasource is modified or deleted, `ConnectionPool.removeConnection(datasourceId)` increments a generation counter specific to that datasource. The pool rejects any `ConnectInfo` objects carrying a stale `poolGeneration` number during subsequent borrow attempts, forcing the creation of new connections that reflect the updated configuration.

### Why does Chat2DB limit connections to 2 per queue instead of using a larger pool?

According to the source code in [`ConnectionPool.java`](https://github.com/OtterMind/Chat2DB/blob/main/ConnectionPool.java), the `MAX_CONNECTIONS` constant is hardcoded to **2** to balance resource utilization with database server constraints. This conservative limit prevents overwhelming target databases while still allowing parallel operations for distinct connection keys within the same datasource.

### How does Chat2DB prevent connection leaks when multiple threads access the same connection?

The **`ConnectInfo`** wrapper provides thread-safe state management through `trySetInUse()` and `releaseInUse()` methods, which use atomic operations to ensure only one thread can mark a connection as active at a time. Additionally, the background cleanup thread periodically scans for connections that remain idle beyond the 30-minute threshold and forcibly closes them.