Big Data AcademyQuery · move · process · govern
All 20 chapters available

Stage 02 · Relational Databases

SQLite

Master SQLite from its embedded database model and sqlite3 shell through schema design, transactions, WAL, query planning, storage internals, JSON and search, application integration, backup, security, performance, and a production capstone.

Start first lesson View all chapters
20chapters
100lesson paths
Beginnerstarting level
SQLite + operationslearning mode
Course state100 published lessons

Course brief

Learn the complete lifecycle of an embedded relational database.

SQLite is more than a database file and a few SQL statements. This course develops engine-specific judgment about types, constraints, transactions, locking, WAL, pages and B-trees, planner evidence, virtual tables, drivers, migrations, backup and recovery, performance, security, deployment, and production operations.

By the end

You will be able to

  • Use SQLite files and the sqlite3 shell confidently, with deliberate schemas, types, keys, and constraints
  • Design reliable transactions, concurrency policies, WAL workflows, busy handling, and checkpoints
  • Measure query plans, indexes, storage behavior, JSON, FTS5, R-Tree, and performance changes with evidence
  • Embed SQLite safely in applications using bound parameters, explicit connection initialization, and versioned migrations
  • Back up, restore, test, secure, troubleshoot, operate, and defend a production SQLite architecture—and recognize when to migrate away

Complete syllabus

20 chapters in prerequisite order.

The curriculum moves from SQLite foundations and the CLI through engine behavior, application integration, operations, security, performance engineering, and a production capstone.

01

Chapter 1

SQLite Foundations: Embedded Databases, Files, and the First Lab

5 lessons
01
What SQLite Is—and What Makes an Embedded Database DifferentPublished lesson
Open 02
Install SQLite and Verify the Toolchain on Windows, Linux, and macOSPublished lesson
Open 03
Create, Open, Inspect, Close, and Reopen Your First Database FilePublished lesson
Open 04
A First Mental Model of Connections, Statements, Transactions, and PersistencePublished lesson
Open 05
Build the Course Lab Database and Adopt Safe Working HabitsPublished lesson
Open
02

Chapter 2

Mastering the sqlite3 Command-Line Shell

5 lessons
01
SQL Statements Versus Dot-Commands, Help, and Shell StatePublished lesson
Open 02
Readable Output: .mode, .headers, .width, .nullvalue, and Current CLI FormattingPublished lesson
Open 03
Run SQL Scripts with .read, Redirection, Batch Mode, and Exit StatusPublished lesson
Open 04
Parameters, Safe Values, and the CLI .parameter FacilityPublished lesson
Open 05
Import, Export, .once/.output, CSV, and Reproducible Data ExchangePublished lesson
Open
03

Chapter 3

SQLite Schema Objects, ROWID, Keys, and Table Design

5 lessons
01
CREATE TABLE in SQLite: Schema, sqlite_schema, and IntrospectionPublished lesson
Open 02
ROWID Tables and INTEGER PRIMARY KEY from First PrinciplesPublished lesson
Open 03
WITHOUT ROWID Tables and Composite Primary KeysPublished lesson
Open 04
Temporary Tables, In-Memory Databases, and the temp SchemaPublished lesson
Open 05
Design a SQLite Schema from Existing RequirementsPublished lesson
Open
04

Chapter 4

SQLite’s Type System, Affinity, STRICT Tables, and Value Representation

5 lessons
01
Storage Classes and Dynamic Typing: The Type Belongs to the ValuePublished lesson
Open 02
Declared Types and Type Affinity RulesPublished lesson
Open 03
STRICT Tables: Rigid Type Enforcement Where You Want ItPublished lesson
Open 04
Dates, Times, Booleans, Money, UUIDs, and Other Logical TypesPublished lesson
Open 05
NULL, BLOBs, Text Encoding, Collations, and Cross-Language BoundariesPublished lesson
Open
05

Chapter 5

Constraints, Foreign Keys, Conflict Handling, and Integrity

5 lessons
01
NOT NULL, UNIQUE, PRIMARY KEY, CHECK, and DEFAULTPublished lesson
Open 02
Foreign Keys and PRAGMA foreign_keysPublished lesson
Open 03
ON DELETE/UPDATE Actions, Deferred Constraints, and Transaction BoundariesPublished lesson
Open 04
ON CONFLICT Algorithms and Constraint-Failure SemanticsPublished lesson
Open 05
Generated Columns, Integrity Checks, and Enforcing Cross-Column RulesPublished lesson
Open
06

Chapter 6

SQLite Data Modification: INSERT, UPDATE, DELETE, UPSERT, and RETURNING

