You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
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.
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.
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:
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL NOT NULL,
quantity INTEGER NOT NULL DEFAULT 0,
metadata BLOB
);
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.
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.
Subscribe to continue reading
Get full access to this lesson and all 10 lessons in this course.