Simon Willison’s Weblog

Weeknotes: datasette-template-sql

18th November 2019

Last week I talked about wanting to take ona a larger Datasette project, and listed some candidates. I ended up pushing a big project that I hadn’t listed there: the upgrade of Datasette to Python 3.8, which meant dropping support for Python 3.5 (thanks to incompatible dependencies).

Since Glitch now runs Python 3.7.5 my biggest reason for supporting 3.5 was gone, so I decided to make the upgrade.

Datasette 0.31 was the first version to drop support. Datasette 0.32 is the first to take advantage of it: I switched Datasette’s template rendering over to use Jinja’s async template support, which requires Python 3.6+.

This has exciting implications for the extra_template_vars plugin hook, which allows plugins to add extra variables (and functions) to the template scope.

Plugin authors can now add custom template functions that are defined with async def ... and make await calls within the body of the function. When the template is rendered, Jinja will automatically await those function calls.

I released a new plugin that takes advantage of this capability: datasette-template-sql. It lets you embed additional SQL queries directly in a custom Datasette template. For example:

{% for article in sql(
    "select headline, date, summary from articles order by date desc limit 5",
    "news"
) %}
    <h3>{{ article.headline }}</h2>
    <p class="date">{{ article.date }}</p>
    <p>{{ article.summary }}</p>
{% endfor %}

This new sql() function takes a SQL query and the optional name of the database to run the query against (in case you have more than one database file attached to your Datasette instance).

I’m really excited about this capability. I quipped about it on Twitter:

If you’re thinking “aren’t you just reinventing PHP at this point?” I’m actually more going for ColdFusion

- Simon Willison (@simonw) November 15, 2019

This is the great thing about having a plugin system: even if I’m not convinced this is the right technical direction for the core Datasette project, I can still expose this kind of feature in a plugin that people can opt into if they want to have this ability.

The official Datasette website is going to make extensive use of this plugin. I have an early prototype of that up and running now, which inspired me to release datasette-render-markdown 0.2 with a custom template function for rendering markdown directly:

{{ render_markdown(value) }}

Aside from the work on Python 3, my Datasette time this week has mostly involved ongoing refactors of both the query execution code and the core TableView.data() method. Hopefully these will unblock a flurry of interesting new functionality in the not too distant future.

Niche museums

This week’s new museums on www.niche-museums.com.

• California State Capitol Dioramas in Sacramento
• Zeppelin Museum in Friedrsichshafen
• Dai Loy Museum in Locke (Sacramento Delta)
• Wallace Collection in London
• Cookin’ in San Francisco
• Ramen Museum in Yokohama
• Cable Car Museum in San Francisco