SQLite Boolean Data Type

Whether you’re new to SQLite or have some experience, dealing with Boolean values can be tricky. SQLite doesn’t have a separate Boolean data type. Instead, it uses integers 0 and 1 to represent FALSE and TRUE.

This unusual implementation often confuses developers. In this guide, you’ll learn everything you need to know about Booleans in SQLite.

By the end, you’ll be a Boolean expert ready to build SQLite databases that handle true/false values with ease. Let’s get started!

How SQLite Stores Booleans

First, the basics. SQLite does not have a separate Boolean storage class. Instead, it uses integers:

• 0 for FALSE
• 1 for TRUE

For example:

CREATE TABLE User (
  id INTEGER PRIMARY KEY,
  is_admin BOOLEAN
);

INSERT INTO User VALUES (1, 1); -- is_admin is TRUE
INSERT INTO User VALUES (2, 0); -- is_admin is FALSE

The is_admin column holds Boolean values. But behind the scenes, SQLite stores them as integers 0 and 1.

This is an important distinction from traditional database systems like MySQL that have built-in Boolean types.

Also read: SQLite Data Types

SQLite Keywords for Boolean Values – TRUE/FALSE

As of SQLite 3.23.0, the keywords TRUE and FALSE are available:

INSERT INTO User VALUES (3, TRUE); -- is_admin is TRUE
INSERT INTO User VALUES (4, FALSE); -- is_admin is FALSE 

However, TRUE and FALSE are just aliases for 1 and 0. They do not change the Boolean storage behavior.

Working with SQLite Booleans in Queries

When querying Boolean fields in SQLite using SELECT statement, you can use 1/0 or TRUE/FALSE interchangeably:

SELECT * FROM User WHERE is_admin = 1; 

SELECT * FROM User WHERE is_admin = TRUE;

SELECT * FROM User WHERE is_admin = 0;

SELECT * FROM User WHERE is_admin = FALSE;

All four queries above will return the same results.

You can even mix and match Boolean formats:

SELECT * FROM User WHERE is_admin = TRUE OR is_admin = 0;

For convenience, try to stick to one format within a query. Jumping between 1/0 and TRUE/FALSE can get confusing.

Also read: SQLite WHERE Clause

Boolean Constraints in SQLite

SQLite does not enforce any constraints on Boolean values in columns not declared as Boolean. For example:

CREATE TABLE User (
  id INTEGER PRIMARY KEY, 
  is_admin INTEGER
);

INSERT INTO User VALUES (1, 1); -- Valid
INSERT INTO User VALUES (2, 0); -- Valid 

INSERT INTO User VALUES (3, 55); -- Also valid!

The is_admin column is an integer. But we can insert any integer value, not just 0 and 1.

To restrict a column to Booleans, add a CHECK constraint:

CREATE TABLE User (
  id INTEGER PRIMARY KEY,
  is_admin INTEGER CHECK (is_admin IN (0, 1))  
);

Now SQLite will reject any non-Boolean integers:

INSERT INTO User VALUES (1, 1); -- OK
INSERT INTO User VALUES (2, 55); -- Fails check constraint

Check constraints enforce Boolean rules, without a full Boolean data type.

Boolean Affinity in SQLite

Affinity is SQLite’s way of recommending the “best” data type for a column.

When you declare a column as BOOLEAN, SQLite sets the affinity to Boolean. This means it prefers 0 or 1 as values.

For example:

CREATE TABLE User (
  id INTEGER PRIMARY KEY,
  is_admin BOOLEAN
);

The is_admin column has a Boolean affinity. SQLite will try to convert values like ‘Y’ or ‘True’ to 0/1 when inserting.

Affinity impacts how data is stored and compared. But unlike check constraints, it’s just a recommendation. SQLite will not reject invalid Boolean values.

Also check: SQLite Show Table Columns

Best Practices for SQLite Booleans

Now that you understand the basics, let’s go over some best practices for working with Booleans in SQLite:

Use BOOLEAN for Column Type

Declare Boolean columns with the BOOLEAN type for clarity:

