Tables and schemas

Problem

What becomes confusing, fragile, or impossible without understanding tables and schemas? This lesson answers that through explanation, a worked example, two runnable exercises, and a reference solution. No teacher-supplied worksheet is required.

WebAssembly brought a portable low-level compilation target to the browser; SQLite brought a mature relational engine into one embedded file.

Concept explanation

Tables and schemas belongs to “SQLite inside the browser”. SQLite stores relational data in pages and chooses access using schema, indexes, statistics, and planner. This lesson turns tables and schemas into a working part of weekly fixture.

For tables and schemas, trace concrete input, state transition, output, and failure through an explicit low-level or relational boundary where memory, crossings, schemas, queries, and persistence costs can be measured.

JavaScript calls into a WASM module whose linear memory and host imports form an explicit boundary. SQLite executes queries over pages, indexes, and a planner. Apply that model to supplied normal, boundary, and failure cases; each case below names its input and expected evidence.

Example bank

Compare normal, boundary, failure, and cross-layer cases. Predict each observation before revealing the explanation.

Worked example

Start from supplied lab.sql. Focus: Build tables and schemas around this exact fixture: Create notes table, insert 10k rows, query by indexed and unindexed columns.

1. Run: sqlite3 :memory: < lab.sql
2. Save baseline evidence. Inspect module exports, memory, JS/WASM crossings, schemas, indexes, transactions, query plans, OPFS files, and recovery.
3. Boundary case: Add tables and schemas support for weekly boundary case: Wrap writes in transaction and compare timing.
4. Failure case: Make tables and schemas surface—not hide—this failure: Violate constraint and prove atomic rollback.

RESULT
tables and schemas handles baseline and boundary while preserving this observable result: EXPLAIN QUERY PLAN shows scan versus index; transaction changes cost; failed transaction leaves invariant true. Starter-level baseline: Query plan reports use of observations_topic; final query prints baseline.

Starter material

Create lab.sql, paste this exact content, then run the command below.

CREATE TABLE observations (id INTEGER PRIMARY KEY, topic TEXT NOT NULL, result TEXT NOT NULL);
INSERT INTO observations(topic, result) VALUES ("tables and schemas", 'baseline');
CREATE INDEX observations_topic ON observations(topic);
EXPLAIN QUERY PLAN SELECT result FROM observations WHERE topic = "tables and schemas";
SELECT result FROM observations WHERE topic = "tables and schemas";

STOP / CLEANUPIn-memory SQLite exits after script. Stop any local web server with Ctrl+C.

Independent exercise

Add one real case: tables and schemas

1. Create second case from blank file: Add tables and schemas support for weekly boundary case: Wrap writes in transaction and compare timing.
2. Then create controlled failure: Make tables and schemas surface—not hide—this failure: Violate constraint and prove atomic rollback.
3. Use WASM debugger · SQLite CLI · EXPLAIN QUERY PLAN · OPFS to prove behavior, then repair controlled failure.
4. Compare result against supplied acceptance checks and reference approach before marking complete.

Expected result

• tables and schemas handles baseline and boundary while preserving this observable result: EXPLAIN QUERY PLAN shows scan versus index; transaction changes cost; failed transaction leaves invariant true.
• Query plan reports use of observations_topic; final query prints baseline.
• Controlled tables and schemas failure produces captured evidence; repair restores stated invariant without hiding error.

Hints

Solution

Attempt both exercises before opening reference approach.