Table Data Types

SQLite handles data types differently from many server databases.

Instead of strict fixed column types, SQLite uses type affinity plus runtime storage classes.

Core Concepts

flowchart LR
  A[Declared type text] --> B[SQLite assigns affinity]
  B --> C[Inserted value]
  C --> D[Stored as runtime storage class]

Storage class	Meaning	Example literal
NULL	Missing value	NULL
INTEGER	Signed integer	42
REAL	Floating-point number	3.14
TEXT	Text string	'hello'
BLOB	Raw bytes	X'6869'

Common Declarations

Declared type	Typical affinity
INTEGER	INTEGER
TEXT / VARCHAR(...)	TEXT
REAL / DOUBLE	REAL
NUMERIC / DECIMAL(...)	NUMERIC
BLOB	BLOB

Mental Model

• Declared type: what you intend
• Affinity: how SQLite tries to store/compare values
• Storage class: what value is at runtime

Code Examples

-- Declare columns with common SQLite type names.
CREATE TABLE type_demo (
  id          INTEGER PRIMARY KEY,
  qty         INTEGER,
  price       REAL,
  sku         TEXT,
  meta        BLOB,
  amount      NUMERIC
);

Expected output
Table type_demo is created successfully.

-- Inspect table metadata.
PRAGMA table_info(type_demo);

cid	name	type	notnull	dflt_value	pk
0	id	INTEGER	0	null	1
1	qty	INTEGER	0	null	0
2	price	REAL	0	null	0
3	sku	TEXT	0	null	0
4	meta	BLOB	0	null	0
5	amount	NUMERIC	0	null	0

SQLite-Specific Nuances

SQLite Nuance

SQLite does not enforce strict type matching in the same way as many client/server databases.

Use constraints (NOT NULL, CHECK, UNIQUE) to keep data quality high.

Common Pitfalls / Best Practices

Pitfall

Assuming VARCHAR(20) strictly limits text length in SQLite.

Length limits are not enforced just by the type declaration.

Best Practice

Choose readable type names (INTEGER, TEXT, REAL) and add constraints for business rules.

Quick Challenge

Design a products table with these columns:

• product_id integer primary key
• name text required
• price numeric required

View Solution

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  price      NUMERIC NOT NULL
);

Expected output
Table is created with clear affinities and required fields.