← All courses

🪶 SQLite

A database that is one file and no server — type affinity and why a column will accept anything, WAL, the writer limit, and the surprisingly large set of jobs it is the right answer for.

No lessons published yet — this is the roadmap, so you can see exactly what the course will cover before it lands.

Before you start: Learn SQL — Series 1. Series 1 of Learn SQL is enough — SELECT, WHERE, JOIN and GROUP BY. SQLite's SQL is mostly unremarkable, which is the point; what this track teaches is the engine around it.

Short on time? The 10 episodes marked core are the shortest path to being productive at work. Start there and pick up the rest whenever a job asks for them.

Series 1 · SQLite Essentials

one file, no server, and the type system that will surprise you

  1. 01

    SQLite — A Database That Is a File, a Library and No Process at All

    corebeginnercoming soon
  2. 02

    Type Affinity — Why a Column Will Accept Almost Anything, and STRICT Tables

    corebeginnercoming soon
  3. 03

    rowid and INTEGER PRIMARY KEY — Storing Dates Without a Date Type

    corebeginnercoming soon
  4. 04

    Foreign Keys Are Off by Default — and ALTER TABLE Barely Exists

    corebeginnercoming soon
  5. 05

    PRAGMAs and EXPLAIN QUERY PLAN — The Settings That Actually Matter

    corebeginnercoming soon
  6. 06

    Project — Build a Small Database That Enforces Its Own Rules

    corebeginner#projectcoming soon

Series 2 · Concurrency and Limits

one writer, and knowing exactly when that is a problem

  1. 01

    WAL and the Single Writer — Why 'Database Is Locked' Happens, and BEGIN IMMEDIATE

    coreintermediatecoming soon
  2. 02

    Performance — No Network Hop, and Why One Transaction Beats Ten Thousand

    coreintermediatecoming soon
  3. 03

    When SQLite Is the Right Answer — and the One Misuse That Corrupts Data

    coreintermediatecoming soon
  4. 04

    Backups — VACUUM INTO and the Backup API, and Why Copying the File Is Not One

    intermediatecoming soon

Series 3 · More Than You Expect

JSON, full-text search and extensions

  1. 01

    JSON in SQLite — Querying Documents and Indexing a Path

    intermediatecoming soon
  2. 02

    FTS5 — Real Full-Text Search Inside the File

    intermediatecoming soon
  3. 03

    Window Functions, UPSERT and RETURNING — The Modern SQL People Assume Is Missing

    intermediatecoming soon
  4. 04

    Project — A Searchable Application Database in a Single File

    coreintermediate#projectcoming soon

Not here, on purpose

  • SQL itself — covered in Learn SQL
  • Using SQLite as an analytical engine over files — covered in DuckDB, which is the embedded database built for that question. Database X vs Database Y, Series 3 · 05 compares the two directly
  • Embedding SQLite in a mobile or desktop application — not covered. This track is about the database, not about iOS, Android or Electron