Gera Tools

SQLite Type Affinity Reference

SQLite type affinity rules and storage classes with conversion behavior.

Reference and interactive resolver for SQLite's five storage classes and type affinity rules — enter any declared column type and see which of the five rules applies and the resulting affinity.

What is type affinity in SQLite?

SQLite uses dynamic typing: a column has an affinity that recommends a storage class for values, rather than a strict type. The affinity is derived from the declared type name using five rules and influences how inserted values are converted.

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.

How it works

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

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

So VARCHAR(255) matches rule 2 (it contains CHAR) and gets TEXT affinity, while BIGINT matches rule 1 and gets INTEGER affinity. The affinity then guides conversion: an INTEGER column stores a value with no fractional part as an integer, a TEXT column converts numbers to their text form, and a BLOB column stores data with no coercion at all.

Tips and notes

  • The storage class is per value, so the same column can legitimately hold an integer in one row and a string in another.
  • A column declared with an unrecognised type name like juliandate falls through to rule 5 and gets NUMERIC affinity — not an error.
  • If you want real type enforcement, create the table with the STRICT keyword and use only INT, INTEGER, REAL, TEXT, BLOB or ANY.
  • INTEGER PRIMARY KEY is special: it becomes an alias for the internal rowid and is the most efficient primary key.