Topolearn

Learn SQLite

Serverless SQL database engine stored in a single file

16 lessons · 3 hours · 4 levels · covers SQLite 3.45+ (via Python's sqlite3)

SQLite is a small, fast, self-contained SQL database engine that runs inside your application and stores the whole database in one ordinary file. It is the most widely deployed database in the world, built into phones, browsers, desktop apps and Python's standard library. It is ideal for local and embedded storage, tests, data analysis and many single-server web apps.

Sign in free to track progressOfficial docs ↗

Before you start

SQLite builds on these; the tree links to the exact lessons you need.

SQLEssentials level

How deep do you want to go?

1. EssentialsRead and write simple code, do the everyday basics5 lessons · 52 min in total
2. PracticalUse it productively at work, with the usual idioms10 lessons · 2 hours in total
3. AdvancedThe less common features, performance and trade-offs13 lessons · 3 hours in total
4. ExpertInternals and the edge cases experts know16 lessons · 3 hours in total

The SQLite tree

SQLiteWhat SQLite is and when to use itWhat SQLite is andwhen to use itThe sqlite3 command-line shellThe sqlite3command-line shellPython's sqlite3 modulePython's sqlite3moduleDynamic typing and type affinityDynamic typing andtype affinityrowid, INTEGER PRIMARY KEY and AUTOINCREMENTrowid, INTEGERPRIMARY KEY and…STRICT tables and CHECK constraintsSTRICT tables andCHECK constraintsForeign keys and essential PRAGMAsForeign keys andessential PRAGMAsTransactions, locking and SQLITE_BUSYTransactions, lockingand SQLITE_BUSYALTER TABLE limits and migrationsALTER TABLE limitsand migrationsJSON, upserts and RETURNINGJSON, upserts andRETURNINGINSERT, UPDATE and DELETEINSERT, UPDATE andDELETEChoosing data typesChoosing data typesPrimary keys and identity columnsPrimary keys andidentity columnsForeign keys and referential integrityForeign keys andreferential integrityTransactions and ACIDTransactions andACIDIndex basicsIndex basics
Essentials and Practical shown (10 of 16 lessons); the full list is below. Click a lesson to read it.

All 16 lessons, in order

Basics

  1. What SQLite is and when to use it · Essentials · 8 min

    An embedded, serverless database engine in a library that stores everything in one file.

  2. The sqlite3 command-line shell · Essentials · 8 min

    Open a database with the sqlite3 shell, run SQL, and use dot-commands to inspect schemas and import or export data.

Using SQLite from Python

  1. Python's sqlite3 module · Essentials · 14 min

    Connect, execute parameterised queries, fetch rows, and commit with Python's built-in sqlite3 module.

  2. Transactions, locking and SQLITE_BUSY · Practical · 14 min

    Use DEFERRED, IMMEDIATE and EXCLUSIVE transactions, avoid 'database is locked', and control Python's transaction handling.

Types and schema

  1. Dynamic typing and type affinity · Essentials · 12 min

    SQLite stores a type with each value; declared column types only express an affinity, not a rule.

  2. rowid, INTEGER PRIMARY KEY and AUTOINCREMENT · Essentials · 10 min

    Every ordinary table has a 64-bit rowid; INTEGER PRIMARY KEY makes a column an alias for it.

  3. STRICT tables and CHECK constraints · Practical · 10 min

    Make SQLite enforce column types with STRICT tables (3.37+) and add CHECK constraints for the rest.

  4. Foreign keys and essential PRAGMAs · Practical · 10 min

    Turn on foreign key enforcement per connection and know the PRAGMAs every SQLite app should set.

  5. ALTER TABLE limits and migrations · Practical · 12 min

    Work within SQLite's limited ALTER TABLE, rebuild tables safely, and version schemas with user_version.

  6. JSON, upserts and RETURNING · Practical · 12 min

    Query JSON with -> and ->>, expand arrays with json_each, and write with ON CONFLICT upserts and RETURNING.

Concurrency and performance

  1. Journal modes and WAL · Advanced · 14 min

    Switch to write-ahead logging so readers and the writer don't block each other, and understand checkpoints.

  2. Indexes and EXPLAIN QUERY PLAN · Advanced · 14 min

    Read EXPLAIN QUERY PLAN output, design indexes for SQLite's planner, and keep statistics fresh with ANALYZE and PRAGMA optimize.

  3. Full-text search with FTS5 · Advanced · 14 min

    Build fast word search with FTS5 virtual tables, MATCH queries, bm25 ranking and highlight snippets.

Internals and production

  1. The file format: pages, B-trees and VACUUM · Expert · 15 min

    How a SQLite file is organised into pages and B-trees, what the freelist is, and when VACUUM helps.

  2. The bytecode engine (VDBE) · Expert · 12 min

    SQLite compiles each SQL statement into a bytecode program; EXPLAIN shows it and prepared statements reuse it.

  3. Running SQLite in production · Expert · 15 min

    Configure connections for web workloads, take online backups, and know the limits that call for a server database.