Simon Willison’s Weblog

Django SQL Dashboard 1.0

6th July 2021

Earlier this week I released Django SQL Dashboard 1.0. I also just released 1.0.1, with a bug fix for PostgreSQL 10 contributed by Ryan Cheley.

Django SQL Dashboard is my tool for adding a read-only, bookmarkable SQL querying interface to any Django plus PostgreSQL project. I introduced it with a demo video back in May.

Django SQL Dashboard column menu

The big new feature in 1.0 (aside from the confident version number) is a new contextual menu against each column from the results. This is best demonstrated by an animated demo:

The new cog action menu is inspired by Datasette (and reuses the same icon). It works a little differently though: since Django SQL Dashboard only ever deals with SQL queries (unlike Datasette which often shows results directly from a filtered table) the options in the cog menu work by rewriting the original SQL.

This turns out to be pretty easy, because PostgreSQL (and every other modern SQL implementation) supports nested queries. So if your original SQL query is:

select * from entries
where category = 'python'

You can sort by a column like this:

select * from (
  select * from entries
  where category = 'python'
) as results
order by "title"

And you can count unique values like this:

select "title", count(*) as n from (
  select * from entries
  where category = 'python'
) as results
group by "title" order by n desc

My initial implementation of this feature did have one flaw: if you sorted by a column, then sorted by another column, it would nest the queries multiple times—so you could end up with something like this:

select * from (
  select * from (
    select * from blog_blogmark
  ) as results order by "link_title"
) as results order by "link_url" desc

I’m confident the query optimizer reduces this to the minimum amount of work, but it doesn’t look very pretty. I realized that since I generate most of the SQL I could catch this using a pretty dumb regular expression:

_sort_re = re.compile(
    '(^.*) order by "[^"]+"( desc)?$', re.DOTALL
)

If that regular expression matches, I can extract the first group and append a new order by to it. If it doesn’t match I can fall back to wrapping the entire query.

Testing against multiple PostgreSQL versions

The 1.0.1 release fixed a bug that only showed up in PostgreSQL 10, spotted (and fixed) by Ryan Cheley.

Django SQL Dashboard runs its tests against a temporary PostgreSQL server, which is spun up using the testing.postgresql library.

The default GitHub Actions runner ships with a PostgreSQL 13 server binary just waiting for you to run it. But how can I also run the tests against previous versions?

I ended up following these instructions provided by PostgreSQL on installing different versions on Ubuntu. I combined these into a GitHub Actions matrix build, so now every commit to Django SQL Dashboard is tested sixteen times(!), against every combination of Python 3.6, 3.7, 3.8 and 3.9 and PostgreSQL 10, 11, 12 and 13. I wrote up the pattern I used in this TIL.