SQLite Tutorial
Learn SQLite step by step: 33 lessons in 10 chapters with examples, quizzes, diagrams and projects.
Start learning →
of lessons completed
Meet SQLite
- What Is SQLite? Learn what SQLite is, how a serverless, single-file database works, where it runs, and when to pick it over a client-server database like PostgreSQL or MySQL.
- Running SQLite: Browser and the sqlite3 Shell Learn the two ways to run SQLite: the live in-browser editor on this site and the sqlite3 command-line shell, with dot-commands like .tables, .schema and .mode.
- Your First Database Create your first SQLite table, insert rows, read them back with SELECT and meet the hidden rowid column. A hands-on start with a tiny contacts database.
Types and Tables
- Dynamic Typing and Type Affinity Understand SQLite storage classes (NULL, INTEGER, REAL, TEXT, BLOB), type affinity rules, typeof() and how values are coerced, plus the surprises that catch beginners.
- Tables, rowid and INTEGER PRIMARY KEY Learn how SQLite stores rows: the hidden rowid, INTEGER PRIMARY KEY as an alias, AUTOINCREMENT versus plain keys, WITHOUT ROWID tables and last_insert_rowid().
- Constraints and STRICT Tables Make SQLite enforce your rules: NOT NULL, UNIQUE, CHECK, DEFAULT, conflict clauses and STRICT tables that reject values of the wrong type.
- Dates, Times and Booleans SQLite has no date or boolean type. Learn the three ways to store time, the date and time functions, modifiers like start of month, and how 0 and 1 act as booleans.
Querying
- Querying in SQLite A tour of SELECT in SQLite: WHERE, ORDER BY, LIMIT, GROUP BY, case sensitivity, LIKE versus GLOB, NULL ordering and the SQLite quirks that surprise newcomers.
- Built-in Functions A guided tour of SQLite's built-in functions: string, math, NULL-handling and aggregate functions, plus printf, group_concat, iif, coalesce, instr and substr.
- Joins, Subqueries and CTEs Combine tables in SQLite with INNER, LEFT, RIGHT, FULL and CROSS joins, write subqueries, and build readable queries with WITH and recursive CTEs.
- Window Functions Learn SQLite window functions: OVER, PARTITION BY, ROW_NUMBER, RANK, running totals, LAG and LEAD, frames and named windows, with live examples.
Changing Data
- INSERT, UPDATE and DELETE Change data in SQLite: multi-row INSERT, INSERT ... SELECT, UPDATE with expressions and UPDATE FROM, DELETE patterns, RETURNING, and counting rows with changes().
- UPSERT and RETURNING Handle duplicate keys in SQLite with INSERT OR IGNORE, OR REPLACE and ON CONFLICT DO UPDATE, use the excluded table, and read results back with RETURNING.
- Transactions Learn SQLite transactions: autocommit, BEGIN and COMMIT, ROLLBACK, DEFERRED vs IMMEDIATE vs EXCLUSIVE, savepoints, and why batching inserts is faster.
Schema Design and Integrity
- Foreign Keys Use foreign keys in SQLite: turn on PRAGMA foreign_keys, choose ON DELETE and ON UPDATE actions, defer checks to commit time, and find orphans with foreign_key_check.
- Views and Triggers Save queries as views and react to changes with SQLite triggers: BEFORE and AFTER triggers, INSTEAD OF on views, audit logs, updated_at timestamps and recursion.
- Altering Schema Safely Change SQLite tables that already hold data: what ALTER TABLE can and cannot do, the safe table-rebuild recipe, sqlite_master, and schema versions with user_version.
- Generated Columns and JSON Use SQLite generated columns (virtual vs stored) and the JSON functions json_extract, -> and ->>, json_each and json_group_array, and index JSON expressions.
Performance
- Indexes Speed up SQLite queries with indexes: single and composite indexes, column order, covering, partial, expression and unique indexes, and their costs.
- EXPLAIN QUERY PLAN and the Optimizer Read SQLite's EXPLAIN QUERY PLAN output, tell SCAN from SEARCH, use ANALYZE and PRAGMA optimize, and fix slow queries with a repeatable routine.
- PRAGMAs and Tuning Learn the SQLite PRAGMAs that matter: journal_mode WAL, synchronous, cache_size, page_size, busy_timeout, mmap_size, foreign_keys, and what each one trades off.
Beyond Basics
- Full-Text Search with FTS5 Build fast word search in SQLite with FTS5: virtual tables, MATCH queries, bm25 ranking, snippets, tokenizers and external content tables, with live FTS4 demos.
- Concurrency, Locking and WAL How SQLite handles many readers and one writer: lock states, rollback journal vs WAL, SQLITE_BUSY, BEGIN IMMEDIATE, checkpoints and multi-process pitfalls.
Using SQLite from Code
- SQLite from Python, JavaScript and Other Languages Use SQLite in your programs: Python's sqlite3 module, Node's node:sqlite, better-sqlite3, Go, Java, C and more, with parameter binding and transactions.
- Parameters and SQL Injection Learn how SQL injection works, why string building is dangerous, and how ? and :name placeholders keep SQLite queries safe, plus quoting identifiers.
- Backup, VACUUM and Maintenance Back up a live SQLite database safely with .backup and VACUUM INTO, reclaim space with VACUUM, run integrity_check and avoid the file-copy mistakes.
Projects
- Project: A Notes App Database Build the database behind a notes app in SQLite: notes, tags and a many-to-many link table, search queries, soft delete and trigger-based timestamps.
- Project: Personal Budget Tracker Design a budget tracker in SQLite: accounts, categories and transactions in integer cents, CHECK constraints, monthly summaries and running balances.
- Project: Local-First Event Log and Job Queue Build an append-only event log and a SQLite-backed job queue: claim work atomically with UPDATE RETURNING, retry with backoff and dedupe with upsert.
- Project: Analyzing a CSV Dataset Import a CSV into SQLite, clean messy text into typed columns, then answer questions with aggregations, top-N per group and cohort retention queries.
Practice and Review
- SQLite Exercises with Solutions Practice SQLite with 14 graded exercises on typing, STRICT tables, dates, queries, upsert, RETURNING, triggers, JSON and indexes. Run each one in your browser.
- SQLite Interview Questions 26 SQLite interview questions with short answers: dynamic typing, rowid, foreign keys, locking, WAL, limits and how SQLite differs from PostgreSQL and MySQL.
- SQLite Cheat Sheet A one-page SQLite cheat sheet: sqlite3 shell commands, storage classes, upsert, RETURNING, date and string functions, JSON, windows, pragmas and common patterns.