local notes database
Create a notes table (id, title, body, created_at). Write SQL to insert 3 notes, list newest first, and update one title.
Data / Knowledge library
SQLite
an embedded serverless relational database engine
Why learn SQLite?
SQLite gives apps a zero-admin SQL database in a single file, ideal for local storage, offline-first features, testing, edge deployments, and small-to-medium production workloads without running a separate database server.
Where it came from
2000: Created by D. Richard Hipp to avoid external DB dependencies. 2004: SQLite 3.0 shipped with a new architecture and lasting file-format stability. 2010s: Became default storage in many mobile, desktop, browser, and IoT stacks. 2021: STRICT tables (3.37) added stronger type enforcement options. 2024: JSONB support (3.45) improved JSON storage/performance patterns.
- 2000: Created by D. Richard Hipp to avoid external DB dependencies.
- 2004: SQLite 3.0 shipped with a new architecture and lasting file-format stability.
- 2010s: Became default storage in many mobile, desktop, browser, and IoT stacks.
- 2021: STRICT tables (3.37) added stronger type enforcement options.
- 2024: JSONB support (3.45) improved JSON storage/performance patterns.
Build your understanding
Work through these exercises in order. Try the task before opening a reference.
- Create a notes table (id, title, body, created_at). Write SQL to insert 3 notes, list newest first, and update one title.
- Given a tasks table with 10k rows, design one index for: WHERE status = 'open' AND due_date < ? ORDER BY due_date. Verify using EXPLAIN QUERY PLAN.
- Model a sync_queue table for pending API writes. Add retry_count and last_error fields, then write queries to fetch next 20 unsynced rows and mark one as synced atomically.
- Migrate a loose-typed expenses table into a STRICT table, preserving data quality and rejecting invalid rows. Document each migration step.
- Enable WAL mode, run concurrent read/write simulation, and compare behavior with DELETE journal mode in a short summary.
More ways to practice
index for real query speed
Given a tasks table with 10k rows, design one index for: WHERE status = 'open' AND due_date < ? ORDER BY due_date. Verify using EXPLAIN QUERY PLAN.
offline sync staging
Model a sync_queue table for pending API writes. Add retry_count and last_error fields, then write queries to fetch next 20 unsynced rows and mark one as synced atomically.
strict migration drill
Migrate a loose-typed expenses table into a STRICT table, preserving data quality and rejecting invalid rows. Document each migration step.
wal reliability check
Enable WAL mode, run concurrent read/write simulation, and compare behavior with DELETE journal mode in a short summary.
Documentation and sources
- Official Documentation (sqlite.org)
- SQL Language Reference (sqlite.org)
- Command-Line Shell (sqlite.org)
- Query Planner Overview (sqlite.org)
Catalog research notes
- Recent 3.45+ releases introduced JSONB support for faster JSON workflows.
- STRICT tables continue improving safer schema design and type discipline.
- Query planner and indexing optimizations keep reducing unnecessary full scans.
- CLI/PRAGMA diagnostics and maintenance tooling continue to improve.
Connect the ideas
Explore next
Real World Example
Embedded Configuration Management for Edge IoT Gateways
Industrial IoT gateway manufacturers needing to store local device configurations and sensor metadata on resource-constrained hardware.
The business problem
Traditional client-server databases introduce excessive overhead, network dependencies, and maintenance complexity for devices operating in disconnected or low-bandwidth environments.
How this topic solves it
SQLite provides a serverless, self-contained relational database engine that resides within the application process, eliminating the need for a separate database server daemon while maintaining full SQL support.
- Integrate the SQLite library directly into the gateway application binary during the build process.
- Initialize a local database file on the gateway's persistent storage to house configuration schemas.
- Utilize standard SQL commands to define tables for device settings and sensor thresholds.
- Enable Write-Ahead Log (WAL) mode to allow concurrent reads and writes, improving responsiveness during sensor data logging.
- Perform periodic integrity checks to ensure data consistency despite potential power interruptions.
Shows the relationship between the application process and the embedded data store.
Gateway logic
In-process library
Device settings
Metadata
- App Process → SQL Queries → SQLite Engine
- SQLite Engine → 1:1 → Config Table
- SQLite Engine → 1:N → Sensor Table
Implementation example
This snippet demonstrates opening a local database file and enabling Write-Ahead Log mode to support concurrent operations, as required for the gateway's responsiveness.
sqlite3 *db; // Database handle
sqlite3_open("gateway.db", &db); // Open local file
sqlite3_exec(db, "PRAGMA journal_mode=WAL;", NULL, NULL, NULL); // Enable WAL for concurrencyExpected business outcome
The gateway achieves reliable, high-performance local data persistence with zero operational overhead for database administration.
Limitations and checks
SQLite is designed for single-writer concurrency; while WAL mode improves performance, heavy write contention from multiple processes can lead to locking issues, which can be monitored via result codes returned by the C/C++ API.