From Your First Query to Production-Ready Embedded Databases

Read before you buy: the PDF here is a free sample — the first 20 pages of the 532-page book (payments, sales and reviews on this demo site are sample data). Get the full e-book at thenexbooks on Gumroad.

SQLite is everywhere — in phones, browsers, desktop apps, command-line tools, and embedded devices. It is also one of the most misunderstood databases in daily use.

There is no server to install and no accounts to configure: you open a file and you have a full SQL database with transactions, indexes, triggers, JSON, and full-text search. That simplicity is exactly why it is so often used carelessly — foreign keys left off, dates stored three different ways, "database is locked" errors under load, live files copied as "backups". This book shows you how SQLite really works, so you can use it with confidence from your first query to production.

Every example runs against one realistic lab database — the Pinewick Trail Club, a fictional hiking club with members, trails, hikes, and sign-ups — which you create with a script in Chapter 2. Every SQL example was run on SQLite 3.42, and the results you see are the results it produced. The book ends by building Trailbook, a complete offline-first club app in Python.

• 532 pages · 28 chapters + 4 appendices
• The sqlite3 shell, GUI tools, and a complete lab database script
• SELECT, filtering, sorting, storage classes, type affinity, STRICT tables, and NULL logic
• Text, number, date, and time functions done right
• Joins (including RIGHT and FULL), aggregation, subqueries, recursive CTEs, and window functions
• Table design with constraints and foreign keys; INSERT, UPDATE, DELETE, UPSERT, and RETURNING
• Transactions and savepoints, views, triggers, generated columns, and safe schema migrations
• JSON functions, FTS5 full-text search, R*Tree spatial indexes, CSV import/export, and ATTACH
• Index design, EXPLAIN QUERY PLAN, WAL mode, busy timeouts, backups, VACUUM, and integrity checks
• Security: SQL injection, untrusted database files, and encryption options
• SQLite from Python, C#, Java, Node.js, Go, and mobile; testing and deployment
• A complete case study, a cheat sheet, a function and PRAGMA reference, and a production checklist

Table Of Contents

Part I — Foundations

• Ch 1 · What SQLite Is and When to Use It
• Ch 2 · Setting Up: The sqlite3 Shell, GUI Tools, and the Pinewick Database
• Ch 3 · Querying Basics: SELECT, WHERE, ORDER BY, and LIMIT
• Ch 4 · Storage Classes, Type Affinity, NULLs, and STRICT Tables
• Ch 5 · Built-in Functions: Text, Numbers, Dates, and Times

Part II — Querying in Depth

• Ch 6 · Joins: INNER, LEFT, RIGHT, FULL, CROSS, and Self Joins
• Ch 7 · Grouping and Aggregation
• Ch 8 · Subqueries and Common Table Expressions
• Ch 9 · Window Functions
• Ch 10 · CASE, Set Operations, and Advanced Expressions

Part III — Designing and Changing Data

• Ch 11 · Creating Tables: Keys, Constraints, ROWID, and AUTOINCREMENT
• Ch 12 · INSERT, UPDATE, DELETE, UPSERT, and RETURNING
• Ch 13 · Transactions, Savepoints, and Journal Modes
• Ch 14 · Views, Triggers, and Generated Columns
• Ch 15 · Changing a Schema Safely: ALTER TABLE, Table Rebuilds, and Migrations

Part IV — Beyond Plain Tables

• Ch 16 · JSON in SQLite
• Ch 17 · Full-Text Search with FTS5
• Ch 18 · R*Tree Indexes, Virtual Tables, and Extensions
• Ch 19 · Importing, Exporting, and Attaching Databases

Part V — Performance and Reliability

• Ch 20 · Indexes: How They Work and How to Design Them
• Ch 21 · EXPLAIN QUERY PLAN and Query Tuning
• Ch 22 · Concurrency: WAL Mode, Busy Timeouts, and One Writer at a Time
• Ch 23 · Backup, VACUUM, Integrity Checks, and Recovery
• Ch 24 · Security: SQL Injection, Untrusted Databases, and Encryption Options

Part VI — SQLite in Applications

• Ch 25 · SQLite from Python
• Ch 26 · SQLite from Other Languages: C#, Java, Node.js, Go, and Mobile
• Ch 27 · Testing, Migrations, and Deployment for SQLite Apps
• Ch 28 · Case Study: Trailbook, an Offline-First Club App

Appendices

• App A · SQLite Cheat Sheet
• App B · Function, PRAGMA, and Template Reference
• App C · Production Checklist
• App D · Glossary

WHO IT IS FOR
Beginners who want a complete path from first query to professional SQL, application developers building mobile, desktop, web, or command-line apps that need a reliable local database, data analysts who want a fast file-based SQL engine, and developers who already use SQLite and want to understand locking, typing, performance, and production practices properly.

FORMAT
PDF (A4), 532 pages, English, clickable table of contents and PDF bookmarks. SQL examples run on SQLite 3.42 and work on later versions; newer features are labeled. 1st Edition, 2026. Published by thenexbooks.

This book is independent and is not affiliated with or endorsed by the SQLite developers. SQLite is in the public domain; other product names are trademarks of their respective owners.

Book details