SQLite Quest

Tracks

1. 01📖Why SQLite Exists

   0/10 lessons

   The most deployed database on Earth, hiding inside everything you touch

   Get oriented before you write a single line of SQL. What SQLite is, what it is not, where it wins, where it loses, and why the post-2024 SQLite renaissance changed how serious products think about local-first data.

   Lesson list (10)

   • 01
     The Most Deployed Database on Earth~14 min · sqlite, history, ubiquity
   • 02
     What SQLite Actually Is~14 min · sqlite, architecture, library
   • 03
     Serverless Means No Daemon~12 min · sqlite, architecture, in-process
   • 04
     The File IS the Database~14 min · sqlite, file-format, wal, backup
   • 05
     When SQLite IS the Right Choice~12 min · sqlite, fit, architecture-decisions
   • 06
     When SQLite Is NOT the Right Choice~12 min · sqlite, limits, fit
   • 07
     SQLite vs PostgreSQL vs MySQL~15 min · sqlite, comparison, postgres, mysql
   • 08
     The SQLite Renaissance (2024–2026)~14 min · sqlite, renaissance, edge, local-first
   • 09
     Installation and the sqlite3 CLI~12 min · sqlite, cli, install
   • 10
     The Interactive Shell — Dot Commands & REPL Workflow~14 min · sqlite, cli, dot-commands, repl

   ✓Quiz · 3 questions→

2. 02🧱Schema, Types, and Constraints

   0/10 lessons

   Designing tables that hold their shape under pressure

   CREATE TABLE done well — type affinity, STRICT tables, primary keys, constraints, ALTER TABLE, dates and booleans, AUTOINCREMENT vs rowid, and a real migrations strategy.

   Lesson list (10)

   • 01
     CREATE TABLE Basics~14 min · schema, create-table, ddl
   • 02
     Type Affinity — SQLite's Flexible Typing~14 min · schema, type-affinity, typing
   • 03
     STRICT Tables (3.37+)~12 min · schema, strict, types
   • 04
     PRIMARY KEY and Rowid~14 min · schema, primary-key, rowid
   • 05
     Constraints: NOT NULL, DEFAULT, UNIQUE, CHECK~14 min · schema, constraints, data-integrity
   • 06
     INSERT — All the Shapes~12 min · schema, insert, dml
   • 07
     ALTER TABLE — What You Can and Cannot Change~12 min · schema, alter-table, migrations
   • 08
     Dates, Booleans, and Practical Types~14 min · schema, dates, boolean, types
   • 09
     AUTOINCREMENT vs Implicit Rowid~12 min · schema, autoincrement, rowid
   • 10
     Schema Migrations and Versioning~16 min · schema, migrations, versioning

   ✓Quiz · 3 questions→

3. 03🔍Querying and SQL Shape

   0/10 lessons

   SELECT, WHERE, GROUP BY, subqueries, and the functions you'll actually use

   The reading half of SQL. Filtering, ordering, aggregating, grouping, subqueries, conditional expressions, and the string/date functions you reach for daily.

   Lesson list (10)

   • 01
     SELECT — Retrieving Data~12 min · sql, select, querying
   • 02
     WHERE — Filtering Results~12 min · sql, where, predicates
   • 03
     LIKE, GLOB, and Pattern Matching~12 min · sql, like, glob, patterns
   • 04
     ORDER BY and LIMIT~10 min · sql, order-by, limit, pagination
   • 05
     DISTINCT and Aggregate Functions~12 min · sql, distinct, aggregate
   • 06
     GROUP BY and HAVING~14 min · sql, group-by, having
   • 07
     Subqueries — Inline Selects~12 min · sql, subqueries, scalar-subquery
   • 08
     CASE WHEN — Conditional Expressions~10 min · sql, case, conditional
   • 09
     String Functions That Earn Their Keep~12 min · sql, strings, functions
   • 10
     Date and Time Functions~14 min · sql, dates, functions

   ✓Quiz · 3 questions→

4. 04✏️Writes, Transactions, and WAL

   0/10 lessons

   Joins, foreign keys, transactions, WAL mode, and UPSERT — the writing half of SQL

   Beyond INSERT. Normalization, foreign keys, all four joins, transactions and ACID semantics, WAL mode for concurrent readers/writers, and the UPSERT/RETURNING pair.

   Lesson list (10)

   • 01
     Why Normalize? Avoiding Data Duplication~12 min · normalization, schema, writes
   • 02
     Foreign Keys (Enable Them!)~12 min · foreign-keys, constraints, writes
   • 03
     INNER JOIN — The Default~12 min · joins, inner-join, querying
   • 04
     LEFT JOIN — Keep the Lonely Rows~12 min · joins, left-join, outer-join
   • 05
     Multi-Table Joins and Self-Joins~14 min · joins, self-join, multi-table
   • 06
     Cardinality — One-to-Many and Many-to-Many~14 min · relationships, cardinality, junction-table
   • 07
     ON DELETE — Cascade, SET NULL, RESTRICT~12 min · foreign-keys, cascade, constraints
   • 08
     Transactions — BEGIN, COMMIT, ROLLBACK~14 min · transactions, acid, writes
   • 09
     WAL Mode — The #1 Performance Win~14 min · wal, performance, concurrency
   • 10
     UPSERT and RETURNING~14 min · upsert, on-conflict, returning

   ✓Quiz · 3 questions→

