Schema and data
SQLite storage classes and type affinity
Use SQLite storage classes, type affinity, and STRICT tables without inventing native boolean or date types.
8 minute lesson
SQLite values use five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB.
In an ordinary table, a declared type gives a column an affinity. SQLite tries to convert values toward that affinity, but it can still store a value with another storage class.
Use a STRICT table when you want SQLite to reject values that do not match the declared type:
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;STRICT tables require SQLite 3.37 or newer. Check with SELECT sqlite_version(); if the shell reports a syntax error.
SQLite has no separate boolean or date storage class. This schema stores booleans as 0 or 1 and dates as consistently formatted text, such as 2026-07-29T14:30:00Z.
My advice is to choose one representation, enforce it with constraints where possible, and use it everywhere in the application.
Lesson completed