index.html

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>SQL Query Playground — DuckDB in your browser</title>
<meta name="description" content="Write and run real SQL against a seeded warehouse — DuckDB compiled to WebAssembly, executed entirely in your browser. 55 tested templates included.">
<link rel="icon" href="data:image/svg+xml,<svg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 100 100'><text y='.9em' font-size='90'>🦆</text></svg>">
<style>
@font-face {
  font-family: "Space Grotesk";
  src: url("https://raw.githubusercontent.com/Reaver1000/sql-query-builder/master/assets/fonts/SpaceGrotesk.woff2") format("woff2");
  font-display: swap;
}
:root {
  --bg: #0d1117; --panel: #161b22; --border: #2b3442; --text: #e6edf3;
  --muted: #93a0b4; --accent: #7ee787; --accent2: #79c0ff; --err: #ff7b72;
}
* { box-sizing: border-box; }
body {
  margin: 0; background: var(--bg); color: var(--text);
  font-family: "Space Grotesk", system-ui, sans-serif; letter-spacing: -0.2px;
}
header { display: flex; align-items: baseline; gap: 14px; flex-wrap: wrap; padding: 18px 22px 0; }
header h1 { font-size: 1.35rem; margin: 0; letter-spacing: -0.5px; }
header .tag { color: var(--muted); font-size: .85rem; }
main { display: grid; grid-template-columns: 280px 1fr; gap: 16px; padding: 16px 22px 30px; }
aside, section.panel { background: var(--panel); border: 1px solid var(--border); border-radius: 12px; }
aside { padding: 14px; max-height: 74vh; overflow: auto; }
aside h2 { font-size: .8rem; text-transform: uppercase; letter-spacing: .8px; color: var(--muted); margin: 4px 0 8px; }
details { margin-bottom: 6px; }
summary { cursor: pointer; padding: 7px 10px; border-radius: 8px; background: #1c2330; border: 1px solid var(--border); font-weight: 600; font-size: .9rem; }
summary:hover { background: #232b3b; }
.cols { padding: 6px 4px 0 10px; font-size: .78rem; color: var(--muted); line-height: 1.7; }
.cols b { color: var(--text); font-weight: 600; }
.cols i { font-style: normal; color: var(--accent2); font-size: .72rem; }
#templates h2 { margin-top: 16px; }
.tpl-item { display: block; width: 100%; text-align: left; cursor: pointer; color: var(--text);
  background: none; border: none; font: inherit; font-size: .85rem; padding: 5px 10px 5px 18px; border-radius: 6px; }
.tpl-item:hover { background: #1f2637; color: var(--accent); }
.tpl-desc { display: block; font-size: .72rem; color: var(--muted); padding: 0 10px 4px 18px; }
.editorwrap { padding: 14px; }
textarea#sql {
  width: 100%; min-height: 180px; resize: vertical; background: #0b0e14; color: #c9e1b8;
  border: 1px solid var(--border); border-radius: 8px; padding: 12px; font-size: .92rem;
  font-family: ui-monospace, "Cascadia Code", Consolas, monospace; line-height: 1.55; outline: none;
}
textarea#sql:focus { border-color: var(--accent); }
.bar { display: flex; gap: 10px; align-items: center; margin: 10px 0 6px; flex-wrap: wrap; }
button#run { background: var(--accent); color: #04140a; font-weight: 700; border: none; border-radius: 8px;
  padding: 9px 22px; font-size: .95rem; cursor: pointer; font-family: inherit; letter-spacing: -0.3px; }
button#run:hover { filter: brightness(1.1); }
button#run:disabled { opacity: .45; cursor: wait; }
#status { font-size: .82rem; color: var(--muted); }
#status.ok { color: var(--accent); }
#status.err { color: var(--err); }
#errbox { display: none; white-space: pre-wrap; background: rgba(255,123,114,.08); border: 1px solid rgba(255,123,114,.4);
  border-radius: 8px; color: var(--err); padding: 10px 12px; font-size: .82rem; font-family: ui-monospace, Consolas, monospace; margin-top: 8px; }
