Summary: This tutorial provides a brief overview of SQLite and its unique features that make it the most widely deployed SQL database engine.
What is SQLite
SQLite is a software library that provides a relational database management system. The “lite” in SQLite means lightweight nature regarding setup, database administration, and required resources.
SQLite has the following notable features:
- Self-contained
- Serverless
- Zero-configuration
- Transactional
Serverless
An RDBMS such as MySQL or PostgreSQL typically requires a separate server process to run. The applications that need to access the database server use TCP/IP protocol to send and receive requests. This setup is known as client/server architecture.
The following diagram illustrates the client/server architecture of an RDBMS:
SQLite does NOT work this way.
SQLite does NOT require a server to run.
SQLite database is integrated with the application that accesses it. The application interacts with the SQLite database by reading and writing directly from the database files stored on disk.
The following diagram illustrates the server-less architecture of SQLite:
Self-Contained
SQLite is self-contained, requiring minimal support from the operating system or external libraries. This makes SQLite suitable in any environment, especially in embedded devices like smartphones, game consoles, handheld media players, etc.
SQLite is developed using ANSI-C and the source code is available as a single file sqlite3.c, along with its header file sqlite3.h.
If you want to develop an application that uses SQLite, you can drop these files into your project and compile them with your source code.
Zero-configuration
Thanks to SQLite’s serverless architecture, you don’t need to “install” SQLite before using it. There is no need to configure, start, or stop a server process. Additionally, SQLite does not require any configuration files.
Transactional
All transactions in SQLite are fully ACID-compliant, meaning that all queries and changes are Atomic, Consistent, Isolated, and Durable.
In other words, all changes within a transaction take place completely or not at all, even when an unexpected situation like an application crash, power failure, or operating system shut down.
SQLite unique features
SQLite uses dynamic types for tables, meaning you can store any value in any column, regardless of the declared data type.
SQLite allows a single database connection to access multiple database files simultaneously. This feature allows you to join tables in different databases or copy data between databases with a single command.
Additionally, SQLite can create in-memory databases, which is very fast for prototyping and testing.
When to use SQLite
When you need simplicity, speed, and minimal resources, you might consider SQLite. For example:
- Embedded Apps: SQLite is ideal for apps that need to store data locally without the overhead of separating database servers such as mobile apps and desktop software.
- Local storage: SQLite is suitable for apps that need to store user preferences, settings, or cached data locally.
- Cross-platform apps: Since SQLite runs on multiple platforms, it is suitable for apps that need to run cross-platform.
- Prototyping and development: SQLite does not require a complex setup, making it ideal for quick prototyping, development, and testing.
- Internet of Things (IoT) devices: IoT devices have limited resources. SQLite is very lightweight and is suitable for IoT devices.
References
- https://www.sqlite.org – SQLite homepage
- https://www.sqlite.org/features.html – SQLite features
- https://www.sqlite.org/copyright.html – SQLite license
- https://www.sqlite.org/docs.html – SQLite documentation
