workerd limitation: bigint / int64 > 2^53 not supported across DO SqlStorage (upstream workerd#4195)

[grrowl]

Summary

Putting a JavaScript bigint into row data — or storing an INTEGER value greater than 2^53 — does not round-trip through a SyncDurableObject. This is a workerd platform limitation, not a bug in tanstack-do-db-collection, and there is no near-term plan to fix it upstream, so we are documenting it as a known limitation (wontfix) rather than working around it in the library.

Upstream tracking issue: cloudflare/workerd#4195 — "D1 Javascript API: Support BigInt" (OPEN; Cloudflare: "a known limitation for both D1 and SQLite Durable Objects … I am not aware of any immediate plan"). It explicitly covers SQLite-mode Durable Objects.

Traced end-to-end in the real workerd test pool (workerd 1.20260310.1, DO SQLite mode).

TL;DR

• number values ≤ 2^53 are completely unaffected — including Date.now() millisecond timestamps (~1.7×10¹², far below 2^53 ≈ 9.0×10¹⁵), sizes, counts, and ordinary IDs. The overwhelming common case has no problem.
• The only trigger is an actual JavaScript bigint in row data, or an INTEGER value > 2^53.
• Both failure surfaces are in the workerd JS ↔ SQLite bridge — not in this library's logic and not in @tanstack/db.

Environment

• tanstack-do-db-collection 0.3.x / 0.4.0-dev.0 (all affected; not a regression — it's the underlying platform)
• Runtime: workerd (wrangler dev / @cloudflare/vitest-pool-workers), Durable Object + ctx.storage.sql
• Wire codec: default binary (useBigInt64: true)

---

What is actually safe (verified)

• This library's code is bigint-safe. The wire codec uses MessagePack useBigInt64, so it faithfully carries a bigint in both directions. No library code coerces or Math.*-es row values.
• @tanstack/db is bigint-safe. Its order-by comparator (ascComparator) and its gt/gte/lt/lte predicate evaluators all use bare < / > / >= / <=, which JS permits between bigint and number without throwing. (A bare Math.max(bigint, …) does throw Cannot convert a BigInt value to a number, but that is not a code path the library or @tanstack/db actually executes — the client side does not break on bigint.)

What actually breaks — by facet

1. Write path — binding a bigint is rejected by workerd (hard).
SqlStorage's value type is ArrayBuffer | string | number | null — there is no bigint. Binding any bigint throws, regardless of magnitude:

sql.exec("INSERT INTO t(n) VALUES (?)", 42n)
//                                       ^^^  TypeError: Cannot convert a BigInt value to a number

So if a client optimistic mutation puts a bigint into a row, the mut frame carries it to the DO, the author's execute handler binds it, and workerd throws. The client receives a generic rejected / "mutation failed" (EXECUTE_FAILED) — because our error sanitization (ADR-0012) deliberately strips internal detail. The raw Cannot convert a BigInt value to a number only appears in the DO / wrangler dev server log. (Library-owned note: the sanitization is correct for untrusted detail, but it does make this particular failure harder to self-diagnose.)

2. Read path — INTEGER columns return as a lossy JS number (silent above 2^53).
SQLite stores int64 exactly — that is not in question. But workerd's read boundary coerces an INTEGER column to a JS number, which silently loses precision above 2^53:

INSERT … VALUES (9007199254740993)         -- stored exactly (CAST(n AS TEXT) → "9007199254740993")
SELECT n …                                  -- returns the number 9007199254740992  ← lossy, no error

This affects both the object-row and .raw() iterators. It is silent — no throw — so a value > 2^53 written server-side (or via SQL literal) comes back subtly wrong, and (if the client wrote it as a bigint optimistically) the confirmed number won't === the optimistic bigint, causing reconciliation churn.

Cloudflare documents both halves:

• DO SQLite Storage API — "Any numeric value in a column is affected by JavaScript's … precision for numbers. If you store a very large number (in int64), then retrieve the same value, the returned value may be less precise." — https://developers.cloudflare.com/durable-objects/api/sqlite-storage-api/
• D1 Worker API — "D1 supports 64-bit signed INTEGER values internally, however BigInts are not currently supported in the API yet." — https://developers.cloudflare.com/d1/worker-api/

---

What this means for users

Your usage	Effect
Integers ≤ 2^53 typed as JS number (timestamps, sizes, counts, most IDs)	No problem. Fully supported, lossless.
A JS bigint anywhere in row data (cols)	Insert/update is rejected ("mutation failed"); root cause only in the server log. Caused by workerd's bind boundary.
INTEGER column holding a value > 2^53 (written server-side or via literal)	Silent precision loss on read (value comes back rounded). Caused by workerd's read boundary.

Workarounds (today, no library change)

1. Use number, not bigint, for any value ≤ 2^53. This covers virtually all real columns — millisecond timestamps, byte sizes, counts, and string/UUID keys. Recommended; zero issues.
2. For genuinely > 2^53 values (e.g. Snowflake IDs): store them as TEXT, not INTEGER. A string round-trips losslessly through both boundaries; convert to/from BigInt in your application code. This is also Cloudflare's recommended workaround on #4195. (The collection pk is already required to be TEXT-affinity, so large string IDs as the key are unaffected.)
3. If you must keep an INTEGER column and write it server-side, bind the decimal string (sql.exec(…, String(value))) — SQLite's numeric affinity stores it as an exact int64. ⚠️ But reads are still lossy above 2^53 until workerd#4195 lands, so option 2 (TEXT) is strictly better for large values.

---

If we ever choose to fix it in-library (design sketch for pickup)

Both gaps have proven, lossless workarounds; a future fix would own both boundaries:

• Write: hand author execute/authorize a thin sql wrapper that converts bigint bind args → decimal strings before calling ctx.storage.sql.exec (the library already constructs that handler context). A bigint→string bind is correct for INTEGER and TEXT affinity.
• Read: build a per-collection column-affinity map at registerSync (we already pragma_table_info-introspect there) and emit CAST("intCol" AS TEXT) for INTEGER columns in the two SELECT * read sites (hydrateRows, compileSubsetQuery), reconstructing in JS as number-when-safe (≤2^53), bigint only when it doesn't fit (keeps every existing consumer's small-int columns as number — non-breaking; matches the #4195 community design).
• Would warrant a new ADR (a wire/read/write/registry-spanning change). Gated on real demand — and ideally on workerd#4195, which would make the read side unnecessary.

Minimal reproduction (runs in @cloudflare/vitest-pool-workers)

import { env, runInDurableObject } from "cloudflare:test"
const stub = env.TEST_DO.get(env.TEST_DO.idFromName("repro"))
await runInDurableObject(stub, (_i, s) => {
  const sql = s.storage.sql
  sql.exec(`CREATE TABLE t (id TEXT PRIMARY KEY, n INTEGER)`)

  // (1) BIND a bigint → throws (any magnitude, even 42n):
  try { sql.exec(`INSERT INTO t(id,n) VALUES ('a', ?)`, 42n) }
  catch (e) { console.log("bind:", String(e)) } // TypeError: Cannot convert a BigInt value to a number

  // (2) READ an int64 > 2^53 → silently lossy as a JS number:
  sql.exec(`INSERT INTO t(id,n) VALUES ('b', 9007199254740993)`)
  console.log("number:", String([...sql.exec<{n:number}>(`SELECT n FROM t WHERE id='b'`)][0]!.n))         // 9007199254740992  (lossy)
  console.log("text:",   [...sql.exec<{s:string}>(`SELECT CAST(n AS TEXT) s FROM t WHERE id='b'`)][0]!.s)  // "9007199254740993" (exact)
})

---

Status: wontfix — known platform limitation, tracked upstream at cloudflare/workerd#4195. Kept open as the library-side tracker. No code change planned unless demand or a workerd fix changes the calculus.