Weeknotes: django-sql-dashboard widgets
A few small releases this week, for
django-sql-dashboard widgets and permissions
django-sql-dashboard, my subset-of-Datasette-for-Django-and-PostgreSQL continues to come together.
New this week: widgets and permissions.
To recap: this Django app borrows some ideas from Datasette: it encourages you to create a read-only PostgreSQL user and grant authenticated users the ability to run one or more raw SQL queries directly against your database.
You can execute more than one SQL query and combine them into a saved dashboard, which will then show multiple tables containing the results.
This week I added support for dashboard widgets. You can construct SQL queries to return specific column patterns which will then be rendered on the page in different ways.
There are four widgets at the moment: “big number”, bar chart, HTML and Markdown.
Big number is the simplest: define a SQL query that returns two columns called
big_number and the dashboard will display that result as a big number:
select 'Entries' as label, count(*) as big_number from blog_entry;
Bar chart is more sophisticated: return columns named
bar_quantity to display a bar chart of the results:
select to_char(date_trunc('month', created), 'YYYY-MM') as bar_label, count(*) as bar_quantity from blog_entry group by bar_label order by count(*) desc
HTML and Markdown are simpler: they display the rendered HTML or Markdown, after filtering it through the Bleach library to strip any harmful elements or scripts.
select '## Ten most recent blogmarks (of ' || count(*) || ' total)' as markdown from blog_blogmark;
I’m running the dashboard application on this blog, and I’ve set up an example dashboard here that illustrates the different types of widget.
Defining custom widgets is easy: take the column names you would like to respond to, sort them alphabetically, join them with hyphens and create a custom widget in a template file with that name.
So if you wanted to build a widget that looks for
geojson columns and renders that data on a Leaflet map, you would create a
geojson-label.html template and drop it into your Django
templates/django-sql-dashboard/widgets folder. See the custom widgets documentation for details.
Which reminds me: I decided a README wasn’t quite enough space for documentation here, so I started a Read The Docs documentation site for the project.
django-sql-dashboard I’ve decided to try out Sphinx and Markdown instead, using MyST—a Markdown flavour and parser for Sphinx.
I picked this because I want to add inline help to
django-sql-dashboard, and since it ships with Markdown as a dependency already (to power the Markdown widget) my hope is that using Markdown for the documentation will allow me to ship some of the user-facing docs as part of the application itself. But it’s also a fun excuse to try out MyST, which so far is working exactly as advertised.
I’ve seen people in the past avoid Sphinx entirely because they preferred Markdown to reStructuredText, so MyST feels like an important addition to the Python documentation ecosystem.
HTTP Basic authentication
datasette-auth-passwords implements password-based authentication to Datasette. The plugin defaults to providing a username and password login form which sets a signed cookie identifying the current user.
Basic auth has some disadvantages—most notably that it doesn’t support logout without the user entirely closing down their browser. But it’s useful for a number of reasons:
- It’s easy to protect every resource on a website with it—including static assets. Adding
"http_basic_auth": trueto your plugin configuration adds this protection, covering all of Datasette’s resources.
- It’s much easier to authenticate with from automated scripts.
httpxall have simple built-in support for passing basic authentication usernames and passwords, which makes it a useful target for scripting—without having to install an additional authentication plugin such as datasette-auth-tokens.
I’m continuing to flesh out authentication options for Datasette, and adding this to
datasette-auth-passwords is one of those small improvements that should pay off long into the future.
A fix for datasette-publish-vercel
Datasette instances published to Vercel using the datasette-publish-vercel have previously been affected by an obscure Vercel bug: characters such as + in the query string were being lost due to Vercel unescaping encoded characters before the request got to the Python application server.
Vercel fixed this earlier this month, and the latest release of
datasette-publish-vercel includes their fix by switching to the new
@vercel/python builder. Thanks @styfle from Vercel for shepherding this fix through!
New photos on Niche Museums
My Niche Museums project has been in hiberation since the start of the pandemic. Now that vaccines are rolling out it feels like there might be an end to this thing, so I’ve started thinking about my museum hobby again.
Hopefully someday soon I’ll get to visit and add an entirely new museum!
Releases this week
django-sql-dashboard: 0.4a1—(10 releases total)—2021-03-21
Django app for building dashboards using raw SQL queries
datasette-publish-vercel: 0.9.2—(14 releases total)—2021-03-20
Datasette plugin for publishing data using Vercel
datasette-auth-passwords: 0.4—(9 releases total)—2021-03-19
Datasette plugin for authentication using passwords