Issue Review: cloudflare/workers-sdk#7507

Summary

D1 SQL export generates INSERT statements without column names, causing import failures when table column order differs from definition order.

Findings

• Created: 2024-12-10
• Updated: 2025-10-30
• Version: 3.94.0 (wrangler) → 4.60.0 (current)
• Component: D1 / miniflare (packages/miniflare/src/workers/d1/dumpSql.ts)
• Labels: bug, d1
• Comments: 0

Key Evidence

1. Code Analysis Confirms Bug Still Exists: The current dumpSql.ts at line ~90 generates:

   yield `INSERT INTO ${escapeId(table)} VALUES(${formattedCells.join(",")});`;
   

   This produces INSERT INTO "table" VALUES(...) without column names.

2. Related PR #9866 Did NOT Fix This Issue: The merged PR "fix: escape column names and handle mismatched data types in D1 SQL dump" focuses on:

   • Properly escaping column and table names
   • Handling SQLite's loose typing
   • It does NOT add column names to INSERT statements

3. Issue Not Referenced in Any Changelog: Searched both wrangler and miniflare changelogs - no mention of #7507.

4. Issue Clearly Documented: The reporter provided:

   • Clear reproduction steps
   • Root cause analysis (pointed to exact line in code)
   • Expected vs actual behavior
   • Valid use case (iterative development with schema changes)

Recommendation

Status: KEEP OPEN

Reasoning: The bug is verified to still exist in the current codebase. The INSERT statement generation at line ~90 of dumpSql.ts explicitly omits column names, which causes import failures when column order differs between export and import schemas. This is a valid bug affecting developers doing iterative D1 development.

Action: Implement the fix by including column names in INSERT statements.

---

Root Cause Analysis

The bug is in packages/miniflare/src/workers/d1/dumpSql.ts at approximately line 90.

Current Code (problematic)

yield `INSERT INTO ${escapeId(table)} VALUES(${formattedCells.join(",")});`;

This generates SQL like:

INSERT INTO "users" VALUES(1,'John','john@example.com');

Problem

When a table is later recreated with columns in a different order, or with additional columns that have defaults, the positional VALUES syntax fails because SQLite interprets values by position, not by column name.

Expected Output

INSERT INTO "users" ("id","name","email") VALUES(1,'John','john@example.com');

Proposed Solution

The fix is straightforward. The column information is already available in the columns array, which is fetched via PRAGMA table_info. We just need to include the column names in the INSERT statement.

Code Change

File: packages/miniflare/src/workers/d1/dumpSql.ts

Current code (~line 90):

yield `INSERT INTO ${escapeId(table)} VALUES(${formattedCells.join(",")});`;

Proposed fix:

const columnNames = columns.map((c) => escapeId(c.name)).join(",");
yield `INSERT INTO ${escapeId(table)} (${columnNames}) VALUES(${formattedCells.join(",")});`;

Full Context (lines ~68-93)

const columns_cursor = db.exec(`PRAGMA table_info=${escapeId(table)}`);

const columns = Array.from(columns_cursor) as {
  cid: string;
  name: string;
  type: string;
  notnull: number;
  dflt_val: string | null;
  pk: number;
}[];
if (stats) {
  stats.rows_read += columns_cursor.rowsRead;
  stats.rows_written += columns_cursor.rowsWritten;
}

const select = `SELECT ${columns.map((c) => escapeId(c.name)).join(", ")} FROM ${escapeId(table)};`;
const rows_cursor = db.exec(select);
const columnNames = columns.map((c) => escapeId(c.name)).join(",");  // NEW LINE
for (const dataRow of rows_cursor.raw()) {
  const formattedCells = dataRow.map((cell: unknown, i: number) => {
    // ... cell formatting logic unchanged ...
  });

  yield `INSERT INTO ${escapeId(table)} (${columnNames}) VALUES(${formattedCells.join(",")});`;  // MODIFIED
}

Implementation Difficulty: Easy

Justification

• Single line change: Only one line needs modification, plus one line addition for clarity
• No new dependencies: Uses existing columns array and escapeId function
• No architectural changes: Same code flow, just different string output
• Low risk: Adding column names to INSERT is always valid SQL and more portable
• Existing test infrastructure: PR #9866 added tests for dumpSql that can be extended

Files to Modify

1. packages/miniflare/src/workers/d1/dumpSql.ts - Main fix (as described above)

2. NOTE: The file header states:

   "NOTE: this file is duplicated between miniflare and d1-workers, and should be kept in sync by hand."

   The D1 team should be tagged to sync changes to d1-workers.

3. Test file - Based on PR #9866, tests likely exist in packages/miniflare/src/workers/d1/ that should be updated to verify column names are included.

Testing Recommendations

Unit Tests

Add/modify tests in the dumpSql test suite to verify:

1. INSERT statements include column names
2. Column names are properly escaped (already covered by #9866)
3. Column order in INSERT matches column order in VALUES

Integration Test

# Create table with specific column order
npx wrangler d1 execute test-db --command "CREATE TABLE test (a TEXT, b TEXT, c TEXT);" --local
npx wrangler d1 execute test-db --command "INSERT INTO test VALUES ('1', '2', '3');" --local

# Export
npx wrangler d1 export test-db --table=test --output ./test.sql --no-schema --local

# Verify export contains column names
cat test.sql | grep -E 'INSERT INTO.*\(.*\).*VALUES'

# Drop and recreate with different column order
npx wrangler d1 execute test-db --command "DROP TABLE test;" --local
npx wrangler d1 execute test-db --command "CREATE TABLE test (c TEXT, b TEXT, a TEXT);" --local

# Import should succeed
npx wrangler d1 execute test-db --file=./test.sql --local

# Verify data is in correct columns
npx wrangler d1 execute test-db --command "SELECT a, b, c FROM test;" --local
# Should return: 1, 2, 3 (not 3, 2, 1)

Edge Cases to Test

• Tables with quoted/special column names
• Tables with many columns
• Empty tables (should produce no INSERT statements)
• Tables with NULL values
• Tables with BLOB data