Value encoding

SQLite has five storage classes; JSON has three useful types. The mapping is therefore lossy unless it is designed, and two cases are where it goes wrong if left to JSON.stringify.

The straightforward part

SQLite	JSON	Notes
NULL	null	Distinct from "", in both directions.
INTEGER	number	While it fits exactly in a double — see below.
REAL	number
TEXT	string	UTF-8.
BLOB	{"$blob": "…"}	Base64. JSON has no byte type.

As a parameter, a JSON true becomes 1 and false becomes 0, the way every SQLite driver does — SQLite has no boolean type. Reading it back gives you 1 or 0, not true. Non-finite numbers (NaN, Infinity) are rejected rather than silently coerced, because JSON cannot represent them and the alternatives are all wrong.

64-bit integers

SQLite integers are 64-bit. JSON numbers are IEEE doubles, exact only to 2^53. An id past that point silently changes value in transit — the quietest possible data-corruption bug.

So integers outside ±2^53 are wrapped, in both directions:

{ "$int": "4611686018427387904" }

curl -sS "$SQLITED_BASE/v1/databases/$DB/query" \
  -H "Authorization: Bearer $SQLITED_TOKEN" -H 'content-type: application/json' \
  -d '{"sql":"INSERT INTO big (v) VALUES (?)","params":[{"$int":"4611686018427387904"}]}'

Reading it back gives you the identical wrapper. Integers inside the safe range stay plain numbers, so the common case costs nothing:

{ "columns": ["small", "big"], "rows": [[42, { "$int": "4611686018427387904" }]] }

The digits must match ^-?\d+$ and fit in a signed 64-bit integer; anything else is a 400. Note that a {"$int": …} may appear in lastInsertRowid too, if a table’s rowids have grown that large.

Blobs

Bytes are base64 in a wrapper:

{ "$blob": "SGVsbG8=" }

Invalid base64 is rejected outright rather than stored truncated, which is the difference between an error you see today and a corrupt row you find in a year.

Remember the 8 MiB request limit applies to the whole encoded body, and base64 costs 33% overhead — so the practical ceiling for a single blob is around 6 MiB. Large objects belong in object storage directly with a key in the database, not in the database.

The symmetry is the point

What a query returns is exactly what a parameter accepts. You can read a row and write it back without a translation layer, and there is no parallel types array to keep in step with the data. Only the values that need a wrapper pay for one.

That is why the wrappers are shaped as objects with a $-prefixed key rather than, say, a tagged tuple: a JSON object is unambiguously not a SQLite value, so no real value can be mistaken for a wrapper.

The one acknowledged gap

A REAL that happens to be integral is indistinguishable from an INTEGER. SELECT 1.0 returns 1, identical to SELECT 1:

{ "columns": ["r", "integral"], "rows": [[1.5, 1]] }

Reporting it would mean a wrapper on every float, and SQLite’s own type affinity makes the distinction slippery in the first place — a column declared REAL will store an integer in it. If the difference matters to your application, store the value as TEXT or scale it to an integer; do not rely on the JSON type telling you which storage class a value came from.

Two smaller consequences of the same JSON boundary, worth knowing before they surprise you:

• A JSON number that is not an integer stays a REAL. params: [1.0] binds a REAL, not an INTEGER, so it will not match an INTEGER value in a strict comparison.
• Very large REALs lose precision the way doubles do, and there is no $real wrapper. If you are storing money, store integer minor units — advice that predates this API by about fifty years.