Simon Willison’s Weblog

VIAL is now live, plus django-sql-dashboard

15th March 2021

Originally posted to my internal blog at VaccinateCA

Our new Django backend has now officially graduated from preview mode! We’ve been running it to collect caller reports for Oregon for over a week now, and today we finally turned off the old Heroku app and promoted https://vial.calltheshots.us/ to be the place that our caller app writes to.

We also have https://vial-staging.calltheshots.us/ as a staging environment.

Calls made in California are still being logged directly to Airtable. The next big milestone for VIAL will be replacing Airtable for our California calls. That’s going to be my focus for this week.

django-sql-dashboard for reporting

This weekend I span up a new package which acts as a Django-based imitation of (and research playground for) my Datasette project.

django-sql-dashboard takes some of the key ideas from Datasette—the ability to use raw SQL queries in a safe, read-only, time limited environment to build bookmarkable interfaces—and turns it into a dashboard for applications written in Django and running against PostgreSQL.

It’s part of my ongoing goal to replace Airtable while avoiding the loss of the flexibility that has made Airtable so valuable to us.

If you are a staff user, you can access it at https://vial.calltheshots.us/dashboard/. The default interface lets you start running PostgreSQL SQL queries against a subset of our database tables:

It borrows a number of key features from Datasette. Firstly, queries can be bookmarked and shared. Any time you submit new queries you get back a URL with signed parameters, which you can share with other staff users. The page in the screenshot is at https://vial.calltheshots.us/dashboard/?sql=InNlbGVjdCBjb3VudCgqKSBmcm9tIGxvY2F0aW9uIg:1lM2mA:0uepYBAAxILOU_-jOrm12grM965gk83KazIAKUtgMmw&sql=InNlbGVjdCAqIGZyb20gbG9jYXRpb24i:1lM2mA:_tVz6msUDzgWYpVYZeZ7jgwo8YuoTLzTJCBploNucTY

Secondly, you can use named parameters in your queries—select * from location where state_id = (select id from state where name = %(state)s) for example—and the dashboard will extract those parameters out and turn them into form fields.

https://vial.calltheshots.us/dashboard/?sql=InNlbGVjdCAqIGZyb20gbG9jYXRpb24gd2hlcmUgc3RhdGVfaWQgPSAoc2VsZWN0IGlkIGZyb20gc3RhdGUgd2hlcmUgbmFtZSA9ICUoc3RhdGUpcyki%3A1lM2p5%3AwxWUfq1Vf6FkEPjCerWsEm7ljkyPEbhDVjsorHZ2bt4&state=Oregon

Unlike Datasette, it allows multiple SQL queries to be embedded in the same page. This means you can build quite complex dashboards, all sharing the same user-configurable parameter.

The most exciting feature though is custom widgets. If you write a SQL query that returns columns with specific names, custom widgets will kick in to render that data in formats other than a table.

This is best illustrated by the dashboard I built here: https://vial.calltheshots.us/dashboard/numbers-by-state/?state_name=California—this is an example of a “stored dashboard” which has been written to the database.

Note how the following SQL query is rendered as a “big number”:

select 'Number of locations in ' || %(state_name)s as label,
count(*) as big_number
from location where state_id = (select id from state where name = %(state_name)s)

And the bar chart at the bottom of the page is rendered automatically for this SQL query, because it returns columns called bar_quantity and bar_label:

select count(*) as bar_quantity,
to_char(created_at, 'YYYY-MM-DD') as bar_label
from reports
where reports.location_id in
(select id from location where state_id = (select id from state where name = %(state_name)s))
group by to_char(created_at, 'YYYY-MM-DD')

You can even create SQL statements that return dynamically concatenated markdown or HTML (run through Bleach to avoid any nasty XSS problems).

select '# Chart: number of reports filed per day in ' || %(state_name)s as markdown

This ability to construct dashboards by saving a list of SQL queries feels really powerful to me—it takes some of the best aspects of Datasette (which currently only works against SQLite databases) and makes them available to us within our Django/PostgreSQL app, protected by the Django authentication mechanism.

You can follow ongoing developement of django-sql-dashboard in the issues at https://github.com/simonw/django-sql-dashboard/issues