How Chat2DB Manages Database Connection Pooling: Architecture and Implementation

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, 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 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

// 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

// 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)

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

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