Type affinity

SQLite’s type system is the engine’s most-discussed departure from ISO SQL. Where most relational engines apply strict column types — a column declared INTEGER rejects values that are not integers — SQLite applies a discipline called type affinity, in which a column’s declared type is a recommendation that influences how values are stored but does not, by default, prevent values of other types from being stored. The discipline preserves the appearance of conventional SQL while permitting the lazy, dynamically typed style that is convenient in scripting and in evolving schemas. It also permits a small set of bugs that strict typing would prevent. Programmers who prefer the strict regime can opt in per-table with the STRICT keyword introduced in version 3.37.

Storage classes

Every value stored in an SQLite database belongs to one of five storage classes:

Storage class	Encoding
NULL	The absence of a value.
INTEGER	A signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on magnitude.
REAL	An IEEE 754 64-bit double-precision floating-point number.
TEXT	A variable-length string in UTF-8 (default), UTF-16BE, or UTF-16LE.
BLOB	A variable-length sequence of bytes, stored exactly as supplied.

There is no separate Boolean class; the values TRUE and FALSE are recognised by the parser and stored as the integers 1 and 0. There is no separate date or time class; date and time values are conventionally stored as TEXT in ISO 8601 form, as REAL Julian day numbers, or as INTEGER Unix epochs, with the built-in date functions accepting any of the three (see Built-in functions).

The storage class is a property of the value, not of the column. A single column may, under the default rules, contain values of several storage classes — text in some rows, integers in others — and aggregate functions and comparisons treat them according to a defined ordering.

Type affinity

A column’s affinity is the storage class the engine prefers when storing a value into the column. SQLite computes a column’s affinity from its declared type by a small set of textual rules:

1. If the declared type contains the substring INT, the affinity is INTEGER.
2. Otherwise, if the declared type contains CHAR, CLOB, or TEXT, the affinity is TEXT.
3. Otherwise, if the declared type contains BLOB or no type is declared, the affinity is BLOB.
4. Otherwise, if the declared type contains REAL, FLOA, or DOUB, the affinity is REAL.
5. Otherwise, the affinity is NUMERIC.

The rules are textual and are applied in this order. As a consequence, every type name accepted by other database engines maps to one of the five SQLite affinities, even if the engine cannot enforce length, precision, or scale.

Declared type	Affinity
INTEGER, INT, BIGINT, TINYINT, INT2, INT8	INTEGER
TEXT, VARCHAR(255), CHAR(20), CLOB	TEXT
BLOB, <no type>	BLOB
REAL, DOUBLE, FLOAT, DOUBLE PRECISION	REAL
NUMERIC, DECIMAL(10,2), BOOLEAN, DATE, DATETIME	NUMERIC

When a value is inserted into a column, SQLite applies a conversion attempt:

• A column with INTEGER affinity converts a text representation of an integer ('42') to an integer; if the text cannot be converted, the value is stored as text.
• A column with TEXT affinity converts numbers to their text representation.
• A column with BLOB affinity stores the value unchanged.
• A column with REAL affinity converts integers to floating-point (and vice versa where exact).
• A column with NUMERIC affinity stores the value as INTEGER if it can be represented exactly without loss, otherwise as REAL, otherwise as TEXT.

Crucially, the conversion is attempted; if the value cannot be converted, the value is stored as is. A column declared INTEGER may therefore contain a string in some rows. The exception is INTEGER PRIMARY KEY, which is treated specially (it aliases the rowid) and does enforce integer storage.

CREATE TABLE example (
    id   INTEGER PRIMARY KEY,
    n    INTEGER,
    name TEXT,
    blob BLOB
);

INSERT INTO example VALUES (1, '42',     'Ada',   X'cafe');
INSERT INTO example VALUES (2, 'forty',  3.14,    X'beef');
SELECT id, n, typeof(n), name, typeof(name) FROM example;
-- 1|42|integer|Ada|text
-- 2|forty|text|3.14|text

The typeof() function returns the storage class of an expression and is the conventional diagnostic for this kind of question.

STRICT tables

STRICT tables, introduced in version 3.37 (2021), enforce conventional column typing. A STRICT table is declared with the keyword STRICT after the closing parenthesis of the column list. The declared type of each column must be one of the six accepted strict types — INT, INTEGER, REAL, TEXT, BLOB, or ANY — and inserts that violate the declared type are rejected.

CREATE TABLE order_strict (
    id     INTEGER PRIMARY KEY,
    amount REAL    NOT NULL,
    note   TEXT
) STRICT;

