How Chat2DB's SQL Execution Service Manages Connection Pooling

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

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

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

For advanced scenarios requiring direct pool interaction:

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

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