cloudflare / cloudflare/workers-sdk
D1 export silently corrupts INTEGER values outside JavaScript's safe range (and emits invalid SQL for ±Infinity)
- Dominant language
- TypeScript
- Stars
- 4.5k
- Forks
- 1.5k
- Avg merge
- 3d 8h
- Merged PRs (30d)
- 186
Description
### What versions & operating system are you using?
- `wrangler` **4.126.0** (also reproduced on 4.80.0 — the generated SQL is byte-identical)
- Node **v25.6.1**, macOS **26.2** (arm64)
- Remote (not `--local`) D1, paid plan
- `sqlite3` 3.51.0 used for the comparison below
Reproduced against **both** `wrangler d1 export --remote` and the REST endpoint
`POST /client/v4/accounts/{account_id}/d1/database/{database_id}/export`. The two produce
byte-identical SQL, so the SQL text appears to be generated server-side rather than by
Wrangler — but `wrangler d1 export` is the documented user-facing path, so filing here.
### Please provide a link to a minimal reproduction
No repo needed — the whole reproduction is four rows and two commands. Self-contained
script inlined below (it creates and deletes its own throwaway databases).
repro.sh
```sh
#!/bin/sh
set -eu
SRC=d1-precision-repro-src
DST=d1-precision-repro-dst
wrangler d1 create "$SRC"
wrangler d1 create "$DST"
cat > seed.sql <<'SQL'
CREATE TABLE t (id INTEGER PRIMARY KEY, big INTEGER, r REAL);
INSERT INTO t (id, big, r) VALUES
(1, 9007199254740993, 1.0), -- 2^53 + 1
(2, 9223372036854775807, 1.0), -- int64 max
(3, -9223372036854775808, 1.0), -- int64 min
(4, 1, 9e999); -- +Infinity
SQL
wrangler d1 execute "$SRC" --remote --file=seed.sql -y
# all four are stored exactly, as legal SQLite values
wrangler d1 execute "$SRC" --remote -y --command \
"SELECT id, CAST(big AS TEXT) AS big, typeof(big) AS big_type, CAST(r AS TEXT) AS r, typeof(r) AS r_type FROM t ORDER BY id;"
wrangler d1 export "$SRC" --remote --output=out.sql
cat out.sql
# import D1's own export back into an empty D1 -> fails on Infinity
wrangler d1 execute "$DST" --remote --file=out.sql -y || true
# drop the Infinity row and import again to see the silent integer corruption
grep -v Infinity out.sql > out-no-inf.sql
wrangler d1 execute "$DST" --remote --file=out-no-inf.sql -y
wrangler d1 execute "$DST" --remote -y --command \
"SELECT id, CAST(big AS TEXT) AS big, typeof(big) AS big_type FROM t ORDER BY id;"
# for comparison, sqlite3 .dump keeps all four exactly
sqlite3 local.db < seed.sql && sqlite3 local.db .dump
wrangler d1 delete "$SRC" -y
wrangler d1 delete "$DST" -y
```
### Describe the Bug
`d1 export` round-trips column values through JavaScript numbers when generating SQL. Two
consequences:
1. **Silent data corruption.** SQLite stores `INTEGER` as a signed 64-bit value, but any
magnitude above `2^53 - 1` is not representable as an IEEE-754 double. Such values are
exported as a *different* number, with no error and no warning. An export/import round
trip therefore returns different data than it was given.
2. **Invalid generated SQL.** `±Infinity` (a legal SQLite `REAL`) is emitted as a bare
identifier `Infinity`, so importing D1's own export fails with
`no such column: Infinity`.
(1) is the serious one: `d1 export` is documented as the backup/portability path, and a
backup that silently differs from the source is worse than a backup that fails.
#### Steps to reproduce
Create a database with values that are legal in SQLite but not representable as doubles:
```sql
CREATE TABLE t (id INTEGER PRIMARY KEY, big INTEGER, r REAL);
INSERT INTO t (id, big, r) VALUES
(1, 9007199254740993, 1.0), -- 2^53 + 1
(2, 9223372036854775807, 1.0), -- int64 max
(3, -9223372036854775808, 1.0), -- int64 min
(4, 1, 9e999); -- +Infinity
```
All four are confirmed stored exactly before export —
`SELECT id, CAST(big AS TEXT), typeof(big), CAST(r AS TEXT), typeof(r) FROM t` returns
`9007199254740993`/`integer`, `9223372036854775807`/`integer`,
`-9223372036854775808`/`integer`, and `Inf`/`real`.
Then export, and import the result into a second, empty database:
```sh
wrangler d1 export --remote --output=out.sql
wrangler d1 execute --remote --file=out.sql
```
#### Actual behaviour
The generated `out.sql`:
```sql
PRAGMA defer_foreign_keys=TRUE;
CREATE TABLE t (id INTEGER PRIMARY KEY, big INTEGER, r REAL);
INSERT INTO "t" ("id","big","r") VALUES(1,9007199254740992,1);
INSERT INTO "t" ("id","big","r") VALUES(2,9223372036854776000,1);
INSERT INTO "t" ("id","big","r") VALUES(3,-9223372036854776000,1);
INSERT INTO "t" ("id","big","r") VALUES(4,1,Infinity);
```
| Stored value (`CAST(big AS TEXT)` / `typeof`) | Exported literal | Restored value / `typeof` |
|---|---|---|
| `9007199254740993` / `integer` | `9007199254740992` | `9007199254740992` / `integer` |
| `9223372036854775807` / `integer` | `9223372036854776000` | `9.2233720368547758e+18` / `real` |
| `-9223372036854775808` / `integer` | `-9223372036854776000` | `-9.2233720368547758e+18` / `real` |
| `Inf` / `real` | `Infinity` (bare identifier) | import fails — see logs |
- `9007199254740993` is exported as `9007199254740992` — off by one, silently rounded to
the nearest double. No error, no warning, and the import succeeds.
- The int64 extrema are rounded **and** change storage class: they leave as `integer` and
come back as `real`.
- Row 4's `+Infinity` is emitted as the bare identifier `Infinity`, so importing D1's own
export fails. The failure is atomic, so one infinity anywhere in a database makes the
entire export unrestorable.
#### Expected behaviour
- Every `INTEGER` in the int64 range round-trips exactly.
- Special `REAL` values are emitted as something SQLite accepts. `sqlite3 .dump` renders
the same rows as `9.0e+999`, which re-imports as infinity.
- Failing that, the export should refuse loudly rather than emit altered data.
For comparison, `sqlite3 .dump` on the identical four rows preserves every value exactly:
```sql
INSERT INTO t VALUES(1,9007199254740993,1.0);
INSERT INTO t VALUES(2,9223372036854775807,1.0);
INSERT INTO t VALUES(3,-9223372036854775808,1.0);
INSERT INTO t VALUES(4,1,9.0e+999);
```
#### Why this is a correctness issue rather than a nit
The [known limitations](https://developers.cloudflare.com/d1/best-practices/import-export-data/#known-limitations)
do note a JavaScript precision caveat, but the observable behaviour is silent corruption of
a documented backup path, which seems worth treating as a bug rather than a documented
constraint.
Values above `2^53` are ordinary in real applications: snowflake-style IDs, externally
issued 64-bit identifiers, financial amounts stored as integer minor units, and hash values
stored as `INTEGER`. Any of these are silently altered by an export/import round trip today.
Because the corruption is value-level rather than structural, row counts, schema comparison,
and import success all still pass — so ordinary verification does not catch it.
#### Suggested fix
Generate the SQL text from the stored value without an intermediate double — e.g. serialize
integers from their int64 representation, and render special reals the way SQLite's own
`.dump` does. A storage-class-preserving encoding (integer/real/text/blob tagged, blobs as
hex literals) round-trips exactly.
#### Related, and it would make the above moot
D1 is SQLite, and a database copy is fundamentally a *file* copy. There is currently no
D1-to-D1 clone: duplicating a database requires generating SQL, transferring it, and
replaying it — which is what introduces the precision problem above, and is far slower than
copying pages. Time Travel already restores a database to a previous point in time, which
implies page/snapshot-level machinery exists; it just can't target a *new* database.
A `clone` operation (or `--from-database` on create) producing a byte-exact copy into a
fresh database would be faster than export/import, exact by construction, and would remove
this entire class of correctness bug from every tool that has to round-trip through SQL.
Happy to file that separately as a feature request if preferred.
### Please provide any relevant error logs
Importing D1's own unmodified export:
```text
🌀 Executing on remote database :
├ Checking if file needs uploading
│
├ 🌀 Uploading ..sql
│ 🌀 Uploading complete.
│
✘ [ERROR] no such column: Infinity at offset 45: SQLITE_ERROR
```
The integer corruption produces **no** log output at all — export and import both report
success.
Contributor guide
Research direction
Start by tracing the `wrangler d1 export --remote` entry point and the REST export endpoint, then identify where stored values become SQL literals. Reproduce the four-row case from the issue and verify that int64 values retain their exact integer representation, special reals produce valid importable SQL, and export/import round-trips successfully.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sqlite, typescript
- Domain
- api, cli, database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 52/100