SQLite
A self-contained, serverless, zero-configuration SQL database engine that stores entire databases in single files.
Also known as: SQLite3, SQLite Database
Category: Software Development
Tags: databases, software-development, data-integrity
Explanation
SQLite is a lightweight, embeddable relational database engine that runs in-process with applications rather than as a separate server. Created by D. Richard Hipp in 2000, it has become the most widely deployed database engine in the world, embedded in every smartphone, most web browsers, and countless applications.
Unlike client-server databases such as PostgreSQL or MySQL, SQLite requires no separate server process, no configuration, and no administration. The entire database is stored in a single cross-platform file, making it exceptionally portable and easy to use. Despite its simplicity, SQLite provides full ACID compliance with complete transaction support.
**Key characteristics**:
- **Serverless**: No separate server process needed
- **Zero-configuration**: No setup or administration required
- **Self-contained**: Single library with minimal dependencies
- **Single file**: Entire database in one portable file
- **ACID compliant**: Full transaction support with rollback capabilities
- **Cross-platform**: Works on any operating system
- **Public domain**: No licensing restrictions
**When to use SQLite**:
SQLite excels in mobile and desktop applications, embedded systems, IoT devices, local caching, development environments, small to medium websites, data analysis, and as an application file format. It is not ideal for high-concurrency write-heavy workloads, very large datasets (over 1TB), applications with many concurrent users requiring client-server architecture, or distributed systems needing replication.
**Common use cases**:
- **Mobile apps**: Both iOS and Android use SQLite extensively for local storage
- **Web browsers**: Chrome, Firefox, and Safari store data in SQLite databases
- **Desktop applications**: Local storage for Electron apps and native applications
- **Caching**: Local cache for applications and command-line tools
- **Configuration**: Application settings and preferences storage
- **Analytics**: Quick data exploration with SQL queries
**Extensions and capabilities**:
SQLite supports powerful extensions including FTS5 for full-text search, R*Tree for spatial indexing, and built-in JSON functions since version 3.38. Tools like Litestream enable streaming replication to cloud storage.
The file format supports databases up to 281 TB in size, with common extensions being .db, .sqlite, or .sqlite3. Performance can be optimized through transactions for bulk operations, proper indexing, WAL journal mode for better concurrency, and prepared statements for security and efficiency.
Related Concepts
← Back to all concepts