cloudflare / cloudflare/workers-sdk

D1 export silently corrupts INTEGER values outside JavaScript's safe range (and emits invalid SQL for ±Infinity)

Closed
#15,378 1 comment 0 reactions 0 assignees View on GitHub
product:d1
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.