Data types are one of the first things you learn about in any database course, and for most database systems, the rules are pretty rigid: a column declared as INT holds integers, a column declared as VARCHAR holds text of a bounded length, and trying to mix them up gets you an error. SQLite plays by a different set of rules, and understanding exactly how those rules work will save you a lot of confusion down the line. In this guide, I’ll walk through SQLite’s data types from the ground up, with plenty of practical examples.
The Five Fundamental Types
Underneath everything, SQLite recognizes exactly five storage classes, which function as its true, fundamental data types:
- NULL — represents missing or unknown data
- INTEGER — a signed whole number, stored using between 1 and 8 bytes depending on how large the value is
- REAL — a floating-point number, stored as an 8-byte IEEE 754 value
- TEXT — a string of characters, stored using the database’s text encoding, typically UTF-8
- BLOB — raw binary data, stored exactly as given, with no interpretation applied
Every single value stored anywhere in a SQLite database belongs to exactly one of these five classes. There’s no separate DATE type, no BOOLEAN type, no fixed-length CHAR type at the storage level — everything ultimately reduces to one of these five.
Declaring Column Types
When you write a CREATE TABLE statement, you can declare column types using familiar-sounding names, even ones that don’t map directly to SQLite’s five storage classes:
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2),
description TEXT,
in_stock BOOLEAN,
created_at DATETIME
);
This is completely valid SQL in SQLite, even though VARCHAR(100), DECIMAL(10,2), BOOLEAN, and DATETIME aren’t real SQLite storage classes. SQLite parses the declared type name, looks for certain keywords inside it, and assigns the column a type affinity based on that — a topic covered in depth in a companion article on storage classes, but worth summarizing briefly here since it’s central to understanding how types actually behave.
Type Affinity in Brief
Each column gets one of five affinities based on its declared type: TEXT, NUMERIC, INTEGER, REAL, or BLOB. The affinity is a preference SQLite uses when deciding how to store an incoming value, not a hard restriction. If a value can be losslessly converted to match the column’s preferred affinity, SQLite converts it; if not, it stores the value using whatever storage class fits it as given.
INSERT INTO products (name, price, in_stock) VALUES ('Desk Lamp', '24.99', 1);
SELECT price, typeof(price), in_stock, typeof(in_stock) FROM products;
24.99 | real
1 | integer
Even though price was inserted as the string '24.99', because the column has NUMERIC affinity (from DECIMAL), SQLite converted it to an actual REAL value automatically.
Working with Boolean Values
SQLite has no dedicated BOOLEAN storage class. Instead, boolean logic is represented using integers: 0 for false, 1 for true. You can still declare a column as BOOLEAN in your CREATE TABLE statement for readability’s sake — SQLite will just quietly treat it with NUMERIC affinity underneath.
CREATE TABLE tasks (
id INTEGER PRIMARY KEY,
title TEXT,
completed BOOLEAN DEFAULT 0
);
INSERT INTO tasks (title, completed) VALUES ('Write article', 1);
SELECT title, completed, typeof(completed) FROM tasks;
Write article | 1 | integer
Even though we declared completed as BOOLEAN, and even though TRUE/FALSE literal keywords do work in more recent SQLite versions as convenient synonyms for 1 and 0, the underlying storage is always an integer. When writing application code, remember to treat these values as 0 and 1 rather than expecting a native boolean type to come back from a query.
Working with Dates and Times
Similarly, SQLite has no dedicated DATE or DATETIME storage class. Dates are typically stored in one of three common formats, and which one you choose has real consequences for how easily you can query and sort them:
As TEXT, using the ISO-8601 string format, like '2024-03-15' or '2024-03-15 14:30:00'. This is the most common and generally recommended approach, because ISO-8601 formatted strings sort correctly using plain alphabetical/text comparison, and they’re human-readable.
CREATE TABLE events (
id INTEGER PRIMARY KEY,
name TEXT,
event_date TEXT
);
INSERT INTO events (name, event_date) VALUES ('Product Launch', '2024-06-01');
SELECT name FROM events WHERE event_date >= '2024-01-01' ORDER BY event_date;
As INTEGER, storing Unix timestamps (the number of seconds since January 1, 1970). This is compact and efficient for calculations but not human-readable without conversion.
CREATE TABLE logs (
id INTEGER PRIMARY KEY,
message TEXT,
logged_at INTEGER
);
INSERT INTO logs (message, logged_at) VALUES ('Startup complete', 1710500000);
As REAL, storing Julian day numbers, which is less common in everyday application code but used internally by some of SQLite’s own date functions.
SQLite provides built-in date and time functions that work with all three formats, which is genuinely convenient:
SELECT date('now');
SELECT datetime('now');
SELECT strftime('%Y-%m-%d', 'now');
I’d recommend sticking with ISO-8601 TEXT format for the vast majority of applications unless you have a specific performance reason to prefer Unix timestamps — the readability and natural sortability are worth a great deal during development and debugging.
Working with BLOB Data
BLOB storage is for raw binary data — images, files, encrypted data, or anything that isn’t naturally text. You insert BLOB literals using the x'...' hexadecimal syntax:
CREATE TABLE files (
id INTEGER PRIMARY KEY,
filename TEXT,
content BLOB
);
INSERT INTO files (filename, content) VALUES ('greeting.txt', x'48656C6C6F');
That hex string decodes to the bytes for “Hello.” In practice, you’ll rarely type BLOB literals by hand like this — instead, your application code (in Python, JavaScript, or whatever language you’re using) will pass raw binary data through parameterized queries, and the SQLite driver handles the encoding for you.
import sqlite3
connection = sqlite3.connect('files.db')
cursor = connection.cursor()
with open('image.png', 'rb') as f:
image_data = f.read()
cursor.execute('INSERT INTO files (filename, content) VALUES (?, ?)', ('image.png', image_data))
connection.commit()
It’s worth noting that while SQLite can store binary files as BLOBs perfectly well, for very large files (multiple megabytes or more), it’s often more practical to store the file on the filesystem and just keep a path reference in the database, rather than bloating the database file itself. SQLite handles this fine either way, but the tradeoffs are worth considering based on your specific use case.
NULL and How It Behaves
NULL represents the absence of a value, not zero, not an empty string — genuinely “unknown” or “not applicable.” It behaves distinctly in comparisons, which trips up a lot of people new to SQL generally, not just SQLite specifically.
CREATE TABLE contacts (
id INTEGER PRIMARY KEY,
name TEXT,
phone TEXT
);
INSERT INTO contacts (name, phone) VALUES ('Ali', NULL);
SELECT * FROM contacts WHERE phone = NULL;
That last query returns nothing, even though there’s clearly a row with a NULL phone. This is because NULL isn’t equal to anything, including another NULL — comparisons involving NULL always evaluate to NULL (essentially “unknown”), never true. To check for NULL correctly, use IS NULL or IS NOT NULL:
SELECT * FROM contacts WHERE phone IS NULL;
This correctly returns Ali’s row. This behavior is standard across SQL databases, not unique to SQLite, but it’s worth reiterating because it catches beginners constantly.
Numeric Precision Considerations
SQLite’s INTEGER storage class can hold values up to 8 bytes, giving you the same range as a 64-bit signed integer — roughly -9.2 quintillion to 9.2 quintillion. That’s more than enough for the vast majority of applications.
REAL values are stored as 8-byte IEEE floating point, which comes with the same precision caveats as floating point numbers in any programming language — certain decimal values can’t be represented exactly, which can cause subtle issues in financial calculations if you’re not careful.
SELECT 0.1 + 0.2;
This returns 0.30000000000000004, not a clean 0.3, due to how floating point numbers work at the hardware level — this isn’t a SQLite-specific quirk, it’s true of essentially every programming language and database using IEEE 754 floating point. For financial data where exact precision genuinely matters, consider storing values as integers representing the smallest unit (cents instead of dollars, for example) rather than using REAL directly.
CREATE TABLE transactions (
id INTEGER PRIMARY KEY,
amount_cents INTEGER
);
INSERT INTO transactions (amount_cents) VALUES (2999);
Storing $29.99 as 2999 cents sidesteps floating point rounding issues entirely, and you convert back to a display-friendly dollar amount in your application layer.
STRICT Tables for Genuine Type Enforcement
As mentioned briefly elsewhere, more recent versions of SQLite support STRICT tables, which enforce declared column types much more rigidly than the traditional flexible system described throughout this article.
CREATE TABLE strict_products (
id INTEGER PRIMARY KEY,
name TEXT,
price REAL
) STRICT;
INSERT INTO strict_products (name, price) VALUES ('Mug', 'not a number');
In a STRICT table, that last insert fails outright with a type error, rather than SQLite silently storing the string as-is. If you’re building something new and want the safety net of rigid typing without giving up SQLite’s other advantages, STRICT tables are worth adopting as your default going forward, provided your SQLite version supports them.
Common Mistakes to Avoid
Assuming declared types are enforced like they would be in MySQL or PostgreSQL. Without STRICT mode or CHECK constraints, SQLite is far more permissive, and it’s easy to accidentally store inconsistent data types in the same column without any error being raised.
Storing dates inconsistently across a project — sometimes as ISO-8601 text, sometimes as Unix timestamps — which makes comparisons and sorting behave unpredictably. Pick one format and stick with it across your entire schema.
Using REAL for financial data and being surprised by floating point rounding errors later. Use integer-cents or a dedicated decimal handling approach in your application layer instead.
Forgetting that NULL requires IS NULL, not = NULL, in WHERE clauses — a classic mistake that produces confusing, silently empty query results rather than an obvious error.
Not considering STRICT tables for new projects where rigid type safety would genuinely help catch bugs earlier, especially in larger applications with many contributors.
Best Practices Worth Adopting
Pick one consistent date/time storage format — ISO-8601 text is usually the best default — and use it everywhere across your schema.
Store financial amounts as integers in the smallest currency unit, rather than using REAL, to avoid floating point precision issues.
Use IS NULL and IS NOT NULL explicitly whenever checking for missing values, never = NULL.
Consider STRICT tables for new schemas if your SQLite version supports them and you want stronger type guarantees without giving up SQLite’s other benefits.
Use typeof() during development and debugging any time query results seem inconsistent with what you expected the underlying data types to be.
Wrapping Up
SQLite’s approach to data types is more flexible, and frankly more forgiving, than most database systems you’ll encounter. That flexibility is genuinely useful in a lot of situations, especially for smaller applications and rapid prototyping, but it does mean the responsibility for maintaining data consistency shifts more toward you, the developer, than it would in a strictly typed database. Understand the five underlying storage classes, know how type affinity works, be deliberate about how you store dates and financial data, and consider STRICT tables where appropriate, and you’ll get all the benefits of SQLite’s simplicity without falling into its more common traps.