SQLite Boolean: How to Store and Query TRUE and FALSE

Ninad Pathak

October 7, 2026

SQLite has no boolean type. A column declared BOOLEAN is accepted, but the values are stored as the integers 1 and 0. That’s workable once you know it, and in this article I’ll walk you through storing yes/no values and querying them without surprises.

TLDR

SQLite has no boolean type. It stores true as the integer 1 and false as 0.
TRUE and FALSE work as keywords since SQLite 3.23.0, and they are only other spellings of 1 and 0.

Declare the column INTEGER (or BOOLEAN) and add CHECK (done IN (0, 1)) so nothing else gets in.
Query with WHERE done and WHERE NOT done.

What is a boolean in SQLite?

A boolean in SQLite is an integer that is either 1 (true) or 0 (false). The docs say it directly: “SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true)” (Boolean datatype).

The TRUE and FALSE keywords are those same integers:

sqlite> SELECT TRUE AS t, FALSE AS f, typeof(TRUE) AS type, TRUE = 1 AS same_as_1;
t  f  type     same_as_1
-  -  -------  ---------
1  0  integer  1

SQLite boolean syntax

A boolean column is a column declaration plus an optional CHECK constraint:

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

Declared type	What SQLite does with it
`INTEGER`	INTEGER affinity: 1 and 0 are stored as integers
`BOOLEAN`	NUMERIC affinity: still stored as integers, but the name documents the intent
`BOOLEAN` in a `STRICT` table	Rejected, because STRICT tables accept only six type names

How to store and query booleans in SQLite

To show you how this works, I will use a small task list with a done column.

Step 1: Create a boolean column

A column declared BOOLEAN gets NUMERIC affinity. So the keywords and the numbers 1 and 0 are all stored as integers:

sqlite> CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done BOOLEAN NOT NULL DEFAULT FALSE);
sqlite> INSERT INTO tasks (title, done) VALUES ('write docs', TRUE), ('test examples', FALSE), ('ship', 1);
sqlite> SELECT id, title, done, typeof(done) FROM tasks;
id  title          done  typeof(done)
--  -------------  ----  ------------
1   write docs     1     integer
2   test examples  0     integer
3   ship           1     integer

Step 2: Keep the column to 0 and 1 with CHECK

The declared type alone doesn’t restrict anything. Text and other numbers go in too:

sqlite> INSERT INTO tasks (title, done) VALUES ('oops', 'yes'), ('oops 2', 2);
sqlite> SELECT id, title, done, typeof(done) FROM tasks WHERE id > 3;
id  title   done  typeof(done)
--  ------  ----  ------------
4   oops    yes   text
5   oops 2  2     integer

A CHECK constraint closes that hole. It still accepts TRUE and FALSE, because they are 1 and 0:

sqlite> CREATE TABLE tasks2 (id INTEGER PRIMARY KEY, done INTEGER NOT NULL DEFAULT 0 CHECK (done IN (0, 1)));
sqlite> INSERT INTO tasks2 (done) VALUES (TRUE), (0);
sqlite> INSERT INTO tasks2 (done) VALUES ('yes');
Error: stepping, CHECK constraint failed: done IN (0, 1) (19)
sqlite> SELECT id, done FROM tasks2;
id  done
--  ----
1   1
2   0

Step 3: Query true and false rows

WHERE done finds the true rows and WHERE NOT done finds the false ones. IS TRUE gives the same result as a bare WHERE done:

sqlite> SELECT title FROM tasks WHERE done ORDER BY id;
title
----------
write docs
ship
oops 2
sqlite> SELECT title FROM tasks WHERE done IS TRUE ORDER BY id;
title
----------
write docs
ship
oops 2
sqlite> SELECT title FROM tasks WHERE NOT done ORDER BY id;
title
-------------
test examples
oops

The stray value 2 counts as true, and the text 'yes' counts as false. The edge cases below explain why.

Examples

Each example below uses the same tasks table.

Turn a boolean into a label

iif() returns the value paired with the first true condition (iif()). A CASE expression does the same:

sqlite> SELECT title, iif(done, 'done', 'open') AS status FROM tasks WHERE id <= 3;
title          status
-------------  ------
write docs     done
test examples  open
ship           done

Booleans in a STRICT table

A STRICT table rejects BOOLEAN as a type name, because “The datatype must be one of the following: INT INTEGER REAL TEXT BLOB ANY” (STRICT tables). Use INTEGER with the CHECK constraint instead:

sqlite> CREATE TABLE flags (id INTEGER PRIMARY KEY, on_off BOOLEAN) STRICT;
Error: in prepare, unknown datatype for flags.on_off: "BOOLEAN"
sqlite> CREATE TABLE flags (id INTEGER PRIMARY KEY, on_off INTEGER NOT NULL CHECK (on_off IN (0, 1))) STRICT;
sqlite> INSERT INTO flags (on_off) VALUES (TRUE);
sqlite> INSERT INTO flags (on_off) VALUES ('1');
sqlite> SELECT id, on_off, typeof(on_off) FROM flags;
id  on_off  typeof(on_off)
--  ------  --------------
1   1       integer
2   1       integer

The text '1' was stored as the integer 1. STRICT tables still convert values, and an error is raised only “If the value cannot be losslessly converted in the specified datatype”.

Booleans from Python

Python’s sqlite3 module stores True as 1, and reads it back as the integer 1, not as True. Convert it with bool() where it matters:

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE tasks (title TEXT, done BOOLEAN CHECK (done IN (0, 1)))")
con.execute("INSERT INTO tasks VALUES (?, ?)", ("write docs", True))   # Python bool in
row = con.execute("SELECT title, done FROM tasks").fetchone()
print(row, type(row[1]).__name__)                                    # comes back as int
print("as bool:", bool(row[1]))

$ python3 bool_python.py
('write docs', 1) int
as bool: True

Edge cases

SQLite decides what counts as true by converting the value to a number. The docs: “A numeric zero value (integer value 0 or real value 0.0) is considered to be false. A NULL value is still NULL. All other values are considered true” (Boolean expressions).

Text like ‘abc’ or ‘yes’ is false

Text that isn’t a number, like 'abc' or 'yes', counts as false. Text like '1' counts as true:

sqlite> SELECT 'abc' IS TRUE AS abc, 'yes' IS TRUE AS yes_text, '1' IS TRUE AS one_text, 2 IS TRUE AS two, 0.5 IS TRUE AS half, NULL IS TRUE AS null_value;
abc  yes_text  one_text  two  half  null_value
---  --------  --------  ---  ----  ----------
0    0         1         1    1     0

That also means a bare string in WHERE matches no rows:

sqlite> SELECT count(*) AS rows_matched FROM tasks WHERE 'abc';
rows_matched
------------
0

This is the reason to add the CHECK constraint. Without it, a row stored as 'yes' silently lands in the “not done” list.

NULL is neither true nor false

A NULL is not true and not false, so WHERE done and WHERE NOT done both skip it:

sqlite> SELECT NULL IS TRUE AS is_true, NULL IS FALSE AS is_false, (SELECT count(*) FROM (SELECT NULL AS done) WHERE NOT done) AS not_done_rows;
is_true  is_false  not_done_rows
-------  --------  -------------
0        0         0

Declare the column NOT NULL with a default, as in Step 1, so every row is one or the other.

Conclusion

SQLite stores booleans as 1 and 0. Declare the column INTEGER or BOOLEAN with NOT NULL DEFAULT 0. Then add CHECK (done IN (0, 1)), and query it with WHERE done or WHERE NOT done. In a STRICT table, use INTEGER, because BOOLEAN isn’t allowed there.