Query this Blog with DuckDB

Written 2026-06-25

I wanted to have some fun with DuckDB, and for some reason, I thought it would be fun to query my blog using it. So, I added two endpoints for querying the current posts and resource pages as CSV files. This is already enough for DuckDB, because it has CSV parsing and an HTTP client built-in:

Note: I used .mode html in the DuckDB CLI to give me a headstart on formatting the query results.

SELECT title, date, link
FROM 'https://australorp.dev/blog.csv'
ORDER BY date DESC

I love how easy DuckDB makes it to query a CSV. Let’s do one more for fun. Let’s find the largest pages by the length of their source Markdown files.

WITH pages AS (
  SELECT title, markdown
  FROM 'https://australorp.dev/resources.csv'
  UNION
  SELECT title, markdown
  FROM 'https://australorp.dev/blog.csv'
)
SELECT title, length(markdown) as length
FROM pages
ORDER BY length(markdown) DESC

Nice. The length of the Markdown source is not a great way to estimate the length or reading time of the page, but it’s not the worst way either.

Serving the pages as CSV files almost feels like a poor man’s RSS. Since I am using Astro, it was very easy to add. All I had to do was make a new static file endpoint that queries my blog collection. I wrote the CSV escaping and formatting myself because I hate adding new dependencies if I don’t have to.

import { getCollection } from 'astro:content'

export async function GET(_) {
  const posts = await getCollection('blog')
  const data = csv(posts.map(x => [
    x.id,
    `https://australorp.dev/blog/${x.id}`,
    x.data.title,
    x.data.date,
    x.data.tags.join(','),
    x.body,
  ]))
  return new Response(data)
}

function csv(rows) {
  const data = rows
    .map(row => 
      row.map(field => formatField(field)).join(',')
    )
  data.unshift(['id','link','title','date','tags','markdown'])
  return data.join('\n')
}

function formatField(x) {
  return `"${escape(x)}"`
}

function escape(x) {
  return x.replaceAll('"', '""')
}

That’s all I have for now. Go download DuckDB and write some queries against my blog.