Simon Willison’s Weblog

APIs from CSS without JavaScript: the datasette-css-properties plugin

7th January 2021

I built a new Datasette plugin called datasette-css-properties. It’s very, very weird—it adds a .css output extension to Datasette which outputs the result of a SQL query using CSS custom property format. This means you can display the results of database queries using pure CSS and HTML, no JavaScript required!

I was inspired by Custom Properties as State, published by by Chris Coyier earlier this week. Chris points out that since CSS custom properties can be defined by an external stylesheet, a crafty API could generate a stylesheet with dynamic properties that could then be displayed on an otherwise static page.

This is a weird idea. Datasette’s plugins system is pretty much designed for weird ideas—my favourite thing about having plugins is that I can try out things like this without any risk of damaging the integrity of the core project.

So I built it! Here are some examples:

roadside_attractions is a table that ships as part of Datasette’s “fixtures” test database, which I write unit tests against and use for quick demos.

The URL of that table within Datasette is /fixtures/roadside_attractions. To get the first row in the table back as CSS properties, simply add a .css extension:

/fixtures/roadside_attractions.css returns this:

:root {
  --pk: '1';
  --name: 'The Mystery Spot';
  --address: '465 Mystery Spot Road, Santa Cruz, CA 95065';
  --latitude: '37.0167';
  --longitude: '-122.0024';
}

You can make use of these properties in an HTML document like so:

<link rel="stylesheet" href="https://latest-with-plugins.datasette.io/fixtures/roadside_attractions.css">
<style>
.attraction-name:after { content: var(--name); }
.attraction-address:after { content: var(--address); }
</style>
<p class="attraction-name">Attraction name: </p>
<p class="attraction-address">Address: </p>

Here that is on CodePen. It outputs this:

Attraction name: The Mystery Spot

Address: 465 Mystery Spot Road, Santa Cruz, CA 95065

Apparently modern screen readers will read these values, so they’re at least somewhat accessible. Sadly users won’t be able to copy and paste their values.

Let’s try something more fun: a stylesheet that changes colour based on the time of the day.

I’m in San Francisco, which is currently 8 hours off UTC. So this SQL query gives me the current hour of the day in my timezone:

SELECT strftime('%H', 'now') - 8

I’m going to define the following sequence of colours:

• Midnight to 4am: black
• 4am to 8am: grey
• 8am to 4pm: yellow
• 4pm to 6pm: orange
• 6pm to midnight: black again

Here’s a SQL query for that, using the CASE expression:

SELECT
  CASE
    WHEN strftime('%H', 'now') - 8 BETWEEN 4
    AND 7 THEN 'grey'
    WHEN strftime('%H', 'now') - 8 BETWEEN 8
    AND 15 THEN 'yellow'
    WHEN strftime('%H', 'now') - 8 BETWEEN 16
    AND 18 THEN 'orange'
    ELSE 'black'
  END as [time-of-day-color]

Execute that here, then add the .css extension and you get this:

:root {
  --time-of-day-color: 'yellow';
}

This isn’t quite right. The yellow value is wrapped in single quotes—but that means it won’t work as a colour if used like this:

<style>
nav {
  background-color: var(--time-of-day-color);
}
</style>
<nav>This is the navigation</nav>

To fix this, datasette-css-properties supports a ?_raw= querystring argument for specifying that a specific named column should not be quoted, but should be returned as the exact value that came out of the database.

So we add ?_raw=time-of-day-color to the URL to get this:

:root {
  --time-of-day-color: yellow;
}

(I’m a little nervous about the _raw= feature. It feels like it could be a security hole, potentially as an XSS vector. I have an open issue about that and I’d love to get some feedback—I’m serving the page with the X-Content-Type-Options: nosniff HTTP header which I think should keep things secure but I’m worried there may be attack patterns that I don’t know about.)

Let’s take a moment to admire the full HTML document for this demo:

<link rel="stylesheet" href="https://latest-with-plugins.datasette.io/fixtures.css?sql=SELECT%0D%0A++CASE%0D%0A++++WHEN+strftime(%27%25H%27,+%27now%27)+-+8+BETWEEN+4%0D%0A++++AND+7+THEN+%27grey%27%0D%0A++++WHEN+strftime(%27%25H%27,+%27now%27)+-+8+BETWEEN+8%0D%0A++++AND+15+THEN+%27yellow%27%0D%0A++++WHEN+strftime(%27%25H%27,+%27now%27)+-+8+BETWEEN+16%0D%0A++++AND+18+THEN+%27orange%27%0D%0A++++ELSE+%27black%27%0D%0A++END+as+%5Btime-of-day-color%5D&_raw=time-of-day-color">
<style>
nav {
  background-color: var(--time-of-day-color);
}
</style>
<nav>This is the navigation</nav>

That’s a SQL query URL-encoded into the querystring for a stylesheet, loaded in a <link> element and used to style an element on a page. It’s calling and reacting to an API with not a line of JavaScript required!

Is this plugin useful for anyone? Probably not, but it’s a really fun idea, and it’s a great illustration of how having plugins dramatically reduces the friction against trying things like this out.