How do SQLite storage classes differ from declared column types?

Not necessarily. In an ordinary, non-STRICT SQLite table, a declared column type determines its affinity: a storage preference that may cause conversions, but generally does not limit the column to values of one storage class. The value itself has a storage class, so “declared INTEGER” and “stored as INTEGER” are not interchangeable descriptions. [S1]

SQLite’s five storage classes are NULL, INTEGER, REAL, TEXT, and BLOB. [S1] For example, if you inspect a database and find a TEXT value in a column declared INTEGER, that describes the stored value; the declaration alone doesn’t prove every value has the same storage class.

Use this concept reference to distinguish the labels; it does not predict how a particular input will be stored:

• Declared type: The type name written in the table definition, such as INTEGER or TEXT.
• Affinity: The column’s storage preference, determined by its declared type in an ordinary table. SQLite lists TEXT, NUMERIC, INTEGER, REAL, and BLOB affinities. [S1]
• Storage class: The category of an individual value: NULL, INTEGER, REAL, TEXT, or BLOB. [S1]

Disposable examples of observations:

• score declared INTEGER → an inspected value is INTEGER.
• score declared INTEGER → another inspected value is TEXT.
• note declared TEXT → an inspected value is NULL.

These hypothetical observations show why the two labels should be recorded separately. They are not promises about which storage class SQLite will choose for a given input. The documentation establishes that affinity can cause conversions, but does not establish the outcome for every input-and-declaration combination. [S1]

Write down both the column declaration and the storage class you actually observed, then state what you expected. For example:

Column: score | declared type: INTEGER | observed storage class: TEXT | expected: INTEGER

That description is more precise than saying “the column is text,” which could mean either that its declared type is TEXT or that a particular value is stored as TEXT. Keep the observation tied to the specific row or value you checked. Affinity may cause conversions, but the declaration generally does not enforce one storage class in ordinary tables. [S1]

STRICT tables provide more rigid type enforcement than ordinary tables and have been available since SQLite 3.37.0. They still have rules and limitations, so don’t assume that STRICT means every declaration or input behaves exactly as you expect; consult the documentation for the specific rules that apply. [S1] The excerpted information does not list those detailed rules or establish the outcome for any particular value.

For a reliable note, record whether the table is ordinary or STRICT, the exact declared type, and the storage class of the value you observed. Keep expected behavior separate from observed behavior. That makes the distinction useful without treating a column’s declaration as proof of every value’s stored form.