taylor.town
about now spam rss

Podcast RSS XML
(in Ugly SQL)

I recently built a tool to deliver flashcards via podcast feeds.

Let me start by saying that I hate SQL. Its verbose, inconsistent syntax feels like bathing in strangers’ vomit.

Unfortunately, SQL is the best means to get reasonable performance from real-world datasets. Postgres is an unbelievably consistent platform, where I can test all my queries in a live environment and inspect the state of the DB without a separate debugger. Large classes of errors don’t exist, thanks to transaction-safety. As I write more SQL, I’m convinced that web servers should play second-fiddle whenever possible.

While working on flashcasts, I could run my ugly XML query against local or production data – no packages, no docker, no builds, no versioning, no deployments. Is this what the prolog folks have been trying to tell us all along?

Anyway, before I get into the SQL, wanted to note that I’m using Hono and Postgres.js on Deno, and overall happy with the experience:

app.all("/feed/:feed_id{[-\\w]+\\.xml$}", async c => {
  const feed_id = c.req.param("feed_id").replace(/\.xml$/, "");
  c.header("Content-Type", "application/xml");
  if (!["HEAD", "GET"].includes(c.req.method)) return c.body(null, 405);
  const [x] = await sql`
    // incoming SQL

Here’s the RSS header for the entire feed:

select xmlelement(name "rss",
  xmlattributes('2.0' AS "version", 'http://www.itunes.com/dtds/podcast-1.0.dtd' AS "xmlns:itunes"),
  xmlelement(name "channel",
    xmlelement(name "title", (select title from feed_)),
    xmlelement(name "link", 'https://flashcasts.com/feed/' || ${feed_id} || '.xml'),
    xmlelement(name "description", (select description from feed_)),
    xmlelement(name "language", 'en-us'),
    xmlelement(name "itunes:author", 'Flashcasts'),
    xmlelement(name "itunes:explicit", 'no'),
    xmlelement(name "itunes:category", xmlattributes('Education' AS "text"),
      xmlelement(name "itunes:category", xmlattributes('Self-Improvement' AS "text"))),
    xmlelement(name "itunes:image", xmlattributes((select image_url from feed_) as "href")),

Here’s how I build the episodes:

select
  xmlagg(
    xmlelement(name "item",
      xmlelement(name "title", (select ('Episode #' || n from feed_)),
      xmlelement(name "link", 'https://flashcasts.com/episode/' || e.episode_id || '.mp3'),
      xmlelement(name "guid", 'https://flashcasts.com/episode/' || e.episode_id),
      xmlelement(name "description", e.description::xml),
      xmlelement(name "pubDate", to_char(e.created_at, 'Dy, DD Mon YYYY HH24:MI:SS +0000')),
      xmlelement(name "itunes:duration", duration),
      xmlelement(name "itunes:episode", n),
      xmlelement(name "itunes:season", 1),
      xmlelement(name "itunes:explicit", 'no'),
      xmlelement(name "enclosure",
        xmlattributes(
          'https://flashcasts.com/episode/' || e.episode_id || '.mp3' AS "url",
          'audio/mpeg' AS "type", bytes as "length"))
    )
  )
from (
  select *, row_number() over (partition by e.feed_id order by e.created_at asc) as n
  from episode e
  cross join lateral (
    select
      sum(c.bytes_f + c.bytes_b) + (${SILENT_BYTES} * sum(f.pause::int + fd.pause::int)) as bytes
    , sum(duration_f + c.duration_b + (fd.pause::int + f.pause::int) * interval '1 second') as duration
    , ''
      || '<![CDATA['
      ||   '<p><a href="https://flashcasts.com/episode/' || e.episode_id || '">View this episode in your browser.</a></p>'
      ||   '<ul>'
      ||     string_agg('<li>' || c.body_f || '</li>', '')
      ||   '</ul>'
      ||   '<p>This episode was narrated by OpenAI''s Text-to-Speech API.</p>'
      || ']]>' as description
    from card c
    left join feed_deck fd on (fd.feed_id,fd.deck_id) = (e.feed_id,c.deck_id)
    left join feed f using (feed_id)
    where c.card_id = any(e.card_ids)
    group by e.feed_id, e.episode_id
  ) c
  where e.feed_id = ${feed_id}
) e

Here’s the whole thing, in all its copy/pastable glory. As a bonus, I left some frugly logic in there:

with feed_ as (select * from feed left join usr using (usr_id) where feed_id = ${feed_id})
select xmlelement(name "rss",
  xmlattributes('2.0' AS "version", 'http://www.itunes.com/dtds/podcast-1.0.dtd' AS "xmlns:itunes"),
  xmlelement(name "channel",
    xmlelement(name "title", (select (case when is_pro is false then '[FREE] ' else '' end) || title from feed_)),
    xmlelement(name "link", 'https://flashcasts.com/feed/' || ${feed_id} || '.xml'),
    xmlelement(name "description", (select (case when is_pro is false then '[FREE] ' else '' end) || description from feed_)),
    xmlelement(name "language", 'en-us'),
    xmlelement(name "itunes:author", 'Flashcasts'),
    xmlelement(name "itunes:explicit", 'no'),
    xmlelement(name "itunes:category", xmlattributes('Education' AS "text"),
      xmlelement(name "itunes:category", xmlattributes('Self-Improvement' AS "text"))),
    xmlelement(name "itunes:image", xmlattributes(
      (select case when is_pro is false then 'https://flashcasts.com/cover-free.jpg' else coalesce(image_url,'https://flashcasts.com/cover.jpg') end from feed_) as "href")),
    (
      select
        xmlagg(
          xmlelement(name "item",
            xmlelement(name "title", (select (case when is_pro is false then '[FREE] ' else '' end) || 'Episode #' || n from feed_)),
            xmlelement(name "link", 'https://flashcasts.com/episode/' || e.episode_id || '.mp3'),
            xmlelement(name "guid", 'https://flashcasts.com/episode/' || e.episode_id),
            xmlelement(name "description", e.description::xml),
            xmlelement(name "pubDate", to_char(e.created_at, 'Dy, DD Mon YYYY HH24:MI:SS +0000')),
            xmlelement(name "itunes:duration", duration),
            xmlelement(name "itunes:episode", n),
            xmlelement(name "itunes:season", 1),
            xmlelement(name "itunes:explicit", 'no'),
            xmlelement(name "enclosure", xmlattributes('https://flashcasts.com/episode/' || e.episode_id || '.mp3' AS "url", 'audio/mpeg' AS "type", bytes as "length"))
          )
        )
      from (
        select *, row_number() over (partition by e.feed_id order by e.created_at asc) as n
        from episode e
        cross join lateral (
          select
            sum(c.bytes_f + c.bytes_b) + (${SILENT_BYTES} * sum(f.pause::int + fd.pause::int)) as bytes
          , sum(duration_f + c.duration_b + (fd.pause::int + f.pause::int) * interval '1 second') as duration
          , ''
            || '<![CDATA['
            ||   '<p><a href="https://flashcasts.com/episode/' || e.episode_id || '">View this episode in your browser.</a></p>'
            ||   '<ul>'
            ||     string_agg('<li>' || c.body_f || '</li>', '')
            ||   '</ul>'
            ||   '<p>This episode was narrated by OpenAI''s Text-to-Speech API.</p>'
            || ']]>' as description
          from card c
          left join feed_deck fd on (fd.feed_id,fd.deck_id) = (e.feed_id,c.deck_id)
          left join feed f using (feed_id)
          where c.card_id = any(e.card_ids)
          group by e.feed_id, e.episode_id
        ) c
        where e.feed_id = ${feed_id}
      ) e
    )
  )
) AS feed