Every column in a table has a data type. It tells the database what kind of data you plan to store there. SQLite is actually pretty flexible about this — it uses something called "type affinity" — but you should still use proper types.
SQLite Data Types
SQLite has five main storage classes:
NULL— missing or unknown valueINTEGER— whole numbers (1, 42, -7)REAL— floating point numbers (3.14, -0.5)TEXT— strings ("Hello", "Kano")BLOB— binary data, stored exactly as input
How Other Databases Compare
Other databases are more strict about types. Here is a quick comparison:
-- SQLite: very forgiving, '123' can be stored in an INTEGER column
-- MySQL: more strict, has VARCHAR, BOOLEAN, DATE, TIMESTAMP, etc.
-- PostgreSQL: very strict, has SERIAL, BOOLEAN, DATE, JSON, ARRAY, etc.
-- Examples of MySQL-specific types:
-- id INT AUTO_INCREMENT PRIMARY KEY,
-- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- is_active BOOLEAN DEFAULT TRUE,
-- bio TEXTIf you are learning SQL for a specific job or project, find out which database they use and learn its specific types. The concepts transfer, but the details matter.
Choosing the Right Type
My advice? Keep it simple:
- IDs and counts → INTEGER
- Prices and measurements → REAL
- Names, descriptions, codes → TEXT
- Dates → TEXT (in ISO format: '2026-06-25') or use DATE type if available
- Booleans → INTEGER (0 or 1) or BOOLEAN if supported
Starting with these simple rules will get you far. You can learn the edge cases as you encounter them.