Data Types

Every value in SQLite has a storage class: the kind of value SQLite is actually storing. Every column can also have a declared type, which gives the column a type affinity.

That sounds technical, but the beginner idea is simple:

In SQLite, the value itself has the strongest type. The column's declared type is a helpful preference, not always a strict wall.

SQLite is flexible about types

Some database systems are rigid: if a column is declared as a number, text that is not a number is rejected.

SQLite is different. SQLite uses dynamic typing. A column declaration gives SQLite a preference, called affinity, but SQLite may still store a value of another kind.

This flexibility is convenient, but it also means you should be thoughtful. If a column is meant to hold ages, you should still insert ages, not random words.

The five storage classes

SQLite stores values using five storage classes:

Most beginner tables use INTEGER, TEXT, REAL, and sometimes NUMERIC as declared column types. NULL is a value state, not a declared column type you usually choose.

Declared types become affinities

When you write a column type, SQLite maps it to an affinity. Affinity is the column's preferred storage behavior.

Here is a beginner-friendly summary:

SQLite's full mapping rules look at words inside the declared type. For beginners, use clear names like INTEGER, TEXT, REAL, and NUMERIC.

typeof() shows the storage class

SQLite has a helpful function named typeof() that reports how a value is actually stored.

A NUMERIC affinity column tries to store numeric-looking text as numbers. So '42' may become an integer, and '3.14' may become real. But 'banana' cannot sensibly become a number, so SQLite stores it as text.

SQLite compared with rigid type systems

Think of a rigid system as a locked door and SQLite affinity as a helpful sign.

This does not mean types do not matter in SQLite. They still matter because they help:

• document what the column is for,
• make numeric-looking values behave like numbers,
• make your table easier for people to understand,
• support predictable queries when you store consistent values.

NULL means missing or unknown

NULL means the value is missing, unknown, or not applicable. It is not the same as zero, an empty string, or the word 'NULL'.

For example, if you do not know a customer's phone number yet, NULL is better than making up a fake value.

Booleans in SQLite

SQLite does not have a separate boolean storage class. Store true/false values as integers:

• 1 for true
• 0 for false

Example column:

is_active INTEGER

Then query it like this:

SELECT name
FROM users
WHERE is_active = 1;

Dates and times in SQLite

SQLite does not have a separate date or timestamp storage class. Common choices are:

For beginners, storing dates as TEXT in ISO format is easiest:

created_on TEXT

Example values:

• '2026-03-14'
• '2026-03-14 09:30:00'

The YYYY-MM-DD format sorts naturally as text when every date uses the same format.

See flexible typing in action

This example stores values in columns with different affinities and uses typeof() to show what SQLite actually stored.

Look closely at the output. SQLite converts some values based on affinity, but not every value. That is the key idea.

Choosing types as a beginner

Use this simple guide:

Check your understanding