SQLite fundamentals explained

What SQLite is

SQLite is a self-contained, serverless, zero-configuration relational database engine written in C. The library is typically under 1 MB, runs in-process inside your app, and stores all tables, indexes, and metadata in a single cross-platform file (conventionally .db or .sqlite). Because there is no network protocol, a query is a function call — microseconds of latency instead of milliseconds over TCP.

SQLite targets embedded and edge workloads: mobile apps, desktop tools, browser storage (via WebAssembly ports), IoT firmware, test fixtures, and read-heavy web tiers that replicate a static snapshot. It is not designed for high-concurrency OLTP with dozens of simultaneous writers — that is where Postgres shines. Understanding that boundary is the core architectural decision.

Architecture: library, file, single writer

In-process execution

Your app calls sqlite3_open(), issues SQL through sqlite3_prepare_v2() and sqlite3_step(), then closes the handle. No daemon listens on port 5432. Multiple processes can open the same database file, but SQLite serializes writes with file-level locks — only one writer at a time.

Page-based storage

Data is organized in fixed-size pages (default 4 KB, configurable at creation). Tables and indexes are B-trees stored across pages. The sqlite3_analyzer tool reports page utilization — useful when diagnosing bloat from heavy UPDATE churn without VACUUM.

Concurrency model

Readers do not block readers. One writer blocks all other writers and can block readers depending on journal mode. When a write lock is held, other connections get SQLITE_BUSY. Set a busy_timeout (e.g., 5,000 ms) so SQLite retries instead of failing immediately — essential for multi-threaded apps sharing one connection pool or multiple processes on the same file.

Journaling: rollback vs WAL

SQLite must survive crashes mid-transaction. Two journal modes dominate production:

• DELETE / TRUNCATE / PERSIST (rollback journal) — the classic mode. On write, affected pages copy to a journal file; commit deletes the journal. Readers can block during writes.
• WAL (write-ahead log) — the modern default for most apps. Writers append changes to a -wal file; readers see a consistent snapshot from the main database. Readers do not block writers and writers do not block readers, though only one writer still runs at a time. Enable with PRAGMA journal_mode=WAL;.

WAL also enables continuous readers during writes — critical for local-first apps that sync in the background while the UI queries. Checkpoint the WAL periodically (PRAGMA wal_checkpoint(TRUNCATE);) on shutdown or before copying the database file — copying a live WAL database without checkpointing can produce an inconsistent snapshot.

Types, constraints, and SQL dialect

Dynamic typing with type affinity

SQLite uses dynamic typing: a column declared INTEGER can store text if you insert a string — unlike Postgres's strict schemas. Type affinity guides conversions: INTEGER affinity prefers integers, TEXT prefers strings, REAL prefers floats, BLOB stores raw bytes. Declare columns intentionally; do not rely on SQLite to enforce types unless you enable STRICT tables (SQLite 3.37+).

Constraints and foreign keys

PRIMARY KEY, UNIQUE, NOT NULL, CHECK, and FOREIGN KEY are supported. Foreign keys are off by default for backward compatibility — enable per connection: PRAGMA foreign_keys = ON;. For transaction semantics and isolation behavior, see our transactions and isolation guide.

SQL coverage and gaps

SQLite implements most of standard SQL: JOINs, subqueries, window functions (3.25+), CTEs, UPSERT (INSERT ... ON CONFLICT), and RETURNING. Gaps versus Postgres include limited ALTER TABLE (no drop column in older versions), no native BOOLEAN type (use INTEGER 0/1), weaker concurrent write throughput, and no built-in replication — you ship the file or use Litestream/Turso for remote sync.

Indexes and query planning

SQLite uses B-tree indexes identical in spirit to other relational engines. The query planner is cost-based; run EXPLAIN QUERY PLAN (not just EXPLAIN) to see whether an index scan or table scan is chosen.

• Single and composite indexes — leading-column rule applies: index on (user_id, created_at) helps WHERE user_id = ? but not WHERE created_at > ? alone.
• Partial indexes — CREATE INDEX ... WHERE status = 'open' shrinks index size for filtered queries.
• Covering indexes — include extra columns so the engine satisfies the query from the index alone without table lookups.

For deeper index theory, pair this with our database indexing guide. Run ANALYZE; after bulk loads so the planner has accurate statistics.

Extensions: FTS5 and JSON1