INSERT INTO order_strict VALUES (1, 'forty-two', 'a note');
-- Error: stored type for column "amount" is TEXT but declared type is REAL

STRICT tables behave like the type system of any conventional SQL engine: if the declared type is REAL, only values that can be stored as REAL are accepted. The ANY declared type permits any of the five storage classes, providing an opt-out within an otherwise strict table. The recommendation in new code is to declare STRICT tables unless there is a specific reason to want the permissive behaviour.

NULL and the typeof function

NULL is the absence of a value. It is not a value of any type — typeof(NULL) returns the string 'null'. The treatment of NULL in comparisons, aggregates, and indexes is described in NULL semantics; the present page notes only that NULL is a distinct storage class and that any column without a NOT NULL constraint may contain it.

SELECT typeof(NULL), typeof(1), typeof(1.0), typeof('x'), typeof(X'ff');
-- null|integer|real|text|blob

Booleans

There is no separate Boolean storage class. SQLite recognises the literals TRUE and FALSE (since version 3.23) as syntactic sugar for 1 and 0, and the values returned by comparison and logical operators are integers, with 1 denoting true and 0 denoting false. Tests for truthiness in WHERE clauses follow the SQL convention: a value of 0 (or 0.0, or an empty string, or NULL) is treated as false, anything else as true.

CREATE TABLE feature (id INTEGER PRIMARY KEY, enabled BOOLEAN NOT NULL);
INSERT INTO feature (enabled) VALUES (TRUE), (FALSE), (1), (0);
SELECT id, enabled, typeof(enabled) FROM feature;
-- 1|1|integer
-- 2|0|integer
-- 3|1|integer
-- 4|0|integer

The BOOLEAN declared type has NUMERIC affinity and stores its values as integers.

Date and time

There is no separate date or time storage class. SQLite stores date and time values in any of three encodings — text in ISO 8601 form, real-valued Julian day numbers, or integer Unix epochs — and the built-in date functions (date, time, datetime, julianday, unixepoch, strftime) accept any of the three. The conventional choice for new code is text in ISO 8601 form, because it sorts correctly under string comparison, is human-readable, and round-trips through text-only export.

CREATE TABLE event (
    id INTEGER PRIMARY KEY,
    occurred_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

INSERT INTO event DEFAULT VALUES;
SELECT id, occurred_at FROM event;
-- 1|2026-05-08T14:23:01.234Z

The date functions are covered in Built-in functions. For reporting, strftime('%Y-%m', occurred_at) extracts a year-month bucket; for arithmetic, julianday(b) - julianday(a) returns the number of days between two text-encoded dates.

Comparison and sort order

Comparisons between values of different storage classes follow a defined ordering:

1. NULL is less than any non-NULL value.
2. INTEGER and REAL values are less than TEXT values.
3. TEXT values are less than BLOB values.
4. Two values of the same class are compared numerically (for INTEGER and REAL), or by collation (for TEXT), or byte-by-byte (for BLOB).

SELECT '2' < 10;        -- 0 (false): '2' is text, 10 is integer; text > integer
SELECT 2 < 10;          -- 1 (true): both integers, numeric comparison
SELECT cast('2' AS INTEGER) < 10;  -- 1 (true): explicit cast

The comparison rules are the source of a category of bug in which a numeric column unexpectedly contains text values: the offending rows compare incorrectly with the integer-valued rows. The remedy is STRICT tables or explicit CAST expressions.

CAST

The CAST expression converts a value to a target type:

SELECT CAST('42' AS INTEGER);      -- 42
SELECT CAST('forty' AS INTEGER);   -- 0  (silent: non-numeric becomes zero)
SELECT CAST(3.7 AS INTEGER);       -- 3  (truncation toward zero)
SELECT CAST(NULL AS INTEGER);      -- NULL

CAST rounds toward zero rather than to the nearest integer; values that cannot be converted become 0 or 0.0 rather than producing an error. The CAST expression is the canonical way to coerce a value’s storage class within a query and is used liberally where the lax type affinity might otherwise produce a surprise.

Type affinity in summary

The discipline can be summarised as a contract between the application and the engine. The engine guarantees: that the storage class of every stored value is recoverable through typeof; that comparisons follow a defined cross-class ordering; that conversions on insert are attempted but not enforced under the default rules; and that STRICT tables and INTEGER PRIMARY KEY columns behave as a programmer arriving from a strictly typed engine would expect. The application is responsible for not relying on the lax behaviour where data integrity is at stake, and for declaring STRICT tables in the schemas of new applications unless there is a specific reason to want the permissive form.