How the Dify DB-Query Plugin Handles Connection Pool Recycling (pool_recycle=36)

The Dify DB-Query plugin sets SQLAlchemy's pool_recycle=36 to automatically close and replace any database connection older than 36 seconds, preventing stale-connection errors during long-running Dify workflows.

The junjiem/dify-plugin-tools-dbquery repository provides database query capabilities for Dify AI workflows. Understanding how this plugin manages connection pool recycling is critical for maintaining stable database interactions, especially when dealing with cloud databases or network infrastructure that aggressively drops idle connections.

Understanding pool_recycle in SQLAlchemy

SQLAlchemy's connection pooling system maintains a pool of active database connections to avoid the overhead of establishing new connections for every query. However, connections that remain idle for extended periods can become "stale" when database servers or network intermediaries (like AWS RDS, Azure Database, or corporate firewalls) terminate idle connections without notifying the client.

The pool_recycle parameter solves this by specifying a maximum age in seconds for any connection. When a connection reaches this age, SQLAlchemy automatically closes it and creates a fresh replacement before the next use.

Implementation in the Dify Plugin

Engine Configuration

In db_query/tools/db_util.py (and the identical implementation in db_query_pre_auth/tools/db_util.py), the plugin initializes its SQLAlchemy engine with explicit pool management settings:

from sqlalchemy import create_engine

class DbUtil:
    def __init__(self, db_type, username, password, host, port, database):
        # ... credential handling ...

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

The pool_recycle=36 setting ensures that no connection remains in the pool longer than 36 seconds. This aggressive recycling prevents the "MySQL server has gone away" or "connection reset by peer" errors common in long-running Dify plugin instances.

Connection Pool Settings

The plugin pairs pool_recycle=36 with pool_size=100, allowing up to 100 simultaneous connections while ensuring each is refreshed regularly. This combination supports high-concurrency Dify workflows without exhausting database server connection limits or hitting idle timeouts.

When the DbUtil instance closes (via the close() method or context manager exit), the engine calls dispose(), immediately releasing all pooled connections back to the database server.

Practical Code Examples

Basic Usage with Context Manager

The context manager pattern ensures proper pool disposal after query execution:

from db_query.tools.db_util import DbUtil

# Initialize with automatic pool management

with DbUtil(
    db_type="postgresql",
    username="my_user",
    password="my_pass",
    host="db.example.com",
    port="5432",
    database="my_db",
) as db:
    # Connection is fresh (less than 36 seconds old)

    rows = db.run_query("SELECT id, name FROM customers LIMIT 10")
    for row in rows:
        print(row)

# Engine.dispose() called automatically, releasing all 100 pool slots

Plugin Action Implementation

For Dify plugin actions requiring explicit lifecycle control:

from db_query_pre_auth.tools.db_util import DbUtil

def query_action(params):
    db = DbUtil(
        db_type=params["db_type"],
        username=params["username"],
        password=params["password"],
        host=params["host"],
        port=params.get("port"),
        database=params.get("database"),
    )
    try:
        # pool_recycle=36 ensures connection validity

        result = db.run_query(params["sql"])
        return result
    finally:
        db.close()  # Forces immediate pool disposal

Both implementations rely on the pool_recycle=36 configuration in db_util.py to guarantee connection freshness, regardless of how long the Dify workflow remains idle between queries.

Summary

• The Dify DB-Query plugin configures SQLAlchemy with pool_recycle=36 in db_query/tools/db_util.py to prevent stale connections.
• This setting automatically closes and replaces any connection older than 36 seconds, eliminating "server has gone away" errors from idle timeouts.
• The plugin combines this with pool_size=100 to support high-concurrency workflows while maintaining connection freshness.
• Proper resource cleanup occurs via engine.dispose() in the close() method or context manager exit.

Frequently Asked Questions

What does pool_recycle=36 mean in SQLAlchemy?

pool_recycle=36 tells SQLAlchemy to automatically close and replace any database connection that has been open for more than 36 seconds. This prevents the application from using stale connections that may have been silently dropped by the database server or network infrastructure, which commonly occurs with default idle timeouts of 300 seconds or more on cloud database services.

Why does the Dify plugin use 36 seconds specifically?

The 36-second value in db_query/tools/db_util.py provides an aggressive safety margin below common database idle timeouts. Many cloud providers and corporate firewalls drop idle connections after 60–300 seconds. By recycling at 36 seconds, the plugin ensures connections remain fresh during long-running Dify AI workflows without being so aggressive as to cause unnecessary connection churn for typical query durations.

How does pool_recycle interact with pool_size?

The plugin sets pool_size=100 alongside pool_recycle=36 to balance concurrency and reliability. The pool_size determines the maximum number of persistent connections maintained (100), while pool_recycle ensures each of those 100 connections is refreshed every 36 seconds. This combination supports high-throughput Dify applications where many simultaneous queries execute, while preventing any single connection from becoming stale due to idle time.

Where is the connection pool configured in the source code?

The connection pool configuration resides in the DbUtil class constructor within db_query/tools/db_util.py (and identically in db_query_pre_auth/tools/db_util.py). Specifically, lines creating the SQLAlchemy engine set pool_recycle=36 and pool_size=100. The close() method in the same file calls self.engine.dispose() to release all pooled connections when the utility is destroyed.

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