Spaces:
Running
Running
| <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> | |