SQLite Type Affinity Reference

Understand how SQLite types your columns

Unlike most databases, SQLite does not strictly type columns. Instead each column has a type affinity derived from its declared type name, and each value is stored in one of five storage classes. This tool resolves any declared type name to its affinity, shows which of the five rules matched, and lists the storage classes — so you can predict how SQLite will store and convert your data. It runs entirely in your browser.

The five affinity rules

SQLite applies these rules to the declared type name, in order, stopping at the first match. The tests are case-insensitive substring checks:

1. type name contains "INT"                         -> INTEGER affinity
2. contains "CHAR" or "CLOB" or "TEXT"              -> TEXT affinity
3. contains "BLOB", or no type was declared         -> BLOB affinity
4. contains "REAL" or "FLOA" or "DOUB"             -> REAL affinity
5. otherwise                                        -> NUMERIC affinity

Quick affinity lookup table

Note that BOOLEAN and DATE — types many developers expect to behave like dedicated types — fall through to NUMERIC affinity and store values as integers. true inserts as 1, false as 0, and date strings store as text unless you use Julian day numbers.

What affinity actually controls

Affinity is a preference, not an enforcement. It guides type coercion when a value is inserted:

• INTEGER affinity: stores a value as an integer if it is losslessly representable as one (so inserting 3.0 stores 3).
• TEXT affinity: converts integers and reals to their text representation before storing.
• BLOB affinity: stores the value exactly as provided, with no conversion.
• NUMERIC affinity: tries to store as INTEGER first, then REAL, then TEXT as a last resort.
• REAL affinity: converts integers to floating-point.

Where this catches developers by surprise

DECIMAL(10,2) gets NUMERIC, not a fixed-point type. SQLite has no native decimal type. A column declared as DECIMAL(10,2) will store whole-number values as integers and decimal values as IEEE 754 floating-point, which means 0.1 + 0.2 will not equal 0.3. Store currency as integer cents instead.

BOOLEAN is just NUMERIC. There is no boolean storage class. TRUE and FALSE are stored as 1 and 0. Querying WHERE flag = true works because SQLite resolves true to 1, but WHERE flag = 'true' does not — it compares an integer to a string.

DATE and TIMESTAMP are NUMERIC or TEXT. SQLite has no dedicated date type. The built-in date functions accept text in ISO 8601 format, Julian day numbers as REAL, or Unix timestamps as INTEGER. The affinity doesn’t help here; consistency in your insertion format is what keeps date comparisons working.

STRICT tables (SQLite 3.37+)

If you want real type enforcement, declare the table STRICT:

CREATE TABLE events (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  score REAL
) STRICT;

STRICT tables enforce the declared type exactly and reject values that do not conform. Allowed types in a STRICT table are INT, INTEGER, REAL, TEXT, BLOB, and ANY. The affinity rules no longer apply inside STRICT tables.

INTEGER PRIMARY KEY — the special case

A column declared as INTEGER PRIMARY KEY becomes an alias for SQLite’s internal rowid. It is the most efficient primary key, uses zero extra storage beyond the rowid B-tree, and auto-increments by default. Using INT PRIMARY KEY (without the second E) does not have the same effect — it creates a regular column with a unique index rather than a rowid alias.