Simon Willison's Weblog: django-sql-dashboard

New dashboard: alt text for all my images

2025-04-28T01:22:27+00:00

New dashboard: alt text for all my images

I got curious today about how I'd been using alt text for images on my blog, and realized that since I have Django SQL Dashboard running on this site and PostgreSQL is capable of parsing HTML with regular expressions I could probably find out using a SQL query.

I pasted my PostgreSQL schema into Claude and gave it a pretty long prompt:

Give this PostgreSQL schema I want a query that returns all of my images and their alt text. Images are sometimes stored as HTML image tags and other times stored in markdown.

blog_quotation.quotation, blog_note.body both contain markdown. blog_blogmark.commentary has markdown if use_markdown is true or HTML otherwise. blog_entry.body is always HTML

Write me a SQL query to extract all of my images and their alt tags using regular expressions. In HTML documents it should look for either <img .* src="..." .* alt="..." or <img alt="..." .* src="..." (images may be self-closing XHTML style in some places). In Markdown they will always be ![alt text](url)

I want the resulting table to have three columns: URL, alt_text, src - the URL column needs to be constructed as e.g. /2025/Feb/2/slug for a record where created is on 2nd feb 2025 and the slug column contains slug

Use CTEs and unions where appropriate

It almost got it right on the first go, and with a couple of follow-up prompts I had the query I wanted. I also added the option to search my alt text / image URLs, which has already helped me hunt down and fix a few old images on expired domain names. Here's a copy of the finished 100 line SQL query.

Tags: accessibility, alt-text, postgresql, sql, ai, django-sql-dashboard, generative-ai, llms, ai-assisted-programming, claude

Dashboard: Tools

2024-10-21T03:33:41+00:00

Dashboard: Tools

I used Django SQL Dashboard to spin up a dashboard that shows all of the URLs to my tools.simonwillison.net site that I've shared on my blog so far. It uses this (Claude assisted) regular expression in a PostgreSQL SQL query:

select distinct on (tool_url)
    unnest(regexp_matches(
        body,
        '(https://tools\.simonwillison\.net/[^<"\s)]+)',
        'g'
    )) as tool_url,
    'https://simonwillison.net/' || left(type, 1) || '/' || id as blog_url,
    title,
    date(created) as created
from content

I've been really enjoying having a static hosting platform (it's GitHub Pages serving my simonw/tools repo) that I can use to quickly deploy little HTML+JavaScript interactive tools and demos.

Tags: javascript, postgresql, projects, sql, tools, django-sql-dashboard, ai-assisted-programming

Blogmarks that use markdown

2024-04-25T04:34:18+00:00

Blogmarks that use markdown

I needed to attach a correction to an older blogmark (my 20-year old name for short-form links with commentary on my blog) today - but the commentary field has always been text, not HTML, so I didn't have a way to add the necessary link.

This motivated me to finally add optional Markdown support for blogmarks to my blog's custom Django CMS. I then went through and added inline code markup to a bunch of different older posts, and built this Django SQL Dashboard to keep track of which posts I had updated.

Tags: blogging, projects, markdown, django-sql-dashboard, site-upgrades

My blog's year archive pages now have tag clouds

2024-01-04T21:02:51+00:00

My blog's year archive pages now have tag clouds

Inspired by the tag cloud I used in my recent 2023 AI roundup post, I decided to add a tag cloud to the top of every one of my archive-by-year pages showing what topics I had spent the most time with that year.

I already had old code for this, so I pasted it into GPT-4 along with an example of the output of my JSON endpoint from Django SQL Dashboard and had it do most of the work for me.

Via simonwillisonblog/issues/391

Tags: projects, ai, django-sql-dashboard, chatgpt, llms

Everything new in Datasette since January, plus Django SQL Dashboard

2021-08-10T01:28:43+00:00

Everything new in Datasette since January, plus Django SQL Dashboard

I sent out the first Datasette newsletter since late January this year, covering everything that’s new in Datasette and sqlite-utils this year and introducing Django SQL Dashboard.

Tags: datasette, sqlite-utils, django-sql-dashboard

Django SQL Dashboard 1.0

2021-07-06T02:19:57+00:00

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.

