Data Types in SQLite

SQLite uses a flexible, dynamic type system called type affinity that differs significantly from the strict static typing of PostgreSQL or MySQL. Understanding how SQLite handles types prevents surprises and helps you write schemas that behave as expected.

Storage Classes

SQLite stores every value using one of five storage classes:

Storage Class	Description
NULL	The null value
INTEGER	A signed integer stored in 1, 2, 3, 4, 6, or 8 bytes
REAL	An 8-byte IEEE 754 floating-point number
TEXT	A UTF-8 or UTF-16 encoded string
BLOB	Raw binary data, stored exactly as provided

These are the only types that actually exist on disk. Column type declarations are advisory, not enforced.

Type Affinity

Each column has a type affinity that governs how SQLite coerces values when you insert them. SQLite derives affinity from the column's declared type name using a set of rules:

• INTEGER affinity — declared types containing INT (e.g. INTEGER, TINYINT, BIGINT)
• TEXT affinity — declared types containing CHAR, CLOB, or TEXT (e.g. VARCHAR(255), NCHAR)
• BLOB affinity (also called NONE) — declared type is empty or BLOB
• REAL affinity — declared types containing REAL, FLOA, or DOUB
• NUMERIC affinity — everything else (e.g. DECIMAL, BOOLEAN, DATE, DATETIME)

CREATE TABLE products (
  id        INTEGER PRIMARY KEY,
  name      TEXT    NOT NULL,
  price     REAL    NOT NULL,
  quantity  INTEGER NOT NULL DEFAULT 0,
  metadata  BLOB
);

Dynamic Typing in Practice

Because SQLite does not enforce column types, you can insert a string into an INTEGER column without an error:

CREATE TABLE demo (val INTEGER);
INSERT INTO demo VALUES (42);       -- stored as INTEGER
INSERT INTO demo VALUES (3.14);     -- stored as REAL
INSERT INTO demo VALUES ('hello');  -- stored as TEXT (no error!)
INSERT INTO demo VALUES (NULL);     -- stored as NULL

This flexibility is intentional — it simplifies importing data from CSV files or other weakly typed sources. However, it means you must validate types in your application if strict enforcement matters.

Booleans

SQLite has no dedicated BOOLEAN storage class. By convention, 0 represents false and 1 represents true. Declaring a column as BOOLEAN gives it NUMERIC affinity.