SQLite BLOB, explained

BLOB is SQLite saying “I won’t touch this”: bytes go in and the same bytes come out. It’s also the affinity of a column with no type at all, and the cousin of STRICT tables’ ANY. Here’s how it all works.

In one paragraph (ELI12)

A BLOB is a box of raw bytes — a photo, a file, a fingerprint of some data. SQLite doesn’t try to understand it or turn it into a number or words. Whatever you put in comes back out exactly the same.

How it’s stored

Serial type 2·n + 12 for n bytes (even numbers ≥ 12), followed by the bytes. A BLOB bigger than about a quarter of a page spills into a chain of overflow pages, so a 10 MB image in a 4 KB-page database is a linked list of ~2,560 pages that must be followed in order. The viewer shows a BLOB’s size and its first bytes in hex — enough to recognise a PNG (89 50 4e 47) or a gzip stream (1f 8b).

SELECT hex(substr(cover_thumb, 1, 8)), length(cover_thumb), typeof(cover_thumb)
FROM albums LIMIT 1;
-- 89504E470D0A1A0A | 48 | blob

No type = BLOB affinity

A column declared with no type — CREATE TABLE kv (k, v) — gets BLOB affinity (older docs call it “none”). It performs no conversion: '42' stays TEXT, 42 stays INTEGER, bytes stay bytes. That makes untyped columns handy for key/value tables that hold mixed data.

CREATE TABLE kv (k, v);
INSERT INTO kv VALUES ('a', '42'), ('b', 42), ('c', x'CAFE');
SELECT k, v, typeof(v) FROM kv;
-- a | 42   | text
-- b | 42   | integer
-- c | x'CAFE' | blob

STRICT tables and ANY

Since SQLite 3.37, a table can be declared STRICT. Every column must then use one of INT, INTEGER, REAL, TEXT, BLOB or ANY, and SQLite rejects values of the wrong kind instead of quietly keeping them. ANY is the escape hatch: it accepts every storage class and — unlike BLOB affinity in an ordinary table — is guaranteed never to convert anything.

CREATE TABLE audit_log (id INTEGER PRIMARY KEY, at TEXT NOT NULL, actor INT, details ANY) STRICT;
INSERT INTO audit_log (at, actor, details) VALUES ('2026-09-29', 7, 12.5);      -- ok
INSERT INTO audit_log (at, actor, details) VALUES ('2026-09-29', '7', x'00FF'); -- ok: '7' converts losslessly to 7
INSERT INTO audit_log (at, actor) VALUES ('2026-09-29', 'bob');
-- Error: cannot store TEXT value in INT column audit_log.actor

STRICT still converts when nothing is lost — '7' becomes the integer 7 — but a value that can’t be converted is an error instead of a silent surprise.

In a non-STRICT table, ANY is just an unknown word, so by rule 5 it gets NUMERIC affinity — the opposite of what the name suggests.

Examples

SELECT CAST(x'48656C6C6F' AS TEXT), hex(zeroblob(4)), length(randomblob(2));
-- Hello | 00000000 | 2

SELECT length(x'00FF00'), quote(x'00FF00');
-- 3 | X'00FF00'

Limits

• Largest BLOB: 1,000,000,000 bytes by default (SQLITE_MAX_LENGTH).
• Reading a BLOB column reads its whole overflow chain, even if you only need the length. Keep big files in a separate table, so scans of the main table stay small.
• SQLite’s own measurements show blobs under ~100 KB are faster to read from the database than from separate files; above that, storing a path to a file on disk is often better.
• Comparisons are byte-wise (memcmp), and BLOBs sort after every other storage class.

Storage class vs. declared type vs. affinity

SQLite is dynamically typed: the type belongs to the value, not the column. Every value is one of five storage classes — NULL, INTEGER, REAL, TEXT, BLOB. A column’s declared type only sets its affinity: a preference SQLite applies when you store a value, by five rules checked in order:

Order matters: CHARINT matches rule 1 before rule 2 and is INTEGER; FLOATING POINT contains INT and is INTEGER too. The viewer implements exactly these rules:

function affinityOf(declType) {
  const t = String(declType || '').toUpperCase();
  if (t.includes('INT')) return 'INTEGER';
  if (t.includes('CHAR') || t.includes('CLOB') || t.includes('TEXT')) return 'TEXT';
  if (t.includes('BLOB') || t.trim() === '') return 'BLOB';
  if (t.includes('REAL') || t.includes('FLOA') || t.includes('DOUB')) return 'REAL';
  return 'NUMERIC';
}

The other types

Open any .sqlite, .db or .sqlite3 file in the SQLite viewer — every column header has an ELI12 button that explains its type and links here. How the viewer reads a 20 GB database without loading it: how to open a 20 GB SQLite database in the browser.