SQLite is a RDBMS (Relational Database Management System) similar to MySQL or Oracle. It has all the features of relational database engine including normalization. It’s lightweight and one of the most often used database engine across the globe, particularly with mobile applications.
Why Use SQLite?
SQLite is a database engine designed with an objective to keep database design and management as simple as possible. SQLite is easier to administer with no overhead, easy to operate and embed in an application, simpler to maintain, and extremely efficient in terms of resource usage and customization. The following are some of the distinguishing attributes of SQLite Database.
- SQLite is ‘server-less’ means it operates without a separate server.
- SQLite requires ‘zero configuration’ since creating a new SQLite database instance is as easy as opening a file and requires no complex configuration on an isolated hosted system.
- SQLite uses ‘Transactional SQL’ (T-SQL), which is ACID (atomicity, consistency, isolation and durability) compliant. This ensures safe access to SQLite database from multiple threads using modifications through transactions, which maintains consistency in a database. Therefore, these transactions make it durable even on a system failure or crash.
- SQLite is ‘self -contained’, which means a single library that has a complete database system embeds directly into the application. It requires a size less than 300 KB and saved as single cross-platform disk file.
With the simplicity of SQLite, reliability is also there because of less complication involved in the implementation. To achieve this simplicity, SQLite removed some characteristics that include a set of built-in functions, stored procedure, XML and Java extensions and full-gained access control. Therefore, SQLite is not designed to replace an enterprise database engine like Oracle.
When to Use SQLite?
Due to the small size of SQLite code structure that makes it memory efficient, it’s widely used in mobile application development owing to the limitations of memory and storage on hand held devices. It is also preferable to use SQLite in small to medium size websites since there is no need for complex configuration and maintenance, which also makes SQLite a replacement for an enterprise database engine for testing and early launch of an application.
SQLite commands interact with the relational database in a way similar to SQL. There are three groups of these commands based on the category of operations they perform. These are Data Definition Language (CREATE, ALTER and DROP), Data Manipulation Language (INSERT, UPDATE and DELETE) and Data Query Language (SELECT).
SQLite Data Types
Unlike SQL, SQLite uses a dynamic type system. In SQL, the data type of a value is determined by its column data type in a table. In contrast, the data type of value is related to the value itself.
Every value stored in a SQLite database is associated with one of five storage classes.
- ‘NULL’ value.
- ‘INTEGER’ is a signed 64 bit numeric value. SQLite optimizes the storage of small integers by itself, which is why it is stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the integer value.
- ‘REAL’ is a 64 bit floating point value.
- ‘TEXT’ is a text string, which is stored using database encoding such as UTF-8.
- ‘BLOB’ is a blob of data, which is stored as direct input. Both ‘Text’ and ‘BLOB’ have default size of 1,000,000,000 bytes.
SQLite uses the concept of ‘type affinity’ on columns of a table that reduces the compatibility issues with other database engines. Any column can store any type of data, but the recommended storage class for a column is called its affinity. These type affinities are ‘TEXT’, ‘NUMERIC’, ‘INTEGER’, ‘REAL’ and ‘NONE’. The following is the details of the data types that are used to create the database table and their affinities.
- ‘TEXT’ affinity applies on VARCHAR, NCHAR, NVARCHAR, and TEXT, etc.
- ‘NUMERIC’ applies on BOOLEAN, DATE and DATETIME, etc.
- ‘INTEGER’ applies on INT, TINYINT, SMALLINT, MEDIUMINT and BIGINT, etc.
- ‘REAL’ applies on REAL, DOUBLE and FLOAT, etc.
- ‘NONE’ applies on BLOB and column with no specified data type.
From the above list of affinities, it is clear that there is no specified data type available for date-time in SQLite, but by default, these values are saved as numeric data (number of seconds since the Jan 01, 1970 midnight). Sometimes, a text string is also used to save the date-time. Same goes for a Boolean where true and false are stored as 1 and 0 respectively in a column.
SQLite CRUD Statement
SQLite statements start with keywords such as CREATE, INSERT, UPDATE, DELETE, SELECT, ALTER, DROP, etc. and is case insensitive. CRUD (Create, Read, Update and Delete) are commonly used operations in every database engine. To use CRUD, a SQLite database is required that is created using a ‘sqlite3’ command (need precompiled binaries of SQLite with a folder path set in PATH environment variable) followed by ‘DatabaseName.db’ in a command prompt.
The ‘CREATE’ statement is used to create new tables in SQLite database. Basic syntax and a simple example of ‘CREATE’ ‘TABLE’ are as follow.
Create Table Statement Syntax:
CREATE TABLE DATABASE_NAME. TABLE_NAME (
PRIMARY KEY (one or more columns)
Create EMPLOYEE Table:
sqlite3> CREATE TABLE COMPANY.EMPLOYEE (
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
The ‘INSERT INTO’ statement inserts new rows in a table and ‘UPDATE’ is used to modify the existing rows based on a certain criteria (‘WHERE’ clause). Following is the SQLite syntax and examples for these statements.
Insert Into Statement Syntax:
INSERT INTO TABLE_NAME (column1, column2… columnN) VALUES (value1, value2…
Update Statement Syntax:
UPDATE TABLE_NAME SET column1=value1, column2=value2 … columnN=valueN [where
Insert 1 row in EMPLOYEE Table:
INSERT INTO EMPLOYEE (ID, NAME, AGE) VALUES (1, ‘John’, 22);
Update 1 row in EMPLOYEE Table:
UPDATE EMPLOYEE SET AGE = 21 WHERE ID = 1;
The ‘SELECT’ statement retrieves the specific data of specific rows in table. Finally ‘DELETE’ removes the particular rows based on criteria or all the rows in table. Following example demonstrates the syntax of these statements in SQLite.
Select Statement Syntax:
SELECT column1, column2, columnN FROM TABLE_NAME [where criteria_To_Retrieve_Row];
Delete Statement Syntax:
DELETE FROM TABLE_NAME [where criteria_To_Delete_Row];
Select All EMPLOYEE’s Data with AGE greater than 25:
SELECT * FROM EMPLOYEE WHERE AGE > 25;
Delete All EMPLOYEE’s Data:
DELETE FROM EMPLOYE;