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 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.
More recent articles
- Weeknotes: datasette-enrichments, datasette-comments, sqlite-chronicle - 8th December 2023
- Datasette Enrichments: a new plugin framework for augmenting your data - 1st December 2023
- llamafile is the new best way to run a LLM on your own computer - 29th November 2023
- Prompt injection explained, November 2023 edition - 27th November 2023
- I'm on the Newsroom Robots podcast, with thoughts on the OpenAI board - 25th November 2023
- Weeknotes: DevDay, GitHub Universe, OpenAI chaos - 22nd November 2023
- Deciphering clues in a news article to understand how it was reported - 22nd November 2023
- Exploring GPTs: ChatGPT in a trench coat? - 15th November 2023
- Financial sustainability for open source projects at GitHub Universe - 10th November 2023
- ospeak: a CLI tool for speaking text in the terminal via OpenAI - 7th November 2023