Simon Willison’s Weblog

Weeknotes: datasette-auth0

28th March 2022

Datasette 0.61, a Twitter Space and a new Datasette plugin for authenticating against Auth0.

datasette-auth0

I’ve been figuring out how best to integrate with Auth0 for account management and sign-in, for a project I’m working on with Natalie.

I used Auth0 for VIAL with Vaccinate CA last year, following the Auth0 Django tutorial using Python Social Auth.

This time I decided to try and do it from first principles rather than use a library, for reasons I discussed in this Twitter thread.

It turns out Auth0 is using regular OAuth 2, which can be implemented from scratch in just three steps:

1. Generate a URL to a page on Auth0 that will display the login screen
2. Implement a page that handles the redirect back from Auth0, which needs to exchange the code from the ?code= parameter for an access token by POSTing it to an authenticated API endpoint
3. Use that access token to retrieve the authenticated user’s profile

I wrote up the steps in this TIL: Simplest possible OAuth authentication with Auth0

Since it turned out to be pretty straight-forward, I turned it into a new authentication plugin for Datasette: datasette-auth0.

You can try that out with the live demo at datasette-auth0-demo.datasette.io—click on the top right menu icon and select “Sign in with Auth0”.

The live demo is deployed automatically any time a push to the main branch passes its tests. I’ve implemented this pattern a few times now, so I wrote it up in another TIL: Deploying a live Datasette demo when the tests pass.

Datasette 0.61.0 (and 0.61.1)

I wrote about these in the annotated weeknotes. They were pretty significant releases, representing 86 commits since 0.60.2 released back in January.

New features as documentation

Some of my favourite feature requests for my projects are ones that can be solved by documentation. I had a great example of that this week.

In #420: Transform command with shared context Mehmet Sukan wanted a way to speed up the following operation using sqlite-utils insert --convert—described in What’s new in sqlite-utils 3.20 and 3.21:

cat items.json | jq '.data' | sqlite-utils insert listings.db listings - --convert '
d = enchant.Dict("en_US")
row["is_dictionary_word"] = d.check(row["name"])
' --import=enchant --ignore

The --convert option lets you specify Python code that will be executed against each inserted item. Mehmet’s problem was that the enchant.Dict("en_US") operation is quite expensive, and it was being run every time around the loop.

I started looking into ways to speed this up... and realized that there was already a mechanism for doing this, but it was undocumented and I hadn’t previously realized it was even possible!

The recipe that works looks like this:

echo '[
  {"name": "notaword"},
  {"name": "word"}
]' | python3 -m sqlite_utils insert listings.db listings - --convert '
import enchant
d = enchant.Dict("en_US")

def convert(row):
    global d
    row["is_dictionary_word"] = d.check(row["name"])
'

The result:

% sqlite-utils rows listings.db listings        
[{"name": "notaword", "is_dictionary_word": 0},
 {"name": "word", "is_dictionary_word": 1}]

This takes advantage of a feature of the sqlite-utils Python snippet mechanism, which is implemented here. It first attempts exec() against the provided code to see if it defined a convert(value) function—if that fails, it composes a function body (to cover simple expressions like row["ok"] = True).

So I got to close the issue by adding some documentation showing how to do this!

Another, smaller example this week: when I figured out Extracting web page content using Readability.js and shot-scraper I learned that Playwright can accept and execute async () => {...} functions, enabling this pattern:

shot-scraper javascript https://simonwillison.net/2022/Mar/24/datasette-061/ "
async () => {
  const readability = await import('https://cdn.skypack.dev/@mozilla/readability');
  return (new readability.Readability(document)).parse();
}"

So I added that pattern to the shot-scraper documentation.

SQLite Happy Hour Twitter Space

I hosted my first Twitter Space. The recording and notes can be found in SQLite Happy Hour—a Twitter Spaces conversation about three interesting projects building on SQLite.

I also learned how to download the audio of a Twitter Spaces in two different ways, as documented in my TIL on Exporting and editing a Twitter Spaces recording.

Releases this week

• datasette-auth0: 0.1—2022-03-27
  Datasette plugin that authenticates users using Auth0
• datasette-packages: 0.1.1—(2 releases total)—2022-03-25
  Show a list of currently installed Python packages
• datasette-hashed-urls: 0.4—(5 releases total)—2022-03-24
  Optimize Datasette performance behind a caching proxy
• datasette: 0.61.1—(110 releases total)—2022-03-23
  An open source multi-tool for exploring and publishing data
• datasette-publish-vercel: 0.13—(20 releases total)—2022-03-20
  Datasette plugin for publishing data using Vercel

TIL this Weeknotes

• Async fixtures with pytest-asyncio
• The simplest recursive CTE
• Counting SQLite virtual machine operations
• Using the GitHub Actions cache with npx and no package.json
• Rewriting a repo to contain the history of just specific files
• Exporting and editing a Twitter Spaces recording
• Extracting web page content using Readability.js and shot-scraper
• Simplest possible OAuth authentication with Auth0
• Deploying a live Datasette demo when the tests pass