Issue Review: cloudflare/workers-sdk#6378

Summary

The wrangler d1 execute --json command incorrectly converts SQL NULL values to the string "null" instead of preserving JSON null, producing invalid JSON output.

Findings

• Created: 2024-07-30
• Updated: 2025-10-30
• Version: 3.67.1 → 4.60.0 (current)
• Component: D1 (packages/wrangler/src/d1/execute.ts)
• Labels: bug, d1
• Comments: 0

Key Evidence

• No PRs found that reference issue #6378
• Changelog does not mention this issue being fixed
• Root cause identified in source code at packages/wrangler/src/d1/execute.ts:260-262:

  if (value === null) {
    value = "null";  // BUG: converts null to string "null"
  }
  

• The bug affects local execution only (executeLocally function). Remote execution via API likely preserves proper JSON null values since it just passes through API response.
• The issue persists in the current codebase (confirmed via GitHub API)

Recommendation

Status: KEEP OPEN

Reasoning: The bug is confirmed to still exist in the current codebase. The code explicitly converts null values to the string "null" which violates JSON specification (RFC 4627 section 2.1). This affects users who rely on --json output for programmatic processing.

Action: Fix the bug in packages/wrangler/src/d1/execute.ts by preserving null values when JSON output is requested.

Root Cause Analysis

The bug is in the executeLocally function in packages/wrangler/src/d1/execute.ts at lines 250-268.

The code processes query results and transforms each row's values:

// packages/wrangler/src/d1/execute.ts:250-268
const allResults = results.map<QueryResult>((result) => ({
  results: (result.results ?? []).map((row) =>
    Object.fromEntries(
      Object.entries(row).map(([key, value]) => {
        if (Array.isArray(value)) {
          value = `[${value.join(", ")}]`;
        }
        if (value === null) {
          value = "null";  // <-- BUG: This converts null to the string "null"
        }
        return [key, value];
      })
    )
  ),
  success: result.success,
  meta: { duration: result.meta?.duration },
}));

Why this happens:

1. The QueryResult type defines results as Record<string, string | number | boolean>[]
2. This type definition excludes null as a valid value
3. The code converts null to the string "null" to satisfy the type
4. When JSON.stringify() is called later, "null" becomes "\"null\"" in the output

The root issue is the QueryResult type definition:

// packages/wrangler/src/d1/execute.ts:32-40
export type QueryResult = {
  results: Record<string, string | number | boolean>[];  // <-- Missing null
  success: boolean;
  meta?: {
    duration?: number;
  };
  query?: string;
};

Proposed Solution

Option 1: Minimal Fix (Recommended)

Update the type to include null and preserve null values:

// packages/wrangler/src/d1/execute.ts:32-40
export type QueryResult = {
  results: Record<string, string | number | boolean | null>[];  // Add null
  success: boolean;
  meta?: {
    duration?: number;
  };
  query?: string;
};

Then update the value transformation to preserve null:

// packages/wrangler/src/d1/execute.ts:250-268
const allResults = results.map<QueryResult>((result) => ({
  results: (result.results ?? []).map((row) =>
    Object.fromEntries(
      Object.entries(row).map(([key, value]) => {
        if (Array.isArray(value)) {
          value = `[${value.join(", ")}]`;
        }
        // Remove the null -> "null" conversion
        // null values are now preserved for JSON output
        return [key, value];
      })
    )
  ),
  success: result.success,
  meta: { duration: result.meta?.duration },
}));

Option 2: Conditional Conversion Based on Output Format

If the string "null" is intentional for interactive (non-JSON) table display, we could conditionally convert:

// In executeLocally function, pass json flag:
const allResults = formatResults(results, json);

// Helper function:
function formatResults(results: D1Result[], preserveNull: boolean): QueryResult[] {
  return results.map<QueryResult>((result) => ({
    results: (result.results ?? []).map((row) =>
      Object.fromEntries(
        Object.entries(row).map(([key, value]) => {
          if (Array.isArray(value)) {
            value = `[${value.join(", ")}]`;
          }
          if (value === null && !preserveNull) {
            value = "null";  // Only convert for display purposes
          }
          return [key, value];
        })
      )
    ),
    success: result.success,
    meta: { duration: result.meta?.duration },
  }));
}

Interactive Display Consideration

Note that the interactive table display also converts values to strings:

// packages/wrangler/src/d1/execute.ts:143-148
logger.table(
  results.map((r) =>
    Object.fromEntries(
      Object.entries(r).map(([k, v]) => [k, String(v)])  // String(null) = "null"
    )
  )
);

This is acceptable for display purposes since tables need string values. The JSON output path is the only one that should preserve actual null values.

Implementation Difficulty

Easy

Justification:

• Single file modification
• Clear, isolated bug with straightforward fix
• Type change + removing 3 lines of code
• No architectural changes required
• Existing test infrastructure can be used

Files to Modify

1. packages/wrangler/src/d1/execute.ts
   • Line 34: Update QueryResult type to include null
   • Lines 260-262: Remove the if (value === null) conversion block

Testing Recommendations

1. Unit Test: Add a test case for executeLocally that verifies null values are preserved in the results array:

   it("should preserve null values in JSON output", async () => {
     // Setup mock D1 database with null values
     // Execute query with --json flag
     // Assert that result contains actual null, not "null" string
   });
   

2. Integration Test: Add an e2e test that:

   • Creates a D1 table with nullable columns
   • Inserts rows with NULL values
   • Runs wrangler d1 execute --json --local
   • Parses the JSON output and verifies null values are proper JSON nulls

3. Manual Verification:

   # Create test database
   wrangler d1 create test-db
   wrangler d1 execute test-db --local --command="CREATE TABLE test (id INTEGER, nullable_col TEXT)"
   wrangler d1 execute test-db --local --command="INSERT INTO test VALUES (1, NULL)"
   
   # Verify JSON output
   wrangler d1 execute test-db --local --json --command="SELECT * FROM test"
   # Should output: [{"results":[{"id":1,"nullable_col":null}],...}]
   # NOT: [{"results":[{"id":1,"nullable_col":"null"}],...}]
   

4. Backwards Compatibility: Ensure that existing scripts parsing the (incorrect) "null" string are noted in changelog/migration guide.

Suggested Comment

Hi! We've reviewed this issue and confirmed the bug still exists in the current codebase (wrangler 4.60.0).

Root cause: In packages/wrangler/src/d1/execute.ts, the executeLocally function explicitly converts null values to the string "null" at lines 260-262. This was likely done to satisfy the QueryResult type which doesn't include null in its union type.

Fix: Update the QueryResult type to include null and remove the conversion logic. This is a straightforward fix affecting a single file.

We'd be happy to submit a PR to fix this if that would be helpful.