SQLite - Data Type

SQLite - Data Types

A key feature that distinguishes SQLite from most other SQL databases is its dynamic type system. In SQLite, the data type is a property of the value itself, not the column in which the value is stored. This provides great flexibility, but it’s important to understand the underlying concepts of Storage Classes and Type Affinity.

Storage Classes

Every value stored in an SQLite database is assigned one of the following five storage classes:

Sr.No.	Storage Class & Description
1	NULL The value is a NULL value.
2	INTEGER A signed integer (1, 2, 3, 4, 6, or 8 bytes), depending on the magnitude.
3	REAL A floating-point value, stored as an 8-byte IEEE floating-point number.
4	TEXT A text string, stored using the database encoding (e.g., UTF-8).
5	BLOB (Binary Large Object) A blob of data, stored exactly as it was input. Used for files, images, etc.

Type Affinity

While any column can technically store any type of value, columns are assigned a “type affinity”. Affinity is a recommendation for the column’s preferred storage class. SQLite will try to coerce values into the column’s affinity type where it makes sense.

Affinity	Description	Triggered by Type Names Containing
INTEGER	Prefers storing data as an integer. Values that look like numbers but have decimal points might be stored as REAL.	INT
TEXT	Prefers storing data as text.	CHAR, CLOB, TEXT
BLOB	Prefers storing data as a BLOB. No conversions are attempted.	BLOB (or no type specified)
REAL	Prefers storing data as a floating-point number.	REAL, FLOA, DOUB
NUMERIC	A flexible affinity that can store values in any format. It will try to store data as INTEGER or REAL if possible, otherwise falls back to TEXT.	NUMERIC, DECIMAL, BOOL, DATE

Modern Data Types and Usages

Boolean Values

SQLite does not have a distinct Boolean storage class. It stores boolean values as INTEGER 0 (false) and 1 (true). Modern versions of SQLite recognize the keywords TRUE and FALSE in SQL statements, making queries more readable.

CREATE TABLE tasks (id INTEGER PRIMARY KEY, description TEXT, is_complete INTEGER);

-- Both statements below are equivalent
INSERT INTO tasks (description, is_complete) VALUES ('Write tutorial', 1);
INSERT INTO tasks (description, is_complete) VALUES ('Publish tutorial', TRUE);

Date and Time Values

As covered in the Date & Time chapter, SQLite handles dates by storing them as TEXT, REAL, or INTEGER and manipulating them with built-in functions. Declaring a column with DATE or DATETIME gives it NUMERIC affinity, but TEXT is often the most practical choice for storing ISO 8601 strings.

Working with JSON

SQLite has excellent support for JSON data, which is stored in columns with TEXT affinity. A rich set of json_* functions allows you to query and manipulate JSON documents directly within your SQL.

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    attributes TEXT -- Storing JSON data as text
);

INSERT INTO products (name, attributes) VALUES (
    'Laptop',
    '{"brand": "AwesomeCorp", "ram_gb": 16, "storage": ["512gb_ssd", "1tb_hdd"]}'
);

-- Extract a value from the JSON object using json_extract()
SELECT name, json_extract(attributes, '$.brand') AS brand
FROM products
WHERE json_extract(attributes, '$.ram_gb') >= 16;