Datasette plugin for executing SQL queries from templates.

datasette.io uses this plugin extensively with custom page templates, check out simonw/datasette.io to see how it works.

www.niche-museums.com uses this plugin to run a custom themed website on top of Datasette. The full source code for the site is here - see also niche-museums.com, powered by Datasette.

simonw/til is another simple example, described in Using a self-rewriting README powered by GitHub Actions to track TILs.

Run this command to install the plugin in the same environment as Datasette:

$ pip install datasette-template-sql

This plugin makes a new function, sql(sql_query), available to your Datasette templates.

You can use it like this:

{% for row in sql("select 1 + 1 as two, 2 * 4 as eight") %}
    {% for key in row.keys() %}
        {{ key }}: {{ row[key] }}<br>
    {% endfor %}
{% endfor %}

The plugin will execute SQL against the current database for the page in database.html, table.html and row.html templates. If a template does not have a current database (index.html for example) the query will execute against the first attached database.

You can construct a SQL query using ? or :name parameter syntax by passing a list or dictionary as a second argument:

{% for row in sql("select distinct topic from til order by topic") %}
    <h2>{{ row.topic }}</h2>
    <ul>
        {% for til in sql("select * from til where topic = ?", [row.topic]) %}
            <li><a href="{{ til.url }}">{{ til.title }}</a> - {{ til.created[:10] }}</li>
        {% endfor %}
    </ul>
{% endfor %}

Here's the same example using the :topic style of parameters:

{% for row in sql("select distinct topic from til order by topic") %}
    <h2>{{ row.topic }}</h2>
    <ul>
        {% for til in sql("select * from til where topic = :topic", {"topic": row.topic}) %}
            <li><a href="{{ til.url }}">{{ til.title }}</a> - {{ til.created[:10] }}</li>
        {% endfor %}
    </ul>
{% endfor %}

You can pass an optional database= argument to specify a named database to use for the query. For example, if you have attached a news.db database you could use this:

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