SQLite Essentials

What Is SQLite?

A complete SQL database in a single file, with no server. Where it is used and when to choose something bigger.

Tools: the sqlite3 Shell & DB Browser

Open and explore SQLite files with the command-line shell and the free DB Browser for SQLite.

Creating Tables & SQLite Types

CREATE TABLE in SQLite, its flexible type affinity, and STRICT tables for exact types.

Inserting & Selecting Data

INSERT rows, SELECT columns, rename with aliases, calculate values and remove duplicates.

Updating & Deleting Safely

Change and remove rows with UPDATE and DELETE, use RETURNING, and avoid the classic missing-WHERE disaster.

Filtering & Sorting

WHERE with AND/OR, IN, BETWEEN, LIKE and NULL, ORDER BY and LIMIT.

Joining Tables

Combine members with their contributions and loans using INNER and LEFT joins.

Totals, Averages & GROUP BY

Summarise with COUNT, SUM, AVG, MIN and MAX, group by member or month, and filter groups with HAVING.

Subqueries, CTEs & Window Functions

Step-by-step queries with WITH, comparisons with subqueries, and rankings with window functions (all supported by SQLite).

Dates & Text Functions

Work with dates using date(), strftime() and julianday(), and tidy text with upper, trim, replace and substr.

Constraints & Foreign Keys

NOT NULL, UNIQUE, CHECK and foreign keys, and why SQLite needs PRAGMA foreign_keys = ON.

Indexes & EXPLAIN QUERY PLAN

Speed up lookups on big tables with indexes and confirm they are used with EXPLAIN QUERY PLAN.

JSON in SQLite

Store and query JSON with json_extract and the -> / ->> operators, and expand arrays with json_each.

Views & Triggers

Save reports as views and automate rules with triggers, such as keeping a balance or an audit log up to date.

Transactions, Locking & WAL Mode

Group changes with BEGIN/COMMIT/ROLLBACK, understand SQLite's single-writer model, and use WAL mode for better concurrency.

SQLite from Python

Use Python's built-in sqlite3 module: connect, create tables, insert with parameters and query rows.

Full-Text Search with FTS5

Build fast, ranked search for notes, messages or products with SQLite's FTS5 extension.

SQLite in Mobile & Web Apps

How Android (Room), Flutter (sqflite/drift), iOS and browsers use SQLite for offline-first apps that sync later.

Backups, Integrity & Performance Tips

Back up a live SQLite database safely, check its integrity, and keep it fast.

Project: A Chama Treasurer Tool

Build a complete SQLite-backed tool for a savings group: members, contributions, loans, fines and monthly statements.