How the Dify db_query Plugin Implements Connection Pooling for Database Connections

The Dify db_query plugin implements connection pooling by leveraging SQLAlchemy's Engine with a fixed pool size of 100 connections and a 36-second recycle timeout, configured in the DbUtil class constructor.

The Dify db_query plugin provides robust database querying capabilities within the Dify AI workflow platform. Efficient connection pooling is critical for maintaining performance under load, preventing connection exhaustion, and ensuring rapid query execution. The plugin achieves this through SQLAlchemy's mature pooling infrastructure, wrapped in a clean utility class that handles multiple database backends.

SQLAlchemy Engine as the Foundation for Dify db_query Plugin Connection Pooling

The pooling mechanism centers on SQLAlchemy's create_engine() function, invoked during DbUtil initialization in [db_query/tools/db_util.py](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/db_query/tools/db_util.py). Rather than opening and closing physical database sockets for every query, the plugin maintains a persistent pool of DB-API connections managed by the SQLAlchemy Engine.

When a DbUtil instance is created, it immediately initializes this engine with specific pooling parameters designed for high-throughput AI workflows. The engine persists for the lifetime of the utility object, serving all subsequent query operations.

Configuring Connection Pool Parameters in db_util.py

The constructor in db_util.py applies two critical tuning parameters to optimize connection behavior for production deployments.

Pool Size and Recycling Settings

The engine creation line explicitly sets pool_size=100 and pool_recycle=36:

self.engine = create_engine(self.get_url(), pool_size=100, pool_recycle=36)

• pool_size=100: Configures SQLAlchemy to maintain up to 100 open connections in the pool. This accommodates high concurrency when multiple Dify workflow nodes execute database queries simultaneously.
• pool_recycle=36: Forces each connection to be recycled (closed and reopened) after 36 seconds of use. This prevents stale connection errors when interacting with databases that enforce short idle timeouts or aggressive firewall rules.

Database URL Construction

Before engine creation, the get_url() method assembles the database connection string from configuration parameters:

def get_url(self):
    # Assembles driver, credentials, host, port, and database name

    # Supports MySQL, PostgreSQL, and other SQLAlchemy-compatible drivers

    return f"{self.driver}://{self.username}:{self.password}@{self.host}:{self.port}/{self.database}"

This dynamic URL construction allows the same pooling logic to work across different database backends without code changes.

Executing Queries with Pooled Connections

All query execution flows through the run_query() method, which leverages the pooled engine rather than creating new connections:

def run_query(self, query_sql):
    df = pd.read_sql_query(sql=query_sql, con=self.engine, parse_dates="%Y-%m-%d %H:%M:%S")
    return df.to_dict(orient="records")

By passing self.engine as the connection argument to pandas read_sql_query(), the operation automatically checks out a connection from the SQLAlchemy pool, executes the SQL, and returns the connection to the pool when the DataFrame operation completes. This implicit connection management eliminates connection leaks and reduces latency for subsequent queries.

Proper Resource Cleanup and Pool Disposal

The DbUtil class implements Python's context manager protocol to ensure pooled connections are properly released when the plugin finishes processing:

def __enter__(self):
    return self

def __exit__(self, exc_type, exc_val, exc_tb):
    self.close()

def close(self):
    if self.engine:
        self.engine.dispose()

When the DbUtil object exits the with block or when the Dify workflow node completes execution, engine.dispose() is called. This method closes all DB-API connections currently checked into the pool, preventing resource exhaustion in long-running Dify server processes.

Practical Implementation Examples

The following examples demonstrate how the connection pooling behaves in practice when using the Dify db_query plugin:

Creating a utility with implicit pooling:

from db_query.tools.db_util import DbUtil

# Initialize with MySQL configuration

db = DbUtil(
    db_type="mysql",
    username="my_user",
    password="my_pass",
    host="db.example.com",
    port="3306",
    database="my_db"
)

# The engine is now active with a pool of up to 100 connections

Executing multiple queries reusing pooled connections:

# First query checks out a connection from the pool

active_users = db.run_query("SELECT id, name FROM users WHERE status = 'active'")

# Second query reuses an available connection from the pool

recent_orders = db.run_query("SELECT * FROM orders WHERE created_at > NOW() - INTERVAL 1 DAY")

# Connections are returned to the pool automatically after each execution

Ensuring proper cleanup with context managers:

with DbUtil(db_type="postgresql", username="admin", password="secret", 
            host="postgres.internal", port="5432", database="analytics") as db:
    
    result = db.run_query("SELECT COUNT(*) as total FROM events")
    print(result)

# Upon exiting the with block, engine.dispose() closes all 100 pooled connections

Summary

The Dify db_query plugin implements connection pooling through SQLAlchemy's Engine infrastructure with the following key characteristics:

• Pool Configuration: Maintains up to 100 concurrent connections (pool_size=100) with a 36-second recycle timeout (pool_recycle=36) to prevent stale connections.
• Centralized Management: The DbUtil class in db_query/tools/db_util.py encapsulates engine creation, URL construction, and query execution.
• Automatic Reuse: All queries executed via run_query() automatically check out connections from the pool and return them after execution, eliminating connection overhead.
• Resource Safety: Context manager support ensures engine.dispose() is called to cleanly close all pooled connections when workflow execution completes.

Frequently Asked Questions

What is the maximum number of concurrent connections the Dify db_query plugin supports?

The plugin supports up to 100 concurrent connections by default, configured via the pool_size=100 parameter passed to SQLAlchemy's create_engine() function in db_query/tools/db_util.py. This limit can accommodate high-concurrency Dify workflows, though the actual number of simultaneous connections depends on the database server's own connection limits and the parallelism of your workflow nodes.

Why does the plugin recycle connections every 36 seconds?

The pool_recycle=36 setting forces SQLAlchemy to recycle (close and reopen) connections after 36 seconds of use. This prevents stale connection errors that occur when database servers or intermediate firewalls terminate idle connections after short timeouts. The 36-second interval ensures connections remain fresh without excessive overhead from constant recreation, balancing reliability and performance for AI workflow queries.

How does the plugin ensure database connections are properly closed?

The DbUtil class implements Python's context manager protocol through __enter__ and __exit__ methods. When the Dify workflow finishes executing or when exiting a with block, __exit__ automatically calls self.close(), which invokes self.engine.dispose(). This SQLAlchemy method cleanly closes all DB-API connections currently held in the pool, preventing resource leaks in long-running Dify server processes.

Can the connection pool settings be customized for different database types?

While the current implementation in db_query/tools/db_util.py hardcodes pool_size=100 and pool_recycle=36 in the create_engine() call, SQLAlchemy's engine configuration is highly extensible. Advanced users could modify the DbUtil constructor to accept additional pooling parameters (such as max_overflow, pool_timeout, or pool_pre_ping) and pass them to create_engine(). The plugin currently supports MySQL, PostgreSQL, and other SQLAlchemy-compatible drivers through the dynamic URL construction in get_url().

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