Advanced SQLite: durability, search and encryption
You will learn SQLite concurrency, full-text search, JSON, encryption and forensic inspection of application databases.
Level 201 · 16 lessons · 15 practice labs
Lessons
Durability and settings
- Transactions, journal modes and WAL: You will see how a transaction applies all or nothing and how WAL mode improves read-write concurrency.
- PRAGMA settings: foreign keys, sync and busy timeout: You will turn on foreign key checks and tune write safety and lock waiting with PRAGMA.
- Indexes and EXPLAIN QUERY PLAN: You will read whether a query uses an index with EXPLAIN QUERY PLAN and build composite and partial indexes.
- Online backup, VACUUM and integrity checks: You will back up a live database with .backup, shrink it with VACUUM and catch corruption with integrity_check.
- STRICT tables and generated columns
- Short task: the right PRAGMA settings for an app
Advanced queries
- CTEs and window functions: You will break a query into steps with WITH and compute ranking and running totals across rows.
- SQLite JSON functions: You will store JSON text and pull values from it with functions such as json_extract and use them in queries.
- Full-text search with FTS5: You will build fast, relevance-ranked search over large text with an FTS5 virtual table.
- Triggers and change trails: You will write changes to a trail table with a trigger that fires when a row changes.
- UPSERT and RETURNING
- Short task: add full-text search to a notes app
Security and application use
- Parameterised queries with Python sqlite3: You will pass user input from Python safely with ? placeholders instead of pasting it into the query.
- Encrypting the database file with SQLCipher: You will encrypt the database with SQLCipher so a stolen file is unreadable, and keep the key out of code.
- Forensic inspection of application databases: You will open browser and messaging app SQLite files read-only and understand traces of deleted records (freelist, WAL).
- Capstone: inspect and strengthen a mobile app database
Practice labs
- Turn on foreign key enforcement: Products are left behind when a category is deleted; enable PRAGMA foreign_keys and test the behaviour again.
- Reduce "database is locked" errors with WAL: In a log database written by two processes, measure and reduce lock errors with WAL mode and busy_timeout.
- Shrink a bloated database and verify it: Measure free pages in a file that stayed large after deletions, take an online backup, run VACUUM and verify with integrity_check.
- Spot a full table scan in the plan: Use EXPLAIN QUERY PLAN to tell which queries do a SCAN and which do a SEARCH.
- Add the right index to a slow search screen: Time a phone book app's surname and city search, add a composite index and compare the timing.
- Harden data with a STRICT table and generated column: Move a loosely typed invoice table to a STRICT table, compute the taxed amount in a generated column and index it.
- Insert or update in one statement: Apply a stock count file with UPSERT: update existing items' quantity and insert new ones.
- Report from inside a JSON field: From a table that stores settings as JSON, use json_extract to report the spread of theme and language choices.
- Keep a change trail with triggers: Create triggers that log every salary update with old and new values into an audit table.
- Run a full-text search over notes: Write queries on a ready FTS5 table that search for two words together and as a phrase.
- Rank search results by relevance: Write a knowledge base search that orders by bm25 and returns highlighted snippets.
- Keep the FTS index in sync with the main table: Link an external-content FTS5 table to its main table with triggers and prove deletes and updates show up in search.
- Find the latest messages in an app database: In a copied messaging app database, list the last 20 messages with time and contact.
- Parameterise a concatenated query: Turn a string-concatenated query in a Python script into a parameterised one and test it with hostile input.
- Decode timestamps in a phone backup: Decode timestamps from three app databases that use different epoch formats and merge them into one timeline.
About this course
You will learn SQLite concurrency, full-text search, JSON, encryption and forensic inspection of application databases.
What you will learn
- Durability and settings
- Advanced queries
- Security and application use
Prerequisites
Finishing the level 101 courses of the same path, or knowing those topics, is enough.
Lab environment
Lessons run in your own Kali Linux terminal that opens in the browser. Nothing to install; the lab starts clean in every lesson.
Frequently asked questions
- Is this course right for my level?
- The course is at level 201; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.