Cloudflare D1 exports round every integer past 2^53

The obvious way to get data out of Cloudflare D1 is wrangler d1 export. Cloudflare generates a SQL dump, you load it into SQLite, and you are done. It is the vendor's own tool, so it should be the highest-fidelity path out.

We tested it before shipping a D1 importer, and it is not. Any integer above 2^53 comes out of the dump as a different number, and then changes storage class on the way back in.

Cloudflare does document the cause. It is one sentence on their import and export page: "Any numeric value in a column is affected by JavaScript's 52-bit precision for numbers." That sentence reads like a footnote about the JSON API. It is not. It is also about the file you were planning to migrate with.

Here is what it actually costs, measured end to end, and the rule we took away from it.

The measurement

Store the largest signed 64-bit integer, a plain 1.0, and nothing exotic:

CREATE TABLE t (id INTEGER PRIMARY KEY, big INTEGER, r REAL);
INSERT INTO t VALUES (1, 9223372036854775807, 1.0);

Read it back with a cast to text, so the value is not routed through a number on the way out:

SELECT CAST(big AS TEXT), typeof(big) FROM t;

9223372036854775807|integer

Exact, as expected. The database is fine. Now export and open the dump:

INSERT INTO "t" ("id","big","r") VALUES(1,9223372036854776000,1);

9223372036854775807 became 9223372036854776000. Off by 193.

Load that dump into SQLite and it gets worse, because the rounded literal no longer fits in a signed 64-bit integer:

sqlite> SELECT typeof(big), CAST(big AS TEXT) FROM t;
real|9.22337203685478e+18

The column is still declared INTEGER. The value in it is now a float. Nothing errored, the table is there, and the row count matches.

The REAL column has a quieter version of the same problem. 1.0 exports as bare 1. Because that column is declared REAL, SQLite's type affinity converts it back to 1.0 on import and you get away with it. In a column with no declared type, which SQLite allows and plenty of schemas use, the same 1 stays an integer and the storage class has silently changed under you.

Why a dump does this

A JavaScript number is a double. Doubles carry 53 bits of integer precision, so every integer up to 2^53 (9007199254740992) is exact and past that there are gaps. The dump generator formats each value as a JavaScript number on its way into the text of the file, which puts every one of your values through that funnel.

The database never held a wrong value. The renderer produced one.

Who this hits: snowflake ids, Discord and Twitter ids, nanosecond timestamps, bitfields, hashes kept as integers, and any 64-bit key handed to you by another system. All of them look like ordinary integers and all of them live above 2^53.

What makes it nasty is that nothing looks wrong afterwards. No error, no warning, row counts match on both sides, and SELECT * FROM users LIMIT 10 in a dashboard shows you numbers that look about right.

The inference that gets you

The reasoning that leads people here is sound and wrong: "the JSON query API returns numbers, and JSON numbers are doubles, so the API is lossy. Use the dump instead, it is a file of SQL text."

The dump is a file of SQL text generated by the same JavaScript. Text output does not mean the value avoided a double, it means the double got printed.

The useful version of the rule is not about which endpoint you pick. It is about where the value gets turned into text:

• A value that crosses a JSON boundary as a number is suspect, whatever produced it.
• A value that crosses as a string is fine, as long as the string was produced inside the database.

The other half: CAST is lossy on a REAL

Once you accept that, the fix looks obvious. Do not ask for numbers, ask for text: SELECT CAST(col AS TEXT). That is correct for integers, and it quietly loses data on floats.

sqlite> SELECT CAST(3.141592653589793 AS TEXT);
3.14159265358979

Fifteen significant digits, and the original had sixteen. That string does not parse back to the double you started with. Almost every migration tool that reaches for CAST as the safe option is making this trade without noticing, because the value still looks like a number and it is only wrong in the last place or two.

SQLite's printf has a format that does round-trip:

sqlite> SELECT printf('%!.20g', 3.141592653589793);
3.141592653589793116

Twenty significant digits is more than the seventeen a double needs, so it parses back bit for bit.

The encoding that survives

So the whole recipe, done inside SQLite before anything crosses a wire:

• typeof(x) alongside every value, so the storage class is carried explicitly instead of inferred at the other end
• integers as exact decimal text via CAST(x AS TEXT)
• reals through printf('%!.20g', x)
• text and blobs as hex(x)

Every value then arrives as ASCII text or NULL, and nothing numeric is ever handed to a JavaScript number.

Two edge cases we hit doing this for real, both worth knowing before you write your own:

• Infinity has no JSON representation. It has to go back into the target as a SQL literal (9e999) rather than as a value the transport carries.
• A TEXT column can hold bytes that are not valid UTF-8. SQLite lets you, and any layer that decodes the payload as UTF-8 will mangle them. They have to be bound as a blob and cast back to text on insert.

The principle underneath all of it: do type-preserving encoding inside the database engine, never in the transport layer. Do it in the engine and fidelity is a property of the SQL you wrote. Do it in the transport and fidelity depends on what JSON, a wire proxy, or a driver's type mapping happens to do that day, which is not something you can test once and rely on.

Checking data you already moved

If you migrated a D1 database with an export at some point, this is cheap to check. Against the source, per table with a suspect column:

SELECT count(*) FROM t
WHERE typeof(big) = 'integer' AND abs(big) > 9007199254740992;

Any non-zero count is rows that could not have survived the dump intact.

On the copy, the fingerprint is the storage class rather than the value:

SELECT typeof(big), count(*) FROM t GROUP BY 1;

real in a column the source reported as integer is the flip described above. Comparing the values themselves is harder than it looks, because reading them back through most clients puts them through a double a second time. Compare CAST(big AS TEXT) on both sides, not big.

What we ended up building

The Layerbase D1 importer never calls the export endpoint. It reads your rows over D1's query API with the encoding above applied inside SQLite, which is what makes an int64 id arrive exactly as stored rather than nearly as stored.

Routing around the dump turned out to fix two other things for free. Cloudflare's export blocks other requests to the database while it runs, so exporting a production D1 is downtime you have to schedule; reads do not block, so the copy runs while your Worker keeps serving. And export refuses outright on a database containing a virtual table, which quietly means "no full-text search", while a read-based copy recreates the FTS5 table and flags it for a rebuild.

The honest cost of reading a live database instead of dumping a frozen one: the copy is not a single point-in-time snapshot. We count rows before and after and report, per table, whether anything was written mid-copy. Migrate from a quiet database, or pause writes, if you need an exact instant.

If you are moving a D1 database, start at layerbase.com/migrate/cloudflare-d1. The step-by-step version, including what changes in your application code, is in Migrating from Cloudflare D1 to Layerbase.