5 lessons
01
INSERT Forms, Defaults, Multi-Row Inserts, and Generated KeysPublished lesson
Open 02
UPDATE Correctly: Predicates, Expressions, and Avoiding Accidental Wide ChangesPublished lesson
Open 03
DELETE, Cascades, Soft Delete, and Data LifecyclePublished lesson
Open 04
UPSERT with ON CONFLICT DO NOTHING / DO UPDATEPublished lesson
Open 05
RETURNING, changes(), total_changes(), and Observable WritesPublished lesson
Open
07

Chapter 7

SQLite Expressions, Functions, CTEs, Window Functions, and Dialect Features

5 lessons
01
SQLite Operators, Comparison Rules, LIKE/GLOB, and Expression SemanticsPublished lesson
Open 02
Built-In Scalar, Aggregate, and Core Utility FunctionsPublished lesson
Open 03
Date and Time Functions in SQLitePublished lesson
Open 04
WITH CTEs and Recursive QueriesPublished lesson
Open 05
Window Functions and Analytic Queries in SQLitePublished lesson
Open
08

Chapter 8

Transactions, Atomicity, Journaling, and Savepoints

5 lessons
01
Autocommit, Implicit Transactions, and Explicit BEGIN/COMMIT/ROLLBACKPublished lesson
Open 02
DEFERRED, IMMEDIATE, and EXCLUSIVE TransactionsPublished lesson
Open 03
Rollback Journals and Atomic Commit from the Pager’s Point of ViewPublished lesson
Open 04
SAVEPOINT, ROLLBACK TO, RELEASE, and Nested Units of WorkPublished lesson
Open 05
Transaction Design, Idempotency, and Failure InjectionPublished lesson
Open
09

Chapter 9

Concurrency, Locking, WAL Mode, Busy Handling, and Checkpoints

5 lessons
01
Readers, Writers, Locks, and Why SQLite Has One Writer at a TimePublished lesson
Open 02
SQLITE_BUSY, busy_timeout, Retries, and Short TransactionsPublished lesson
Open 03
Write-Ahead Logging: How WAL Changes Read/Write ConcurrencyPublished lesson
Open 04
WAL Checkpoints, Autocheckpointing, Long Readers, and WAL GrowthPublished lesson
Open 05
Concurrency Reliability, WAL-Reset History, and Production PatternsPublished lesson
Open
10

Chapter 10

Indexes and the SQLite Query Planner

5 lessons
01
How SQLite B-Tree Indexes Work and Why Indexes Trade Reads for WritesPublished lesson
Open 02
Composite Indexes, Leftmost Prefixes, Sorting, and Covering IndexesPublished lesson
Open 03
Partial Indexes and Indexes on ExpressionsPublished lesson
Open 04
EXPLAIN QUERY PLAN, EXPLAIN, SCAN/SEARCH, and Planner DiagnosticsPublished lesson
Open 05
ANALYZE, sqlite_stat Tables, PRAGMA optimize, and Planner StatisticsPublished lesson
Open
11

Chapter 11

Database Files, Pages, B-Trees, Freelist, and Storage Internals

5 lessons
01
The SQLite Database File: Header, Page Size, Schema Cookie, and File FormatPublished lesson
Open 02
Table B-Trees, Index B-Trees, Interior/Leaf Pages, and CellsPublished lesson
Open 03
Payload, Overflow Pages, Large Rows, and BLOB/Text StoragePublished lesson
Open 04
Freelist, Deleted Space, auto_vacuum, VACUUM, and File ShrinkagePublished lesson
Open 05
Cache, Memory Mapping, Temporary Storage, and the Pager LayerPublished lesson
Open
12

Chapter 12

Views, Triggers, ATTACH, Multiple Databases, and Schema-Level Automation

5 lessons
01
Views and Readable Query InterfacesPublished lesson
Open 02
Triggers: BEFORE, AFTER, INSTEAD OF, OLD/NEW, and Side EffectsPublished lesson
Open 03
ATTACH DATABASE, DETACH, and Cross-Database QueriesPublished lesson
Open 04
URI Filenames, Read-Only/Immutable Opens, Shared Cache Caveats, and Connection OptionsPublished lesson
Open 05
Application IDs, user_version, schema_version, and Database-as-File-Format DesignPublished lesson
Open
13

Chapter 13

JSON and Semi-Structured Data in SQLite

5 lessons
01
When JSON Belongs in SQLite—and When Columns/Tables Are BetterPublished lesson
Open 02
JSON Paths, json_extract, -> / ->>, and Value ConstructionPublished lesson
Open 03
json_each, json_tree, and Querying JSON Arrays/Objects RelationallyPublished lesson
Open 04
JSONB, Internal Representation, and Performance TradeoffsPublished lesson
Open 05
Indexing JSON Paths, Generated Columns, Validation, and Hybrid DesignsPublished lesson
Open
14

