A Query Surface of Your Own

Eight lessons built a data layer that only your code can talk to. This one hands it to the user: a textarea, a Run button, and a database that answers whatever you type. It also makes the app show its work — every query it has run, with parameters filled in, and a profiled execution plan for any statement you care to ask about. This is the last lesson of the course, and the data layer is finished when it ends. Checkpoint: engine-09.

Errors are values here

A console’s normal state is failure. People mistype, misremember column names, and paste half a statement. If a bad query throws, the page has to catch it somewhere far from where it happened, and the natural implementations all end up blanking the results area on every typo.

So runQuery never throws:

async function runQuery(sql: string): Promise<SqlRunResult> {
    const t0 = performance.now();
    try {
        const res = await conn.query(sql);
        const columns = res.schema.fields.map(f => f.name);
        const all = res.toArray();
        const truncated = all.length > CONSOLE_ROW_CAP;
        const rows = all.slice(0, CONSOLE_ROW_CAP).map(r => columns.map(c => normalizeValue(r[c])));
        return { columns, rows, ms: performance.now() - t0, truncated, error: null };
    } catch (error) {
        return {
            columns: [],
            rows: [],
            ms: performance.now() - t0,
            truncated: false,
            error: error instanceof Error ? error.message : String(error),
        };
    }
}

Both paths return the same shape; error is either a string or null. The caller branches on data instead of wrapping every call in a try.

And the error text is DuckDB’s own, verbatim. Type SELEC * FROM sales and you get:

Parser Error: syntax error at or near "SELEC"
LINE 1: SELEC * FROM sales
        ^

That caret is more useful than anything a wrapper could write, and replacing it with “Invalid query” would be actively destructive. The engine already produced the best available explanation — the only job is not to lose it.

CONSOLE_ROW_CAP is 500. SELECT * FROM sales matches twenty thousand rows, and rendering all of them locks the tab; truncated lets the UI say so honestly rather than silently lying about what it showed.

Any column type, one shape

The dashboard queries return known types. A console returns whatever the user asks for — COUNT(*) gives a BigInt, a DATE column gives whatever lesson 6 warned about, a list aggregate gives an Arrow vector. All of it has to survive a structured clone:

function normalizeValue(value: unknown): string | number | null {
    if (value == null) return null;
    if (typeof value === 'number' || typeof value === 'string') return value;
    if (typeof value === 'bigint') return Number(value);
    if (value instanceof Date) return value.toISOString().split('T')[0];
    const n = Number(value);
    return Number.isNaN(n) ? String(value) : n;
}

Ordered from certain to speculative, ending in a fallback that tries a number and settles for a string. bigint is the one that would otherwise bite: it clones fine but formats as 20000n and breaks arithmetic silently.

Making the app show its work

The other half of this lesson is introspection. Every dashboard query is a prepared statement whose text contains ? marks, which is unreadable to anyone trying to learn from it. So runPrepared gains a label and keeps a display copy:

const lastSql: Record<string, string> = {};

// Only ever used to *display* a query, never to run one. The running version
// still binds its parameters; this is a readable copy for humans.
function inlineParams(sql: string, params: (string | number)[]): string {
    let i = 0;
    return sql
        .replace(/\?/g, () => {
            const value = params[i++];
            return typeof value === 'number' ? String(value) : `'${String(value).replace(/'/g, "''")}'`;
        })
        .replace(/\n\s+/g, '\n  ');
}

async function runPrepared(label: string, sql: string, params: (string | number)[]) {
    lastSql[label] = inlineParams(sql, params);
    const stmt = await conn.prepare(sql);
    // …unchanged…
}

Read the comment twice, because this function is the thing this course spent five lessons telling you not to write. The difference is total: the string it produces is never executed. The real query still binds its parameters; this is a copy for reading. Quote-doubling is there so the display is not misleading, not because it is a defence.

The cost is visible in the diff — five call sites gain a label. That is the honest price of an app that can explain itself, and it is small.

Reading a plan

EXPLAIN ANALYZE runs a statement and reports what actually happened per operator, rather than what the planner guessed:

async function explainQuery(sql: string): Promise<string> {
    try {
        const res = await conn.query(`EXPLAIN ANALYZE ${sql}`);
        const rows = res.toArray();
        return rows.map(r => String(r.explain_value ?? '')).join('\n');
    } catch (error) {
        return error instanceof Error ? error.message : String(error);
    }
}

Run it on the grouped query from lesson 7 and read from the bottom up: a scan producing twenty thousand rows, a hash aggregate reducing them to eight, timings on each. That bottom-up shape — leaves are where the data is, the root is your result — is how every query plan in every database reads.

Two more one-liners land here for the dashboard course’s maintenance controls: vacuum() and checkpoint(). With them the contract is complete.

The data layer is finished. db-worker.ts, db.ts, and config.ts are not edited again by either remaining course — the dashboard is built entirely on top of what exists at this tag.

Build it

File	Action	What goes in it
src/types.ts	modify	SqlRunResult; add getLastSql, runQuery, explainQuery, vacuum, checkpoint to DbWorkerApi
src/db-worker.ts	modify	lastSql, inlineParams, the label parameter on runPrepared and all five call sites, normalizeValue, CONSOLE_ROW_CAP, runQuery, explainQuery, vacuum, checkpoint
index.html	modify	A console panel — textarea, Run and EXPLAIN buttons, status line, results table, plan block — and a panel showing the SQL behind the dashboard queries
src/index.ts	modify	Run and explain handlers; render errors from result.error; print getLastSql()
src/style.css	modify	Textarea, error, and monospace block styles

Done when: SELEC * FROM sales renders DuckDB’s parser error with its caret while the tables above stay on screen, a valid query returns rows with a millisecond timing, and EXPLAIN ANALYZE prints a plan.

Answer key: engine-08...engine-09.

Challenge: profile the thing you built

Paste the daily aggregate from lesson 7 into the console and run EXPLAIN ANALYZE on it. Find the row counts at each operator and the time in the window function. Then run the same query with a date filter narrow enough to match a tenth of the data and compare. You are now measuring your own database, in your own browser, with no tooling you did not build in the last nine lessons.

You have a complete data layer and a plain interface for driving it. The dashboard course opens by deleting that interface — and keeps every line of this engine untouched.