SQL Practice

An interactive, browser-based SQL learning platform for students. Write real SQL queries against live SQLite databases — no server, no installation, no ongoing costs.

Built with Blazor WebAssembly (.NET 10), sql.js (SQLite compiled to WebAssembly), CodeMirror 6, and Markdig.

---

Features

• Interactive SQL editor — CodeMirror 6 with SQLite syntax highlighting and autocomplete. Press Ctrl+Enter to run.
• In-browser SQLite — queries execute entirely in the browser via sql.js (no server required).
• Lessons — markdown-based lessons with live databases, rendered with full support for tables, code blocks, headings, and more.
• Problems — practice problems with a Verify button that checks student answers against expected results.
• Playground — free-form query editor against any loaded database.
• Database management — load, reset to original data, download as .db, or upload your own .sql script or .db file.
• Three starter databases — School, Northwind Lite, Chinook Lite.
• Deployable as static files — works on GitHub Pages, Azure Static Web Apps, Netlify, or any static host.

---

Prerequisites

Tool	Minimum version	Purpose
.NET SDK	10.0	Build the Blazor app
Node.js	18 LTS	Bundle the CodeMirror editor (one-time step)

---

Getting Started

1. Clone the repository

git clone <your-repo-url>
cd SQL-Practice

2. Install Node dependencies (first time only)

npm install

This installs CodeMirror 6, the SQL language plugin, and esbuild. It does not affect the .NET project.

3. Run the app locally

cd SQL-Practice
dotnet run

