Boolean Datatype in SQLite Database

The SQLite database is an embedded file-based relational database management system, or RDBMS, written in the C programming language. It is available in the form of a library out of the box.

A file-based database creates a single file that stores all the databases and tables.

Since SQLite is a lightweight database, compared to its peers such as PostgreSQL, MySQL, etc., it has various limitations. For example, SQLite doesn’t provide efficient access, lacks scalability, has no multi-user capabilities, database size constraints, slow processing of gigantic tables, bare minimum security, no stored procedures, basic datatypes, etc.

SQLite also misses a basic datatype, Boolean. This article will teach how to implement the Boolean datatype using existing datatypes.

Boolean Datatype in SQLite Database

SQLite database doesn’t support Boolean datatype. However, we can use two ways to represent Boolean values in an SQLite database.

Represent Boolean Datatype as Integers

A Boolean field can only have two values: true and false. We can represent true as 1 and false as 0 using integers.

This is a better approach to represent Booleans than using strings because it takes longer to process a string, and they take up more space for storage. A Boolean field can be represented even using a single bit because we need two values, 0 and 1.

Refer to the following SQL script for an example.

-- creating a table
CREATE TABLE students (
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    international_student INT NOT NULL
);

-- inserting some data into the old table
INSERT INTO students VALUES (1, "Stefan", 13, 1);
INSERT INTO students VALUES (2, "Damon", 14, 0);
INSERT INTO students VALUES (3, "Elena", 12, 1);
INSERT INTO students VALUES (4, "Caroline", 12, 1);
INSERT INTO students VALUES (5, "Bonnie", 13, 0);

-- printing table
SELECT "Students";
SELECT "--------";
SELECT * FROM students;

Output:

Students
--------
1|Stefan|13|1
2|Damon|14|0
3|Elena|12|1
4|Caroline|12|1
5|Bonnie|13|0

Represent Boolean Datatype as Strings

We can represent true as "true" and false as "false" using string. Other representation styles can be T/F, TRUE/FALSE, and True/False.

Refer to the following SQL script for an example.

-- creating a table
CREATE TABLE students (
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    international_student VARCHAR(5) NOT NULL
);

-- inserting some data into the old table
INSERT INTO students VALUES (1, "Stefan", 13, "true");
INSERT INTO students VALUES (2, "Damon", 14, "false");
INSERT INTO students VALUES (3, "Elena", 12, "true");
INSERT INTO students VALUES (4, "Caroline", 12, "true");
INSERT INTO students VALUES (5, "Bonnie", 13, "false");

-- printing table
SELECT "Students";
SELECT "--------";
SELECT * FROM students;

Output:

Students
--------
1|Stefan|13|true
2|Damon|14|false
3|Elena|12|true
4|Caroline|12|true
5|Bonnie|13|false