Tutorial

A log of bird sightings, and the app to copy when writing your own. Three routes, one table, no extension of its own: everything here is SQL that ends up inside birdlog.duckdb.

nix run .#demo

That serves it on http://127.0.0.1:8080. The rest of this page builds it from an empty file.

An empty file

An app is a .sql file. Running it against a DuckDB database leaves behind the tables it created and the macros it defined; that database, checkpointed, is the app. birdlog/build.sh does exactly that and nothing else.

The one macro that has to be there is render:

CREATE MACRO render(method, route, headers, body) AS TABLE (
  SELECT 'HTTP/1.1 204 No Content' || chr(13) || chr(10) || chr(13) || chr(10) AS chunk
);

One VARCHAR column, and the rows concatenated are the response. Nothing else is required — but writing HTTP framing by hand gets old immediately, so duckweb supplies it.

The first route

CREATE MACRO render(method, route, headers, body) AS TABLE (
            SELECT duckweb_head(200, 'text/html', '') AS chunk
            FROM duckweb_match(route, '/')
  UNION ALL SELECT duckweb_chunk('<h1>birdlog</h1>')
            FROM duckweb_match(route, '/')
  UNION ALL SELECT duckweb_end()
            FROM duckweb_match(route, '/')
);

duckweb_head writes the status line, the content type and Transfer-Encoding: chunked; duckweb_chunk frames one chunk of body; duckweb_end writes the terminator. Emit them in that order and never sort: render carries no ORDER BY because sorting is blocking, and a blocked query cannot stream.

Gate every branch

duckweb_match(route, '/') is a table function returning one row when the route matches and no rows when it does not. Every branch above joins against it, so a request for something else produces nothing from any of them.

The temptation is WHERE route = '/', and it is a bug. An ungrouped aggregate returns its row even when its filter matched nothing, so one route’s output leaks into every other route. Gate relationally, always. A branch that does not match then costs nothing at all — DuckDB prunes it.

The same trap has a second form. Keep aggregates out of the top level of a branch, or the branch’s row count stops coming from duckweb_match:

-- leaks
SELECT count(*) FROM duckweb_match(route, '/'), sightings
-- correct
SELECT duckweb_chunk((SELECT count(*)::VARCHAR FROM sightings))
FROM duckweb_match(route, '/')

Rows, on the other hand, are exactly what you want for the body. Join the gate to the table and every row becomes a chunk on the wire:

  UNION ALL SELECT duckweb_chunk('<tr><td>' || duckweb_escape(s.location) || '</td></tr>')
            FROM duckweb_match(route, '/'), sightings s

duckweb_escape on anything that came from data or from the request. It is not optional, and it is the shortest thing in the file.

Everything else the app answers

Two branches close the surface. A route that exists reached with a method it does not take is a 405; nothing matching at all is a 404:

CREATE MACRO hit(route) AS TABLE (
            SELECT 1 FROM duckweb_match(route, '/')
  UNION ALL SELECT 1 FROM duckweb_match(route, '/species/:name')
  UNION ALL SELECT 1 FROM duckweb_match(route, '/style.css')
);
  UNION ALL SELECT duckweb_head(405, 'text/html', duckweb_header('Allow', 'GET'))
            FROM hit(route) WHERE method <> 'GET'
  UNION ALL SELECT duckweb_head(404, 'text/html', '')
            WHERE NOT EXISTS (SELECT 1 FROM hit(route))

hit is where the app’s routing table lives, and it is a list of literals because it has to be: duckweb_match reads its pattern at bind time, so patterns cannot come out of a table.

Reading a query parameter

The route arrives with its query string attached, and duckweb_query pulls a parameter out of it. A GET form needs no more than that:

  UNION ALL SELECT duckweb_chunk('<form method="get" action="/">'
                     || '<input name="species" value="'
                     || duckweb_escape(duckweb_query(route, 'species')) || '">'
                     || '<button>filter</button></form>')
            FROM duckweb_match(route, '/') WHERE method = 'GET'

and the listing filters on the same call:

WHERE i.species ILIKE '%' || duckweb_query(route, 'species') || '%'

An absent parameter reads as the empty string rather than NULL, which is why that pattern matches everything on a first visit and needs no COALESCE.

A path parameter works the other way round: duckweb_match(route, '/species/:name') returns :name as a column of the row it yields, so the branch reads m.name from its own gate.

A chart, in a slot

svg_charts draws a chart as a column of a query result — a complete <svg> string, no JavaScript and no requests. Drawing takes long enough to be worth not waiting for, so the species page emits a placeholder where the chart goes, carries on with the rest of the page, and sends the chart last:

  UNION ALL SELECT duckweb_chunk('<figure>' || duckweb_slot('chart', 'drawing…') || '</figure>'
                     || '<table>…')
            FROM duckweb_match(route, '/species/:name') m WHERE method = 'GET'
  -- …the table's rows…
  UNION ALL SELECT duckweb_chunk(duckweb_slot_fill('chart',
                     (SELECT svg_chart(list({'day': d.day, 'seen': d.seen} ORDER BY d.day),
                                       {'type': 'bar', 'aspect_ratio': 2.5})
                      FROM (…) d)))
            FROM duckweb_match(route, '/species/:name') m WHERE method = 'GET'

duckweb_slot_fill carries a small self-contained script that moves the content into place, so a browser reading the response straight off the socket needs nothing loaded to make it work. The script runs on every flush, so it is idempotent by construction.

Two things about the chart itself. Points are plotted in the order they arrive, and a parallel scan delivers them in whatever order it likes — hence list(… ORDER BY …) rather than a bare aggregate. And a chart with no background_color is transparent and takes currentColor for its text, so it follows the page into dark mode without being told.

The app loads nothing

app.sql has no LOAD in it. The extensions are the server’s business, and that includes svg_charts: leave it out and every route fails, not just the one with the chart, because a macro is bound as a single query and one missing name takes all of it down.

Catalog Error: Scalar Function with name svg_chart does not exist!

The same rule covers tables. Anything render reads has to exist before the first request arrives, on every route.

Serving it

The stylesheet is a route like any other — duckweb_serve takes an asset directory, but a single small CSS file is one less thing to deploy:

  UNION ALL SELECT duckweb_head(200, 'text/css', '')
            FROM duckweb_match(route, '/style.css') WHERE method = 'GET'

Then build the database and serve it:

nix build .#birdlog-db
nix run .#duckdb
ATTACH 'result/birdlog.duckdb' AS app (READ_ONLY);
SELECT duckweb_serve('127.0.0.1', 8080, '.', 'app.main');

nix run .#duckdb already has duckweb and svg_charts loaded. duckweb_serve returns as soon as the listener is up — what keeps the server alive is the REPL it returned to, so .quit stops it. It binds loopback and has no authentication; any other interface is something you have to write out.

Next