Two ways to learn SQL on the Pacific Northwest towns / counties / flights datasets:
- Static site (current, deployable to GitHub Pages free tier) — Astro + sql.js, runs
entirely in the browser, no server. Lives in
site/. - Legacy learnr tutorials (still works locally; previously hosted on shinyapps.io) —
the original
*.Rmd+*.qmdfiles at the repo root.
The static site is the active version. The legacy R sources are kept in place so the
content can be re-generated from them via site/scripts/port_rmd.py.
/
├── README.md ← you are here
├── .github/workflows/deploy.yml ← GitHub Actions: builds /site and deploys to Pages on push to main
│
├── site/ ← NEW: the Astro project that becomes the deployed site
│ ├── package.json ← npm scripts: dev / build / preview
│ ├── astro.config.mjs ← site=https://ismayc.github.io, base=/sql-tutorial/
│ ├── public/
│ │ ├── data/*.sqlite ← the two databases, served as-is to the browser
│ │ ├── sql-wasm.wasm ← copied from node_modules/sql.js/dist/ (also re-copied in the workflow)
│ │ ├── images/ ← ER diagrams + JOIN visualizations (spaces in filenames renamed to hyphens)
│ │ └── favicon.svg
│ ├── src/
│ │ ├── pages/ ← Astro / MDX page routes
│ │ │ ├── index.astro ← landing page
│ │ │ ├── scratchpad.astro ← free-form editor against both DBs
│ │ │ ├── progress.astro ← export / import / reset localStorage progress
│ │ │ ├── examples/{index,*}.mdx ← worked examples (towns + counties)
│ │ │ └── exercises/{index,*}.mdx← practice (PNW flights)
│ │ ├── layouts/BaseLayout.astro ← header, sidebar TOC, theme toggle, footer
│ │ ├── components/ ← SqlExercise, SqlScratchpad, Sidebar, SchemaReference, ZoomImage, Hint, ThemeToggle, SectionFooter
│ │ ├── lib/ ← sql.ts (sql.js loader), diagnostics.ts (result-set diff + friendly errors),
│ │ │ editor.ts (CodeMirror 6 + SQL autocomplete), exercise.ts (component hydrator),
│ │ │ progress.ts (localStorage), theme.ts, sidebar.ts, zoom.ts
│ │ ├── styles/global.css ← Tailwind v4 + custom (sidebar, zoom dialog, schema reference)
│ │ ├── generated-toc.json ← regenerated by port_rmd.py — section list + per-section exercise IDs + solutions
│ │ └── generated-schemas.json ← regenerated by port_rmd.py — column metadata used by autocomplete + schema panel
│ ├── scripts/
│ │ ├── port_rmd.py ← parses the legacy .Rmd files → MDX pages, dumps SQLite schemas to JSON
│ │ └── run-feedback-tests.mjs ← drives the real UI in headless Chromium; writes fixtures/
│ ├── fixtures/ ← committed test artifacts (see fixtures/README.md)
│ │ ├── feedback-tests.jsonl ← one test case per line, machine-readable
│ │ ├── feedback-tests.md ← human-readable report grouped by section
│ │ └── README.md ← format spec + jq query examples
│ └── .env.example ← analytics config template (Plausible / GoatCounter / Umami) — disabled by default
│
├── examples.Rmd, exercises/, *.qmd ← LEGACY: original learnr/quarto tutorials, source of truth for content
├── pnw_database.sqlite ← LEGACY: copied into site/public/data/ by hand (re-copy if updated)
├── pnw_flights_database.sqlite ← LEGACY: same
├── images/ ← LEGACY: copied into site/public/images/ (with spaces → hyphens)
├── 01-…/02-…/03-…/04-….R ← LEGACY: data prep + deploy scripts for the Shiny workflow
└── renv.lock, *.Rproj, rsconnect/ ← LEGACY: R env + shinyapps.io deploy metadata
Requirements: Node 20+, Python 3 (only if you'll regenerate content from .Rmd),
sqlite3 CLI (only for schema dump).
cd site
npm install
npm run dev # → http://localhost:4321/sql-tutorial/cd site
python3 scripts/port_rmd.py
# Re-writes site/src/pages/{examples,exercises}/*.mdx, generated-toc.json, generated-schemas.jsonThe parser groups each {sql queryN, exercise=TRUE} chunk with its matching -hint-N
and -solution chunks; -check (gradethis) chunks are intentionally dropped because the
new site does result-set comparison + friendly errors in site/src/lib/diagnostics.ts.
For each exercise with a solution, generates ~5 variant queries (correct, empty, typo-table, typo-column, plus conditional missing-where / missing-orderby), runs them in headless Chromium against the dev server, captures the user-visible feedback, and writes fixtures used for QA.
cd site
npm run dev # in one terminal
node scripts/run-feedback-tests.mjs # in another (~40s)Outputs land in site/fixtures/; see fixtures/README.md for the
schema and example jq queries.
Pushing to main triggers .github/workflows/deploy.yml, which:
npm ciinsidesite/- Copies
node_modules/sql.js/dist/sql-wasm.wasmintosite/public/sql-wasm.wasm(so the in-browser SQLite engine resolves correctly) npm run build- Uploads
site/dist/as a GitHub Pages artifact - Deploys via
actions/deploy-pages@v4
One-time repo setup (in the GitHub web UI, after the first push):
- Push the workflow + site to
main. - Go to Settings → Pages.
- Set Source = "GitHub Actions" (not "Deploy from a branch").
- The first push to
mainruns the workflow; subsequent pushes auto-deploy.
The site will be served at https://ismayc.github.io/sql-tutorial/. If you move the
repo or rename, update site and base in site/astro.config.mjs
to match.
Disabled by default. Copy site/.env.example → site/.env, fill in one provider's vars
(Plausible / GoatCounter / Umami all wired in site/src/layouts/BaseLayout.astro),
then rebuild. None of the wired providers use cookies.
Static hosting (GitHub Pages or Netlify free tier) doesn't have a concurrent-users limit — the constraints are bandwidth (~100 GB/month) and per-IP request rate. Per fresh visit to a flights-exercises page: ~17.5 MB (377 KB JS + 644 KB WASM + 16 MB DB + small CSS/HTML). Repeat visits are essentially zero bytes (browser cache). That's roughly 5–6 k fresh exercises-page loads per month on the free tier; with cache it supports tens of thousands of total page views. If you ever blow past 100 GB, Cloudflare Pages free tier is unlimited bandwidth and serves the exact same artifacts.
The PNW data is sourced from the pnwflights23 and
related public datasets (see the original .Rmd setup chunks).