How the Dify DB-Query Plugin Handles NULL Values in Query Results

The Dify DB-Query plugin converts all SQL NULL values to empty strings ('') before returning results to the Dify platform, ensuring the JSON response never contains null literals.

When querying databases through the junjiem/dify-plugin-tools-dbquery repository, understanding how NULL values in query results are processed is critical for building reliable chatflows. The plugin's DbUtil.run_query method implements a strict normalization strategy that replaces database NULLs with empty strings at the DataFrame level, then preserves this representation through the final JSON serialization.

How NULL Values Are Processed in DbUtil.run_query

The NULL handling logic resides in the run_query method found in both db_query/tools/db_util.py and db_query_pre_auth/tools/db_util.py. The implementation follows a three-stage pipeline that ensures consistent empty string representation across all database types.

Step 1: Loading Results into a Pandas DataFrame

The plugin executes SQL through SQLAlchemy and immediately loads the result set into a pandas DataFrame using pd.read_sql_query. At this stage, SQL NULL values are represented as pandas NaN (Not a Number) floats, which cannot be directly serialized to JSON without causing errors.

# From db_query/tools/db_util.py

df = pd.read_sql_query(text(sql), engine)

Step 2: Normalizing NULLs with fillna()

Immediately after DataFrame construction, the code calls df.fillna('') to replace every NaN with an empty string. This operation occurs before any type conversion or dictionary serialization, ensuring that NULL values never propagate as JSON null literals.

# NULL normalization happens here

df = df.fillna('')

Step 3: Converting to Dictionaries for JSON Output

The normalized DataFrame is converted to a list of dictionaries using df.to_dict(orient="records"). During the final iteration over these records, the plugin checks if value is None or value == '' to handle any edge cases, leaving the empty string unchanged for the final JSON payload sent to Dify.

records = df.to_dict(orient="records")
for record in records:
    for key, value in record.items():
        if value is None or value == '':
            # Preserves empty string representation

            continue
        # Additional processing for non-empty values...

Code Implementation Details

The NULL handling logic is identical across both the standard and pre-authentication variants of the plugin. The DbUtil class encapsulates the database connection and query execution, with run_query serving as the primary entry point.

Key implementation characteristics:

• Location: db_query/tools/db_util.py (lines 45-60 in typical implementations)
• Method signature: run_query(self, sql: str) -> list[dict]
• Dependency: Requires pandas and sqlalchemy for NULL normalization
• Output format: List of dictionaries with empty strings replacing NULLs

Practical Example: Querying Nullable Columns

When querying tables with optional columns, the empty string conversion becomes immediately apparent in the output.

from db_query.tools.db_util import DbUtil

# Initialize connection

db = DbUtil(
    db_type="postgresql",
    username="analytics_user",
    password="secure_pass",
    host="db.company.com",
    port="5432",
    database="production"
)

# Query containing NULL values in optional_note column

sql = "SELECT id, username, optional_note FROM users LIMIT 2;"
results = db.run_query(sql)

print(results)

Example output:

[
  {
    "id": 1,
    "username": "alice_smith",
    "optional_note": ""
  },
  {
    "id": 2,
    "username": "bob_jones",
    "optional_note": ""
  }
]

Notice that optional_note returns as an empty string even when the database stores NULL for that column.

Key Files and Functions

The NULL handling behavior is implemented across these critical source files:

• db_query/tools/db_util.py – Contains the core DbUtil class and run_query method that executes df.fillna('') to normalize NULL values
• db_query_pre_auth/tools/db_util.py – Identical NULL handling logic for the pre-authentication variant of the plugin
• db_query/provider/db_query.py – Provider wrapper that instantiates DbUtil and invokes run_query for standard database connections
• db_query_pre_auth/provider/db_query.py – Pre-authentication provider wrapper that processes query results after NULL normalization

Summary

• The Dify DB-Query plugin converts all SQL NULL values to empty strings ('') using pandas.DataFrame.fillna('') immediately after query execution.
• This normalization occurs in the DbUtil.run_query method located in db_query/tools/db_util.py and its pre-authentication counterpart.
• The final JSON payload sent to Dify contains empty strings rather than JSON null literals, ensuring consistent string typing across all result fields.
• This behavior applies uniformly across PostgreSQL, MySQL, and other supported database types, as the normalization happens at the DataFrame level after SQLAlchemy retrieval.

Frequently Asked Questions

Does the plugin preserve SQL NULL as JSON null?

No. The plugin explicitly converts SQL NULL values to empty strings ('') before serialization. In db_query/tools/db_util.py, the code executes df.fillna('') immediately after loading query results into a pandas DataFrame, ensuring the final JSON contains empty strings rather than null literals.

Why does the plugin convert NULL to empty strings?

The conversion prevents JSON serialization errors and simplifies downstream processing in Dify chatflows. Pandas represents SQL NULL as NaN (Not a Number), which cannot be directly serialized to JSON. By converting to empty strings at the DataFrame level, the plugin ensures all values are string-serializable and consistent for LLM processing.

Can I modify the NULL handling behavior?

Currently, the NULL-to-empty-string conversion is hardcoded in the run_query method of db_util.py. The line df = df.fillna('') executes unconditionally for all queries. To preserve NULLs as JSON null, you would need to fork the repository and modify the DataFrame processing logic to use df.where(pd.notnull(df), None) or similar before dictionary conversion.

Which database types does this behavior apply to?

The NULL handling applies uniformly to all databases supported by the plugin, including PostgreSQL, MySQL, MariaDB, SQL Server, and Oracle. The normalization occurs after SQLAlchemy retrieves the result set and loads it into a pandas DataFrame, making the behavior database-agnostic and consistent regardless of the underlying SQL dialect.

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