SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine embedded directly into applications. Unlike traditional client-server database systems, SQLite reads and writes directly to ordinary disk files, making it the most widely deployed database in the world—present in billions of devices including smartphones, browsers, and embedded systems. Its simplicity masks sophisticated features: full ACID compliance, a rich SQL implementation, and remarkable reliability backed by one of the most thorough test suites in open-source software. One key insight: SQLite excels when your application needs local storage with SQL capabilities but doesn't require concurrent write-heavy workloads across multiple clients—it is optimized for read-heavy scenarios and single-writer patterns, which is perfect for mobile apps, IoT devices, and data analysis tools.
What This Cheat Sheet Covers
This topic spans 29 focused tables and 200 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.
Table 1: Storage Classes and Data Types
| Type | Example | Description |
|---|---|---|
CREATE TABLE t(id INTEGER) | • Signed integer stored in 1, 2, 3, 4, 6, or 8 bytes depending on magnitude • range −9,223,372,036,854,775,808 to +9,223,372,036,854,775,807. | |
name TEXT | • String stored in UTF-8, UTF-16BE, or UTF-16LE encoding • variable length up to 1 GB. | |
price REAL | • 8-byte IEEE 754 floating-point number • used for decimal values. | |
image BLOB | • Binary data stored exactly as input • used for images, files, or any binary content. |