Boolean Datatype in SQLite Database

SQLite has no separate Boolean storage class. It can still represent Boolean values: the usual convention is to store 0 for false and 1 for true in an integer column. A consistent text convention is another option when it suits an application or an interface it exchanges data with.

Boolean Datatype in SQLite Database

SQLite does not have a native Boolean storage class. Choose a representation and use it consistently in the schema, inserted values, and queries. Integer values are the practical default; a CHECK constraint can restrict a column to the intended two states because a declared type alone does not do that.

Represent Boolean Datatype as Integers

For a two-state flag, the integer convention stores false as 0 and true as 1. Declare the column INTEGER so its purpose is clear, then add CHECK (flag IN (0, 1)) to restrict the stored values to those two choices. SQLite uses flexible typing, so the declaration by itself does not guarantee that every stored value is one of them. The constraint expresses the rule in the database, where SQLite enforces it on inserts and updates from any client. That enforcement has one limit worth stating plainly: a CHECK expression fails only when it evaluates to false. Because NULL IN (0, 1) evaluates to NULL rather than false, the constraint alone still admits NULL; keeping NULL out is the separate job of NOT NULL, covered next.

NOT NULL addresses a separate question: whether the flag must have a value for every row. Without it, a row can have NULL, which means unknown or not supplied rather than false. Use both constraints when each record must have a definite answer. If the application intentionally allows an unanswered flag, omit NOT NULL and make queries account for NULL explicitly.

Insert numeric 0 and 1 values, preferably through bound parameters in application code. Query true rows with WHERE flag = 1 and false rows with WHERE flag = 0; do not mix these comparisons with strings such as 'true'. This makes the convention visible at each layer and avoids having different parts of an application interpret the same column differently. The example creates a students table, inserts five rows, and reads each stored flag. It also creates a separate table using text labels so the two conventions can be compared directly.

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("""
    CREATE TABLE students (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER NOT NULL,
        international_student INTEGER NOT NULL
            CHECK (international_student IN (0, 1))
    )
""")
connection.executemany(
    "INSERT INTO students (id, name, age, international_student) VALUES (?, ?, ?, ?)",
    [
        (1, "Stefan", 13, 1),
        (2, "Damon", 14, 0),
        (3, "Elena", 12, 1),
        (4, "Caroline", 12, 1),
        (5, "Bonnie", 13, 0),
    ],
)
integer_rows = connection.execute(
    "SELECT name, international_student FROM students ORDER BY id"
).fetchall()

connection.execute("""
    CREATE TABLE student_flags (
        student_id INTEGER PRIMARY KEY,
        international_student TEXT NOT NULL
            CHECK (international_student IN ('true', 'false'))
    )
""")
connection.executemany(
    "INSERT INTO student_flags VALUES (?, ?)",
    [(1, "true"), (2, "false"), (3, "true"), (4, "true"), (5, "false")],
)
text_rows = connection.execute(
    "SELECT student_id, international_student FROM student_flags ORDER BY student_id"
).fetchall()

print("Integer convention:", integer_rows)
print("Text convention:", text_rows)

Integer convention: [('Stefan', 1), ('Damon', 0), ('Elena', 1), ('Caroline', 1), ('Bonnie', 0)]
Text convention: [(1, 'true'), (2, 'false'), (3, 'true'), (4, 'true'), (5, 'false')]

The displayed rows preserve the integer values in the first table and the text labels in the second. The example selects all rows to make the stored representation visible; in an application query, add a predicate such as WHERE international_student = 1 for the integer table. Keep the chosen literal type consistent with the column’s convention.

Represent Boolean Datatype as Strings

Text is useful when an external format already uses labels such as true and false, or when those labels make stored values easier to inspect. Declare the column as TEXT, choose one spelling and capitalization, and validate it with a CHECK constraint as in student_flags above. The query returns the text labels directly; application code should use the same convention when writing and reading them. SQLite’s type affinity does not make either declaration a Boolean type or by itself restrict the column to two values.

With this convention, inserts must use the chosen text labels and queries must compare against the same label, for example WHERE international_student = 'true'. A CHECK list protects spelling consistency, while NOT NULL requires each row to carry one of the labels. Text may fit an interface or file format that already exchanges words, but it adds a convention that every writer and reader must follow. Avoid storing a mixture such as 1, TRUE, and true, because comparisons and application parsing then need extra rules.

For most tables whose flag is used as a two-state value inside the database, use INTEGER with 0 and 1, plus NOT NULL and CHECK when each row requires a definite, constrained value. Choose text when matching an established textual interface has practical value. Before settling on either representation, check how existing data and clients encode the field. Document the mapping, enforce it in the schema, bind values with the same representation, and use matching comparisons in every query. SQLite’s BOOLEAN type name can also be declared as an affinity label, but it does not create a separate Boolean storage class or replace constraints that enforce the application’s rule.