CREATE TABLE User (
  id INTEGER PRIMARY KEY,
  is_admin BOOLEAN
);

Don’t use INTEGER, TEXT, or other generic types. BOOLEAN makes your intention clear.

Use CHECK Constraints in SQLite

Add CHECK constraints to enforce Boolean rules:

CREATE TABLE User (
  id INTEGER PRIMARY KEY,
  is_admin BOOLEAN CHECK (is_admin IN (0, 1))  
);

Constraints prevent invalid data from polluting your tables.

Use TRUE and FALSE Over 1 and 0

Prefer TRUE and FALSE in your queries:

SELECT * FROM User WHERE is_admin = FALSE;

The keywords are more readable than 1/0.

Validate Input Values

Before inserting data, validate that Boolean fields receive only true/false values.

Cast strings like ‘Y’ to proper Boolean integers:

// JavaScript example
const boolValue = value === 'Y' ? 1 : 0; 

db.run('INSERT INTO User (is_admin) VALUES (?)', [boolValue]);

Input validation prevents bad data from ending up in the database.

Use a Helper Function

For easy input validation, create a reusable function:

// JavaScript example
function parseBoolean(value) {
  if (value === true || value === 1 || value === '1' || value === 'true') {
    return 1;
  
  } else if (value === false || value === 0 || value === '0' || value === 'false') {
    return 0;
  }
}

const boolValue = parseBoolean(valueFromForm);
db.run('INSERT INTO User (is_admin) VALUES (?)', [boolValue]);

This handles all the edge cases and keeps your code DRY.

Common Boolean Issues in SQLite

Let’s troubleshoot some frequent problems that arise with SQLite Booleans:

Unexpected Type Conversion

SQLite automatically converts values like ‘Y’ and ‘True’ to Boolean integers because of affinity.

This can cause issues when you want to preserve the original string:

CREATE TABLE User (
  id INTEGER PRIMARY KEY,
  raw_value TEXT,
  is_admin BOOLEAN
);


INSERT INTO User VALUES (1, 'Y', 'Y');

-- SQLite converts 'Y' to 1 due to Boolean affinity
SELECT * FROM User;
-- id = 1, raw_value = 'Y', is_admin = 1 

To prevent conversion, disable affinity on the table:

CREATE TABLE User (
  id INTEGER PRIMARY KEY,
  raw_value TEXT,
  is_admin BOOLEAN
) WITHOUT ROWID; 

Or store strings in a separate table.

Inconsistent Boolean Formats

When querying, mixing formats like 1/0 and TRUE/FALSE can lead to bugs:

SELECT * FROM User WHERE is_admin = 1 OR is_admin = FALSE;

This will not return rows where is_admin is 0. Stick to ONE Boolean format to avoid logical errors.

Invalid Boolean Data

Without constraints, SQLite allows non-Boolean values like ‘Y’ in Boolean columns:

INSERT INTO User (is_admin) VALUES ('Y'); -- Permitted but problematic

Add CHECK constraints to prevent this. Or validate values before inserting.

Garbage data will break things like queries and Booleans in application code.

Boolean Parameters in Prepared Statements

Prepared statements may not understand TRUE/FALSE parameters:

// JavaScript example

db.run('SELECT * FROM User WHERE is_admin = ?', [true]); 
// Won't work! Needs integer value

db.run('SELECT * FROM User WHERE is_admin = ?', [1]);
// Use integer values for params

Make sure to convert Boolean parameters to integers before passing them into prepared queries.

Final Words

That covers everything you need to know about working with Booleans in SQLite. Here are some key takeaways:

• SQLite stores Booleans as integers 0 and 1 rather than a separate data type.
• Use BOOLEAN for column types and CHECK constraints to enforce true/false values.
• Validate and sanitize Boolean inputs before inserting them to keep data clean.
• Beware of automatic type conversions with Boolean affinity.
• Use helper functions and prepared statement parameters carefully.

Adopting these best practices will prevent the common pitfalls that trip up developers new to SQLite.

You now have a solid grasp of Boolean management – from storage representation to query syntax to solving issues.