Full-text search (FTS5)

The FTS5 virtual table module provides tokenized full-text search with ranking, phrase queries, and prefix matching — enough for in-app search without Elasticsearch. Create with CREATE VIRTUAL TABLE docs USING fts5(title, body); and query via MATCH. FTS5 shines in offline mobile apps and desktop tools; at web scale with heavy concurrent indexing, consider a dedicated search engine.

JSON1 functions

SQLite's JSON1 extension (enabled by default in modern builds) stores JSON as TEXT and exposes json_extract(), json_each(), and -> path operators. Useful for semi-structured config blobs without a document store — but no GIN-equivalent index on JSON paths; extract hot fields into typed columns if you filter on them often.

Worked example: local-first task tracker

A desktop productivity app stores tasks locally and syncs to a server hourly. Schema design:

CREATE TABLE tasks (
  id          TEXT PRIMARY KEY,
  title       TEXT NOT NULL,
  status      TEXT NOT NULL DEFAULT 'open'
              CHECK (status IN ('open','done','archived')),
  priority    INTEGER NOT NULL DEFAULT 0,
  updated_at  INTEGER NOT NULL  -- Unix epoch ms
);
CREATE INDEX idx_tasks_status_updated
  ON tasks(status, updated_at DESC);
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;

The UI thread reads open tasks: SELECT * FROM tasks WHERE status = 'open' ORDER BY priority DESC, updated_at DESC; — the composite index covers the filter and sort. A background sync worker UPSERTs rows from the server. WAL lets reads continue during the bulk INSERT ... ON CONFLICT(id) DO UPDATE batch. On app quit, run PRAGMA wal_checkpoint(TRUNCATE); so the next cold start opens a clean single file. When monthly active users exceed ~100 concurrent writers or the database grows past a few GB with heavy churn, plan a migration path to Postgres — not because SQLite fails, but because connection-level locking becomes the bottleneck.

SQLite vs alternatives: decision table

Common pitfalls

• Foreign keys left off — orphaned rows silently accumulate; always PRAGMA foreign_keys = ON; on every connection.
• Default rollback journal on multi-threaded apps — readers stall during writes; switch to WAL.
• No busy_timeout — sporadic SQLITE_BUSY errors under light contention look like random bugs.
• One connection per thread without pooling — opening thousands of handles exhausts file descriptors; use a connection pool or a single writer queue.
• Copying the database file live — without checkpointing WAL, backups can be corrupt; use sqlite3 .backup or Litestream.
• Storing large BLOBs — databases above ~1 GB with big media files perform poorly; store files on disk and keep paths in SQLite.
• SQLite in production web tier with many writers — network filesystems (NFS) and SQLite do not mix; use local SSD only.
• Assuming strict typing — inserting wrong types causes subtle comparison bugs; use STRICT tables or validate in application code.

Production checklist

• PRAGMA journal_mode = WAL; enabled and verified on first open.
• PRAGMA foreign_keys = ON; set on every connection.
• busy_timeout configured (3,000–10,000 ms depending on workload).
• Indexes on every WHERE, JOIN, and ORDER BY hot path — verified with EXPLAIN QUERY PLAN.
• ANALYZE; run after schema changes or bulk imports.
• Parameterized queries only — never string-concatenate user input.
• WAL checkpoint on graceful shutdown; scheduled backup via .backup API or Litestream.
• Database file on local filesystem, not NFS or SMB mounts.
• Migration strategy documented for the day write concurrency exceeds SQLite's comfort zone.
• Schema version tracked with PRAGMA user_version or a migrations table.

Key takeaways

• SQLite is an embedded, serverless SQL engine — one file, one library, microsecond query latency.
• WAL mode is the default recommendation for any app with concurrent reads and writes.
• Single-writer locking is the hard ceiling — plan Postgres before you hit it in production SaaS.
• FTS5 and JSON1 cover search and semi-structured data without extra services for modest scale.
• Pair SQLite skills with solid SQL fundamentals and clear migration paths to PostgreSQL when you scale.

Related reading

• SQL fundamentals explained — SELECT, JOINs, aggregates, and subqueries that SQLite implements
• PostgreSQL fundamentals explained — when your app outgrows embedded SQLite
• Database indexing explained — B-tree theory, composite indexes, and covering indexes
• Database transactions and isolation levels explained — ACID guarantees and locking behavior