SQLite Database

Introduction

• SQLite is a database engine written in the C programming language.
• D. Richard Hipp designed SQLite to fulfill the purpose of ‘no administration required’ for operating a program.
• The first release date -17 August 2000.
• Website is – https://www.sqlite.org.

Definition

• SQLite is a lightweight and compact database that does not require any kind of server to run.
• SQLite is a serverless database management system that does not require a separate server process to be running and can be embedded directly into an application.

Characteristics

• It has an in-memory library that we can call and use directly without installation or configuration.
• SQLite is very small and lightweight, with less than 400KiB fully configured or less than 250KiB with optional features omitted.
• It is not a standalone app; rather, it is a library that software developers embed in their apps. Hence, it is called the family of embedded databases.
• There are many libraries and classes available on Android to perform any kind of database queue on SQLite.
• SQLite stores the whole database (definitions, tables, indices, and the data itself) as a single cross-platform file on a host machine, allowing several processes or threads to access the same database concurrently.
• SQLite is suitable for smaller databases.
• SQLite is only for local, embedded RDBMS databases.
• To access databases on the network, we need to use whatever API is offered, such as a web service API used over HTTP(S).
• An SQLite database is normally stored in a single ordinary disk file. However, in certain circumstances, the database might be stored in memory. 
• SQLite uses PostgreSQL as a reference platform; hence, it generally follows PostgreSQL syntax.
• SQLite does not enforce type-checking; the type of a value is dynamic and not strictly constrained by the schema.
• SQLite packages the entire database into a single file. 
• SQLite is an offline database that is locally stored in the user’s device, and we do not have to create a connection to connect to this database

Advantages

• It is very simple with an easy-to-use API.
• SQLite itself is fast.
• SQLite has higher performance.
• SQLite comes with zero configuration, i.e., no setup or administration required.
• SQLite is self-contained, i.e., no external dependencies.
• SQLite is an open-source database, and hence its code is available in the public domain and is free to everyone to use for any purpose.
• It is easily integrated into any kind of mobile application.

Disadvantage

• One of the main drawbacks of the SQLite system is its lack of multi-user capabilities, which can be found in full-fledged RDBMS systems.

Use

• It is the most widely deployed database engine and is used by several of the top web browsers(Chrome, Safari, Opera, Android Browsers, etc), operating systems(Android, iOS, BlackBerry, MAC OS, Windows 10 OS, Symbian OS, Solaris, Fedora & Red Hat Linux OS, etc ), mobile phones, Middlewares (ADO.NET, ODBC, COM[ActiveX]), Web Applications Frameworks (Symfony, Laravel, Django’s, Drupal, Ruby on Rails, ) and other embedded systems(Adobe systems, Audacity, Skype, Flame – a malware, TomTom – a GPS system etc. ).
• Many programming languages(Java, Python, PHP, Perl, etc.) have bindings to the SQLite library.

Example

• SQLite is one of the simplest databases to use because it does not require a separate server installation.
• Download and install it from https://www.sqlite.org/download.html.
• Now, navigate to the folder where it sqlite3 is located/installed.
• Open the terminal/command prompt and change the current directory to the sqlite3 folder.
• To create a new database named mydatabase.db in SQLite3 (or open it if it already exists) –

sqlite3> sqlite3 mydatabase.db (Press enter)

• To create a table using SQL commands in SQLite –

sqlite>CREATE TABLE students (

id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER NULL,
grade TEXT,
dob DATE,
admission_time TIME,
address TEXT,
image BLOB,
marks FLOAT
); (Press enter)

• To insert/save the record in SQLite3

sqlite>INSERT INTO students (name, age, grade, dob, admission_time, address, marks)
VALUES (‘Rahul’, 20, ‘A’, ‘2004-05-10′, ’09:30:00’, ‘123 Main Street, New Delhi’, 89.75);

• To retrieve/fetch data or records from sqlite3 –

• To Update/Edit/Modify the record –

Syntax: UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

sqlite>UPDATE students
SET age = 21,
grade = ‘A’,
marks = 91.50
WHERE name = ‘Rahul’;

sqlite>UPDATE students
SET dob = ‘2005-01-15’,
admission_time = ’08:45:00′
WHERE id = 2;

• To Delete/Remove the record –

sqlite>DELETE FROM students
WHERE id = 1;

sqlite>DELETE FROM students; (Deletes only the data, not table structure.)

sqlite>DROP TABLE students; (Deletes both the data and table structure.)

• To Exit from the SQLite3 database –

sqlite> .exit (Press enter)