TopolearnSQLite's flexible typing surprises everyone coming from other databases and causes subtle data bugs.
Most databases enforce a column's type. SQLite (in ordinary tables) uses dynamic typing: each value has one of five storage classes — NULL, INTEGER, REAL, TEXT, BLOB — and a column can hold any of them.
The declared type gives the column a type affinity, a preference SQLite applies when converting values on insert:
- Type name contains
INT→ INTEGER affinity. - Contains
CHAR,CLOBorTEXT→ TEXT. - Contains
BLOB, or no type → BLOB (store as given). - Contains
REAL,FLOAorDOUB→ REAL. - Anything else (e.g.
NUMERIC,DECIMAL,BOOLEAN,DATE) → NUMERIC.
So in an INTEGER column, the text '42' is converted to the integer 42, but 'hello' is stored as text, without complaint. VARCHAR(10) has TEXT affinity and the 10 is ignored: longer strings are stored in full.
There are no dedicated date or boolean types. Store dates as ISO-8601 text ('2026-05-03 14:00:00'), Unix time integers or Julian day reals, and use the date functions (date(), datetime(), strftime(), unixepoch()). Booleans are integers 0 and 1; TRUE and FALSE are keywords for them.
typeof(x) reveals a value's actual storage class. If you want enforcement, use CHECK constraints or STRICT tables (a later lesson).
Example
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE t (n INTEGER, s VARCHAR(3), d DATE, x)")
con.executemany("INSERT INTO t VALUES (?, ?, ?, ?)", [
("42", "abcdef", "2026-05-03", 1.0),
("hello", 7, "2026-05-03", "1.0"),
])
for row in con.execute("SELECT n, typeof(n), s, typeof(s), typeof(d), typeof(x) FROM t"):
print(row)
# (42, 'integer', 'abcdef', 'text', 'text', 'real') '42' became 42, no length limit
# ('hello', 'text', '7', 'text', 'text', 'text') no error for 'hello' in INTEGER
print(con.execute("SELECT date('2026-01-31', '+1 month'), TRUE + TRUE").fetchone())
# ('2026-03-03', 2)Remember
- Values have storage classes; columns only have an affinity
- VARCHAR(n) lengths are not enforced
- Dates are text, integers or reals; booleans are 0/1
Watch out for
- Relying on a column type to reject bad data in a non-STRICT table.