Tags: django, postgresql, projects, sql, datasette, weeknotes, github-actions, django-sql-dashboard

PAGNIs: Probably Are Gonna Need Its

2021-07-01T19:13:58+00:00

Luke Page has a great post up with his list of YAGNI exceptions.

YAGNI - You Ain't Gonna Need It - is a rule that says you shouldn't add a feature just because it might be useful in the future - only write code when it solves a direct problem.

When should you over-ride YAGNI? When the cost of adding something later is so dramatically expensive compared with the cost of adding it early on that it's worth taking the risk. On when you know from experience that an initial investment will pay off many times over.

Lukes's exceptions to YAGNI are well chosen: things like logging, API versioning, created_at timestamps and a bias towards "store multiple X for a user" (a many-to-many relationship) if there's any inkling that the system may need to support more than one.

Because I like attempting to coin phrases, I propose we call these PAGNIs - short for Probably Are Gonna Need Its.

Here are some of mine.

A kill-switch for your mobile apps

If you're building a mobile app that talks to your API, make sure to ship a kill-switch: a mechanism by which you can cause older versions of the application to show a "you must upgrade to continue using this application" screen when the app starts up.

In an ideal world, you'll never use this ability: you'll continue to build new features to the app and make backwards-compatible changes to the API forever, such that ancient app versions keep working and new app versions get to do new things.

But... sometimes that simply isn't possible. You might discover a security hole in the design of the application or API that can only be fixed by breaking backwards-compatibility - or maybe you're still maintaining a v1 API from five years ago to support a mobile application version that's only still installed by 30 users, and you'd like to not have to maintain double the amount of API code.

You can't add a kill-switch retroactively to apps that have already been deployed!

Apparently Firebase offers this to many Android apps, but if you're writing for iOS you need to provide this yourself.

Automated deploys

Nothing kills a side project like coming back to it in six months time and having to figure out how to deploy it again. Thanks to GitHub Actions and hosting providers like Google Cloud Run, Vercel, Heroku and Netlify setting up automated deployments is way easier now than it used to be. I have enough examples now that getting automated deployments working for a new project usually only takes a few minutes, and it pays off instantly.

Continuous Integration (and a test framework)

Similar to automated deployment in that GitHub Actions (and Circle CI and Travis before it) make this much less painful to setup than it used to be.

Introducing a test framework to an existing project can be extremely painful. Introducing it at the very start is easy - and it sets a precedent that code should be tested from day one.

These days I'm all about pytest, and I have various cookiecutter templates (datasette-plugin, click-app, python-lib) that configure it on my new projects (with a passing test) out of the box.

(Honestly, at this point in my career I consider continuous integration a DAGNI - Definitely Are Gonna Need It.)

One particularly worthwhile trick is making sure the tests can spin up their own isolated test databases - another thing which is pretty easy to setup early (Django does this for you) and harder to add later on. I extend that to other external data stores - I once put a significant amount of effort into setting up a mechanism for running tests against Elasticsearch and clearing out the data again afterwards, and it paid off multiple times over.

Even better: continuous deployment! When the tests pass, deploy. If you have automated deployment setup already adding this is pretty easy, and doing it from the very start of a project sets a strong cultural expectation that no-one will land code to the main branch until it's in a production-ready state and covered by unit tests.

(If continuous deployment to production is too scary for your project, a valuable middle-ground is continuous deployment to a staging environment. Having everyone on your team able to interact with a live demo of your current main branch is a huge group productivity boost.)

API pagination

Never build an API endpoint that isn't paginated. Any time you think "there will never be enough items in this list for it to be worth pagination" one of your users will prove you wrong.

This can be as simple as shipping an API which, even though it only returns a single page, has hard-coded JSON that looks like this:

{
  "results": [
    {"id": 1, "name": "One"},
    {"id": 2, "name": "Two"},
    {"id": 3, "name": "Three"}
  ],
  "next_url": null
}

But make sure you leave space for the pagination information! You'll regret it if you don't.

Detailed API logs

This is a trick I learned while porting VaccinateCA to Django. If you are building an API, having a mechanism that provides detailed logs - including the POST bodies passed to the API - is invaluable.

