SQLite is a lightweight, open-source, serverless relational database management system (RDBMS). Unlike PostgreSQL or MySQL, SQLite does not require a separate database server. The complete database is stored in a single ordinary file, making SQLite simple to install, configure, and use.
SQLite is an embedded SQL database engine designed to provide database capabilities directly inside an application. An application can create, read, update, and delete data directly from an SQLite database file without communicating with a separate database server.
Application → SQLite Library → SQLite Database File
For example:
Java Application → JDBC Driver → SQLite → student.db
SQLite was created by D. Richard Hipp in 2000 while working on a project that required a database without the complexity of a database server. The name SQLite comes from its lightweight nature and its support for SQL. It was designed as a small, self-contained database engine that could be embedded directly into applications. Over the years, SQLite became one of the most widely deployed database engines and is used in many applications, mobile devices, browsers, and embedded systems.
Important Features
| Feature | Description |
|---|---|
| Lightweight | Very small compared with traditional database servers. |
| Serverless | Does not require a separate database server process. |
| Zero Configuration | No complex installation or server configuration is required. |
| Self-Contained | The database engine and storage are contained within the application environment. |
| Single Database File | Complete database can normally be stored in a single file. |
| Open Source | SQLite is freely available and released into the public domain. |
| SQL Support | Supports SQL for creating and manipulating data. |
| Transaction Support | Supports ACID transactions. |
| Cross-Platform | Works across major operating systems and platforms. |
| Embedded | Can be directly integrated into applications. |
| Portable | Database files can easily be moved between compatible systems. |
| Reliable | Designed for reliable local data storage. |
SQLite has a very simple architecture: There is no separate database server between the application and the database file.The application sends SQL commands through the SQLite interface, and SQLite directly performs the required operations on the database file.
For example:
Java Application → JDBC → SQLite JDBC Driver → college.db
Common SQLite Operations
SQLite supports commonly used SQL commands:
- CREATE – Creates database objects such as tables
- INSERT – Adds records
- SELECT – Retrieves records
- UPDATE – Modifies records
- DELETE – Removes records
- ALTER – Modifies table structure
- DROP – Removes database objects
SQLite is popular because it eliminates the need to install and manage a database server. A developer can simply include SQLite in an application and create a database file when required. It is particularly useful when:
- The application needs local data storage
- A separate database server is unnecessary
- The application needs a portable database
- Simple deployment is important
- The database is embedded inside another application
SQLite vs PostgreSQL
| SQLite | PostgreSQL |
|---|---|
| Serverless database | Client-server database |
| Very lightweight | More feature-rich and resource-intensive |
| Database stored primarily in a file | Database managed by PostgreSQL server |
| Minimal configuration | Requires server/database configuration |
| Excellent for embedded applications | Excellent for enterprise and server applications |
| Suitable for local storage | Suitable for multi-user applications |
| Easy deployment | More sophisticated deployment |
| Limited concurrent write capability | Strong concurrent multi-user support |
Applications of SQLite
SQLite is commonly used in:
- Mobile applications
- Desktop applications
- Embedded systems
- IoT devices
- Web browsers
- Local application storage
- Testing and prototyping
- Small and medium-sized applications
- Offline-first applications
- Educational applications
SQLite is a complete relational database engine embedded directly into an application, with the database stored in a simple file and without requiring a separate database server.
For SQLite Installations refer https://sqlite.org/download.html