SQLite data types

SQLite data types work differently from other databases. Instead of enforcing a strict column type on every value, SQLite uses a concept called type affinity: a column has a preferred type, but you can store any kind of value in it. This surprises developers coming from PostgreSQL or MySQL.

The five storage classes

SQLite stores every value as one of five storage classes. These are what actually end up on disk, regardless of what column type you declared.

| Storage class | Description | |---------------|-------------| | NULL | A missing value | | INTEGER | Signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the value | | REAL | 8-byte IEEE 754 floating-point number | | TEXT | String encoded as UTF-8 or UTF-16 | | BLOB | Raw bytes, stored exactly as provided |

A value's storage class is determined by what you insert, not by the column's declared type.

Type affinity

Each column has an affinity, which is the type SQLite prefers to store in that column. When you insert a value, SQLite tries to coerce it to the column's affinity. If the coercion succeeds and produces a valid value, SQLite stores the converted value. If not, SQLite stores the value as-is.

There are five affinities: TEXT, NUMERIC, INTEGER, REAL, and BLOB (also called NONE).

SQLite determines a column's affinity from the declared type name using these rules, checked in order:

1. If the type name contains INT: INTEGER affinity
2. If the type name contains CHAR, CLOB, or TEXT: TEXT affinity
3. If the type name contains BLOB, or if no type is specified: BLOB affinity
4. If the type name contains REAL, FLOA, or DOUB: REAL affinity
5. Otherwise: NUMERIC affinity

This means VARCHAR(255) gets TEXT affinity (it contains CHAR), DOUBLE PRECISION gets REAL affinity, and DECIMAL gets NUMERIC affinity.

| Declared type | Affinity | |---------------|----------| | INTEGER, INT, BIGINT | INTEGER | | TEXT, VARCHAR, CHAR, CLOB | TEXT | | BLOB (or no type) | BLOB | | REAL, FLOAT, DOUBLE | REAL | | NUMERIC, DECIMAL, BOOLEAN, DATE | NUMERIC |

What this means in practice

Because SQLite coerces values to match affinity, inserting '42' into an INTEGER column stores the integer 42, not the string '42'. The typeof() function shows you the actual storage class of a value.

Notice that '49.99' (a string) was coerced to a REAL, and '120' (a string) was coerced to an INTEGER. SQLite handled the conversion because the affinity rules allowed it.

Common type declarations

Even though SQLite doesn't enforce types, use sensible column declarations. ORMs, migration tools, and other developers rely on them. The conventions are:

• INTEGER for IDs, counts, and whole numbers
• REAL for prices, measurements, and other floating-point values
• TEXT for strings, dates, and any human-readable data
• BLOB for binary data like images or serialized objects
• NUMERIC when a column might hold either integers or decimals

Dates and times

SQLite has no native date or time type. You have three options for storing temporal data:

| Format | Example | Affinity | |--------|---------|----------| | ISO 8601 text | '2026-05-23' | TEXT | | Unix timestamp | 1748044800 | INTEGER | | Julian day | 2461197.5 | REAL |

SQLite's built-in date functions (date(), datetime(), strftime()) work with all three formats. The TEXT format is the most readable and sorts correctly with standard string comparison. Use TEXT with ISO 8601 unless you have a specific reason to use the others.

CREATE TABLE events (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  starts_at TEXT  -- Store as '2026-05-23T09:00:00'
);

Booleans

SQLite has no boolean type. Store boolean values as integers: 0 for false and 1 for true.

SQLite does accept the keywords TRUE and FALSE, but they are stored as 1 and 0 respectively.

CREATE TABLE users (
  id        INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  is_active INTEGER DEFAULT 1
);

INSERT INTO users VALUES (1, 'Alice', TRUE);
-- Stores 1, not the text 'TRUE'

SELECT is_active, typeof(is_active) FROM users;
-- is_active: 1, typeof: integer

STRICT tables

SQLite 3.37.0 (released November 2021) added strict tables. When you append STRICT to a CREATE TABLE statement, SQLite enforces column types and rejects values that cannot be stored as the declared type.

CREATE TABLE orders (
  id       INTEGER PRIMARY KEY,
  amount   REAL    NOT NULL,
  status   TEXT    NOT NULL
) STRICT;

With a strict table, inserting 'hello' into the amount column raises an error instead of storing the string. The valid types in a strict table are: INT, INTEGER, REAL, TEXT, BLOB, and ANY. The ANY type accepts any value without coercion.

Differences from PostgreSQL and MySQL

| Feature | SQLite | PostgreSQL | MySQL | |---------|--------|------------|-------| | Type enforcement | Flexible by default | Strict | Strict | | Opt-in strict mode | Yes (STRICT tables, 3.37+) | No (always strict) | No (always strict) | | Native boolean type | No (use INTEGER) | Yes (BOOLEAN) | Yes (TINYINT(1) / BOOLEAN) | | Native date/time types | No (use TEXT, INTEGER, or REAL) | Yes (DATE, TIMESTAMP, etc.) | Yes (DATE, DATETIME, etc.) | | UUID type | No (use TEXT) | Yes (UUID) | No (use CHAR(36)) | | JSON type | No (use TEXT) | Yes (JSON, JSONB) | Yes (JSON) |

The biggest practical difference: PostgreSQL and MySQL reject a value that doesn't match the column type. SQLite stores it anyway unless the table was created with STRICT.

Quick reference

| Goal | Type to use | |------|-------------| | Auto-incrementing ID | INTEGER PRIMARY KEY | | Whole numbers | INTEGER | | Decimal numbers | REAL | | Strings | TEXT | | Dates (recommended) | TEXT (ISO 8601) | | Dates (compact) | INTEGER (Unix timestamp) | | Booleans | INTEGER (0 or 1) | | Binary data | BLOB | | Enforce types | Add STRICT to CREATE TABLE |