Chapter 14

Full-Text Search, R-Tree, Virtual Tables, and Extensions

5 lessons
01
Virtual Tables and the Extension ArchitecturePublished lesson
Open 02
FTS5 Fundamentals: Full-Text Tables, MATCH, and TokenizationPublished lesson
Open 03
FTS5 Ranking, Highlighting, External Content, and MaintenancePublished lesson
Open 04
R-Tree and Geospatial/Multidimensional Range SearchPublished lesson
Open 05
Loadable Extensions, Built-In Extensions, and Supply-Chain SafetyPublished lesson
Open
15

Chapter 15

Embedding SQLite in Applications

5 lessons
01
Application Integration Architecture: Drivers, Connections, Prepared Statements, and BindingPublished lesson
Open 02
Python sqlite3: A Beginner-to-Production WorkflowPublished lesson
Open 03
Node.js and JavaScript: Built-In/Driver Options and Async BoundariesPublished lesson
Open 04
.NET and Java: ADO.NET/Provider and JDBC PatternsPublished lesson
Open 05
The SQLite C API Mental Model and What Higher-Level Drivers WrapPublished lesson
Open
16

Chapter 16

Backup, Export, Recovery, Integrity, and Disaster Preparedness

5 lessons
01
Why Copying a Live Database File Can Be UnsafePublished lesson
Open 02
.backup, Online Backup API, and VACUUM INTOPublished lesson
Open 03
.dump, SQL Logical Exports, and Migration/Interchange UsesPublished lesson
Open 04
integrity_check, quick_check, foreign_key_check, and Corruption DiagnosisPublished lesson
Open 05
.recover, Recovery Limitations, Restore Drills, and a Backup PolicyPublished lesson
Open
17

Chapter 17

Schema Evolution, Migrations, Testing, and Release Compatibility

5 lessons
01
ALTER TABLE Capabilities and SQLite’s Schema-Rewrite ModelPublished lesson
Open 02
Versioned Migrations with PRAGMA user_versionPublished lesson
Open 03
Safe Table-Rebuild Migrations and Foreign-Key HandlingPublished lesson
Open 04
Database Tests: Schema, Constraints, Queries, Transactions, and ConcurrencyPublished lesson
Open 05
Compatibility Testing Across SQLite Versions and Build OptionsPublished lesson
Open
18

Chapter 18

Performance Engineering, PRAGMAs, Maintenance, and Benchmarking

5 lessons
01
Measure First: Timers, Query Plans, Dataset Shape, and Repeatable BenchmarksPublished lesson
Open 02
Transaction Batching and Write ThroughputPublished lesson
Open 03
synchronous, journal_mode, cache_size, temp_store, mmap_size, and Other PRAGMAsPublished lesson
Open 04
VACUUM, ANALYZE, PRAGMA optimize, auto_vacuum, and Routine MaintenancePublished lesson
Open 05
Performance Anti-Patterns and When SQLite Has Reached the Wrong WorkloadPublished lesson
Open
19

Chapter 19

Security, Deployment, Reliability Boundaries, and When Not to Use SQLite

5 lessons
01
SQL Injection, Prepared Statements, and Trust BoundariesPublished lesson
Open 02
File Permissions, Process Identity, Secrets, and Encryption RealityPublished lesson
Open 03
Defensive Configuration, trusted_schema, Extension Loading, and Untrusted DatabasesPublished lesson
Open 04
Deployment on Local Disks, Containers, Network Filesystems, Mobile, and EdgePublished lesson
Open 05
When SQLite Is the Right Database—and When to Move to PostgreSQL/MySQL/DuckDB/etc.Published lesson
Open
20

Chapter 20

Production Capstone: Build and Operate a Complete SQLite Application Database

5 lessons
01
Capstone Requirements, Workload, Data Model, and SQLite Fit ReviewPublished lesson
Open 02
Implement the Schema, Constraints, Indexes, Views, and MigrationsPublished lesson
Open 03
Implement Application Access, Transactions, JSON/Search Features, and Concurrency HandlingPublished lesson
Open 04
Test, Benchmark, Back Up, Restore, and Secure the DatabasePublished lesson
Open 05
Operate, Troubleshoot, Document, and Defend the Final ArchitecturePublished lesson
Open

Keep knowledge open

Help the academy stay free and grow.

If these tutorials save you time, a small donation supports new lessons, technical review, diagrams, examples, and long-term maintenance.

ETHEthereum / ERC-20 only
0x716c4Ab160C4B66F31a28AE2448BfF68fc3a2ef0

Send only assets compatible with the Ethereum/ERC-20 network. Do not send TRC-20/TRON assets.