SQLite storage classes and type affinity

Every value SQLite stores falls into one of five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. There is no separate boolean type and no native date type. Booleans are integers, and dates are usually text.

That surprises people coming from PostgreSQL, where BOOLEAN and TIMESTAMP are first-class types. SQLite is more flexible, and that flexibility cuts both ways.

Type affinity on ordinary tables

On a non-strict table, the type you declare in CREATE TABLE sets a column’s affinity. SQLite tries to convert incoming values toward that affinity, but it can still store a value with a different storage class if conversion fails.

Try it on an ordinary table:

CREATE TABLE demo (count INTEGER);
INSERT INTO demo (count) VALUES ('hello');
SELECT typeof(count) FROM demo;
-- text

SQLite accepted the string 'hello' in an integer column and stored it as text. No error, no warning. Your application now has a column that was supposed to hold numbers but contains strings.

STRICT tables reject mismatches

A STRICT table tells SQLite to reject values that do not match the declared type. The same insert on a strict table fails immediately:

CREATE TABLE demo_strict (count INTEGER) STRICT;
INSERT INTO demo_strict (count) VALUES ('hello');
-- Runtime error: cannot store TEXT value in INTEGER column demo_strict.count

STRICT tables require SQLite 3.37 or newer. Check your version with SELECT sqlite_version(); if the shell reports a syntax error.

A realistic schema pattern

Here is how I model booleans and timestamps in SQLite:

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  completed INTEGER NOT NULL DEFAULT 0
    CHECK (completed IN (0, 1)),
  created_at TEXT NOT NULL
) STRICT;

completed stores 0 or 1, enforced by the CHECK constraint. created_at stores ISO-8601 text such as 2026-07-29T14:30:00Z. SQLite compares and sorts that format correctly as text when you keep the format consistent.

My advice: pick one representation for each concept, enforce it with CHECK constraints where you can, and use the same representation everywhere in your application. Strict tables catch type mistakes at insert time instead of letting them pile up silently.