SQLite Data Types: Storage Classes, Type Affinity and STRICT Tables

Ninad Pathak

October 7, 2026

SQLite will store the word hello in an INTEGER column without complaint. It treats a column’s type as a preference, not a rule. In this article I’ll walk you through how its types work and how to make it enforce them when you need to.

TLDR

SQLite stores every value in one of five storage classes, such as INTEGER or TEXT.
A column’s declared type only sets its type affinity, a preference SQLite applies when it can convert a value without loss.

Length limits such as VARCHAR(255) are ignored, and values of the wrong type are still stored.
Use STRICT tables (SQLite 3.37.0 and later) or CHECK constraints to enforce types.
There’s no boolean or date type. Use integers or text, with CHECK constraints where needed.

What data types does SQLite support?

SQLite supports five storage classes, and every value belongs to exactly one of them. In SQLite’s words, “the datatype of a value is associated with the value itself, not with its container” (Datatypes In SQLite). typeof() tells you which class a value has:

sqlite> SELECT typeof(NULL) AS a, typeof(42) AS b, typeof(4.2) AS c, typeof('42') AS d, typeof(x'2A') AS e;
a     b        c     d     e
----  -------  ----  ----  ----
null  integer  real  text  blob

Storage class	What it holds (from the docs)
NULL	A NULL value
INTEGER	“a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value”
REAL	“a floating point value, stored as an 8-byte IEEE floating point number”
TEXT	“a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE)”
BLOB	“a blob of data, stored exactly as it was input”

There is no separate boolean or date class. Booleans are stored as the integers 1 and 0, and dates as text or numbers (Boolean, Date and Time).

SQLite column type syntax

You declare a type after each column name, as in any SQL database. Add STRICT after the closing parenthesis to make SQLite enforce it:

CREATE TABLE table_name (column_name TYPE_NAME [constraints], ...);
CREATE TABLE table_name (column_name INT | INTEGER | REAL | TEXT | BLOB | ANY, ...) STRICT;

SQLite turns the type name into one of five affinities by applying these rules in order (Determination of column affinity):

Rule	If the declared type…	Affinity
1	contains `INT`	INTEGER
2	contains `CHAR`, `CLOB` or `TEXT`	TEXT
3	contains `BLOB`, or no type is given	BLOB
4	contains `REAL`, `FLOA` or `DOUB`	REAL
5	anything else	NUMERIC

So BIGINT and UNSIGNED BIG INT get INTEGER affinity by rule 1, and NVARCHAR(100) gets TEXT by rule 2.

How type affinity works

To show you how this works, I will insert the same kinds of values into columns with different declared types, and check what SQLite stored with typeof().

Step 1: Values convert to the column’s affinity when they can

When you insert a value, SQLite converts it to the column’s affinity if the conversion is lossless:

sqlite> CREATE TABLE t (i INTEGER, r REAL, txt TEXT, n NUMERIC, b BLOB);
sqlite> INSERT INTO t VALUES ('42', '42', 42, '42.0', '42');
sqlite> SELECT typeof(i), typeof(r), typeof(txt), typeof(n), typeof(b) FROM t;
typeof(i)  typeof(r)  typeof(txt)  typeof(n)  typeof(b)
---------  ---------  -----------  ---------  ---------
integer    real       text         integer    text

Step 2: Values that can’t convert are stored as they are

If the conversion isn’t possible, the value is stored as it is. An INTEGER column keeps text:

sqlite> INSERT INTO t (i) VALUES ('forty-two');
sqlite> SELECT i, typeof(i) FROM t WHERE rowid = 2;
i          typeof(i)
---------  ---------
forty-two  text

Step 3: Check what each type name does

STRING has NUMERIC affinity under rule 5, so a numeric-looking string becomes an integer. Type names such as BOOLEAN and DATETIME are NUMERIC too. A column with no type keeps values as given. Here each of eleven columns has a different declared type and gets a short text or number value. typeof() shows what SQLite stored:

