Decoding 9-byte Huffman varints, serial type headers, B-tree overflow chains, and compiling Drizzle/Prisma schemas in browser RAM.
If you have ever embedded a browser-based SQLite viewer into a web application using CDN-hosted sql.js or duckdb-wasm, you have probably run into two frustrating production walls:
Corporate CSP & Air-Gapped Network Failures: Strict enterprise browser policies (
Content-Security-Policy) frequently block external.wasmfetches from third-party CDNs or rejectwasm-unsafe-eval.Missing Analytical Ergonomics & ORM Codegen: Once a
.sqliteor.dbfile opens, standard SQLite doesn't give you DuckDB’sSUMMARIZE <table>orSELECT * EXCLUDE (col), doesn't draw Entity-Relationship Diagrams when developers omitted explicitFOREIGN KEYclauses, and cannot compile a binary.sqlitefile directly into a Drizzle ORM (schema.ts) or Prisma (schema.prisma) schema.
When we built the database engineering workbench on KivaroTools, we wrote a self-contained, zero-CDN binary SQLite 3 B-tree parser, serializer, and DuckDB analytical pre-processor in pure TypeScript (lib/sqliteExplorerEngine.ts).
Here is how the SQLite 3 binary format actually works under the hood—focusing on the three mechanisms that trip up most custom parsers: 9-byte Huffman varints, Record Serial Types, and B-Tree Overflow Page Chains.
1. Decoding SQLite’s 9-Byte Big-Endian Varints
Unlike Protocol Buffers (which use little-endian base-128 varints), SQLite 3 encodes cell payload sizes, rowid keys, and record header lengths using big-endian variable-length integers (1 to 9 bytes).
For the first 8 bytes (i = 0..7), bit 7 (0x80) is the continuation bit and the lower 7 bits (0x7f) hold the value. However, if the varint reaches the 9th byte, all 8 bits of byte 9 are used as data (value * 256 + b9) so a 9-byte varint can represent a full 64-bit integer:
export function readSqliteVarint(
bytes: Uint8Array,
offset: number
): { value: number; byteLength: number } {
let value = 0;
for (let i = 0; i < 8; i++) {
if (offset + i >= bytes.length) {
return { value, byteLength: i + 1 };
}
const b = bytes[offset + i];
value = value * 128 + (b & 0x7f);
if ((b & 0x80) === 0) {
return { value, byteLength: i + 1 };
}
}
const b9 = offset + 8 < bytes.length ? bytes[offset + 8] : 0;
value = value * 256 + b9;
return { value, byteLength: 9 };
}
2. How SQLite Packs Columns: The Serial Type Header
Inside a Table B-Tree Leaf page (0x0d), each cell begins with two varints (payload_size and rowid), followed by the Record Payload.
SQLite does not store column names or fixed-width fields inside rows. Instead, the record starts with a compact array of Serial Type varints—one per column—that tells the decoder both the data type and the exact byte length of each column value:
0->NULL(0 bytes)1, 2, 3, 4, 5, 6-> Big-endian signed two's-complement integers of1, 2, 3, 4, 6, or 8 bytes7-> IEEE-754 64-bit floating-point number (8 bytes)8-> Integer constant0(0 bytesstored in the body!)9-> Integer constant1(0 bytesstored in the body!)N >= 12(even) ->BLOBof exactly(N - 12) / 2bytesN >= 13(odd) ->TEXTstring of exactly(N - 13) / 2bytes (decoded using the database’s header encoding:UTF-8,UTF-16le, orUTF-16be)
Notice serial types 8 and 9: boolean 0 and 1 columns consume zero bytes in the record body because the value is encoded directly inside the serial type integer itself.
3. Reassembling Split Rows Across Overflow Page Chains
What happens when a row contains a 15 KB JSON document or BLOB, but the database page size is only 4,096 bytes?
If totalPayloadSize > usableSize - 35 (maxLocal), SQLite keeps only the first k bytes locally on the 0x0d leaf page and appends a 4-byte big-endian page number pointing to the first Overflow Page. Each overflow page begins with a 4-byte pointer to the next overflow page (or 0x00000000 for the final page in the chain), followed by up to usableSize - 4 bytes of payload:
function extractCellPayloadWithOverflow(
dbBytes: Uint8Array,
pageSize: number,
usableSize: number,
cellPayloadStart: number,
totalPayloadSize: number
): Uint8Array {
const maxLocal = usableSize - 35;
const minLocal = ((usableSize - 12) * 32) / 255 - 23;
if (totalPayloadSize <= maxLocal) {
return dbBytes.subarray(cellPayloadStart, cellPayloadStart + totalPayloadSize);
}
let k = minLocal + ((totalPayloadSize - minLocal) % (usableSize - 4));
if (k > maxLocal) k = minLocal;
const localBytesCount = Math.max(0, Math.floor(k));
const out = new Uint8Array(totalPayloadSize);
out.set(dbBytes.subarray(cellPayloadStart, cellPayloadStart + localBytesCount), 0);
let written = localBytesCount;
const dv = new DataView(dbBytes.buffer, dbBytes.byteOffset, dbBytes.byteLength);
let nextOverflowPage = dv.getUint32(cellPayloadStart + localBytesCount, false);
while (nextOverflowPage > 0 && written < totalPayloadSize) {
const pageStart = (nextOverflowPage - 1) * pageSize;
nextOverflowPage = dv.getUint32(pageStart, false);
const chunkLen = Math.min(usableSize - 4, totalPayloadSize - written);
out.set(dbBytes.subarray(pageStart + 4, pageStart + 4 + chunkLen), written);
written += chunkLen;
}
return out;
}
4. Layering DuckDB SUMMARIZE, Implicit ERDs, and Drizzle/Prisma Codegen on Top
Once the B-tree pages are decoded into in-memory tables, we layered three developer workflows on top of the engine:
DuckDB Analytical SQL: Run
SUMMARIZE orders;,DESCRIBE users;,SELECT * EXCLUDE (metadata_json) FROM orders;, andGROUP BY ALLright alongside standard SQLite queries.Declared + Implicit (
*_id -> id) ERD Discovery: Automatically linksorganization_idtoorganizations.ideven whenFOREIGN KEYconstraints were omitted inCREATE TABLE, exporting to MermaiderDiagramanddbdiagram.ioDBML.1-Click ORM & Postgres Migration Compiler: Exports Drizzle ORM (
schema.ts), Prisma (schema.prisma), and PostgreSQL 16 DDL (BIGSERIAL,JSONB,TIMESTAMPTZ)—plus full read/write inline editing that re-serializes a valid binary.sqlitefile.
You can test the live studio with 3 built-in sample databases in the KivaroTools SQLite & DuckDB In-Browser Studio, or format complex multi-join queries in our companion SQL Query Formatter & 19-Dialect Linter.
Top comments (0)