SQLite Data Types: Storage Classes and Dynamic Typing

Five Storage Classes, Not Many Types

SQLite stores every value as one of five storage classes:

• NULL — the absence of a value.
• INTEGER — a signed whole number, 1 to 8 bytes depending on size.
• REAL — an 8-byte IEEE floating-point number.
• TEXT — a string, stored in the database's encoding (usually UTF-8).
• BLOB — raw bytes, stored exactly as you provided them.

That's it. There's no separate BOOLEAN, no DATETIME, no VARCHAR, no DECIMAL. Other databases have dozens of types; SQLite has five, and everything else is built on top of them.

CREATE TABLE samples (
    a INTEGER,
    b REAL,
    c TEXT,
    d BLOB
);

INSERT INTO samples VALUES (42, 3.14, 'hello', x'deadbeef');

SELECT a, typeof(a), b, typeof(b), c, typeof(c), d, typeof(d)
FROM samples;

typeof() reports the actual storage class of each value. You'll see integer, real, text, blob. Those four — plus null — are everything SQLite knows.

Typing Is Dynamic

Here's the part that surprises people coming from Postgres or MySQL. In SQLite (without STRICT), the type you declare on a column is more of a suggestion than a contract. The actual type lives with each value:

CREATE TABLE notes (
    id   INTEGER,
    body TEXT
);

INSERT INTO notes VALUES (1, 'a real note');
INSERT INTO notes VALUES ('two', 12345);

SELECT id, typeof(id), body, typeof(body) FROM notes;

Both rows were accepted. The id column holds an integer in one row and text in another; body holds text and an integer. SQLite happily stores values of any class in any column.

This is dynamic typing, and it's a deliberate design choice. It makes SQLite forgiving for prototyping and quick scripts. It also means a typo in your application code can silently store the wrong shape of data for years. If that trade-off bothers you — and for most production schemas, it should — STRICT tables are the answer. We'll get to those soon.

Type Affinity in One Paragraph

The declared type on a column isn't ignored; it gives the column an affinity. When you insert a value, SQLite tries to convert it toward the column's affinity if a clean conversion exists. A TEXT column receiving the number 42 keeps it as text '42'; an INTEGER column receiving the string '42' stores it as integer 42. If the conversion would lose information, the original type is preserved.

CREATE TABLE prices (
    label TEXT,
    cents INTEGER
);

INSERT INTO prices VALUES (42, '100');
INSERT INTO prices VALUES ('coffee', '3.5');

SELECT label, typeof(label), cents, typeof(cents) FROM prices;

Row one: the integer 42 got coerced to text '42', and the string '100' got coerced to integer 100. Row two: '3.5' couldn't go to INTEGER losslessly, so it stayed as text. Affinity has its own page coming up — for now, just know that the column type still influences storage, even if it doesn't enforce it.

Booleans

There is no BOOLEAN storage class. SQLite stores booleans as integers — 0 for false, 1 for true:

CREATE TABLE tasks (
    title TEXT,
    done  BOOLEAN
);

INSERT INTO tasks VALUES ('write docs', TRUE);
INSERT INTO tasks VALUES ('ship it', FALSE);

SELECT title, done, typeof(done) FROM tasks
WHERE done = 1;

The keywords TRUE and FALSE are recognised (since SQLite 3.23) and translate to 1 and 0. The BOOLEAN declaration gives the column numeric affinity but doesn't restrict it to 0/1 — without STRICT, you could insert 'maybe' and SQLite wouldn't object.

Dates and Times

There's no DATETIME either. You pick one of three encodings, and SQLite's date functions work on all of them:

• TEXT as ISO-8601: '2026-04-23 14:30:00'.
• REAL as Julian day numbers.
• INTEGER as Unix epoch seconds.

CREATE TABLE events (
    name      TEXT,
    happened  TEXT
);

INSERT INTO events VALUES ('launch', '2026-04-23 09:00:00');
INSERT INTO events VALUES ('demo',   datetime('now'));

SELECT name, happened, date(happened), strftime('%H:%M', happened)
FROM events;

ISO-8601 text is the most common choice — it sorts correctly as a string, it's human-readable, and the built-in functions (date(), time(), datetime(), strftime(), julianday()) all consume it. Pick one encoding per column and stick with it; mixing formats inside a single column is the kind of thing that bites you six months later.

VARCHAR, CHAR, and Other Familiar Names

SQLite accepts the type names you know from other databases — VARCHAR(255), CHAR(10), NVARCHAR, DECIMAL(10,2), DOUBLE, FLOAT, INT, BIGINT, MEDIUMINT. They all parse fine. They just map down to one of the five storage classes by affinity rules.

CREATE TABLE users (
    id        BIGINT,
    name      VARCHAR(255),
    bio       NVARCHAR(2000),
    balance   DECIMAL(10, 2)
);

INSERT INTO users VALUES (1, 'Ada', 'inventor', 99.50);

SELECT typeof(id), typeof(name), typeof(bio), typeof(balance) FROM users;

VARCHAR(255) does not enforce a 255-character limit — SQLite ignores the length. DECIMAL(10, 2) does not store a fixed-precision decimal — it gets numeric affinity and is held as INTEGER or REAL. The names exist purely so that schemas copied from other databases will run; they don't bring along the constraints those names imply elsewhere.

If you need exact decimal arithmetic for money, store cents as INTEGER. Floating-point REAL will introduce rounding errors at the third decimal place sooner or later.

NULL Is a Storage Class

NULL is not just "no value" — it's a value with its own storage class, returned by typeof():

CREATE TABLE optional (
    a INTEGER,
    b TEXT
);

INSERT INTO optional (a) VALUES (5);

SELECT a, typeof(a), b, typeof(b) FROM optional;

b reports as null. This matters because NULL doesn't equal anything — not even another NULL. b = NULL is never true; you have to write b IS NULL. That comes up properly in the operators-and-null page later, but it starts here, with the storage class.

Storing Bytes With BLOB

BLOB stores raw bytes verbatim — useful for small images, hashes, encoded data, anything that isn't text or a number:

CREATE TABLE files (
    name  TEXT,
    data  BLOB
);

INSERT INTO files VALUES ('signature', x'89504e470d0a1a0a');

SELECT name, length(data), hex(data), typeof(data) FROM files;

The x'...' literal lets you write blobs as hex in SQL; from application code, you'd typically pass a byte array via a parameter. length() on a blob returns the byte count, not a character count.

A practical note: SQLite happily stores large blobs, but pulling a 50 MB blob through every query that touches the row is slow. For large files, store the file on disk and keep a path in the database.

What You Take Away

• Five storage classes — NULL, INTEGER, REAL, TEXT, BLOB — cover everything.
• Booleans are integers; dates are text, real, or integer (your choice).
• Declared column types are hints, not contracts (unless you use STRICT).
• VARCHAR(255) and friends parse but don't enforce the length or precision they imply elsewhere.
• typeof(value) is your friend whenever you're not sure what's actually stored.

Next: Type Affinity

The "hint" behaviour we glossed over has a precise rule set behind it — five affinity classes, derived from the declared type name, applied every time you insert. That's the next page, and it's the key to predicting what SQLite will actually do with the values you hand it.