Open the URL shown in the terminal (typically https://localhost:5001).

---

Workflow: Making Changes and Updating the Site

Different types of changes require different rebuild steps.

Changing lessons, problems, or databases (content only)

Edit the files under SQL-Practice/wwwroot/content/. No rebuild is needed during development — dotnet run serves the files directly. For a production deployment, just rebuild and redeploy:

cd SQL-Practice
dotnet publish -c Release

Output lands in bin/Release/net10.0/publish/wwwroot/ — copy that folder to your host.

Changing C# code (pages, services, components, models)

cd SQL-Practice
dotnet build        # check for errors
dotnet run          # run with hot reload

For deployment:

dotnet publish -c Release

Changing the SQL editor (CodeMirror)

The editor source lives in editor-src/editor.js. After editing it, rebuild the bundle:

# From the repo root (where package.json is)
npm run build          # minified production bundle
npm run build:dev      # unminified with source maps (for debugging)

Then run or publish the .NET project as normal.

Full rebuild (all three steps together)

Run this from the repo root when you want to do everything at once:

npm run build && cd SQL-Practice && dotnet publish -c Release

---

Project Structure

SQL-Practice/                        ← repo root
├── package.json                     ← npm config (CodeMirror + esbuild)
├── editor-src/
│   └── editor.js                    ← CodeMirror 6 source (edit this to change editor behaviour)
└── SQL-Practice/                    ← .NET Blazor WASM project
    ├── SQL-Practice.csproj
    ├── Program.cs                   ← DI service registrations
    ├── App.razor                    ← root component / router
    ├── _Imports.razor               ← global @using directives
    ├── Models/                      ← C# data models (Lesson, Problem, QueryResult, …)
    ├── Services/
    │   ├── ContentService.cs        ← loads JSON/Markdown from wwwroot via HttpClient
    │   ├── SqliteService.cs         ← IJSRuntime wrapper for sql.js
    │   ├── DatabaseManager.cs       ← tracks loaded databases, lazy-loads from scripts
    │   └── VerificationService.cs   ← compares student results to expected output
    ├── Components/
    │   ├── SqlEditor.razor          ← CodeMirror wrapper component
    │   ├── QueryResults.razor       ← renders a result table
    │   └── MarkdownView.razor       ← Markdig markdown → HTML renderer
    ├── Pages/
    │   ├── Home.razor
    │   ├── Lessons.razor            ← /lessons
    │   ├── LessonView.razor         ← /lesson/{id}
    │   ├── Problems.razor           ← /problems
    │   ├── ProblemSolve.razor       ← /problem/{id}
    │   ├── Playground.razor         ← /playground
    │   └── Databases.razor          ← /databases
    ├── Layout/
    │   ├── MainLayout.razor
    │   └── NavMenu.razor
    └── wwwroot/
        ├── index.html               ← single HTML entry point
        ├── js/
        │   ├── sql-wasm.js          ← sql.js loader (from npm, committed to repo)
        │   ├── sql-wasm.wasm        ← SQLite compiled to WASM (~1.5 MB)
        │   ├── sqlInterop.js        ← multi-database JS interop module
        │   └── editor.bundle.js     ← CodeMirror bundle (output of npm run build)
        └── content/                 ← ALL editable content lives here
            ├── manifest.json        ← master list of lessons, problems, and databases
            ├── lessons/
            │   └── {id}/
            │       ├── meta.json    ← title, order, database, description, tags
            │       └── content.md   ← lesson body (full markdown)
            ├── problems/
            │   └── {id}/
            │       ├── meta.json    ← title, database, hint, expectedResults
            │       └── description.md ← problem statement (markdown)
            └── databases/
                ├── school.sql
                ├── northwind.sql
                └── chinook.sql

---

Adding Content

Add a new lesson

1. Create a folder: wwwroot/content/lessons/{your-id}/
2. Create meta.json:

{
  "id": "your-id",
  "title": "Your Lesson Title",
  "description": "One-line description shown on the lessons list.",
  "database": "school",
  "order": 5,
  "tags": ["your", "tags"]
}

3. Create content.md with the full lesson body (standard markdown).
4. Register it in manifest.json — add an entry to the "lessons" array with the same fields as meta.json.

Add a new problem

1. Create a folder: wwwroot/content/problems/{your-id}/
2. Create meta.json:

{
  "id": "your-id",
  "title": "Problem Title",
  "lessonId": "related-lesson-id",
  "database": "school",
  "order": 3,
  "hint": "Optional hint shown when the student asks for help.",
  "expectedResults": {
    "columns": ["column1", "column2"],
    "rows": [
      ["value1a", "value2a"],
      ["value1b", "value2b"]
    ]
  }
}

3. Create description.md with the problem statement.
4. Register it in manifest.json under the "problems" array.

How to get expectedResults: Run the correct query in the Playground, then copy the columns and rows into the JSON. All values are stored as strings (numbers and dates included).

Add a new database

1. Write a .sql file and place it in wwwroot/content/databases/.
2. Register it in manifest.json under the "databases" array:

{
  "id": "my-database",
  "title": "My Database",
  "description": "What this database contains.",
  "script": "databases/my-database.sql"
}

The script should be a series of CREATE TABLE and INSERT INTO statements compatible with SQLite. Avoid BEGIN TRANSACTION / COMMIT wrappers — the service executes the script in a single call.

---

Verification Logic

When a student clicks Verify, the app:

1. Executes their query against the in-memory SQLite database.
2. Compares the result to the expectedResults stored in the problem's meta.json.

The check is exact-match (same columns in the same order, same rows in the same order, case-insensitive string comparison). If a problem requires a specific ORDER BY, the expected rows must be stored in that order.

To update the expected answer for a problem, edit expectedResults in its meta.json and redeploy.

---

Deployment

The published output is a folder of static files that can be hosted anywhere.

Azure Static Web Apps (recommended — free tier)

1. dotnet publish -c Release
2. Deploy the bin/Release/net10.0/publish/wwwroot/ folder.
3. No special headers are needed (sql.js runs on the main thread, not a Web Worker).

GitHub Pages

1. Publish as above.
2. Add a .nojekyll file at the root of the deployed folder (prevents Jekyll from ignoring _framework/).
3. Add a 404.html that is a copy of index.html (needed for client-side routing on direct URL access).

# After dotnet publish
$pub = "SQL-Practice\bin\Release\net10.0\publish\wwwroot"
Copy-Item "$pub\index.html" "$pub\404.html"
New-Item "$pub\.nojekyll" -ItemType File

Netlify / Vercel

Deploy the wwwroot publish folder. Add a redirect rule so all paths serve index.html:

Netlify — create _redirects in the publish root:

/*  /index.html  200

Vercel — create vercel.json in the publish root:

{ "rewrites": [{ "source": "/(.*)", "destination": "/index.html" }] }

---

Architecture Notes

• All SQL runs in the browser. sql.js compiles SQLite to WebAssembly. No query ever leaves the client.
• Databases are in-memory. Data resets on page refresh. Students can click Reset on the Databases page to restore any database to its original state at any time.
• The solution query is never stored. Only the expected output (expectedResults) is stored in meta.json. Students cannot find the answer SQL by inspecting the source.
• No authentication. This is designed as a teaching tool for a classroom setting, not a secure assessment platform.
• Page load size is roughly 3–5 MB gzipped (Blazor WASM runtime + sql.js WASM + CodeMirror bundle). A loading spinner is shown during startup.