Data Types & Type Affinity

This is the single most important behavioral difference between SQLite and databases like MySQL, so it's worth its own lesson before we create a single table. SQLite uses dynamic typing with type affinity — it is not statically, strictly typed by column the way MySQL is. Understanding this now will save you confusion in every lesson that follows.

The 5 Storage Classes

Every single value actually stored in SQLite has one of exactly five storage classes:

The 5 Type Affinities

A column's declared type — VARCHAR(255), INT, whatever you write in CREATE TABLE — does not lock that column to one storage class. Instead, SQLite looks at the declared type name and assigns the column one of five type affinities: a preference for how to store values, not a hard rule.

So title VARCHAR(255) gets TEXT affinity, id INTEGER gets INTEGER affinity, and rating DECIMAL(3,2) gets NUMERIC affinity — these are the same rules SQLite applies to every column you'll declare in this track.

Dynamic Typing in Action

Because affinity is only a preference, SQLite will, by default, still let you store a value of a completely different storage class in a column, no matter what type was declared:

CREATE TABLE demo (
  qty INTEGER
);

-- This looks wrong for an INTEGER column, but SQLite allows it by default:
INSERT INTO demo (qty) VALUES ('a dozen');

SELECT qty, typeof(qty) FROM demo;
-- qty = 'a dozen', typeof(qty) = 'text' -- stored as TEXT, not INTEGER

This surprises people coming from MySQL, where that INSERT would either fail outright or trigger implicit coercion rules. In SQLite, it just quietly stores what you gave it. The typeof() function, shown above, is genuinely useful for debugging exactly this kind of surprise.

STRICT Tables: Opting Into Real Enforcement

If you want MySQL-like type enforcement, SQLite 3.37 (2021) added the STRICT table option. A STRICT table rejects values that don't match a recognized declared type, instead of silently coercing or storing them as-is:

CREATE TABLE demo_strict (
  qty INTEGER
) STRICT;

-- With STRICT, this now fails instead of silently storing text:
INSERT INTO demo_strict (qty) VALUES ('a dozen');
-- Error: cannot store TEXT value in INTEGER column demo_strict.qty

Most existing SQLite tutorials and codebases use plain (non-STRICT) tables, but for new schemas where you want stronger guarantees, STRICT is worth reaching for.

No Native BOOLEAN, No Native DATE/DATETIME

Two gaps trip up almost everyone coming from another database, so it's worth flagging both now — each is covered in full detail later in this track: