What is SQLite?

SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine embedded directly into your application. Rather than running as a separate server process, SQLite reads and writes directly to ordinary files on disk. This simplicity makes it the most widely deployed database engine in the world — found in every smartphone, web browser, desktop application, and billions of IoT devices.

Serverless Architecture

Traditional databases like PostgreSQL or MySQL run as a background server daemon. Your application connects to that server over a network socket, authenticates, sends queries, and receives results. SQLite takes a completely different approach: the entire database — tables, indexes, data — lives in a single file on disk. Your application links the SQLite library directly and calls functions to execute SQL. There is no network round-trip, no authentication handshake, and no separate process to manage.

This serverless design has profound implications:

• Zero administration — no server to install, configure, tune, or restart
• Zero network overhead — queries execute in the same process as your application
• Portability — the database is a single file you can copy, move, email, or back up with cp
• Reliability — fewer moving parts means fewer failure modes

Where SQLite Is Used

# SQLite databases are everywhere:
# - Every Android and iOS app (via the built-in SQLite library)
# - Firefox, Chrome, and Safari (browser history, bookmarks, cookies)
# - macOS and iOS system daemons
# - Python's standard library (the sqlite3 module ships with Python)
# - Electron desktop apps (VS Code, Slack, Discord all use SQLite internally)
# - Embedded firmware and IoT devices

SQLite vs Client-Server Databases

SQLite is optimised for different workloads than PostgreSQL or MySQL. It excels when:

• Data access comes from a single process (or a small number of processes on the same machine)
• You need local, embedded storage without network infrastructure
• Your write rate is moderate — SQLite serialises writes, so thousands of concurrent writers will contend

Client-server databases excel when:

• Many clients connect simultaneously from different machines
• You need fine-grained user permissions or network-level access control
• Write throughput requirements exceed what a single file lock allows

The SQLite File Format

Every SQLite database is stored in a single file using a documented, cross-platform binary format. The file begins with a 100-byte header that identifies it as an SQLite database. The rest of the file is divided into fixed-size pages (default 4 096 bytes). Tables and indexes are stored as B-trees spread across these pages. Because the format is stable and well-documented, SQLite files written today will be readable by software decades from now — the developers consider it a long-term archival format.

ACID Compliance

Despite its simplicity, SQLite is fully ACID-compliant:

• Atomicity — a transaction either commits completely or not at all
• Consistency — constraints (NOT NULL, UNIQUE, FOREIGN KEY) are enforced
• Isolation — readers do not see uncommitted changes from writers
• Durability — once a transaction commits, data survives a crash

SQLite achieves durability through a write-ahead log (WAL) or a rollback journal written to disk before the main database file is modified.

Summary

SQLite is the right choice for local, embedded, single-process storage. Its combination of zero configuration, a portable single-file format, full SQL support, and ACID guarantees makes it uniquely suited to the scenarios where it dominates. Understanding when to use SQLite versus a client-server database is the foundation of choosing the right tool for any project.