5. 05⚡Performance and Search

   0/10 lessons

   Indexes, query plans, ANALYZE, VACUUM, and the rest of the make-it-fast toolbox

   B-trees demystified. CREATE INDEX (single, composite, unique, partial, expression). EXPLAIN QUERY PLAN reading. Covering indexes. ANALYZE / PRAGMA optimize. VACUUM, page size, cache. Benchmarking that doesn't lie.

   Lesson list (10)

   • 01
     What Are Indexes? B-Trees Explained~14 min · indexes, b-tree, performance
   • 02
     CREATE INDEX — Single, Composite, Unique~12 min · indexes, create-index, composite
   • 03
     When to Index — and When Not To~12 min · indexes, tradeoffs, design
   • 04
     EXPLAIN QUERY PLAN — Read the Truth~14 min · explain, query-plan, performance
   • 05
     Covering Indexes~12 min · indexes, covering, performance
   • 06
     Partial and Expression Indexes~12 min · indexes, partial, expression
   • 07
     ANALYZE and PRAGMA optimize~12 min · analyze, stats, optimizer
   • 08
     VACUUM, Page Size, and Cache~12 min · vacuum, page-size, cache, maintenance
   • 09
     Benchmarking Without Lying to Yourself~12 min · benchmarking, performance, measurement
   • 10
     FTS5 — Full-Text Search Without Postgres~16 min · fts5, search, performance

   ✓Quiz · 3 questions→

6. 06🐍Python sqlite3

   0/10 lessons

   The stdlib module nobody fully reads — cursors, parameters, transactions, row factories, pragmas

   Python's built-in sqlite3 module from the inside out. Connection lifecycle, cursors, parameterized queries (and SQL injection prevention), executemany, the implicit transaction model, row factories for dict-like access, custom functions, pragmas, error handling, and the thread-safety story.

   Lesson list (10)

   • 01
     Getting Started — import sqlite3~12 min · python, sqlite3, stdlib
   • 02
     Cursors — execute, fetchone, fetchall~12 min · python, cursor, fetch
   • 03
     Parameterized Queries — Stop SQL Injection~14 min · python, parameters, sql-injection, security
   • 04
     executemany — Fast Bulk Operations~10 min · python, bulk, executemany, performance
   • 05
     Row Factories — Dict-Like Access~10 min · python, row-factory, ergonomics
   • 06
     Transactions in Python — The Implicit Model~14 min · python, transactions, isolation_level
   • 07
     Custom SQL Functions and Aggregates~12 min · python, create_function, extensibility
   • 08
     Connection Pragmas and Configuration~12 min · python, pragma, configuration
   • 09
     Error Handling — sqlite3 Exception Classes~10 min · python, errors, exceptions
   • 10
     Thread Safety and check_same_thread~12 min · python, threading, thread-safety

   ✓Quiz · 3 questions→

7. 07🌊Async Apps and Local Agents

   0/10 lessons

   aiosqlite + FastAPI — the way Pippa actually talks to her own data layer

   Why async needs aiosqlite, the wrapper itself, async cursors and transactions, FastAPI lifecycle, single-connection vs pool patterns, a real ConversationStore CRUD, and the architecture pattern Pippa uses in production.

   Lesson list (10)

   • 01
     Why Async? The Event Loop Problem~12 min · async, event-loop, asyncio
   • 02
     aiosqlite — The Async Wrapper~12 min · aiosqlite, async, wrapper
   • 03
     Async Cursors, Iteration, and Transactions~12 min · aiosqlite, transactions, iteration
   • 04
     Connection Lifecycle in FastAPI~14 min · fastapi, lifespan, aiosqlite
   • 05
     Single Connection vs Pool~12 min · aiosqlite, connection-pool, concurrency
   • 06
     A Real ConversationStore — CRUD End-to-End~18 min · crud, store-pattern, fastapi
   • 07
     FastAPI + aiosqlite Architecture~14 min · fastapi, architecture, real-world
   • 08
     JSON Support — json_extract, json_each, JSONB~14 min · json, json1, jsonb
   • 09
     Window Functions and CTEs~16 min · window-functions, cte, advanced-sql
   • 10
     Generated Columns~10 min · generated-columns, schema

   ✓Quiz · 3 questions→

8. 08🚀Production SQLite

   0/10 lessons

   Backup, corruption prevention, monitoring, migrations, limits — shipping with confidence

   What you actually need before SQLite is touching real users' data. Backup strategies (online + Litestream), corruption prevention and detection, file locking gotchas, SQLite limits, testing strategies, production migrations, and monitoring.

   Lesson list (10)

   • 01
     Backup Strategies — Hot, Cold, Streaming~14 min · backup, production, litestream
   • 02
     Corruption Prevention and Detection~14 min · corruption, integrity, production
   • 03
     File Locking — The OS Layer~12 min · locking, filesystem, production
   • 04
     SQLite Limits~10 min · limits, scale, production
   • 05
     Testing Strategies — In-Memory and Fixtures~12 min · testing, fixtures, production
   • 06
     Production Migrations — Zero-Downtime Patterns~14 min · migrations, production, deploys
   • 07
     Monitoring — What to Watch~12 min · monitoring, production
   • 08
     Litestream and Cloud-Backed SQLite~12 min · litestream, replication, edge
   • 09
     Operating SQLite at Scale — Real Patterns~12 min · scale, operations, production
   • 10
     Wrap-up — When SQLite Is the Right Default~12 min · wrap-up, philosophy, production

   ✓Quiz · 3 questions→