Storage Classes and Dynamic Typing: The Type Belongs to the Value

Learning outcomes

If you learned SQL on a rigidly typed database, you may expect the declared type of a column to determine the type of every value stored there. SQLite starts from a different rule: the runtime value carries its storage class, while the column declaration usually supplies an affinity that influences conversions. This is not “no typing.” It is a different typing model, and the distinction matters whenever data enters, leaves, compares, or crosses an application boundary.

Begin with the rigid-typing expectation

Imagine a column declared INTEGER. In many database systems, inserting arbitrary text into that column is rejected or converted according to rigid type rules. In an ordinary SQLite table, the declaration gives the column INTEGER affinity. SQLite will try useful numeric conversions, but a value that cannot be converted may still be stored using a different storage class.

DROP TABLE IF EXISTS dynamic_demo;CREATE TABLE dynamic_demo (    value INTEGER);INSERT INTO dynamic_demo(value) VALUES    (42),    (3.5),    ('forty-two'),    (x'2A'),    (NULL);SELECT rowid, quote(value) AS displayed, typeof(value) AS storage_classFROM dynamic_demoORDER BY rowid;

The important result is not the exact visual formatting chosen by your shell. The engine-level result is that the five rows can be represented as INTEGER, REAL, TEXT, BLOB, and NULL. The column declaration still matters—Lesson 2 will show the conversions it requests—but it does not permanently stamp one physical type onto every value in an ordinary table.

The five storage classes describe runtime SQL values

SQLite exposes five fundamental value categories. “Storage class” is the term used in SQLite’s SQL documentation; at the C API boundary you will also see corresponding fundamental datatype codes. INTEGER values are signed 64-bit integers, REAL values use IEEE floating-point representation, TEXT values are strings, BLOB values are uninterpreted bytes, and NULL represents the SQL null value.

SELECT    typeof(NULL)     AS t_null,    typeof(42)       AS t_integer,    typeof(42.5)     AS t_real,    typeof('42')     AS t_text,    typeof(x'3432')  AS t_blob;

Expected logical output is null | integer | real | text | blob. This query has no table at all, which proves that storage class belongs first to the value/expression, not to a table declaration.

typeof() is a microscope, not a schema command

The built-in typeof(X) function reports the storage class of the SQL value produced by expression X. It answers a runtime question. By contrast, PRAGMA table_xinfo and sqlite_schema answer schema questions such as “what type name did the designer declare?” Those two questions can legitimately have different answers.

DROP TABLE IF EXISTS type_probe;CREATE TABLE type_probe (    id      INTEGER PRIMARY KEY,    reading INTEGER);INSERT INTO type_probe(reading) VALUES    ('12'),    ('12.5'),    ('offline');PRAGMA table_xinfo('type_probe');SELECT id,       reading,       typeof(reading) AS actual_storage_classFROM type_probeORDER BY id;

table_xinfo continues to report the declared type INTEGER. The three inserted values are typically stored as INTEGER 12, REAL 12.5, and TEXT offline. INTEGER affinity converted the two numeric-looking strings but could not losslessly turn the word offline into a number, so the original text remained text.

Literals already have a value type before a table sees them

A literal is a value written directly in SQL source. The SQL parser determines an initial representation for that literal, then column affinity may request a conversion during insertion or comparison. This is why quoted and unquoted forms are not interchangeable.

SELECT 7,       typeof(7);SELECT 7.0,     typeof(7.0);SELECT '7',     typeof('7');SELECT '007',   typeof('007');SELECT x'303037', typeof(x'303037');SELECT NULL,    typeof(NULL);

A quoted numeric-looking value begins as TEXT. A BLOB literal begins as bytes. Later, if you insert '007' into a NUMERIC- or INTEGER-affinity column, SQLite may convert it to integer 7 and the leading zeros disappear. That is not the parser “forgetting” the string; it is the column affinity being applied after the literal has been produced.

Bound parameters also arrive as typed values

Chapter 2 introduced SQL parameters as the safe way to supply values. A database driver does more than escape text: its binding API tells SQLite what kind of SQL value is being supplied. The C interface has distinct bind operations for integer, floating point, text, BLOB, and NULL values, and language drivers map their own types onto those operations.

import sqlite3con = sqlite3.connect(':memory:')con.execute('CREATE TABLE bound_values(value)')samples = [None, 7, 7.25, '007', sqlite3.Binary(b'\x00\xff')]for item in samples:    con.execute('INSERT INTO bound_values(value) VALUES (?)', (item,))for row in con.execute(    'SELECT rowid, typeof(value), quote(value) FROM bound_values ORDER BY rowid'):    print(row)con.close()

With Python’s standard SQLite driver, these primitive values bind naturally as NULL, INTEGER, REAL, TEXT, and BLOB. Other languages use different host types and adapter APIs, but the same principle holds: the application should bind a value with the type it means. After binding, the target column’s affinity and constraints can still influence whether and how that value is stored.

Flexible typing can be useful—and can hide contract mistakes

SQLite’s flexible model is valuable when an application must preserve irregular source data, stage imports before validation, evolve a local file format gradually, or store heterogeneous values intentionally. The same flexibility can hide bugs when an application assumes every value in a column is numeric, every timestamp has one format, or every identifier arrived as text.

The right response is not “flexibility is bad” or “SQLite will handle it.” The right response is to decide where flexibility belongs: staging tables, ANY columns, final schema constraints, STRICT tables, or application validation.

Misconception: “SQLite ignores types completely”

That slogan is false and causes poor schema decisions. SQLite uses types in several concrete ways. Values always have one of the five storage classes. Ordinary columns have affinity rules that can convert values. Comparisons can apply affinity before comparing operands. CAST performs explicit conversion. Constraints can restrict valid values. STRICT tables enforce a rigid subset of declared types. Host APIs bind and retrieve typed values.

Lab: build a storage-class microscope

Use a disposable database so this experiment does not change the FieldNotes schema from Chapter 3. The column below has no declared type, so it does not request numeric or text conversion; that makes it useful for seeing the values you deliberately supply.

DROP TABLE IF EXISTS storage_probe;CREATE TABLE storage_probe (    label TEXT PRIMARY KEY,    value);INSERT INTO storage_probe(label, value) VALUES    ('null',    NULL),    ('integer', 9001),    ('real',    9001.25),    ('text',    '9001'),    ('blob',    x'39303031');SELECT label,       typeof(value) AS storage_class,       quote(value)  AS quoted_value,       length(value) AS logical_lengthFROM storage_probeORDER BY label;PRAGMA table_xinfo('storage_probe');

Verify two different facts. First, typeof(value) should expose all five storage classes across the rows. Second, schema metadata shows that the value column has no declared type. The observed runtime classes are therefore properties of the inserted values, not of five different columns.

Failure patterns and safe corrections

Summary and bridge to affinity

SQLite is typed at the value level: NULL, INTEGER, REAL, TEXT, and BLOB are observable runtime storage classes. Ordinary column declarations do not rigidly determine those classes, but they are not ignored. Their declared type names produce affinity, which is SQLite’s conversion preference. Lesson 2 turns that preference into deterministic rules you can derive from any declared type name.

References

• SQLite — Datatypes In SQLite.
• SQLite — Fundamental Datatypes (C Interface).
• SQLite — Binding Values To Prepared Statements.
• SQLite — Core Functions (typeof and quote).
• SQLite — STRICT Tables.
• SQLite — Release History.