Introduction to SQLite
This tutorial helps you understand what SQLite is, how it differs from SQL, why it is needed, and how it handles application databases.
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is one of the fastest-growing database engines, which is growing in popularity, not in size. The SQLite source code is not restricted by copyright.
What is SQLite?
SQLite is a library that resides in the process, implementing a self-contained, serverless, zero-configuration, transactional SQL database engine. It is a zero-configuration database, which means unlike other databases, you do not need to configure it on your system.
Like other databases, the SQLite engine is not a standalone process and can be statically or dynamically linked as per application requirements. SQLite directly accesses its storage files.
Why use SQLite?
- No need for a separate server process or operating system (serverless).
- SQLite requires no configuration, meaning no installation or management.
- A complete SQLite database is stored in a single cross-platform disk file.
- SQLite is very small and lightweight, fully configured less than 400KiB, and omitting optional features less than 250KiB.
- SQLite is self-contained, meaning it requires no external dependencies.
- SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads.
- SQLite supports most of the query language features of the SQL92 (SQL2) standard.
- SQLite is written in ANSI-C and provides a simple and easy-to-use API.
- SQLite runs on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT).
History
- 2000 - D. Richard Hipp designed SQLite to operate programs without needing management.
- 2000 - In August, SQLite 1.0 was released with the GNU Database Manager.
- 2011 - Hipp announced the addition of an UNQl interface to SQLite DB, developing UNQLite (document-oriented database).
SQLite Limitations
In SQLite, the following features of SQL92 are not supported:
| Feature | Description |
|---|---|
| RIGHT OUTER JOIN | Only LEFT OUTER JOIN is implemented. |
| FULL OUTER JOIN | Only LEFT OUTER JOIN is implemented. |
| ALTER TABLE | Supports RENAME TABLE and ADD COLUMN variants of ALTER TABLE commands, but does not support DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT. |
| Trigger Support | Supports FOR EACH ROW triggers, but not FOR EACH STATEMENT triggers. |
| VIEWs | In SQLite, views are read-only. You cannot execute DELETE, INSERT, or UPDATE statements on views. |
| GRANT and REVOKE | The only applicable access permissions are the normal file access permissions of the underlying operating system. |
SQLite Commands
The standard SQLite commands for interacting with relational databases are similar to SQL. Commands include CREATE, SELECT, INSERT, UPDATE, DELETE, and DROP. These commands can be categorized based on their nature of operation:
DDL - Data Definition Language
| Command | Description |
|---|---|
| CREATE | Creates a new table, a view of a table, or other objects in the database. |
| ALTER | Modifies an existing database object, such as a table. |
| DROP | Deletes an entire table, a view of a table, or other objects in the database. |
DML - Data Manipulation Language
| Command | Description |
|---|---|
| INSERT | Creates a record. |
| UPDATE | Modifies records. |
| DELETE | Deletes records. |
DQL - Data Query Language
| Command | Description |
|---|---|
| SELECT | Retrieves certain records from one or more tables. |