SQLite BOOLEAN: there is no boolean type

Quick answer

SQLite has no boolean type. Booleans are integers: 1 is true, 0 is false.

CREATE TABLE users (
  id       INTEGER PRIMARY KEY,
  is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1))
);

INSERT INTO users (is_active) VALUES (TRUE);   -- stores 1
SELECT * FROM users WHERE is_active;           -- works

You can write BOOLEAN in a CREATE TABLE and SQLite will accept it, but it does not create a boolean type. The section below shows what it actually does.

What BOOLEAN actually does

Declare a column as BOOLEAN and it works, which is exactly why this is confusing:

CREATE TABLE t (a BOOLEAN, b BOOL, c INTEGER);
INSERT INTO t VALUES (TRUE, FALSE, 1);
SELECT a, b, c FROM t;

a|b|c
1|0|1

The values came back as 1 and 0, not TRUE and FALSE. SQLite converted them on the way in.

The declared type is remembered, though:

SELECT name, type FROM pragma_table_info('t');

name|type
a|BOOLEAN
b|BOOL
c|INTEGER

So the schema says BOOLEAN. But the values are not:

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

typeof(a)|typeof(b)
integer|integer

This is SQLite's type affinity system. The declared type is a hint that determines affinity, not a constraint that determines what can be stored. BOOLEAN contains no recognized affinity keyword, so it gets NUMERIC affinity, and SQLite stores whatever fits.

Which means a BOOLEAN column will take this without complaint:

INSERT INTO t (a) VALUES ('hello');
SELECT a, typeof(a) FROM t;

1|integer
hello|text

A string, in your boolean column. No error. If you want that prevented, you have to say so yourself, with a CHECK constraint.

TRUE and FALSE keywords

TRUE and FALSE are recognized and are simply aliases for 1 and 0:

SELECT TRUE, FALSE, 1=1, 1=2;

1|0|1|0

These were added in SQLite 3.23 (2018). Any SQLite you are likely to be running has them. If you are targeting something ancient or embedded, 1 and 0 are always safe.

Note that a comparison returns 1 or 0 too. There is no separate boolean result type anywhere in SQLite.

The 'true' string trap

This is the one that causes real bugs, usually when data arrives from JSON, a CSV import, or a language that stringifies loosely.

CREATE TABLE s (v BOOLEAN);
INSERT INTO s VALUES ('true'), ('false'), (1), (0);

SELECT v, typeof(v), CASE WHEN v THEN 'truthy' ELSE 'falsy' END AS eval FROM s;

v|typeof(v)|eval
true|text|falsy
false|text|falsy
1|integer|truthy
0|integer|falsy

Read that carefully. The string 'true' is falsy.

Not because SQLite is being perverse, but because it applies numeric coercion: a text value that does not look like a number converts to 0, and 0 is false. So 'true' and 'false' are both false, and a query like WHERE is_active silently returns nothing for every row your importer wrote as a string.

Nothing errors. Nothing warns. The rows just quietly stop matching.

Guard against it at the schema level:

CREATE TABLE users (
  is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1))
);

INSERT INTO users (is_active) VALUES ('true');

Error: CHECK constraint failed: is_active IN (0,1)

That turns a silent wrong answer into a loud failure at the point of insert, which is where you want it.

Querying booleans

Because they are integers, all of these work:

SELECT * FROM users WHERE is_active = 1;
SELECT * FROM users WHERE is_active;      -- shorthand, same thing
SELECT * FROM users WHERE NOT is_active;
SELECT * FROM users WHERE is_active = TRUE;

Counting is where the integer representation is actively convenient:

SELECT
  count(*)          AS total,
  sum(is_active)    AS active,
  count(*) - sum(is_active) AS inactive
FROM users;

sum() over a 0/1 column counts the true rows. That is not a trick, it is just what the data is.

NULL is not false

A nullable boolean has three states, and NULL is not one of the two you were thinking about:

SELECT * FROM users WHERE is_active = 0;      -- does NOT match NULL rows
SELECT * FROM users WHERE is_active IS NULL;  -- matches them
SELECT * FROM users WHERE is_active IS NOT 1; -- matches 0 AND NULL

IS NOT is SQLite's null-safe comparison, and it is genuinely useful here. Prefer NOT NULL DEFAULT 0 on boolean columns unless "unknown" is a state you actually need.

Booleans from other languages

The drivers do not agree with each other, which is worth knowing before you debug:

Python converts automatically in both directions if the column type is declared and detect_types is on. Without it, you get integers back:

cur.execute("INSERT INTO users (is_active) VALUES (?)", (True,))   # stores 1
row = cur.execute("SELECT is_active FROM users").fetchone()
print(row[0])          # 1, an int, not True
print(bool(row[0]))    # True

JavaScript (better-sqlite3, node:sqlite) will not accept a JS boolean as a bind parameter at all. Convert explicitly:

db.prepare("INSERT INTO users (is_active) VALUES (?)").run(active ? 1 : 0);
const row = db.prepare("SELECT is_active FROM users").get();
const active = Boolean(row.is_active);   // 1 -> true

Go with database/sql scans into a bool fine, because the driver does the conversion.

The pattern is the same everywhere: store 0/1, convert at the boundary, and never rely on the driver to guess.

Should you use BOOLEAN or INTEGER?

Both produce an integer column. The difference is what a human reads.

is_active BOOLEAN NOT NULL DEFAULT 1 CHECK (is_active IN (0,1))
is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0,1))

BOOLEAN documents intent and shows up in pragma_table_info, which some ORMs and tools read to decide how to convert values. INTEGER is honest about what is happening.

Either is fine. The CHECK constraint is the part that matters, and it is the part people leave out.

Common problems

My boolean column contains 'true' strings. An importer wrote text. They are all falsy. Fix the data and add a CHECK:

UPDATE users SET is_active = CASE WHEN lower(is_active) IN ('true','1','t','yes') THEN 1 ELSE 0 END;

WHERE is_active returns nothing. Check typeof(is_active). If it says text, see above.

My ORM returns 1 instead of true. Expected. SQLite has no boolean to return. Convert in your model layer.

CHECK constraint failed. Working as designed. Something tried to write a non-boolean.

Quick reference