sql-tutorial-engine

# Skill: sql-tutorial-engine

## What this skill knows

This skill covers the sql-tutorial-engine project: a React SPA for interactive SQL learning powered by sql.js (SQLite in WebAssembly). The agent writes SQL queries in a CodeMirror 6 editor, executes them in-browser, and validates results against lesson step criteria.

## Tech stack

- sql.js v1 (SQLite WASM) for all query execution
- CodeMirror 6 with `@codemirror/lang-sql` for the editor
- React + Zustand for state
- React Router v6 for routing
- Vitest for tests

## SQL engine API

```ts
import { loadSqlJs, createDb, execDataset, runQuery, getSchema } from '@/sql/engine';

// 1. Load WASM (once, on app init)
await loadSqlJs();

// 2. Create DB and load dataset
const db = createDb();
const sql = await fetch('/datasets/ecommerce.sql').then(r => r.text());
execDataset(db, sql);

// 3. Run a query (may throw on SQL error)
try {
  const result = runQuery(db, 'SELECT * FROM customers');
  // result: { columns, rows, rowCount, executionMs }
} catch (err) {
  // err.message is the SQLite error text
}

// 4. Get schema
const schema = getSchema(db);
// schema: TableSchema[]
```

## useRunQuery hook

```ts
const { runQuery } = useRunQuery();

// Executes SQL, updates store, appends to history
await runQuery('SELECT name FROM customers WHERE city = "Austin"');

// Access results from store
const { queryResult, queryError } = useSqlStore();
```

## Lesson step validation

Each step has optional criteria. Validation happens in `useLessonStep`:

```ts
function validateResult(result: QueryResult, step: LessonStep): ValidationResult {
  // Check columns (case-insensitive)
  if (step.expectedColumns) {
    const actual = result.columns.map(c => c.toLowerCase());
    const expected = step.expectedColumns.map(c => c.toLowerCase());
    if (!arraysEqual(actual, expected)) {
      return { valid: false, message: `Expected columns: ${expected.join(', ')}` };
    }
  }

  // Check row count
  if (step.expectedRowCount !== undefined) {
    if (result.rowCount !== step.expectedRowCount) {
      return { valid: false, message: `Expected ${step.expectedRowCount} rows, got ${result.rowCount}` };
    }
  }

  // If no criteria, any successful result passes
  return { valid: true, message: 'Correct!' };
}
```

## Lesson frontmatter

```yaml
---
id: "04-joins"
title: "JOIN"
order: 4
estimatedMinutes: 12
prerequisites: ["01-select", "02-where", "03-aggregates"]
dataset: "ecommerce"
steps:
  - id: "inner-join"
    instruction: "Join orders and customers on customer_id. Return customer_name and total_amount."
    expectedColumns: ["customer_name", "total_amount"]
    expectedRowCount: 50
    allowAnyOrder: true
    hint: "SELECT c.name AS customer_name, o.total_amount FROM orders o JOIN customers c ON o.customer_id = c.id"
---
```

## Datasets

### ecommerce (lessons 01-06, default)

Tables: `customers` (20 rows), `orders` (50 rows), `products` (30 rows), `order_items` (120 rows).

Key relationships:
- `orders.customer_id` -> `customers.id`
- `order_items.order_id` -> `orders.id`
- `order_items.product_id` -> `products.id`

### analytics (lessons 07-09)

Tables: `users` (100 rows), `sessions` (400 rows), `events` (2400 rows), `page_views` (1200 rows).

Load a dataset:

```ts
import { initDb } from '@/store/useSqlStore';
await initDb('ecommerce');
```

`initDb` creates a fresh DB, fetches the SQL file, and runs it.

## CodeMirror 6 setup

```tsx
import { EditorView, keymap } from '@codemirror/view';
import { EditorState } from '@codemirror/state';
import { sql } from '@codemirror/lang-sql';
import { oneDark } from '@codemirror/theme-one-dark';
import { defaultKeymap } from '@codemirror/commands';
import { indentOnInput } from '@codemirror/language';

const view = new EditorView({
  state: EditorState.create({
    doc: '',
    extensions: [
      sql(),
      oneDark,
      indentOnInput(),
      keymap.of([
        ...defaultKeymap,
        { key: 'Ctrl-Enter', mac: 'Cmd-Enter', run: () => { onRun(); return true; } },
      ]),
      EditorView.updateListener.of(update => {
        if (update.docChanged) {
          setCurrentSql(update.state.doc.toString());
        }
      }),
    ],
  }),
  parent: editorRef.current!,
});
```

In React, use `useRef` for the editor container and create/destroy the view in `useEffect`.

## SQL autocomplete from schema

```ts
// src/hooks/useAutoComplete.ts
import { schemaCompletion } from '@codemirror/lang-sql';

export function buildSqlCompletion(schema: TableSchema[]) {
  const tables: Record<string, string[]> = {};
  for (const t of schema) {
    tables[t.name] = t.columns.map(c => c.name);
  }
  return schemaCompletion({ schema: tables });
}
```

Pass the result as an extension to `EditorState.create`.

## Routes

| Path | Component |
|------|-----------|
| `/` | `HomePage` - lesson catalogue |
| `/lesson/:id` | `LessonPage` - 3-column layout |
| `/sandbox` | `SandboxPage` - editor + schema |
| `/progress` | `ProgressPage` |

## Keyboard shortcuts

| Shortcut | Action |
|----------|--------|
| Ctrl+Enter (Cmd+Enter on Mac) | Run query |
| Ctrl+Shift+F | Format SQL |
| Ctrl+L | Clear editor |

## localStorage schema

| Key | Type | Contents |
|-----|------|----------|
| `sql_progress` | JSON | `{ [lessonId]: { completedSteps: string[], completedAt?: number } }` |
| `sql_history` | JSON | Last 50 HistoryEntry objects |
| `sql_settings` | JSON | Font size, page size, auto-advance |

## Vite WASM configuration

sql.js requires copying the WASM file to the build output:

```ts
// vite.config.ts
import wasm from 'vite-plugin-wasm';

export default {
  plugins: [wasm()],
  optimizeDeps: {
    exclude: ['sql.js'],
  },
  build: {
    rollupOptions: {
      external: ['sql.js'],
    },
  },
};
```

Or use `assetsInclude: ['**/*.wasm']` and serve the file from `public/sql-wasm.wasm`.

## Common mistakes

- Always call `loadSqlJs()` before creating a Database. Check `useSqlStore.loading` before rendering the editor.
- `db.exec()` returns an array of result sets (one per statement). Multi-statement SQL returns multiple sets; only process `results[0]` for SELECT.
- `db.run()` does not return rows - use it for DDL/DML. `db.exec()` returns results.
- The sql.js WASM binary is about 1.5 MB. Show a loading spinner on first page load.
- Do not store the `Database` instance in React state directly - it is a class instance with internal state. Store it in a `useRef` or Zustand store that is not serialized.