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

> Learn how the Dify DB-Query plugin converts SQL NULL values to empty strings for cleaner JSON responses. Ensure your Dify platform never receives null literals.

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

---

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

```python

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

```python

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

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

```python
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:**

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