SQLite

A complete database in a single file.

SQLite is taught here in 14 lessons, running from The database in a file, and the sqlite3 CLI through to Next steps: choosing SQLite vs a client-server database. Each lesson takes one topic, shows the working code, and links onward to the next, so the course can be read straight through in order.

Lessons

1. The database in a file, and the sqlite3 CLIOne file, no server, dynamic typing — plus the CLI commands and pragmas that make it usable for real work.
2. Using SQLite from application codePrepared statements, transactions in bulk, and the concurrency rules that keep an embedded database from throwing SQLITE_BUSY.
3. WAL mode, backups, and when not to use SQLiteWhat the write-ahead log changes, how to take a consistent backup of a live database, and the workloads where another engine is the right answer.
4. Data types, affinity, STRICT tables and WITHOUT ROWIDHow type affinity really works, why STRICT tables are worth adopting, rowid versus primary key, and generated columns.
5. Querying with SQL: joins, grouping, upserts and CTEsJoins and aggregates in SQLite syntax, ON CONFLICT upserts with RETURNING, CTEs and recursive queries, and window functions.
6. Transactions, locking and concurrencyBEGIN IMMEDIATE versus DEFERRED, busy_timeout, one writer at a time, connection per thread, and WAL behaviour under load.
7. Indexes, ANALYZE and EXPLAIN QUERY PLANHow the planner chooses, covering and partial and expression indexes, reading a query plan, and keeping statistics fresh.
8. Full-text search with FTS5Virtual tables, tokenizers, prefix and phrase queries, bm25 ranking, external-content tables and snippet highlighting.
9. JSON, generated columns and date functionsThe built-in JSON functions and JSONB, generated columns over JSON, date and time arithmetic, and the string and math extensions.
10. Migrations and schema versioning for embedded appsuser_version as a migration counter, an idempotent runner, safe table rewrites, the limits of ALTER TABLE, and data migrations.
11. SQLite on mobile, desktop and in the browserBundling and upgrading on Android and iOS, SQLCipher encryption, desktop packaging, and WASM builds with OPFS persistence.
12. LiteFS, libSQL and replication for serverless appsReplication models for an embedded database, LiteFS and its FUSE-based primary, Turso and libSQL, read replicas and consistency trade-offs.
13. Testing, tooling and benchmarks for SQLite appsIntegrity checks, dbstat, VACUUM and pragma tuning, in-memory test databases, deterministic testing and honest benchmarking.
14. Next steps: choosing SQLite vs a client-server databaseA workload decision guide, hybrid patterns that use both, the operational differences that matter, and a reading path forward.

More in Databases

SQL MySQL PostgreSQL MongoDB Redis

FAQ