Type affinity and dynamic typing

Most SQL database engines use static, rigid typing, where a value's datatype is determined by its container column; SQLite uses a dynamic type system in which the datatype is associated with the value itself. For tables not declared STRICT, each column still receives a type affinity from its declared type, by rules applied in order: a declared type containing "INT" gets INTEGER affinity; containing "CHAR", "CLOB", or "TEXT" gets TEXT affinity (VARCHAR contains "CHAR"); containing "BLOB" or having no declared type gets BLOB affinity; containing "REAL", "FLOA", or "DOUB" gets REAL affinity; anything else gets NUMERIC affinity. Rule order matters: "CHARINT" gets INTEGER affinity because the first matching rule wins, and "STRING" gets NUMERIC, not TEXT. SQLite applies type conversions based on affinity before comparisons, so an unexpected affinity changes query results, not just storage. Declare column types deliberately rather than relying on defaults (Koskamo editorial guidance). Scope: SQLite. Reviewed October 4, 2026.