# How Chat2DB Manages Database Connection Pooling: Architecture and Implementation

> Discover how Chat2DB performs database connection pooling with its custom ConnectionPool class. Learn about its architecture, generation counters, and dequeue limits for efficient database management.

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

---

**Chat2DB implements a lightweight, home-grown database connection pooling solution using the `ConnectionPool` class that maintains a two-level map of datasource-specific queues, limits each queue to 2 connections, and uses generation counters to handle configuration changes safely.**

Chat2DB, the open-source multi-database management tool, avoids heavy third-party connection pooling libraries by embedding a custom pooling strategy directly into its execution layer. This database connection pooling implementation provides deterministic resource management through tightly coupled components in the SPI (Service Provider Interface) module, ensuring efficient JDBC connection reuse across SQL queries, metadata requests, and data exports.

## Core Architecture of the Connection Pool

The pooling mechanism centers on three primary components that coordinate connection lifecycle management from acquisition to release.

### The ConnectionPool Manager

At [`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), the `ConnectionPool` class serves as the central authority. It maintains a concurrent two-level map structure: `datasourceId → (connectionKey → queue)`, where each queue is a `LinkedBlockingQueue` storing reusable `ConnectInfo` objects.

Key configuration constants hardcoded in this implementation include:

- **Maximum connections per queue**: 2 (`MAX_CONNECTIONS`)
- **Validation skip window**: 30 seconds (`SKIP_VALIDATION_IF_RECENTLY_USED_MS`)
- **Idle timeout**: 30 minutes (enforced during cleanup)
- **Validation query**: `SELECT 1` (or `SELECT 1 FROM DUAL` for Oracle)

A background daemon thread runs `cleanupConnections()` every minute to purge idle or stale connections, preventing resource leaks in long-running instances.

### The ConnectInfo Wrapper

The `ConnectInfo` class at [`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) encapsulates more than just the raw JDBC `Connection`. It tracks:

- Datasource ID and connection key identifiers
- Last-access timestamp for LRU eviction
- Pool generation marker for invalidation logic
- Atomic in-use flag via `trySetInUse()` and `releaseInUse()` methods

This wrapper prevents concurrent usage conflicts and enables the pool to discard connections that belong to obsolete datasource configurations.

### Chat2DBContext Integration

The `Chat2DBContext` class acts as the high-level entry point for all SQL execution. When code calls `Chat2DBContext.getConnection(ConnectInfo)`, the context delegates to `ConnectionPool.getConnection(ConnectInfo)`, which orchestrates borrowing logic. If the pool cannot supply a valid connection, the context falls back to `createNewConnection()`, ensuring execution continuity even when the pool is exhausted.

## Connection Lifecycle and Pool Management

Understanding how connections move through the system reveals why Chat2DB achieves low overhead without sacrificing reliability.

### Borrowing Connections from the Pool

When `tryBorrowConnection` is invoked, the pool performs the following sequence:

1. Locates or creates the appropriate queue via `getOrCreateConnectionQueue()`
2. Polls the queue for an available `ConnectInfo` instance
3. Validates the connection using `checkConnectionIsActive()` (executing `SELECT 1` or the Oracle variant)
4. Skips validation if the connection was used within the last 30 seconds
5. Returns the validated connection to the caller

If validation fails or the connection exceeds age limits, the pool closes it and returns `null`, triggering the creation of a fresh connection.

### Returning Connections to the Pool

Unlike standard JDBC usage where developers call `connection.close()`, Chat2DB requires explicit pool management through `ConnectionPool.close(ConnectInfo)`. This method does **not** terminate the physical database connection. Instead, it updates the last-access timestamp and returns the `ConnectInfo` to its queue via `offerOrClose()`.

The pool checks the current generation for the datasource. If the generation has incremented since the connection was created (indicating a configuration change), the connection is discarded rather than reused.

### Background Cleanup and Validation

The `cleanupConnections()` method iterates over all queues every 60 seconds, removing:

- Connections idle for more than 30 minutes
- Connections that fail the `SELECT 1` validation probe
- Connections marked with stale generations

This proactive validation ensures that broken connections (due to network issues or database server timeouts) do not persist in the pool indefinitely.

## Handling Configuration Changes with Generations

Chat2DB solves the dynamic datasource problem through a generation-based invalidation strategy. Each datasource ID maintains an atomic counter in `CONNECTION_GENERATIONS`.

