← Database Systems Hub
Database Systems · Guide

SQLite

Embedded DB, single-file, mobile and edge use cases.

— min read Database Systems

Theory

SQLite: Embedded DB, single-file, mobile and edge use cases.

SQLite is a serverless, embedded SQL database engine. Unlike PostgreSQL or MySQL, there's no server process — the database is a single .db file that your application opens directly via a C library (or language binding). SQLite is the most deployed database in the world: it's built into Android, iOS, macOS, Windows, Firefox, Chrome, and every Python installation.

When to use SQLite: local-first applications (mobile apps, desktop software, embedded devices), test databases in CI (faster than spinning up Postgres), read-heavy applications with low concurrency, single-user tools, and small web apps (< 100K requests/day). SQLite uses file-level locking — only one writer at a time, which limits concurrent write throughput.

WAL mode (Write-Ahead Logging): pragma journal_mode=WAL enables concurrent reads while a write is in progress (readers don't block writers, writers don't block readers). WAL mode is strongly recommended for all applications with concurrent access. The tradeoff: you get a WAL file (-wal) and shared memory file (-shm) alongside the main .db file.

Data types: SQLite uses dynamic typing (type affinity). A column declared INTEGER can store a text value. Affinity rules: INTEGER, TEXT, BLOB, REAL, NUMERIC. This flexibility is a feature for prototype development but a trap in production — use CHECK constraints and application-layer validation to enforce types, since SQLite won't reject '2024-99-99' in a DATE column.

Limitations vs server databases: no network protocol (must be on the same filesystem as the application), one writer at a time (even with WAL), no stored procedures, limited ALTER TABLE (cannot drop columns or change constraints in older versions — use the "12-step procedure": create new table, copy data, drop old, rename new). No concurrent multi-process write access from different machines.

Performance: SQLite is surprisingly fast for read-heavy workloads — Cloudflare D1 and other edge databases are built on SQLite. Configure for performance: PRAGMA synchronous=NORMAL (default FULL is safe but slow), PRAGMA cache_size=-64000 (64MB page cache), PRAGMA mmap_size=30000000000 (memory-mapped I/O for large reads). Create indexes like any SQL database.

Modern use cases: LiteFS (distributed SQLite with Raft consensus for multi-node read replicas), Turso (libSQL fork with HTTP API for serverless SQLite), Cloudflare D1 (SQLite at edge nodes), Litestream (streams SQLite WAL to S3 for continuous backup). SQLite is increasingly viable for production web apps with the right replication layer.

Architecture Diagram

Users / clients
         |
  SQLite
         |
  Core services
         |
  Data + observability

Examples

bash 
# SQLite
# Embedded DB, single-file, mobile and edge use cases.
# Validate in staging before production rollout.

Key Concepts

Data modelHow SQLite structures and queries data
ConsistencyGuarantees under failure and replication
Scaling axisVertical vs horizontal growth patterns
OpsBackup, monitoring, and upgrade path

Interview Questions

What problem does SQLite solve?

It addresses the core use case described in production architecture — map features to reliability, scale, or velocity outcomes.

Key components of SQLite?

Identify inputs, outputs, control plane, data plane, and failure domains — interviewers want structured decomposition.

Common production pitfalls?

Misconfiguration, missing observability, no rollback path, and scaling bottlenecks under peak load.

How do you test changes safely?

Staging parity, canary/gradual rollout, automated health checks, and documented rollback.

Metrics to prove success?

Error rate, latency percentiles, throughput, cost, and toil reduction — pick one primary SLO.

Beginner vs advanced concern?

Beginners focus on setup; advanced teams focus on blast radius, security boundaries, and operability at 10× scale.

Best Practices

  • Treat SQLite config as code with review and CI validation.
  • Define SLOs and dashboards before production cutover.
  • Document rollback and ownership for on-call.
  • Use least privilege for credentials.

Common Mistakes

  • Adopting SQLite without measurable success criteria.
  • No staging environment mirroring production constraints.
  • Missing rollback path during incidents.
  • Undocumented on-call expectations.

Cheat Sheet

SQLiteEmbedded DB, single-file, mobile and edge use cases.
SLOService level objective
RollbackRevert to last known good
CanaryLimited blast-radius rollout
RunbookIncident steps

Practical Exercises

SQLite sandbox

Stand up SQLite locally or in free tier; document commands and failure recovery.

Failure drill

Introduce misconfiguration; practice detection and rollback under time limit.

← MariaDB

Related topics