.tbl-scroll { max-height: 48vh; overflow: auto; border: 1px solid var(--border); border-radius: 8px; margin-top: 10px; }
table { border-collapse: collapse; width: 100%; font-size: .8rem; font-family: ui-monospace, Consolas, monospace; }
th { position: sticky; top: 0; background: #1c2330; color: var(--accent2); text-align: left; padding: 7px 10px; border-bottom: 1px solid var(--border); }
td { padding: 5px 10px; border-bottom: 1px solid #212938; color: var(--text); white-space: nowrap; }
td.null { color: #55627a; font-style: italic; }
tr:hover td { background: #1a2130; }
.rownote { font-size: .75rem; color: var(--muted); margin-top: 6px; }
footer { padding: 6px 22px 26px; color: var(--muted); font-size: .8rem; }
footer a { color: var(--accent2); text-decoration: none; }
footer a:hover { text-decoration: underline; }
#boot { position: fixed; inset: 0; display: flex; align-items: center; justify-content: center;
  background: var(--bg); color: var(--muted); font-size: .95rem; z-index: 50; flex-direction: column; gap: 12px; }
#boot .spinner { width: 28px; height: 28px; border: 3px solid var(--border); border-top-color: var(--accent); border-radius: 50%; animation: spin 0.8s linear infinite; }
@keyframes spin { to { transform: rotate(360deg); } }
@media (max-width: 900px) { main { grid-template-columns: 1fr; } aside { max-height: none; } }
</style>
</head>
<body>
<div id="boot"><div class="spinner"></div><div>Loading DuckDB into WebAssembly… (~2.5 MB, then cached)</div></div>
<header>
  <h1>🦆 SQL Query Playground</h1>
  <span class="tag">55 tested templates · sequential warehouse runs in <i>your</i> browser — nothing is uploaded</span>
</header>
<main>
  <aside>
    <div id="schema"><h2>Schema</h2></div>
    <div id="templates"><h2>Template library</h2></div>
  </aside>
  <section class="panel">
    <div class="editorwrap">
      <textarea id="sql" spellcheck="false"></textarea>
      <div class="bar">
        <button id="run" disabled>Run (Ctrl+Enter)</button>
        <span id="status">Booting DuckDB…</span>
      </div>
      <div id="errbox"></div>
      <div class="tbl-scroll" id="resultswrap" hidden><table id="results"></table></div>
      <div class="rownote" id="rownote"></div>
    </div>
  </section>
</main>
<footer>
  Data: seeded e-commerce warehouse (1,000 customers · 5,000 orders · 12,663 items) ·
  Templates + code: <a href="https://github.com/Reaver1000/sql-query-builder">github.com/Reaver1000/sql-query-builder</a> ·
  Portfolio: <a href="https://reaver1000.github.io">davidbuchan.dev</a> ·
  Built by <a href="https://www.linkedin.com/in/david-a-buchan/">David Buchan</a>
</footer>
<script type="module">
const $ = (id) => document.getElementById(id);
const CSV_STATS = { customers: 1000, products: 100, orders: 5000, order_items: 12663 };

const STARTER = `-- Top customers by lifetime spend
SELECT c.name, c.country,
       COUNT(o.order_id)     AS orders,
       ROUND(SUM(o.total_amount), 2) AS lifetime_spend
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.name, c.country
ORDER BY lifetime_spend DESC
LIMIT 10`;

function display(v, tstr) {
  if (v === null || v === undefined) return { s: "NULL", cls: "null" };
  const t = tstr || "";
  if (typeof v === "bigint" && t.startsWith("Timestamp")) {
    let ms = Number(v);
    if (t.includes("Nanosecond")) ms /= 1e6;
    else if (t.includes("Microsecond")) ms /= 1e3;
    else if (t.includes("Second")) ms *= 1000;
    return { s: new Date(ms).toISOString().slice(0, 10), cls: "" };
  }
  if (t.startsWith("Date<")) {
    const ms = t.startsWith("Date<Millisecond") ? Number(v) : Number(v) * 86400000;
    return { s: new Date(ms).toISOString().slice(0, 10), cls: "" };
  }
  if (v instanceof Date) { return { s: v.toISOString().slice(0, 10), cls: "" }; }
  if (typeof v === "bigint") { return { s: v.toLocaleString("en-US"), cls: "" }; }
  if (typeof v === "number") { return { s: Number.isInteger(v) ? v.toLocaleString("en-US") : v.toFixed(2), cls: "" }; }
  return { s: String(v), cls: "" };
}

async function boot() {
  const duckdb = await import("https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.33.1-dev57.0/+esm");
  const bundle = await duckdb.selectBundle(duckdb.getJsDelivrBundles());
  const worker = await duckdb.createWorker(bundle.mainWorker);
  const db = new duckdb.AsyncDuckDB(new duckdb.ConsoleLogger(), worker);
  await db.instantiate(bundle.mainModule);

  for (const name of Object.keys(CSV_STATS)) {
    const resp = await fetch(`data/${name}.csv`);
    if (!resp.ok) { throw new Error(`Missing data file: ${name}.csv (HTTP ${resp.status})`); }
    const buf = new Uint8Array(await resp.arrayBuffer());
    await db.registerFileBuffer(name, buf);
  }
  const conn = await db.connect();
  for (const name of Object.keys(CSV_STATS)) {
    await conn.query(`CREATE TABLE ${name} AS SELECT * FROM read_csv_auto('${name}', header=true)`);
  }
  return { duckdb, db, conn };
}

let conn = null;

function describe_table(name, rows) {
  const div = document.createElement("details");
  div.innerHTML = `<summary>${name}</summary>`;
  const cols = document.createElement("div");
  cols.className = "cols";
  cols.innerHTML = rows.map(r => `<div><b>${r[0]}</b> <i>${r[1]}</i></div>`).join("");
  div.appendChild(cols);
  $("schema").appendChild(div);
}

async function buildSchema() {
  for (const name of Object.keys(CSV_STATS)) {
    const res = await conn.query(`DESCRIBE ${name}`);
    const rows = res.toArray().map(o => [o.column_name ?? o["name"], o.column_type ?? o["type"]]);
    describe_table(name, rows);
  }
}

async function buildTemplates() {
  const all = await (await fetch("templates.json")).json();
  const cats = {};
  all.forEach(t => { (cats[t.category] = cats[t.category] || []).push(t); });
  for (const [cat, items] of Object.entries(cats)) {
    const d = document.createElement("details");
    d.open = cat === "Basics";
    const s = document.createElement("summary");
    s.textContent = `${cat} (${items.length})`;
    d.appendChild(s);
    items.forEach(t => {
      const label = document.createElement("span");
      label.className = "tpl-desc";
      label.textContent = t.description || "";
      const b = document.createElement("button");
      b.className = "tpl-item";
      b.textContent = t.name;
      b.addEventListener("click", () => {
        $("sql").value = `-- ${t.description || t.name}\n${t.query}`;
        $("sql").focus();
        run();
      });
      d.appendChild(b);
      d.appendChild(label);
    });
    $("templates").appendChild(d);
  }
}

async function run() {
  const sql = $("sql").value.trim();
  if (!sql || !conn) return;
  const btn = $("run"), st = $("status"), eb = $("errbox");
  btn.disabled = true;
  eb.style.display = "none";
  st.className = "";
  st.textContent = "Executing…";
  $("resultswrap").hidden = true;
  $("rownote").textContent = "";
  const t0 = performance.now();
  try {
    const hsv = sql.replace(/;\s*$/, "");
    const desc = await conn.query(`DESCRIBE SELECT * FROM (${hsv})`);
    const dateCols = desc.toArray()
      .filter(r => r.column_type && /TIMESTAMP|DATE/i.test(r.column_type))
      .map(r => r.column_name);
    let execSql = hsv;
    if (dateCols.length) {
      const casts = dateCols.map(c => {
        const q = `"${c.replace(/"/g, '""')}"`;
        return `CAST(${q} AS VARCHAR) AS ${q}`;
      }).join(", ");
      execSql = `SELECT * REPLACE (${casts}) FROM (${hsv})`;
    }
    const result = await conn.query(execSql);
    const ms = (performance.now() - t0).toFixed(0);
    const rows = result.toArray();
    const types = Object.fromEntries((result.schema.fields || []).map(f => [f.name, String(f.type)]));
    const cols = rows.length ? Object.keys(rows[0]) : (result.schema.fields?.map(f => f.name) || []);
    const head = document.createElement("thead");
    head.innerHTML = "<tr>" + cols.map(c => `<th>${c}</th>`).join("") + "</tr>";
    const body = document.createElement("tbody");
    const CAP = 200;
    rows.slice(0, CAP).forEach(r => {
      const tr = document.createElement("tr");
      tr.innerHTML = cols.map(c => {
        const v = display(r[c], types[c]);
        return `<td class="${v.cls}">${v.s}</td>`;
      }).join("");
      body.appendChild(tr);
    });
    $("results").innerHTML = "";
    if (cols.length) $("results").appendChild(head);
    $("results").appendChild(body);
    $("resultswrap").hidden = false;
    st.className = "ok";
    st.textContent = `✓ ${result.numRows.toLocaleString("en-US")} rows · ${cols.length} cols · ${ms} ms`;
    $("rownote").textContent = rows.length > CAP
      ? `Showing first ${CAP} of ${result.numRows.toLocaleString("en-US")} rows — add a LIMIT to inspect the full result set.`
      : "";
  } catch (e) {
    st.className = "err";
    st.textContent = "✗ Query error";
    eb.style.display = "block";
    eb.textContent = String(e?.message || e).slice(0, 800);
  } finally {
    btn.disabled = false;
  }
}

$("run").addEventListener("click", run);
$("sql").addEventListener("keydown", (e) => {
  if (e.key === "Enter" && (e.ctrlKey || e.metaKey)) { e.preventDefault(); run(); }
});

$("sql").value = STARTER;
try {
  const { conn: c } = await boot();
  conn = c;
  await buildSchema();
  await buildTemplates();
  $("boot").remove();
  $("status").textContent = `${Object.values(CSV_STATS).reduce((a, b) => a + b, 0).toLocaleString("en-US")} rows loaded · zero backend`;
  $("status").className = "ok";
  $("run").disabled = false;
  run();
} catch (e) {
  $("boot").innerHTML = `<div>Failed to load DuckDB: ${String(e?.message || e).slice(0, 200)}</div>`;
}
</script>
</body>
</html>