When `ConnectionPool.removeConnection(datasourceId)` is called—typically after a user updates database credentials or connection parameters—the generation counter increments. Subsequent borrow attempts compare the `ConnectInfo.poolGeneration` against the current generation. Mismatches result in immediate discarding of the stale connection, forcing the creation of new connections with updated configuration.

This mechanism eliminates the risk of executing queries against outdated connection strings or expired credentials without requiring a full application restart.

## Practical Usage Examples

The following patterns demonstrate how to interact with Chat2DB's connection pooling API correctly:

### Acquiring and Releasing Connections

```java
// Prepare connection metadata
ConnectInfo connectInfo = new ConnectInfo();
connectInfo.setDataSourceId(42L);
connectInfo.setKey("username@database_name");

// Borrow from pool (or create if empty)
Connection connection = Chat2DBContext.getConnection(connectInfo);

try {
    // Execute queries using the pooled connection
    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM users");
    // Process results...
} finally {
    // Return to pool instead of closing
    ConnectionPool.close(connectInfo);
}

```

### Force Pool Reset After Configuration Changes

```java
// After updating datasource credentials or URL
Long dataSourceId = 42L;
ConnectionPool.removeConnection(dataSourceId);
// Subsequent getConnection() calls will create fresh connections
// with the new configuration

```

### Direct Queue Interaction (Advanced)

```java
// Access the underlying queue for specific datasource/key combination
LinkedBlockingQueue<ConnectInfo> queue = ConnectionPool.getOrCreateConnectionQueue(42L, "user@db");

// Manual borrow
ConnectInfo pooled = queue.poll();
if (pooled != null) {
    try {
        Connection conn = pooled.getConnection();
        // Use connection...
    } finally {
        // Manual return with generation check
        ConnectionPool.offerOrClose(queue, pooled);
    }
}

```

## Summary

- **Chat2DB uses a custom `ConnectionPool` class** located in the SPI module to manage JDBC connections without external pooling libraries.
- **The pool structure maps datasource IDs to connection keys to queues**, with a hard limit of 2 connections per queue to conserve resources.
- **Connections are validated via `SELECT 1`** (or Oracle-specific variants) unless used within the last 30 seconds, balancing reliability with performance.
- **Generation counters invalidate stale connections** when datasource configurations change, ensuring security and consistency.
- **Background cleanup runs every minute** to remove idle connections older than 30 minutes or those that fail validation.
- **Always use `ConnectionPool.close(ConnectInfo)`** rather than `connection.close()` to return connections to the pool and prevent resource leaks.

## Frequently Asked Questions

### How does Chat2DB validate connections before reuse?

Chat2DB validates connections through the `checkConnectionIsActive()` method in [`ConnectionPool.java`](https://github.com/OtterMind/Chat2DB/blob/main/ConnectionPool.java). It executes a validation SQL query—`SELECT 1` for most databases or `SELECT 1 FROM DUAL` for Oracle—to verify the connection is alive. However, to minimize overhead, validation is skipped if the connection was accessed within the last 30 seconds (`SKIP_VALIDATION_IF_RECENTLY_USED_MS`).

### What happens to pooled connections when a datasource configuration changes?

When a datasource is modified or deleted, calling `ConnectionPool.removeConnection(datasourceId)` increments an internal generation counter for that datasource. The pool stores the generation number in each `ConnectInfo` object upon creation. During subsequent borrow operations, any connection whose stored generation does not match the current generation is immediately closed and discarded, ensuring only connections with current configuration parameters are reused.

### Why does Chat2DB limit the connection pool to 2 connections per queue?

The `MAX_CONNECTIONS` constant is set to 2 in [`ConnectionPool.java`](https://github.com/OtterMind/Chat2DB/blob/main/ConnectionPool.java) as a deliberate trade-off for a desktop application context. This limit prevents resource exhaustion on client machines while still allowing concurrent operations (such as running a query while fetching metadata). For typical Chat2DB usage patterns involving interactive querying, 2 connections per datasource/key combination provides sufficient parallelism without overwhelming local or remote database connection limits.

### How does the pool prevent connection leaks during concurrent execution?

The `ConnectInfo` wrapper implements thread-safety mechanisms through `trySetInUse()` and `releaseInUse()` methods. When a thread borrows a connection, it must successfully atomically mark the `ConnectInfo` as in-use. If another thread attempts to use the same connection simultaneously, the flag prevents concurrent access. The `Chat2DBContext` ensures that `releaseInUse()` is called when `ConnectionPool.close()` is invoked, returning the connection to a safe state for the next borrower.