How the DbQuery Plugin Handles Large Query Result Sets in Dify

The DbQuery plugin caps result sets at 100 rows by default (configurable via DIFY_PLUGIN_MAX_ROWS), fetches one extra row to detect overflow, and returns a truncated flag to indicate when results have been limited.

The junjiem/dify-plugin-tools-dbquery plugin protects Dify instances from memory and bandwidth exhaustion when users execute SQL queries that could return massive datasets. By implementing a hard row limit with explicit truncation signaling, the plugin ensures that large query result sets never overwhelm the host system. This design pattern is implemented across several core modules that coordinate to enforce limits at the database cursor level.

Row Limiting Architecture and Configuration

The plugin employs a centralized configuration approach that affects all database interactions uniformly. Rather than allowing unlimited result streaming, every query execution path respects a configurable maximum row count.

The DIFY_PLUGIN_MAX_ROWS Environment Variable

The hard cap is controlled by the DIFY_PLUGIN_MAX_ROWS environment variable, which defaults to 100 if not explicitly set. This value is read at module import time in db_query/tools/db_util.py, meaning it must be configured before the provider class is instantiated. Operators can increase this limit for data-heavy workflows or decrease it to conserve memory in resource-constrained environments.

Core Components Involved in Result Set Limiting

File	Responsibility	Limiting Mechanism
db_query/tools/db_util.py	Low-level database execution	Uses cursor.fetchmany(limit) where limit equals MAX_ROWS + 1 to detect overflow
db_query/tools/sql_query.py	SQL validation and request parsing	Passes the calculated row limit to db_util.fetch_all()
db_query/provider/db_query.py	Dify ToolProvider interface	Receives bounded results and adds the truncated boolean flag to the response
db_query/main.py	Local CLI testing entry point	Reuses the same limit logic for consistent behavior during development

Implementation Details in db_util.py

The actual protection against large query result sets occurs in db_query/tools/db_util.py, where the plugin interacts directly with database cursors. Rather than using fetchall(), which would load the entire result set into memory, the code uses fetchmany() with a calculated limit.

The implementation fetches MAX_ROWS + 1 rows rather than exactly MAX_ROWS. This pattern allows the plugin to determine whether the result set exceeds the limit without requiring a separate count query:

# db_query/tools/db_util.py (simplified logic)

import os
from typing import Dict, Any, List

MAX_ROWS = int(os.getenv("DIFY_PLUGIN_MAX_ROWS", "100"))

def fetch_all(connection, sql: str) -> Dict[str, Any]:
    cursor = connection.cursor()
    cursor.execute(sql)
    
    # Fetch one extra row to detect if result set is larger than limit

    rows = cursor.fetchmany(MAX_ROWS + 1)
    truncated = len(rows) > MAX_ROWS
    
    if truncated:
        rows = rows[:MAX_ROWS]  # Discard the overflow row

    
    # Convert to list of dictionaries for JSON serialization

    columns = [desc[0] for desc in cursor.description]
    data = [dict(zip(columns, row)) for row in rows]
    
    return {
        "data": data,
        "truncated": truncated
    }

This approach ensures that memory usage remains bounded regardless of the query's actual result size. The truncated flag provides explicit signaling to downstream components that the data represents a subset of the full result set.

How the Provider Returns Truncated Results

After db_util.py processes the query, db_query/provider/db_query.py acts as the bridge between the database layer and the Dify platform. The provider receives the bounded dictionary containing the data and truncation flag, then returns it directly to the Dify tool execution framework.

# db_query/provider/db_query.py (conceptual implementation)

from dify_plugin import ToolProvider
from db_query.tools import db_util

class DbQueryProvider(ToolProvider):
    def run(self, params: dict) -> dict:
        sql_query = params.get("query")
        connection = self._get_connection()  # Establishes DB connection

        
        # fetch_all applies the MAX_ROWS limit and returns truncated flag

        result = db_util.fetch_all(connection, sql_query)
        
        # Result structure: {"data": [...], "truncated": true/false}

        return result

Because the truncation occurs at the database cursor level before the provider ever sees the data, the Dify instance is protected from large payloads even if the SQL query would have returned millions of rows. The truncated flag allows frontend applications or LLM agents to inform users that only a partial result set is being displayed.

Configuring and Testing Row Limits

Operators can adjust the behavior of the large query result set handling by setting environment variables before starting the Dify plugin container or local development server.

To increase the limit to 500 rows:

export DIFY_PLUGIN_MAX_ROWS=500
python -m db_query.main

To verify truncation behavior during local testing using the CLI entry point in db_query/main.py:

# Local testing example

import os
os.environ["DIFY_PLUGIN_MAX_ROWS"] = "10"

from db_query.tools.db_util import fetch_all
import sqlite3

# Create a test database with 100 rows

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE test (id INTEGER)")
conn.executemany("INSERT INTO test VALUES (?)", [(i,) for i in range(100)])
conn.commit()

# Query all rows - should return only 10 with truncation flag

result = fetch_all(conn, "SELECT * FROM test")
print(f"Rows returned: {len(result['data'])}")  # 10

print(f"Truncated: {result['truncated']}")      # True

Remember that the environment variable must be set before importing the db_util module, as the MAX_ROWS constant is evaluated at import time.

Summary

• Hard Row Limit: The plugin enforces a maximum row count (default 100) via the DIFY_PLUGIN_MAX_ROWS environment variable, configurable at deployment time.
• Overflow Detection: The code fetches MAX_ROWS + 1 rows using cursor.fetchmany() to detect when the actual result set exceeds the limit without requiring a separate count query.
• Truncation Signaling: When results exceed the limit, the plugin returns a JSON object with "truncated": true, allowing downstream systems to notify users that data has been limited.
• Memory Protection: By limiting rows at the database cursor level in db_query/tools/db_util.py, the plugin prevents memory exhaustion regardless of query result size.
• Consistent Application: The limit applies uniformly across the Dify provider interface (db_query/provider/db_query.py) and local CLI testing (db_query/main.py).

Frequently Asked Questions

What happens if my SQL query returns more rows than the DIFY_PLUGIN_MAX_ROWS limit?

When the result set exceeds the configured limit, the plugin returns exactly MAX_ROWS rows and sets the "truncated" flag to true in the response. The extra rows are discarded at the database cursor level, protecting the Dify instance from large payloads while signaling to the user that the data represents a partial result set.

How do I increase the row limit for the DbQuery plugin?

Set the DIFY_PLUGIN_MAX_ROWS environment variable to your desired maximum before starting the Dify plugin container or local development server. For example, export DIFY_PLUGIN_MAX_ROWS=500 allows up to 500 rows per query. This variable must be set before the Python process imports db_query/tools/db_util.py, as the limit is read at module initialization time.

Does the plugin use streaming to handle large result sets?

No, the plugin does not implement streaming for large result sets. Instead, it uses cursor.fetchmany(MAX_ROWS + 1) to pull a bounded number of rows into memory, discarding any overflow. This approach prioritizes memory safety and predictable payload sizes over streaming unlimited data, which aligns with Dify's architecture where tool responses are typically JSON objects rather than streams.

Where is the truncation logic implemented in the source code?

The core truncation logic resides in db_query/tools/db_util.py, specifically within the fetch_all function. This module defines the MAX_ROWS constant from the environment variable, executes the SQL using cursor.fetchmany(), detects when the result set exceeds the limit by fetching one extra row, and constructs the response dictionary containing the truncated boolean flag.

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