Skip to content
Tutorials Center
My learning

Topics

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

  1. 1 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.
  2. 2 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.
  3. 3 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

  1. 4 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.
  2. 5 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().
  3. 6 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.
  4. 7 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

  1. 8 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.
  2. 9 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.
  3. 10 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.
  4. 11 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

  1. 12 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().
  2. 13 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.
  3. 14 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

  1. 15 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.
  2. 16 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.
  3. 17 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.
  4. 18 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

  1. 19 Indexes Speed up SQLite queries with indexes: single and composite indexes, column order, covering, partial, expression and unique indexes, and their costs.
  2. 20 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.
  3. 21 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

  1. 22 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.
  2. 23 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

  1. 24 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.
  2. 25 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.
  3. 26 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

  1. 27 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.
  2. 28 Project: Personal Budget Tracker Design a budget tracker in SQLite: accounts, categories and transactions in integer cents, CHECK constraints, monthly summaries and running balances.
  3. 29 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.
  4. 30 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

  1. 31 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.
  2. 32 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.
  3. 33 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.