Introduction to SQLite (original) (raw)

Last Updated : 23 Aug, 2024

**SQLite is a highly **efficient, **serverless, and self-contained SQL database engine that stands out for its simplicity and ease of integration. Designed to be embedded within applications, **SQLite eliminates the need for separate database server processes and complex configurations.

In this article, We will learn about SQLite in detail.

What is SQLite?

#include <sqlite3.h>

int main(int argc, char **argv) { sqlite3 *db; int rc;

rc = sqlite3_open("test.db", &db); if (rc != SQLITE_OK) { // error handling }

// do something with the database

sqlite3_close(db); return 0; }

`

History of SQLite

Why Use SQLite?

There are several reasons why you might choose to use SQLite in your project:

  1. **Ease of use: SQLite is very easy to get started with, as it requires no setup or configuration. You can simply include the library in your project and start using it.
  2. **Embeddability: SQLite is designed to be embedded into other applications. It is a self-contained, serverless database engine, which means you can include it in your application without the need for a separate database server.
  3. **Lightweight: SQLite is a very lightweight database engine, with a small library size (typically less than 1MB). This makes it well-suited for use in applications where the database is embedded directly into the application binary, such as **mobile apps.
  4. **Serverless: As mentioned earlier, SQLite is a serverless database engine, which means there is no need to set up and maintain a separate database server process. This makes it easy to deploy and manage, as there are no additional dependencies to worry about.
  5. **Cross-platform: SQLite is available on many platforms, including Linux, macOS, and Windows, making it a good choice for cross-platform development.
  6. **Standalone: SQLite stores all of the data in a single file on the filesystem, which makes it easy to copy or backup the database.
  7. **High reliability: SQLite has been widely tested and used in production systems for many years, and has a reputation for being a reliable and robust database engine.

**An easy way to get started with SQLite

Because SQLite is an embedded database, you actually don't need to 'download' it in the same way that you would download MySQL or PostgreSQL for example. You can create and interact with SQLite databases using a range of tools.

An easy way to get started would be to:

  1. Download an example SQLite dataset, like the **Sakila dataset, available here on GitHub
  2. Download a **GUI program to access the database, like Beekeeper Studio, or DBeaver
  3. Double-click the ****'.db'** file to open it.
  4. Using these tools, you can navigate your SQLite file in a spreadsheet-like way.

**Installation on Windows:

If you want to install the official SQLite binary and interact with the database using the terminal, you can follow these directions:

1. Visit the official website of SQLite to download the zip file.

2. Download that zip file.

3. Create a folder in C or D ( wherever you want ) for storing SQLite by expanding the zip file.

4. Open the command prompt and set the path for the location of the 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 the password

**sudo apt-get install sqlite3 libsqlite3-dev

sqlite3 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. installation of sqlite3

**Features of SQLite

  1. The transactions follow ACID properties i.e. atomicity, consistency, isolation, and durability even after system crashes and power failures.
  2. The configuration process is very easy, no setup or administration is needed.
  3. All the features of SQL are implemented in it with some additional features like **partial indexes, indexes on expressions, **JSON, and common table expressions.
  4. Sometimes it is faster than the direct file system I/O.
  5. It supports terabyte-sized databases and gigabyte-sized strings and blobs.
  6. 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.
  7. A complete database can be stored in a single cross-platform disk file.

**Applications of SQLite

  1. Due to its small code print and efficient usage of memory, it is the popular choice for the database engine in cell phones, **PDAs, MP3 players, set-top boxes, and other electronic gadgets.
  2. It is used as an alternative for open to writing XML, JSON, CSV, or some proprietary format into disk files used by the application.
  3. 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.
  4. It is faster and accessible through a wide variety of third-party tools, so it has great applications in different software platforms.

**SQLite Commands In SQLite, there are several dot commands which do not end with a semicolon(;). Here are all commands and their description:

SQLite Commands

In SQLite, **DDL (Data Definition Language) is used to create and modify database objects such as tables, indices, and views. Some examples of DDL statements in SQLite are:

**DML (Data Modification Language) is used to modify the data stored in the database. Some examples of DML statements in SQLite are:

**DQL (Data Query Language) is used to retrieve data from the database. Some examples of DQL statements in SQLite are:

Limitations of SQLite

Conclusion

SQLite's lightweight nature and ease of use make it a versatile option for developers needing an embedded database solution. Its self-contained structure and ACID compliance provide reliability and simplicity for handling transactions.