Turso / libSQL

Turso is hosted libSQL (a SQLite fork). We talk to it two ways: the hrana protocol (query/exec over HTTP) and the export HTTP endpoints (bulk DB download). Both hit the database's own hostname (https://<db>-<org>.turso.io), NOT api.turso.tech (that's the control-plane API for creating DBs / minting tokens). Same Authorization: Bearer <jwt> for everything.

bulk read: use the export endpoints, NOT hrana cursors

For pulling a whole table/DB, turso db export (and the libSQL sync bootstrap) use a fast binary path that is ~60× faster than streaming rows over hrana. Measured on a 5.9M-row / 3.2 GB DB:

• export endpoints: 234 s (13.6 MB/s, binary SQLite page stream)
• hrana v3 /v3/cursor NDJSON: ~4 hours (~200 KB/s, per-row JSON the server has to encode)

The cursor endpoint streams logical rows as JSON; the server pays a per-row encoding cost that dominates. The export endpoint streams raw SQLite pages. For bulk, always export.

the export wire format (what turso db export does)

Three GETs on the DB host, bearer auth (source: tursodatabase/turso-cli internal/turso/tursoServer.go):

1. GET /info → {"current_generation": <int>}
2. GET /export/{generation} → raw bytes of a standalone, consistent SQLite main DB file (a server checkpoint). Stream straight to disk.
3. GET /sync/{generation}/{lo}/{hi} → WAL frames (4120 B each = 24 B frame header + 4096 B page), batched 128 at a time. The CLI reassembles these into a <file>-wal sidecar: fresh 32-B WAL header, random salts, per-frame salt rewrite + SQLite Fibonacci checksum chain. ~70 lines of binary surgery.

You can skip step 3 entirely if you only need committed data and tolerate being slightly stale. The /export/{gen} body is internally consistent on its own — open it with NO -wal sidecar and it's a complete database at the checkpoint boundary (never torn). The WAL frames only add writes committed after that checkpoint. For an offline index/snapshot build with its own freshness layer, two GETs (/info + /export) streamed to a file is the whole job.

Zig: stream the export body straight to a file (flat memory)

http.Client.fetch writes the response body to whatever response_writer you give it, incrementally (it calls streamRemaining(response_writer)). Point it at a std.Io.File.Writer and the multi-GB body never buffers in RAM:

const file = try std.Io.Dir.createFileAbsolute(io, part_path, .{ .truncate = true });
var write_buf: [64 * 1024]u8 = undefined;
var fw = std.Io.File.Writer.init(file, io, &write_buf);
const res = try client.fetch(.{
    .location = .{ .url = url },
    .method = .GET,
    .headers = .{ .authorization = .{ .override = auth } },
    .response_writer = &fw.interface,   // streams here, doesn't buffer whole body
    .keep_alive = false,
});
fw.interface.flush() catch {};
file.close(io);
// download to <path>.part then std.Io.Dir.renameAbsolute(part, final, io)
// so a partial download is never mistaken for a complete file.

Read it back via ATTACH DATABASE 'file:<path>?mode=ro' AS src then SELECT ... FROM src.<table>.

hrana protocol versions

• /v2/pipeline — execute requests only (our query/queryRuntime). No streaming.
• /v3/pipeline — adds batch, sequence, store_sql, describe, get_autocommit. Cursor verbs (open_cursor/fetch_cursor/ close_cursor) are WebSocket-only, NOT valid in /v3/pipeline.
• /v3/cursor — the HTTP cursor endpoint. One POST {baton, batch}, response is newline-delimited JSON: first line CursorRespBody {baton, base_url}, then CursorEntry lines (step_begin, row, step_end, step_error, error). Good for streaming a result set without buffering, but slow for bulk (see above).

hrana value encoding (JSON)

Args and row cells are type-tagged objects. Integers are string-encoded to preserve i64 precision through JSON's f64:

{"type": "null"}
{"type": "integer", "value": "1779645433"}
{"type": "float",   "value": 1.5}
{"type": "text",    "value": "did:plc:..."}
{"type": "blob",    "base64": "..."}

cloud limitations

• busy_timeout and journal_mode PRAGMAs are no-ops on Turso Cloud (they only affect a LOCAL libSQL file). Don't rely on setting them against the remote DB.
• turso db tokens create <db> --expiration <N> accepts day-granularity (7d, 30d, never) — NOT 1h/60s.
• TURSO_URL for the hrana client must be the bare hostname (no https:// / libsql:// prefix) — the client prepends the scheme.

usage analytics from the CLI (period-to-date totals)

Three read-only commands, browser-auth session, hit api.turso.tech:

• turso plan show — org-wide quota tiles: USED / LIMIT / % / OVERAGE per resource (storage, rows read, rows written, embedded syncs, dbs) + reset date. The CLI equivalent of the dashboard headline.
• turso db inspect <db> [--verbose] — same metrics for one DB; verbose breaks out by location.
• turso db inspect <db> --queries — per-query rows-read/written attribution (parameterized, bind-variants collapse). The dashboard graphs DON'T show this. Use it to find corpus-proportional scans: any query with tens-of-millions rows read is reading the whole table each run. Caught a COUNT(*)+SUM(CASE) full-scan stats aggregate and index-bypass WHERE handle IN(...) full scans (missing COLLATE NOCASE on a NOCASE index) this way.

Gap: numbers are period-to-date totals, reset monthly — there is NO CLI flag for the daily time series behind the dashboard graphs (that's the platform usage API). For "where's my read budget going," --queries beats the graphs.

query plans: scan the small table, search the big one

For a JOIN where one table is much smaller than the other (e.g. an aggregates table joined to a documents corpus), SQLite's planner will sometimes pick the bigger table as the driving SCAN even when the small one is the obvious choice — because the relevant statistics aren't there, or because the join shape lets the planner argue itself into either order. Verify with EXPLAIN QUERY PLAN. Look for:

• SCAN d (where d is the big table) — bad. Reads every row of d just to filter to the matching subset.
• SCAN r ... SEARCH d USING INDEX ... (uri=?) — good. Scans the small table once, looks up each match by PK on the big one.

In pub-search (a search service over atproto publications) we hit this on the leaderboard query:

-- naive: SCAN over 18k documents to find ~880 with recommends
SELECT d.uri, COUNT(DISTINCT r.did)
FROM documents d JOIN recommends r ON r.document_uri = d.uri
GROUP BY d.uri
HAVING COUNT(*) > 0

ANALYZE would help in some cases, but the more reliable fix is to make the small-table-first order explicit to the planner — reordering FROM clauses doesn't (SQLite reorders joins freely). What works:

-- explicit: pre-aggregate the small table, then look up by PK
SELECT d.uri, agg.recommend_count
FROM (
  SELECT document_uri, COUNT(DISTINCT did) AS recommend_count
  FROM recommends
  GROUP BY document_uri
  HAVING recommend_count > 0
) agg
JOIN documents d ON d.uri = agg.document_uri

The subquery forces aggregation against recommends (small) first, and the planner can only join documents by PK lookup against the aggregated row set. This dropped per-refresh reads ~6× on our setup.

CTEs (WITH agg AS (...)) work the same way for SQLite's planner, but they trip up naive comptime SQL parsers that scan forward for the first SELECT ... FROM pair. Subquery-in-FROM is the safer shape if your metadata extraction layer is shallow.

reference

• spec: https://github.com/tursodatabase/libsql/blob/main/docs/HRANA_3_SPEC.md
• CLI export source: tursodatabase/turso-cli internal/turso/tursoServer.go
• libSQL sync bootstrap: docs.rs libsql src/sync.rs