It's an inexpensive way of maintaining a complete record of what happened with your application - invaluable for debugging, but also for tricks like replaying past API traffic against a new implementation under test.

Logs like these may become infeasible at scale, but for a new project they'll probably add up to just a few MBs a day - and they're easy to prune or switch off later on if you need to.

VIAL uses a Django view decorator to log these directly to a PostgreSQL table. We've been running this for a few months and it's now our largest table, but it's still only around 2GB - easily worth it for the productivity boost it gives us.

(Don't log any sensitive data that you wouldn't want your development team having access to while debugging a problem. This may require clever redaction, or you can avoid logging specific endpoints entirely. Also: don't log authentication tokens that could be used to imitate users: decode them and log the user identifier instead.)

A bookmarkable interface for executing read-only SQL queries against your database

This one is very much exposing my biases (I just released Django SQL Dashboard 1.0 which provides exactly this for Django+PosgreSQL projects) but having used this for the past few months I can't see myself going back. Using bookmarked SQL queries to inform the implementation of new features is an incredible productivity boost. Here's an issue I worked on recently with 18 comments linking to illustrative SQL queries.

(On further thought: this isn't actually a great example of a PAGNI because it's not particularly hard to add this to a project at a later date.)

Driving down the cost

One trick with all of these things is that while they may seem quite expensive to implement, they get dramatically cheaper as you gain experience and gather more tools for helping put them into practice.

Any of the ideas I've shown here could take an engineering team weeks (if not months) to add to an existing project - but with the right tooling they can represent just an hour (or less) work at the start of a project. And they'll pay themselves off many, many times over in the future.

Tags: continuous-deployment, continuous-integration, definitions, software-engineering, testing, pytest, github-actions, django-sql-dashboard, yagni, pagni

Django SQL Dashboard 1.0

2021-07-01T17:44:38+00:00

Django SQL Dashboard 1.0

As part of my ongoing attempt to be braver about 1.0 releases (crucial if you want to do semantic versioning properly) I’ve released version 1.0 of Django SQL Dashboard, my Datasette-inspired app for Django that adds an interface for running read-only, bookmarkable SQL queries against a PostgreSQL database. The new version adds a column cog menu providing shortcuts for changing the sort order, counting distinct values and performing a group-by/count against column values.

Via @simonw

Tags: django, projects, sql, django-sql-dashboard

Weeknotes: Velma, more Django SQL Dashboard

2021-05-18T00:36:20+00:00

Matching locations for Vaccinate The States, fun with GeoJSON and more improvements to Django SQL Dashboard.

Velma

I described a few weeks ago part of the process we've been using to build Vaccinate The States - a map of every COVID vaccine location in the USA (now at just over 70,000 markers and counting).

Short version: we have scrapers and data ingesters for a whole bunch of different sources (see the vaccine-feed-ingest repository).

Part of the challenge here is how to deal with duplicates - with multiple sources of data, chances are high that the same location will show up in more than on of our input feeds.

So in the past weeks we've been building a new tool code-named Velma to help handle this. It shows our volunteers a freshly scraped location and asks them to either match it to one of our existing locations (based on automated suggestions) or use it to create a brand new location in our database.

I've been working exclusively on the backend APIs for Velma: APIs that return new scraped data and accept and process the human matching decisions from our volunteers.

This week we've been expanding Velma to also cover merging potential duplicate locations within our existing corpus, so I've been building out the APIs for that effort as well.

I've also been working on new export code for making our entire set of locations available to partners and interested outside developers. We hope to launch that fully in the next few days.

geojson-to-sqlite

One of the export formats we are working with is GeoJSON. I have a tool called geojson-to-sqlite which I released last year: this week I released an updated version with the ability to create SpatiaLite indexes and a --nl option for consuming newline-delimited GeoJSON, contributed by Chris Amico.

I've also been experimenting with SpatiaLite's KNN mechanism using geojson-to-sqlite to load in data - here's a TIL showing how to use those tools together.

Django SQL Dashboard

I released the first non-alpha version of this last week and it's started to gain some traction: I've heard from a few people who are trying it out on their projects and it seems to work, so that's good!

I released version 0.14 yesterday with a bunch of fixes based on feedback from users, plus a security fix that closes a hole where users without the execute_sql permission but with access to the Django Admin could modify the SQL in saved dashboards and hence execute their own custom queries.

I also made a bunch of improvements to the documentation, including adding screenshots and demo links to the widgets page.

TIL this week

Releases this week

geojson-to-sqlite: 0.3 - (6 releases total) - 2021-05-17
CLI tool for converting GeoJSON files to SQLite (with SpatiaLite)
django-sql-dashboard: 0.14 - (28 releases total) - 2021-05-16
Django app for building dashboards using raw SQL queries

Tags: projects, geojson, weeknotes, vaccinate-ca, django-sql-dashboard

Django SQL Dashboard

2021-05-10T15:39:23+00:00

I've released the first non-alpha version of Django SQL Dashboard, which provides an interface for running arbitrary read-only SQL queries directly against a PostgreSQL database, protected by the Django authentication scheme. It can also be used to create saved dashboards that can be published or shared internally.

I started building this tool back in March as part of my work to port VaccinateCA away from Airtable to a custom Django backend. One of the strengths of Airtable is that it allows ad-hoc data exploration and reporting, and I wanted to provide an alternative to that for the new Django backend.

I also wanted to try out some new ideas for Datasette, which doesn't (yet) work with PostgreSQL.

First, a demo

I recorded this three minute video demo introducing the software, using my blog's database as an example.

In the video I run the following SQL queries to explore the many-to-many table that maps tags to my blog entries:

select * from blog_entry_tags;

The table starts out looking like this - not particularly interesting:

Then I run this query to join it against the blog_tag table and get the details of each tag:

select * from blog_entry_tags
join blog_tag on blog_tag.id = blog_entry_tags.tag_id

This is a bit more useful. I then click on the "count" link at the top of that "tag" column. This constructs a SQL query for me that uses a count(*) and group by to return a count of each value in that column:

select "tag", count(*) as n from (
  select * from blog_entry_tags
  join blog_tag on blog_tag.id = blog_entry_tags.tag_id
) as results group by "tag" order by n desc

Then I demonstrate some of the default widget visualizations that come with Django SQL Dashboard. If I rewrite the query to return columns called bar_label and bar_quantity the tool will render the results as a bar chart:

select "tag" as bar_label, count(*) as bar_quantity from (
  select * from blog_entry_tags
  join blog_tag on blog_tag.id = blog_entry_tags.tag_id
) as results
group by "tag" order by bar_quantity desc

Next, I demonstrate a similar trick that instead produces a word cloud by aliasing the columns to wordcloud_word and wordcloud_count:

select "tag" as wordcloud_word, count(*) as wordcloud_count from (
  select * from blog_entry_tags
  join blog_tag on blog_tag.id = blog_entry_tags.tag_id
) as results
group by "tag" order by wordcloud_count desc

Finally, I show how that query can be turned into a saved dashboard and made available to the public. Here's the saved dashboard I created in the video:

https://simonwillison.net/dashboard/tag-cloud/

This illustrates a key idea underlying both Django SQL dashboard and Datasette: a complete application can be defined as a SQL query!

Much of the work we do as web application developers can be boiled down to constructing a SQL query and hooking it up to output to a web page. If you can safely execute SQL queries from page query strings this means you can build custom applications that exist entirely as bookmarkable URLs.

My draw-a-shape-on-a-map application for searching mini parks in California from a few months ago is another example of this pattern in action.

Custom widgets

Building new custom widgets for this tool is extremely easy - hence the word cloud widget which I actually built specially for this demo. All you need to provide is a single Django template file.

If your widget is going to respond to returned columns wordcloud_word and wordcloud_count the name of that template is those columns, sorted alphabetically and joined with hyphens:

wordcloud_count-wordcloud_word.html

Place that in a django_sql_dashboard/widgets template directory and the new widget will be ready to use. Here's the full implementation of the word cloud widget.

Named parameter support

This is a feature I lifted directly from Datasette. You can construct SQL queries that look like this:

select * from blog_entry where id = %(id)s

This uses psycopg2 syntax for named parameters. The value will be correctly quoted and escaped, so this is a useful tool for avoiding SQL injection attacks.

Djang SQL Dashboard spots these parameters and turns them into form fields. Here's what that looks like in the interface:

These forms submit using GET, so the result can be bookmarked. Here's a saved dashboard you can use to retrieve the details of any of my blog entries by their numeric ID:

https://simonwillison.net/dashboard/blog-entry-by-id/?id=7991

You can include multiple SQL parameters on a single dashboard, and any form parameters will be made available to all of those queries.

This means you can build dashboards that run multiple queries against the same arguments. Imagine for example you want to build a report about a specific user's activity across multiple tables - you can accept their user ID as a parameter, then display the output of multiple queries (including custom visualizations) that each refer to that parameter.

Export through copy and paste

I love copy and paste as a mechanism for exporting data from a system. Django SQL Dashboard embraces this in a couple of ways:

Results from SQL queries can be copied out as TSV from an expandable textarea below the table - up to 1,000 rows. I like this format because you can paste it directly into Google Sheets or Excel to get the data correctly split into cells.
Any time JSON is returned as a value from PostgreSQL, a "copy to clipboard" icon is shown next to the JSON. I use this a lot: both for JSON stored in PostgreSQL as well as the output from JSON aggregation functions.

Export all query results as CSV/TSV

This comes up a lot at Vaccinate CA: we do a lot of data analysis where we need to work with other tools or send data to partners, and having a way to export the full set of results for a query (rather than truncating at the first thousand to avoid crashing the user's browser) was a frequent need. - Django SQL Dashboard provides this option using a combination of Django's streaming HTTP response mechanism and PostgreSQL server-side cursors to efficiently stream large amounts of data without running out of resources.

A complex example: searching code examples across my blog

I decided to see how far I could push PostgreSQL.

I often include code in my blog entries - examples that are wrapped in a <pre> tag. Within that tag I sometimes apply syntax highlighting (a bunch of <span> elements).

It turns out I've included code snippets in 134 different blog entries:

select count(*) from blog_entry where body ~ '<pre>.*<pre>'

Can I use regular expressions in PostgreSQL to extract just the code examples, clean them up (removing those spans, reversing HTML entity encoding) and then provide simple search across the text of those examples, all in one query?

It turns out I can!

Here's a saved dashboard you can use to execute searches against just the contents of those <pre> tags across every entry on my blog:

https://simonwillison.net/dashboard/code-examples/?search=select

with results_stripped as (
  select id, title,
    replace(replace(replace(replace(replace(regexp_replace(
      (regexp_matches(body, '<pre>(.*?)</pre>', 'g'))[1],
        E'<[^>]+>', '', 'gi'
      ), '&quot' || chr(59), '"'), '&gt' || chr(59), '>'), '&lt' || chr(59), '<'), '&#039' || chr(59), chr(39)), '&amp' || chr(59), '&'
    ) as code
  from
    blog_entry
  where
    body ~ '<pre>.*<pre>'
)
select id, title, code, 'https://simonwillison.net/e/' || id as link from results_stripped
where code like '%%' || %(search)s || '%%' limit 10

There's a lot going on here. The key component is this bit:

regexp_matches(body, '<pre>(.*?)</pre>', 'g'))[1]

The regexp_matches() function, with the 'g' flag, returns every match for the given regular expression. As part of a larger select query this means that if the expression matches three times you'll get back three rows in the output (in this case with duplicate id and title columns) - which is what I want here.

It's wrapped in a terrifying nest of extra functions. These serve two purposes: they strip out any nested HTML tags, and the un-escape the ", <, >, & and ' HTML entities. I did this as a nested block of replace() functions - there's probably a neater solution here.

The chr(59) bits are a hack: Django SQL Dashboard disallows the ; character to ensure people can't execute multiple SQL queries - which could be used to work around some of the per-transaction protective settings applied by the tool.

But I need to search-and-replace " - so I use this pattern to include the semicolon:

replace(text, '&quot' || chr(59), '"')

Where || is the PostgreSQL string concatenation operator.

The search itself is constructed like this:

where code like '%%' || %(search)s || '%%'

This constructs a like query against '%your-search-term%' - the double percentage sign escaping is needed because % has a special meaning here (it's part of the %(search)s named parameter).

One last trick: the final output of the query is produced by this:

select id, title, code, 'https://simonwillison.net/e/' || id as link from results_stripped

results_stripped is a CTE defined earlier - I usually try to wrap up complex weird stuff like those nested replace() calls in a CTE so I can write a simple final query.

The 'https://simonwillison.net/e/' || id as link bit here concatenates together a URL that links to my entry based on its ID. My blog uses /yyyy/Mon/slug/ URLs but generating these from a SQL query against the created column was a little fussy, so I added /e/ID redirecting URLs to make generating links in dashboard queries easier.

Future plans

Django SQL Dashboard has already proved itself invaluable for my current project. I imagine I'll be using it for every Django project I build going forward - being able to query the database like this, create ad-hoc visualizations and then link to them is a huge productivity boost.

The bigger question is how it overlaps with Datasette.

Datasette has been SQLite-only since I started the project three and a half years ago - because I know that building a database abstraction layer is a huge additional commitment and, for Datasette's initial purpose of helping publish read-only data, it didn't feel necessary.

I have a growing suspicion that getting Datasette to work against PostgreSQL (and other database backends) in addition to SQLite is less work than I had originally thought.

Datasette is also built on top of ASGI. Django 3.0 introduced ASGI support, so it's now possible to host ASGI applications like Datasette as part of a unified Django application.

So it's possible that the future of Django SQL Dashboard will be for Datasette to eventually make it obsolete.

That doesn't stop it from being extremely useful today. If you try it out I'd love to hear from you! I'm also keen to see people start to expand it for their own projects, especially via the custom widgets mechanism.

Let me know if you try it out!

TIL this week

Scroll page to form if there are errors

Releases this week

django-sql-dashboard: 0.12 - (22 releases total) - 2021-05-08
Django app for building dashboards using raw SQL queries

Tags: databases, django, postgresql, projects, sql, datasette, weeknotes, django-sql-dashboard

Weeknotes: Vaccinate The States, and how I learned that returning dozens of MB of JSON works just fine these days

2021-04-26T01:02:22+00:00

On Friday VaccinateCA grew in scope, a lot: we launched a new website called Vaccinate The States. Patrick McKenzie wrote more about the project here - the short version is that we're building the most comprehensive possible dataset of vaccine availability in the USA, using a combination of data collation, online research and continuing to make a huge number of phone calls.

VIAL, the Django application I've been working on since late February, had to go through some extensive upgrades to help support this effort!

VIAL has a number of responsibilities. It acts as our central point of truth for the vaccination locations that we are tracking, powers the app used by our callers to serve up locations to call and record the results, and as-of this week it's also a central point for our efforts to combine data from multiple other providers and scrapers.

The data ingestion work is happening in a public repository, CAVaccineInventory/vaccine-feed-ingest. I have yet to write a single line of code there (and I thoroughly enjoy working on that kind of code) because I've been heads down working on VIAL itself to ensure it can support the ingestion efforts.

Matching and concordances

If you're combining data about vaccination locations from a range of different sources, one of the biggest challenges is de-duplicating the data: it's important the same location doesn't show up multiple times (potentially with slightly differing details) due to appearing in multiple sources.

Our first step towards handling this involved the addition of "concordance identifiers" to VIAL.

I first encountered the term "concordance" being used for this in the Who's On First project, which is building a gazetteer of every city/state/country/county/etc on earth.

A concordance is an identifier in another system. Our location ID for RITE AID PHARMACY 05976 in Santa Clara is receu5biMhfN8wH7P - which is e3dfcda1-093f-479a-8bbb-14b80000184c in VaccineFinder and 7537904 in Vaccine Spotter and ChIJZaiURRPKj4ARz5nAXcWosUs in Google Places.

We're storing them in a Django table called ConcordanceIdentifier: each record has an authority (e.g. vaccinespotter_org) and an identifier (7537904) and a many-to-many relationship to our Location model.

Why many-to-many? Surely we only want a single location for any one of these identifiers?

Exactly! That's why it's many-to-many: because if we import the same location twice, then assign concordance identifiers to it, we can instantly spot that it's a duplicate and needs to be merged.

Raw data from scrapers

ConcordanceIdentifier also has a many-to-many relationship with a new table, called SourceLocation. This table is essentially a PostgreSQL JSON column with a few other columns (including latitude and longitude) into which our scrapers and ingesters can dump raw data. This means we can use PostgreSQL queries to perform all kinds of analysis on the unprocessed data before it gets cleaned up, de-duplicated and loaded into our point-of-truth Location table.

How to dedupe and match locations?

Initially I thought we would do the deduping and matching inside of VIAL itself, using the raw data that had been ingested into the SourceLocation table.

Since we were on a tight internal deadline it proved more practical for people to start experimenting with matching code outside of VIAL. But that meant they needed the raw data - 40,000+ location records (and growing rapidly).

A few weeks ago I built a CSV export feature for the VIAL admin screens, using Django's StreamingHttpResponse class combined with keyset pagination for bulk export without sucking the entire table into web server memory - details in this TIL.

Our data ingestion team wanted a GeoJSON export - specifically newline-delimited GeoJSON - which they could then load into GeoPandas to help run matching operations.

So I built a simple "search API" which defaults to returning 20 results at a time, but also has an option to "give me everything" - using the same technique I used for the CSV export: keyset pagination combined with a StreamingHttpResponse.

And it worked! It turns out that if you're running on modern infrastructure (Cloud Run and Cloud SQL in our case) in 2021 getting Django to return 50+MB of JSON in a streaming response works just fine.

Some of these exports are taking 20+ seconds, but for a small audience of trusted clients that's completely fine.

While working on this I realized that my idea of what size of data is appropriate for a dynamic web application to return more or less formed back in 2005. I still think it's rude to serve multiple MBs of JavaScript up to an inexpensive mobile phone on an expensive connection, but for server-to-server or server-to-automation-script situations serving up 50+ MB of JSON in one go turns out to be a perfectly cromulent way of doing things.

Export full results from django-sql-dashboard

django-sql-dashboard is my Datasette-inspired library for adding read-only arbitrary SQL queries to any Django+PostgreSQL application.

I built the first version last month to help compensate for switching VaccinateCA away from Airtable - one of the many benefits of Airtable is that it allows all kinds of arbitrary reporting, and Datasette has shown me that bookmarkable SQL queries can provide a huge amount of that value with very little written code, especially within organizations where SQL is already widely understood.

While it allows people to run any SQL they like (against a read-only PostgreSQL connection with a time limit) it restricts viewing to the first 1,000 records to be returned - because building robust, performant pagination against arbitrary SQL queries is a hard problem to solve.

Today I released django-sql-dashboard 0.10a0 with the ability to export all results for a query as a downloadable CSV or TSV file, using the same StreamingHttpResponse technique as my Django admin CSV export and all-results-at-once search endpoint.

I expect it to be pretty useful! It means I can run any SQL query I like against a Django project and get back the full results - often dozens of MBs - in a form I can import into other tools (including Datasette).

TIL this week

Releases this week

django-sql-dashboard: 0.10a1 - (21 total releases) - 2021-04-25
Django app for building dashboards using raw SQL queries

Tags: csv, django, django-admin, postgresql, projects, vaccines, weeknotes, vaccinate-ca, django-sql-dashboard

Weeknotes: django-sql-dashboard widgets

2021-03-21T05:50:25+00:00

A few small releases this week, for django-sql-dashboard, datasette-auth-passwords and datasette-publish-vercel.

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 label and 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_label and 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 label and 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.

Datasette and sqlite-utils both use Sphinx and reStructuredText for their documentation.

For 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.

Version 0.4 introduces optional support for HTTP Basic authentication instead - where the user's browser handles the authentication prompt.

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": true to your plugin configuration adds this protection, covering all of Datasette's resources.
It's much easier to authenticate with from automated scripts. curl and roquests and httpx all 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.

I added some new photos to the site today - on the entries for Novelty Automation, DEVIL-ish Little Things, Evergreen Aviation & Space Museum and California State Capitol Dioramas.

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

Tags: authentication, dashboard, django, postgresql, projects, zeit-now, weeknotes, django-sql-dashboard, sphinx-docs

VIAL is now live, plus django-sql-dashboard

2021-03-15T17:00:00+00:00

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

Tags: postgresql, vaccinate-ca, django-sql-dashboard, vaccinate-ca-blog

Weeknotes: tableau-to-sqlite, django-sql-dashboard

2021-03-14T07:35:18+00:00

This week I started a limited production run of my new backend for Vaccinate CA calling, built a tableau-to-sqlite import tool and started working on a subset of Datasette for PostgreSQL and Django called django-sql-dashboard.

Vaccinate CA backend progress

My key project at the moment is building out a new Django-powered backend for the Vaccinate CA call reporting application - where real human beings constantly call pharmacies and medical sites around California to build a comprehensive guide to where the Covid vaccine is available.

As of this week, the new backend is running for a subset of the overall call volume. It's exciting! It's also a reminder that the single hardest piece of logic in any crowdsourcing-style application is the logic that gives a human being their next task. I'm continuing to evolve that logic, which is somewhat harder when the system I'm modifying is actively being used.

tableau-to-sqlite

The Vaccinate CA project is constantly on the lookout for new sources of data that might indicate locations that have the vaccine. Some of this data is locked up in Tableau dashboards, which are notoriously tricky to scrape.

When faced with problems like this, I frequently turn to GitHub code search: I'll find a unique looking token in the data I'm trying to wrangle and run searches to see if anyone on GitHub has written code to handle it.

In doing so, I came across Tableau Scraper - an open source Python library by Bertrand Martel which does a fantastic job of turning a Tableau dashboard into a Pandas DataFrame.

Writing a Pandas DataFrame to a SQLite database is a one-liner: df.to_sql("table-name", sqlite3.connect(db_path)). So I spun up a quick command-line wrapper around the TableuaScraper class called tableau-to-sqlite which lets you do the following:

% tableau-to-sqlite tableau.db https://results.mo.gov/t/COVID19/views/VaccinationsDashboard/Vaccinations

Considering how much valuable data is trapped in government Tableau dashboards I'm really excited to point this tool at more sources. The README includes tips on combining this with sqlite-utils to get a CSV or JSON export which can then be tracked using Git scraping.

django-sql-dashboard

I'm continuing to ponder the idea of getting Datasette to talk to PostgreSQL in addition to SQLite, but in the meantime I have a growing Django application that runs against PostgreSQL and a desire to build some quick dashboards against it.

One of Datasette's key features is the ability to bookmark a read-only SQL query and share that link with other people. It's SQL injection attacks repurposed as a feature, and it's proved to be incredibly useful over the past few years.

Here's an example from earlier this week where I wanted to see how many GitHub issues I had opened and then closed within 60 seconds. The answer is 17!

django-sql-dashboard is my highly experimental exploration of what that idea looks like against a PostgreSQL database, wrapped inside a Django application

The key idea is to support executing read-only PostgreSQL statements with a strict timelimit (set using PostgreSQL's statement_timeout setting, described here). Users can execute SQL directly, bookmark and share queries and save them to a database table in order to construct persistent dashboards.

It's very early days for the project yet, and I'm still not 100% convinced it's a good idea, but early signs are very promising.

A fun feature is that it lets you have more than one SQL query on the same page. Here's what it looks like running against my blog's database, showing a count query and the months in which I wrote the most blog entries:

Releases this week

hacker-news-to-sqlite: 0.4 - (5 releases total) - 2021-03-13
Create a SQLite database containing data pulled from Hacker News
django-sql-dashboard: 0.1a3 - (3 releases total) - 2021-03-13
Django app for building dashboards using raw SQL queries
datasette-ripgrep: 0.7 - (11 releases total) - 2021-03-11
Web interface for searching your code using ripgrep, built as a Datasette plugin
tableau-to-sqlite: 0.2 - (3 releases total) - 2021-03-11
Fetch data from Tableau into a SQLite database

TIL this week

Tags: django, postgresql, projects, sql, weeknotes, vaccinate-ca, django-sql-dashboard