storage-format
This guide breaks down how SQLite organizes data on disk, from the 100-byte header through page types, B-tree layouts, and cell formats. It covers record encoding with serial types, overflow page chains for large payloads, and freelist structures for space reuse. Includes Turso implementation details and debugging commands.
storage-format explains SQLite's on-disk structure, including headers, B-trees, pages, cells, and overflow handling.
AI-generated summary based on this skill's SKILL.md
Decision gist · record as of 2026-07-28
storage-format explains SQLite's on-disk structure, including headers, B-trees, pages, cells, and overflow handling. This guide breaks down how SQLite organizes data on disk, from the 100-byte header through page types, B-tree layouts, and cell formats. It covers record encoding with serial types, overflow page chains for large payloads, and freelist structures for space reuse. Includes Turso implementation details and debugging commands.
Use it when
- storage-format details how SQLite uses B-tree pages to organize table and index storage.
- storage-format distinguishes interior and leaf pages in SQLite's B-tree.
Verify before relying
Read SKILL.md below before installing (1 file). Open directory: indexed for reading, not audited.
Install
tursodatabase/turso/storage-format · repository language: Rust
Open directory. Skills are indexed for reading, not audited. Review a skill's body before installing it.
Frequently asked questions
AI-generated answers based on this skill's SKILL.md and metadata
What is the SQLite file format structure?
storage-format explains how SQLite organizes data on disk starting with a 100-byte header that defines page size, version, and schema metadata. The file is divided into fixed-size pages (typically 4096 bytes), each containing a page header, cell pointers, and cell content. Pages are typed as leaf tables, interior tables, leaf indexes, or interior indexes, arranged in a B-tree hierarchy. The header also stores the freelist root page and total page count, enabling SQLite to manage space and navigate the entire database structure.
How does SQLite store data on disk using B-trees?
storage-format details how SQLite uses B-tree pages to organize table and index storage. Interior pages contain keys and child page pointers for navigation; leaf pages hold actual records. Each page has a page header (8–12 bytes) followed by a cell pointer array and free space. Cells are variable-length records stored from the page end backward. The B-tree structure allows efficient range queries and insertions. Turso's storage engine implements this same layout, enabling compatibility with standard SQLite tools while supporting distributed features.
What are B-tree interior vs leaf pages in SQLite?
storage-format distinguishes interior and leaf pages in SQLite's B-tree. Interior pages store keys and pointers to child pages, directing searches downward through the tree. Leaf pages store actual data cells—for tables, the full record with rowid; for indexes, the indexed columns and rowid. Interior pages have a right-child pointer; leaf pages do not. Both page types share the same header format but differ in cell content. This separation enables efficient tree navigation and keeps leaf pages focused on data storage.
How does SQLite handle overflow pages for large payloads?
storage-format explains SQLite's overflow mechanism for records exceeding available cell space. When a record is too large, the cell stores a pointer to an overflow page chain. The first overflow page contains a link to the next overflow page and partial record data; subsequent pages continue the chain. This design avoids fragmenting leaf pages and keeps them efficient for typical records. The overflow structure is transparent to queries but critical for debugging large-value storage and understanding space usage in real databases.
What is the SQLite record format with serial types?
storage-format describes how SQLite encodes records using serial types. Each record begins with a varint header specifying the number and types of columns. Serial types encode data type (NULL, integer, float, blob, text) and length, allowing SQLite to parse variable-length fields without schema lookups. Text and blob types include length in the serial type; integers use 1–8 bytes depending on value. This compact encoding minimizes storage and enables fast deserialization during query execution.
How can I debug and inspect Turso/SQLite database files?
storage-format provides guidance for inspecting database files using hex editors, the SQLite command-line tool, and Turso-specific debugging commands. You can examine the 100-byte header to verify page size and schema version, then navigate pages by offset to inspect B-tree structure, cell layouts, and freelist chains. Understanding page types, cell pointers, and record formats lets you trace data organization and diagnose corruption or space issues. Turso's storage engine maintains standard SQLite format, so these inspection techniques apply directly.
SKILL.md
Rendered from the published skill. Quoted content, verbatim.
Storage Format Guide
Database File Structure
┌─────────────────────────────┐
│ Page 1: Header + Schema │ ← First 100 bytes = DB header
├─────────────────────────────┤
│ Page 2..N: B-tree pages │ ← Tables and indexes
│ Overflow pages │
│ Freelist pages │
└─────────────────────────────┘
Page size: power of 2, 512-65536 bytes. Default 4096.
Database Header (First 100 Bytes)
| Offset | Size | Field |
|---|---|---|
| 0 | 16 | Magic: "SQLite format 3\0" |
| 16 | 2 | Page size (big-endian) |
| 18 | 1 | Write format version (1=rollback, 2=WAL) |
| 19 | 1 | Read format version |
| 24 | 4 | Change counter |
| 28 | 4 | Database size in pages |
| 32 | 4 | First freelist trunk page |
| 36 | 4 | Total freelist pages |
| 40 | 4 | Schema cookie |
| 56 | 4 | Text encoding (1=UTF8, 2=UTF16LE, 3=UTF16BE) |
All multi-byte integers: big-endian.
Page Types
| Flag | Type | Purpose |
|---|---|---|
| 0x02 | Interior index | Index B-tree internal node |
| 0x05 | Interior table | Table |
(truncated - see the full file via the links below)
File tree — 1 file
.claude/skills/storage-format/SKILL.md
Let your AI agent find skills like this
Example. Real query, live index.
You found this page by searching. An agent finds it by wishing: SkillFed indexes 56,283 agent skills by what they can do, searchable in plain language.
wish › “Understand SQLite file format structure and on-disk layout”
Give your agent the search over MCP, or paste the wish link into any chat. No install? Search from any chat →
Related skills
Turso is a Rust-based, in-process SQL database that runs SQLite-compatible queries in Node.js, browsers, React Native, and edge functions. It includes vector similarity search, full-text search powered by Tantivy, change tracking, encryption, and remote sync capabilities. This skill covers SDK setup, CLI usage, and recipes for all supported languages and platforms.
This skill documents Turso's experimental Multi-Version Concurrency Control system, which allows readers and writers to operate simultaneously without blocking through row-level snapshot isolation. It covers enabling MVCC via pragma, the versioning model tracking begin/end timestamps, and the architecture built on lock-free data structures. The guide also details checkpointing behavior, current limitations like garbage collection and recovery gaps, and testing patterns for MVCC-enabled code.
This guide walks through Turso's debugging toolkit for tracking down database problems. Compare bytecode between SQLite and Turso to pinpoint code generation bugs versus VM issues, enable trace logging for core components, run stress tests with ThreadSanitizer to catch threading problems, and use deterministic simulation with seeds to reproduce elusive bugs. Corruption debugging tools help diagnose WAL and integrity failures.
This guide explains Turso's Write-Ahead Logging system, covering how transactions are written, read, and checkpointed back to the main database file. It details the concurrency model where one writer coexists with multiple readers, checkpoint strategies from passive to truncate modes, and the recovery process after crashes. The guide also maps Turso's implementation to its core storage layer, distinguishing per-connection state from shared WAL structures.
code-quality establishes standards for writing reliable database code in Rust, prioritizing data integrity over silent failures. It covers error handling discipline, exhaustive pattern matching, and invariant checking to prevent corruption. The guide emphasizes crashing on invalid state rather than continuing in undefined conditions, and avoiding premature abstractions or workarounds.
CDC is Turso's change data capture system that records INSERT, UPDATE, and DELETE operations at the bytecode translation layer. Changes are written to a dedicated CDC table and consumed by the sync engine to replicate local modifications remotely. The system supports two schema versions and operates per-connection via PRAGMA configuration.
More skills yield-injections (MIT) · type-inference (Apache-2.0) · convert-file (MIT)