# How the DbQuery Plugin Handles Large Query Result Sets in Dify

> Learn how the DbQuery plugin manages large result sets in Dify by capping rows, detecting overflow, and flagging truncated data. Optimize your queries now.

- Repository: [Junjie.M/dify-plugin-tools-dbquery](https://github.com/junjiem/dify-plugin-tools-dbquery)
- Tags: internals
- Published: 2026-03-05

---

**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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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:

```python

# 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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/db_util.py) processes the query, [`db_query/provider/db_query.py`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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.

```python

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

```bash
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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/db_query/main.py):

```python

# 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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/db_query/provider/db_query.py)) and local CLI testing ([`db_query/main.py`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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.