sqlite> CREATE TABLE a (c1 INT, c2 BIGINT, c3 VARCHAR(255), c4 CLOB, c5 DOUBLE, c6 FLOAT, c7 DECIMAL(10,2), c8 BOOLEAN, c9 DATETIME, c10 STRING, c11);
sqlite> INSERT INTO a VALUES ('1', '1', 1, 1, '1', '1', '1.50', 'true', '2026-10-06', '1', '1');
sqlite> SELECT typeof(c1), typeof(c2), typeof(c3), typeof(c4), typeof(c5), typeof(c6), typeof(c7), typeof(c8), typeof(c9), typeof(c10), typeof(c11) FROM a;
typeof(c1)  typeof(c2)  typeof(c3)  typeof(c4)  typeof(c5)  typeof(c6)  typeof(c7)  typeof(c8)  typeof(c9)  typeof(c10)  typeof(c11)
----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  -----------  -----------
integer     integer     text        text        real        real        real        text        text        integer      text

Step 4: Enforce types with a STRICT table

The column type in a STRICT table “must be one of the following: INT INTEGER REAL TEXT BLOB ANY” (STRICT tables). Values that convert losslessly are still converted, and anything else is rejected. An ANY column takes any value:

sqlite> CREATE TABLE s (id INTEGER PRIMARY KEY, qty INTEGER, price REAL, note TEXT, extra ANY) STRICT;
sqlite> INSERT INTO s (qty, price, note, extra) VALUES ('7', 3, 'ok', 'anything');
sqlite> SELECT typeof(qty), typeof(price), typeof(extra) FROM s;
typeof(qty)  typeof(price)  typeof(extra)
-----------  -------------  -------------
integer      real           text
sqlite> INSERT INTO s (qty) VALUES ('seven');
Error: stepping, cannot store TEXT value in INTEGER column s.qty (19)
sqlite> CREATE TABLE s2 (d DATETIME) STRICT;
Error: in prepare, unknown datatype for s2.d: "DATETIME"

Examples

These examples cover the types people look for and don’t find in SQLite.

Large integers (BIGINT)

An INTEGER holds up to 8 bytes, so the largest value is 9223372036854775807. Anything larger is stored as REAL. Arithmetic that overflows switches to floating point, because SQLite does integer arithmetic only “if both operands are integers and no overflow would result” (operators):

sqlite> SELECT 9223372036854775807 AS max_int, typeof(9223372036854775807) AS t1, typeof(9223372036854775808) AS t2;
max_int              t1       t2
-------------------  -------  ----
9223372036854775807  integer  real
sqlite> SELECT 9223372036854775807 + 1;
9223372036854775807 + 1
-----------------------
9.22337203685478e+18

Enums with a CHECK constraint

SQLite has no ENUM type. A CHECK constraint with IN gives you the same protection:

sqlite> CREATE TABLE tickets (id INTEGER PRIMARY KEY, status TEXT NOT NULL CHECK (status IN ('open', 'closed', 'on_hold')));
sqlite> INSERT INTO tickets (status) VALUES ('open');
sqlite> INSERT INTO tickets (status) VALUES ('pending');
Error: stepping, CHECK constraint failed: status IN ('open', 'closed', 'on_hold') (19)

Booleans and dates

Store booleans as 1 and 0, and dates as ISO-8601 text or Unix seconds. Both have their own pages: SQLite boolean and SQLite date and time.

Edge cases

Type affinity has two more consequences that catch people out.

Length limits are ignored

SQLite’s documentation says that “numeric arguments in parentheses that following the type name (ex: ‘VARCHAR(255)’) are ignored by SQLite” (Affinity name examples). A VARCHAR(5) column stores 11 characters:

sqlite> CREATE TABLE names (name VARCHAR(5)); INSERT INTO names VALUES ('Bartholomew');
sqlite> SELECT name, length(name) FROM names;
name         length(name)
-----------  ------------
Bartholomew  11

To enforce a maximum length, add a constraint such as CHECK (length(name) <= 5).

Comparing values of different types

Without column affinity in play, SQLite compares by storage class: “An INTEGER or REAL value is less than any TEXT or BLOB value” (Comparison expressions). So the text '10' sorts before '9', and the integer 10 is less than the text '9':

sqlite> SELECT 10 < 9 AS ints, '10' < '9' AS texts, 10 < '9' AS mixed;
ints  texts  mixed
----  -----  -----
0     1      1

Store numbers as numbers. Use STRICT tables or CHECK constraints if the data comes from somewhere you don’t control.

Conclusion

SQLite has five storage classes, and a declared type only sets a preference. That flexibility is convenient until bad data gets in. For tables where the type matters, add STRICT or CHECK constraints. Use typeof() to see what’s really stored.