Introduction to SQLite
SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine. It is the most used database engine in the world. It is an in-process library and its code is publicly available. It is free for use for any purpose, commercial or private. It is basically an embedded SQL database engine. Ordinary disk files can be easily read and write by SQLite because it does not have any separate server like SQL. The SQLite database file format is cross-platform so that anyone can easily copy a database between 32-bit and 64-bit systems. Due to all these features, it is a popular choice as an Application File Format.
It was designed by D. Richard Hipp for the purpose of no administration required for operating a program. in August 2000. As it is very lightweight compared to others like MySql and Oracle, it is called SQLite. Different versions of SQLite are released since 2000.
Installation on Windows:
1. Visit the official website of SQLite for downloading the zip file.
2. Download that zip file.
3. Create a folder in C or D ( wherever you want ) for storing SQLite by expanding zip file.
4. Open the command prompt and set the path for the location of SQLite folder given in the previous step. After that write “sqlite3” and press enter.
You can also directly open the .exe file from the folder where you have stored the SQLite whole thing.
After clicking on the selected .exe file it will open SQLite application
Installation on Linux:
Open Terminal, type this command and enter password
sudo apt-get install sqlite3 libsqlite3-dev
It will automatically install and once it asks Do you want to continue (Y/N) type Y and press enter. After successful installation, we can check it by command sqlite3.
Features of SQLite
- The transactions follow ACID properties i.e. atomicity, consistency, isolation, and durability even after system crashes and power failures.
- The configuration process is very easy, no setup or administration needed.
- All the features of SQL are implemented in it with some additional features like partial indexes, indexes on expressions, JSON, and common table expressions.
- Sometimes it is faster than the direct file system I/O.
- It supports terabyte-sized databases and gigabyte-sized strings and blobs.
- Almost all OS supports SQLite like Android, BSD, iOS, Linux, Mac, Solaris, VxWorks, and Windows (Win32, WinCE, etc. It is very much easy to port to other systems.
- Complete database can be stored in a single cross-platform disk file.
Applications of SQLite
- Due to its small code print and efficient usage of memory, it is the popular choice for the database engine in cellphones, PDAs, MP3 players, set-top boxes, and other electronic gadgets.
- It is used as an alternative for open to writing XML, JSON, CSV or some proprietary format into disk files used by the application.
- As it has no complication for configuration and easily stores file in an ordinary disk file, so it can be used as a database for small to medium sized websites.
- It is faster and accessible through a wide variety of third-party tools, so it has great application in different software platforms.
In SQLite, there are several dot commands which do not end with a semicolon(;). Here are all commands and their description:
Some DDL and DML Commands
It is same as compared to previous technology like MySQL, Oracle.
- Creating Table:
CREATE TABLE STUDENT(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
- Insert Command:
INSERT INTO STUDENT (ID, NAME, AGE, ADDRESS, FEES)
VALUES (1, 'Sunil', 28, 'Mumbai', 20000.00);
- Drop Table:
Drop Table Student;
Disadvantages of SQLite
- It is only used where there is low to medium traffic requests are there.
- The database size is restricted i.e. it is 2GB in most cases.