Issue Review: cloudflare/workers-sdk#6088

Summary

wrangler d1 execute --file --remote returns only metadata instead of query results since version 3.57.0 due to the switch to a new import API.

Findings

• Created: 2024-06-19
• Updated: 2025-10-30
• Version: 3.57+ (reported) -> 4.60.0 (current)
• Component: D1 / Wrangler
• Labels: bug, d1
• Comments: 0

Key Evidence

1. Root cause identified in PR #5696 (merged 2024-05-16): "D1: execute --file --remote now uses dedicated import API"

   • The PR description states: "This hits a new API endpoint 'import', that initially returns a signed R2 PUT url for uploading SQL, then returns polling info as the import completes."
   • The import API was designed for performance with large SQL files, not for returning query results.

2. Code analysis confirms the issue in packages/wrangler/src/d1/execute.ts:

   • When --file is used with --remote, the code uses d1ApiPost(..., 'import', ...) (lines ~300-340)
   • This returns only metadata: Total queries executed, Rows read, Rows written, Database size (MB)
   • When --command is used, it calls d1ApiPost(..., 'query', ...) which returns actual results
   • This is a fundamental limitation of the import API endpoint which doesn't return query results

3. No fix has been merged - searched PRs and changelogs for references to this issue; none found.

4. Reproduction steps are clear - the reporter provided a detailed minimal reproduction repository.

Recommendation

Status: KEEP OPEN

Reasoning: This is a confirmed regression in wrangler 3.57+ affecting users who expect query results from d1 execute --file --remote. The import API was chosen for performance benefits with large files but doesn't support returning query results. This is a design decision tradeoff, not a bug in the traditional sense, but the behavior change was not documented and breaks user expectations.

Action: This requires a design decision from the D1 team:

1. Document the behavior change as expected (results only returned for --command, not --file)
2. Add a threshold to use the old query API for small files where results are likely needed
3. Add a --return-results flag to force using the query API instead of import API

Root Cause Analysis

Location

packages/wrangler/src/d1/execute.ts - executeRemotely() function

Problem

In PR #5696, the --file --remote code path was changed to use the D1 import API (/import endpoint) instead of the query API (/query endpoint). The import API is designed for bulk data loading and:

• Uploads the SQL file to R2
• Processes it server-side asynchronously
• Returns only execution metadata (rows read/written, query count, database size)
• Does NOT return the actual query results

The previous behavior (wrangler 3.56 and earlier) used the query API which splits the SQL file into statements and executes them, returning the results of each query.

Code Reference

// packages/wrangler/src/d1/execute.ts:300-340

if (input.file) {
    // Uses import API - returns metadata only
    // ...
    return [
        {
            results: [
                {
                    "Total queries executed": num_queries,
                    "Rows read": meta.rows_read,
                    "Rows written": meta.rows_written,
                    "Database size (MB)": (meta.size_after / 1_000_000).toFixed(2),
                },
            ],
            success: true,
            finalBookmark: final_bookmark,
            meta,
        },
    ];
} else {
    // Uses query API - returns actual results
    const result = await d1ApiPost<QueryResult[]>(
        config, accountId, db, "query",
        { sql: input.command }
    );
    return result;
}

Proposed Solution

Option 1: Add --return-results flag (Recommended)

Add a flag that forces using the query API even for files, accepting the performance tradeoff.

// In args definition
"return-results": {
    type: "boolean",
    description: "Return query results (uses query API instead of import API, slower for large files)",
    default: false,
},

// In executeRemotely()
if (input.file && !returnResults) {
    // Use import API (current behavior)
} else if (input.file && returnResults) {
    // Use query API (old behavior)
    const sql = readFileSync(input.file);
    const result = await d1ApiPost<QueryResult[]>(config, accountId, db, "query", { sql });
    return result;
} else {
    // Use query API for commands
}

Option 2: Size-based threshold

Automatically use the query API for small files (e.g., < 1MB) where users likely expect results.

if (input.file) {
    const { size } = await fs.stat(input.file);
    const USE_QUERY_API_THRESHOLD = 1_000_000; // 1MB
    
    if (size < USE_QUERY_API_THRESHOLD) {
        // Use query API for small files
        const sql = readFileSync(input.file);
        return await d1ApiPost<QueryResult[]>(config, accountId, db, "query", { sql });
    } else {
        // Use import API for large files (current behavior)
    }
}

Option 3: Documentation-only fix

Document that --file --remote uses the import API and doesn't return results. Users who need results should use --command with the SQL content.

Implementation Difficulty

Medium

Justification

• The fix itself is straightforward (adding a flag or threshold check)
• However, there are design considerations:
  • Which approach best serves users?
  • Should there be a deprecation warning?
  • Testing both code paths
  • Ensuring backward compatibility
• Requires D1 team input on the preferred approach

Files to Modify

1. packages/wrangler/src/d1/execute.ts - Main logic changes
2. packages/wrangler/src/__tests__/d1/execute.test.ts - Add tests for new behavior
3. .changeset/*.md - Add changeset for the fix

Testing Recommendations

1. Unit tests:

   • Test --file --remote with small SQL file returns results when using query API
   • Test --file --remote with large SQL file uses import API
   • Test --return-results flag forces query API usage

2. Integration tests:

   • Create actual D1 database
   • Execute SELECT query via file
   • Verify results are returned (not just metadata)

3. Manual testing:

   • Follow the reporter's reproduction steps
   • Verify the fix resolves the issue
   • Test with both small and large SQL files

Suggested Comment

Thank you for the detailed bug report and reproduction steps!

We've confirmed this is a regression introduced in wrangler 3.57.0 (PR #5696). The change switched d1 execute --file --remote to use a new import API designed for bulk data loading. This API provides better performance for large SQL files but doesn't return query results - only execution metadata.

This was an intentional performance optimization but the behavior change wasn't documented, and we understand it breaks workflows that depend on seeing query results.

We're evaluating a few options:

1. Add a --return-results flag to opt into the older behavior
2. Use a size-based threshold to automatically choose the API
3. Document this as expected behavior

We'd appreciate feedback on which approach would work best for your use case. In the meantime, as a workaround, you can use --command with the SQL content directly if you need query results.