@powersync/node: execute() returns rowsAffected=0 for successful UPDATE statements

[jpawelczyk]

Description

When using @powersync/node with better-sqlite3, the execute() method returns rowsAffected: 0 even when an UPDATE statement successfully modifies rows.

Environment

• @powersync/node: latest (installed Feb 2026)
• better-sqlite3: 12.6.2
• Node.js: v22.x
• OS: macOS (arm64)

Reproduction

import { PowerSyncDatabase } from "@powersync/node";

// After connecting and syncing...

// 1. Create a task
await db.execute(
  "INSERT INTO tasks (id, title, deleted_at) VALUES (?, ?, NULL)",
  [taskId, "Test task"]
);

// 2. Verify task exists
const existing = await db.getOptional(
  "SELECT deleted_at FROM tasks WHERE id = ?",
  [taskId]
);
console.log(existing); // { deleted_at: null } ✓

// 3. Update the task
const result = await db.execute(
  "UPDATE tasks SET deleted_at = ? WHERE id = ? AND deleted_at IS NULL",
  [new Date().toISOString(), taskId]
);
console.log(result);
// Actual:   { rowsAffected: 0, insertId: 2147483647 }
// Expected: { rowsAffected: 1, insertId: ... }

// 4. But the update DID work
const check = await db.getOptional(
  "SELECT deleted_at FROM tasks WHERE id = ?",
  [taskId]
);
console.log(check); // { deleted_at: "2026-02-24T..." } ✓

Observed Behavior

• rowsAffected is 0 even though the UPDATE succeeded
• insertId is 2147483647 (INT_MAX / 0x7FFFFFFF) — suggests uninitialized or sentinel value
• The row IS actually updated (verified by subsequent SELECT)

Expected Behavior

• rowsAffected should be 1 (or the actual count of affected rows)
• insertId should be a valid value or undefined for UPDATE statements

Possible Cause

The Node SDK uses a worker thread for SQLite operations. The result metadata (rowsAffected) may not be correctly marshalled back from the worker thread to the main thread.

Workaround

We implemented a workaround by verifying the operation with a follow-up SELECT instead of trusting rowsAffected:

await db.execute("UPDATE tasks SET deleted_at = ? WHERE id = ?", [now, id]);

// Verify instead of trusting rowsAffected
const check = await db.getOptional("SELECT deleted_at FROM tasks WHERE id = ?", [id]);
return check?.deleted_at !== null;

Impact

Any code relying on rowsAffected to confirm UPDATE/DELETE success will incorrectly think the operation failed.

[rkistner]

I believe this is due to the JSON table structure, where the query actually writes to a view, with triggers writing to the underlying table.

Two options that could work here:

1. Add a RETURNING clause, for example const results = await db.execute("UPDATE tasks SET deleted_at = ? WHERE id = ? RETURNING id", [now, id]). This will return all updated rows, effectively combining your two queries into one.
2. Use raw tables, which should not have this same limitation.

[jpawelczyk]

Thanks for the quick response! The RETURNING clause works perfectly — much cleaner than our verify-after workaround.

const result = await db.execute(
  'UPDATE tasks SET deleted_at = ? WHERE id = ? AND deleted_at IS NULL RETURNING id',
  [now, id],
);
return (result.rows?.length ?? 0) > 0;

One suggestion: it might be worth documenting this behavior in the SDK docs, since rowsAffected is part of the QueryResult type and users might reasonably expect it to work. A note in the Node.js SDK reference about using RETURNING for mutation confirmation would save others some debugging time.

Thanks again!