SQLite Tutorial
SQLite is the world's most widely deployed database engine — not because it
runs on huge servers, but because it doesn't need one: it's a serverless, self-contained,
single-file relational database that ships inside billions of phones, browsers, and desktop
apps. This track covers 37 lessons: the same core SQL query language you'd
use with any relational database, plus SQLite's own distinct features — dynamic typing, JSON
functions, full-text search, WAL mode, and the PRAGMA system that configures the engine
itself. Throughout the track we build one running example together —
RecipeBox, a recipe-manager app storing recipes, ingredients, and reviews
locally on a single device — exactly the kind of app SQLite was built for.
Where this fits: RecipeBox is the same running example used across this site's Android Java, Kotlin, and Jetpack Compose tracks. When those tracks reach Room (Android's local database library), you're looking at SQLite underneath — this track is the SQL foundation that makes those lessons click.
Why Learn SQLite
| Compared to... | SQLite's role |
|---|
| MySQL / PostgreSQL | No server to install, configure, or keep running — the entire database is one file on disk, making it ideal for mobile apps, desktop tools, and anywhere a full client-server database is overkill |
| A NoSQL database like MongoDB | SQLite is relational and speaks standard SQL, so the schema-design and query skills transfer directly to MySQL/PostgreSQL later, unlike NoSQL's very different data model |
| Writing your own file-based storage | SQLite gives you real transactions, indexes, and a full query language for free — while still being just a single file you can copy, back up, or embed directly in your app |
A Quick Look Ahead
Example: Finding a Recipe's Average Rating
SELECT r.title, AVG(rv.rating) AS avg_rating, COUNT(rv.id) AS review_count
FROM recipes r
JOIN reviews rv ON rv.recipe_id = r.id
GROUP BY r.id
ORDER BY avg_rating DESC;
Note: This is correct, real SQL you can run yourself — grab the sqlite3 command-line tool (covered in the very next lesson) and every example in this track runs in seconds with no server setup at all.
What You'll Learn In This Track
| Area | Covered in |
|---|
| Foundations | What SQLite is, installation, opening databases, data types & type affinity |
| Schema design | Creating tables, primary & foreign keys, constraints, altering tables |
| Core SQL | INSERT, SELECT, UPDATE, DELETE, UPSERT, sorting, operators & conditions |
| Querying deeper | Aggregate functions, GROUP BY/HAVING, joins, unions, subqueries |
| Functions & design | String, date & JSON functions, CASE, views, normalization |
| Performance | Indexes, EXPLAIN QUERY PLAN, and query optimization |
| SQLite-specific power | Transactions & ACID, triggers, full-text search (FTS5), WAL mode, ATTACH & PRAGMA |
| Shipping it | Backup & VACUUM, security, connecting from real application code, choosing SQLite vs MySQL/PostgreSQL |
Next steps: Head to
What Is SQLite? before we install anything — a little context makes everything after it click faster.