SQLite Data Types

SQLite handles data types differently from most other databases. Instead of enforcing strict column types, SQLite uses a flexible system called type affinity. Understanding this system prevents surprises when storing and retrieving data.

The Five Storage Classes

Every value SQLite stores belongs to one of five storage classes. Think of storage classes as the actual containers in which data physically lives inside the database file.

SQLITE STORAGE CLASSES
─────────────────────────────────────────────────────
  NULL       →  A missing or unknown value
  INTEGER    →  A whole number (1, 42, -7, 1000000)
  REAL       →  A decimal number (3.14, 2.718, -0.5)
  TEXT       →  A string of characters ("Alice", "hello")
  BLOB       →  Raw binary data (images, files, bytes)
─────────────────────────────────────────────────────

Real-World Analogy

Imagine a school report card stored in a filing cabinet drawer. Each type of information on the card gets stored differently:

REPORT CARD DATA → SQLITE STORAGE CLASS
──────────────────────────────────────────────────
"Grade" field missing    → NULL
Roll Number: 42          → INTEGER
GPA: 3.75                → REAL
Student Name: "Alice"    → TEXT
Profile Photo (raw)      → BLOB
──────────────────────────────────────────────────

Type Affinity

When you declare a column type in SQLite, you set an affinity preference — not a strict rule. SQLite tries to convert incoming values to the preferred type, but it does not reject values of other types.

Why This Matters

-- You declare a TEXT column
CREATE TABLE test (val TEXT);

-- You insert a number
INSERT INTO test VALUES (42);

-- SQLite stores it as TEXT "42"
SELECT typeof(val) FROM test;
-- Result: text

-- This is type affinity at work.

The NULL Type

NULL means absence of a value. It is not zero, not an empty string, not false — it simply means "nothing is here." Every column in SQLite can store NULL unless you add a NOT NULL constraint.

NOT NULL        means no value exists here
0               means the value zero exists
''              means an empty string exists
'unknown'       means the text "unknown" exists

These are four completely different things in SQLite.

INTEGER Storage Sizes

SQLite stores integers using the minimum number of bytes needed. The database engine decides the storage size automatically based on the actual value.

VALUE RANGE              BYTES USED
────────────────────────────────────────
0                        0 bytes (special)
-128 to 127              1 byte
-32768 to 32767          2 bytes
-8388608 to 8388607      3 bytes
-2^31 to 2^31-1          4 bytes
-2^48 to 2^48-1          6 bytes
-2^63 to 2^63-1          8 bytes

REAL Storage

SQLite stores REAL values as 8-byte IEEE 754 floating-point numbers. This is the same format as a double in C or Java.

VALID REAL VALUES
──────────────────────────────────
3.14
2.718281828
-0.001
1.0e10    (scientific notation)

TEXT Encoding

SQLite stores TEXT in UTF-8, UTF-16BE, or UTF-16LE encoding. UTF-8 is the default and handles virtually every language and symbol.

VALID TEXT VALUES
──────────────────────────────────
'Alice'
'hello world'
'Tokyo'         ← Japanese city name
'Ñoño'          ← Spanish characters
'€ 42.00'       ← Euro sign

BLOB Storage

BLOB (Binary Large Object) stores raw bytes exactly as you provide them. No encoding or transformation occurs.

USE BLOBS FOR:
──────────────────────────────────
  Images stored in the database
  Encrypted data
  Compressed files
  Audio or video chunks
  Serialized objects

BOOLEAN and DATE — No Native Type

SQLite has no built-in BOOLEAN or DATE type. These are stored as other types by convention.

BOOLEAN (stored as INTEGER)
──────────────────────────────────────
  0 = false
  1 = true

DATE (stored as TEXT or INTEGER)
──────────────────────────────────────
  TEXT: '2024-07-24'  (ISO 8601 format)
  INTEGER: 1721808000 (Unix timestamp)
  REAL: 2460516.5     (Julian day number)

Checking Value Types at Runtime

sqlite> SELECT typeof(42);
integer

sqlite> SELECT typeof(3.14);
real

sqlite> SELECT typeof('hello');
text

sqlite> SELECT typeof(NULL);
null

sqlite> SELECT typeof(x'FF00');
blob

Practical Column Type Declarations

CREATE TABLE employees (
  id         INTEGER PRIMARY KEY,  -- auto-incremented ID
  name       TEXT    NOT NULL,     -- person's name
  salary     REAL,                 -- monthly pay
  is_active  INTEGER DEFAULT 1,    -- boolean: 0 or 1
  photo      BLOB,                 -- profile picture bytes
  hired_on   TEXT                  -- date as 'YYYY-MM-DD'
);

Key Takeaways

• SQLite has five storage classes: NULL, INTEGER, REAL, TEXT, BLOB
• Column types set a preference (affinity), not a strict rule
• NULL means no value — different from zero or empty string
• SQLite has no native BOOLEAN or DATE type; use INTEGER and TEXT instead
• Use typeof() to check the actual storage class of any value