Embedded database use cases

SQLite is a library embedded in the application process — no separate database server. One database file (or in-memory) with full SQL and ACID transactions in a compact footprint.

Strong fits:

• Mobile and desktop apps with local durable state
• Edge functions and small services with modest concurrency
• Integration tests and fixtures (fast, isolated file per test)
• CLI tools, browsers (WASM), and embedded devices
• Prototyping before committing to a server database

					-- Typical app-open pattern
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;  -- better concurrent readers

CREATE TABLE IF NOT EXISTS settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
);
				

Choose SQLite when the deployment unit is a single app instance, data size is modest, and ops simplicity beats horizontal scale.

On interviews: contrast embedded vs client/server databases, name WAL mode, and explain when SQLite is a deliberate product choice—not a "toy database."

Common pitfalls: putting SQLite on NFS/network drives; expecting multi-writer server semantics; skipping foreign_keys pragma; no migration strategy for shipped app databases.

The trade-off is zero-ops embedded storage versus limited write concurrency and enterprise DBA tooling.

Checklist:

• Match SQLite to single-file, co-located workloads.
• Enable WAL and foreign_keys for app defaults.
• Plan schema migrations for installed bases.
• Define when to graduate to PostgreSQL/MySQL.