The payload of every table row and every index entry is a record: a list of values, each with its own type. The record starts with a header that gives the type and size of each value, then the values follow. Sizes and types are written as varints.
Varints
A varint is a 64-bit two’s-complement integer in 1 to 9 bytes, most significant part first. Each of the first eight bytes gives 7 bits and has its top bit set when more bytes follow; a ninth byte, if reached, gives all 8 of its bits. Small positive numbers are short; a negative number always takes 9 bytes.
- Start with value = 0.
- For each of up to eight bytes: value = value × 128 + (byte AND 0x7F). If the byte’s top bit (0x80) is clear, stop.
- If all eight bytes had the top bit set, read a ninth: value = value × 256 + that byte.
| Bytes | Value |
|---|---|
00 to 7F | 0 to 127 |
81 00 | 128 |
81 5C | 1 × 128 + 92 = 220 |
83 13 | 3 × 128 + 19 = 403 |
FF FF FF FF FF FF FF FF FF | −1 |
Record format
0Header size a varint: the size of the header in bytes, this varint included1 to 9 bytes…Serial types one varint for each value, in column order1 to 9 bytes each…Values the values, in the same order, each as long as its serial type saysvaries
Record header
The header starts with a varint giving its own size in bytes, including that varint. The serial types fill the rest of the header, one varint per value; there is no count, so a reader reads serial types until it reaches the header size. The body starts straight after the header.
Serial types
| Serial type | Bytes in the body | Value |
|---|---|---|
0 | 0 | NULL |
1 | 1 | 8-bit signed integer |
2 | 2 | 16-bit signed integer, big-endian |
3 | 3 | 24-bit signed integer, big-endian |
4 | 4 | 32-bit signed integer, big-endian |
5 | 6 | 48-bit signed integer, big-endian |
6 | 8 | 64-bit signed integer, big-endian |
7 | 8 | 64-bit IEEE 754 floating-point number, big-endian |
8 | 0 | the integer 0 (schema format 4 and later) |
9 | 0 | the integer 1 (schema format 4 and later) |
10, 11 | – | reserved for SQLite’s internal use; never in a well-formed file |
| N ≥ 12, even | (N − 12) ÷ 2 | a BLOB of that many bytes |
| N ≥ 13, odd | (N − 13) ÷ 2 | text of that many bytes in the database’s encoding, with no zero byte at the end |
Serial types 8 and 9 may appear only in a database whose schema format is 4. The serial type of a text value 5 bytes long is 5 × 2 + 13 = 23; of a 6-byte BLOB, 6 × 2 + 12 = 24.
Values
The body holds the values in column order, each taking the number of bytes its serial type gives, with nothing between them. Text is in the encoding given at header offset 56, the same for the whole file.
Table rows
A table row’s record holds the columns in the order the table declares them. A
column declared INTEGER PRIMARY KEY is the rowid itself, kept in the
cell, and is stored in the record as NULL.
A record may hold fewer values than the table has columns, in a database of schema
format 2 or later; the missing ones, always at the end, take the column’s default.
This is how ALTER TABLE ADD COLUMN works without rewriting rows.
Generated columns arrived with SQLite 3.31.0 (2020). VIRTUAL ones take no space in the file; STORED ones, in SQLite’s words, “take up space in the database file”. Where in the record a STORED column sits is not documented, so the explorer names the values of a table with generated columns “Column 1, 2 …” rather than guess.
Index entries
An index entry’s record holds the indexed columns, in index order, followed by the row’s key: the rowid, or for a WITHOUT ROWID table, the PRIMARY KEY columns. See Indexes.
A real record
The first row of the schema table in the database shown under Database header: a table leaf cell at offset 3,824 of page 1.
81 5C 01 07 17 19 19 01 83 13 74 61 62 6C 65 70 61 70 65 72 73 70 61 70 65 72 73 02 43 52 45 41 …81 5C: a payload of 220 bytes.01: rowid 1.07: the record header is 7 bytes. Serial types:17= 23, text of 5 bytes;19= 25, text of 6 bytes, twice;01, an 8-bit integer;83 13= 403, text of 195 bytes.- The values:
table,papers,papers,02= 2, andCREATE TABLE papers …, 195 bytes. - 7 + 5 + 6 + 6 + 1 + 195 = 220, the payload size.
Read as a schema row, it says that table papers has its root at page 2.
See The schema table.
The integer 1 in no bytes
A row (1, 'one') in a small database made with the sqlite3 shell for
this page, a table leaf cell of 8 bytes:
06 01 03 09 13 6F 6E 6506: payload 6 bytes.01: rowid 1.03: a 3-byte header.09: serial type 9, the integer 1, which takes no bytes in the body.13= 19: text of 3 bytes.- The body is just
6F 6E 65,one.