SQLite INTEGER, explained

INTEGER is the type SQLite is best at: a whole number stored in as few bytes as it needs, and — as INTEGER PRIMARY KEY — the key every table is physically sorted by. Here’s how it’s stored, what it can hold, and the traps.

In one paragraph (ELI12)

An INTEGER is a whole number — 7, -3, 2026 — with no decimal point. SQLite is thrifty with them: the numbers 0 and 1 cost zero bytes of data, a number up to 127 costs one byte, and the biggest numbers cost eight. So a column full of small counts or yes/no flags takes almost no space.

How it’s stored

Each row is a record: a header listing one serial type per column, then the column bodies. For integers the serial type says how many bytes follow, big-endian two’s complement:

The size is chosen per value, not per column: declaring BIGINT or SMALLINT changes nothing on disk. Here’s how our generator picks the serial type:

function intSerial(v) {
  if (v === 0) return 8;           // zero bytes
  if (v === 1) return 9;           // zero bytes
  const a = v < 0 ? -v - 1 : v;
  if (a < 0x80) return 1;          // 1 byte
  if (a < 0x8000) return 2;        // 2 bytes
  if (a < 0x800000) return 3;      // 3 bytes
  if (a < 0x80000000) return 4;    // 4 bytes
  if (a < 0x800000000000) return 5; // 6 bytes
  return 6;                        // 8 bytes
}

INTEGER PRIMARY KEY is the rowid

Every ordinary table is a b-tree sorted by a hidden 64-bit key, the rowid. Declare a column exactly INTEGER PRIMARY KEY and it becomes that key — an alias, stored once, in the b-tree cell rather than the record. Looking a row up by it is one root-to-leaf descent: three or four page reads even in a 20 GB table. That’s how the viewer scrolls to row 180,000,000 instantly: WHERE rowid >= ? LIMIT 30.

• It must be spelled INTEGER. INT PRIMARY KEY or BIGINT PRIMARY KEY makes an ordinary column with a separate unique index.
• Insert NULL (or omit it) and SQLite assigns max(rowid) + 1.
• AUTOINCREMENT adds one promise: an id is never reused, even after the row with the largest id is deleted. The counter lives in the sqlite_sequence table (the viewer’s METADATA tab lists it).
• WITHOUT ROWID tables have no rowid; they are b-trees keyed by their PRIMARY KEY instead.

Examples

CREATE TABLE t (id INTEGER PRIMARY KEY, n INTEGER, big BIGINT, flag INT);
INSERT INTO t (n, big, flag) VALUES ('42', 9007199254740993, 1);
INSERT INTO t (n, big, flag) VALUES ('4.0', '12abc', 2.5);

SELECT id, n, typeof(n), big, typeof(big), flag, typeof(flag) FROM t;
-- 1 | 42 | integer |  9007199254740993 | integer | 1   | integer
-- 2 | 4  | integer |  12abc            | text    | 2.5 | real

The text '42' and even '4.0' were converted to integers, because they look like numbers that fit losslessly. '12abc' doesn’t, so it’s kept as TEXT — in an INTEGER column. And 2.5 stays REAL because converting it would lose the half.

Limits

• Range: -9223372036854775808 to 9223372036854775807 (signed 64-bit).
• Arithmetic that overflows switches to REAL: SELECT 9223372036854775807 + 1 returns 9.22337203685478e+18. sum() raises “integer overflow” instead; total() returns a REAL.
• JavaScript numbers are exact only up to 2⁵³ (9,007,199,254,740,991). Past that the viewer shows the exact digits as text instead of rounding them.
• A rowid can reach the maximum; after that SQLite picks random unused rowids — unless the table is AUTOINCREMENT, where inserts then fail with SQLITE_FULL.

Gotchas

• INT PRIMARY KEY is not a rowid alias. Only the exact spelling INTEGER PRIMARY KEY is.
• Integer division truncates: SELECT 7 / 2 is 3. Write 7 / 2.0 or CAST(7 AS REAL) / 2.
• Nothing stops text in an INTEGER column in an ordinary table. Use a STRICT table, or CHECK (typeof(n) = 'integer').
• Booleans are integers: TRUE is 1, FALSE is 0.

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.