SQLite's Type System — Type Affinity, Not Strict Typing

This is the first item from sqlite1-1's own roadmap table, and it's genuinely the most surprising difference for anyone coming from mysql2/mysql3 or postgres1.

What "Type Affinity" Actually Means

MySQL and Postgres both use static, strict column typing — a column declared INTEGER can only ever store integer values (or NULL); inserting 'hello' into it raises a real, hard error at insert time.

SQLite works differently: it's dynamically typed at the value level, not the column level. A column's declared type is really just a hint — a type affinity — that SQLite uses to decide how to try to convert an incoming value, but it does not reject a value just because it doesn't match. A column declared INTEGER can still end up storing a text string, if SQLite's own conversion rules can't reasonably coerce the value — it's stored as-is, using whichever of SQLite's five actual storage classes (NULL, INTEGER, REAL, TEXT, BLOB) genuinely fits it.

There are five type affinities — TEXT, NUMERIC, INTEGER, REAL, BLOB — assigned to a column based on textual matching rules applied to its declared type (a declared type containing "INT" gets INTEGER affinity; "CHAR", "CLOB", or "TEXT" gets TEXT affinity, and so on).

A Concrete Demonstration

CREATE TABLE example (id INTEGER, quantity INTEGER);
INSERT INTO example VALUES (1, 'five');
SELECT * FROM example;
-- 1 | five

This succeeds in SQLite by default, even though 'five' is not a number — SQLite only tries to coerce a value toward its column's own affinity, and simply stores it as-is when coercion isn't reasonably possible, rather than rejecting the insert outright. The identical statement against a MySQL table in strict mode, or against a Postgres table, would fail immediately.

Why This Is a Real Design Trade-off, Not Just a Flaw

This isn't an accident or an oversight — it's documented, deliberate design, tracing directly back to sqlite1-1's own embedding material. SQLite was built as a general-purpose, embedded storage engine meant to work smoothly from dynamically-typed scripting languages, where a value of the "wrong" type shouldn't necessarily hard-fail an entire operation the way it might need to in a strict, statically-typed system serving many different, independently-written applications with different assumptions about the data.

This flexibility is genuinely useful for SQLite's own actual use cases — loosely-typed application data, rapid prototyping, embedded contexts where the application itself is the sole writer and already controls data quality directly. It's also, honestly, the single most commonly cited source of real bugs for anyone arriving from a strict-typing background. Both things are true at once — a real, double-edged design choice worth respecting rather than dismissing outright as a mistake.

STRICT Tables — The Modern Opt-In Fix

SQLite 3.37 (2021) added STRICT tables — an opt-in mechanism giving real, MySQL/Postgres-like enforcement to a specific table:

CREATE TABLE example (id INTEGER, quantity INTEGER) STRICT;
INSERT INTO example VALUES (1, 'five');
-- Error: cannot store TEXT value in INTEGER column quantity

It's worth naming explicitly that this is opt-in and per-table, not the default — most existing SQLite code, and most tutorials, still use the traditional, flexible typing described above. Understanding both modes matters, not just the newer, stricter one.

The Storage Classes Underneath

Regardless of a column's declared type or affinity, every value in SQLite ultimately has one of five actual storage classes: NULL, INTEGER, REAL, TEXT, or BLOB. Affinity influences which storage class a value ends up using — it doesn't guarantee a fixed class the way MySQL/Postgres's own strict types do.

Hands-On Exercises