Simon Willison’s Weblog

Building a desktop application for Datasette (and weeknotes)

30th August 2021

This week I started experimenting with a desktop application version of Datasette—with the goal of providing people who aren’t comfortable with the command-line the ability to get Datasette up and running on their own personal computers.

Update 8th September 2021: I made a bunch more progress over the week following this post, see Datasette Desktop—a macOS desktop application for Datasette for details or download the app to try it out.

Why a desktop application?

On Monday I kicked off an enormous Twitter conversation when I posted:

I wonder how much of the popularity of R among some communities in comparison to Python comes down to the fact that with R you can install the RStudio desktop application and you’re ready to go

This ties into my single biggest complaint about Python: it’s just too hard for people to get started with. Setting up a Python development environment for the first time remains an enormous barrier to entry.

I later put this in stronger terms:

The more I think about this the more frustrated I get, thinking about the enormous amount of human potential that’s squandered because the barriers to getting started learning to program are so much higher than they need to be

Which made me think of glass houses. My own Datasette project has exactly the same problem: to run it locally you need to install Python and then install Datasette! Mac users can use Homebrew, but telling newcomers to install Homebrew first isn’t particularly welcoming either.

Ideally, I’d like people to be able to install a regular desktop application and start using Datasette that way, without even needing to know that it’s written in Python.

There’s been an open issue to get Datasette running as a standalone binary using PyInstaller since November 2017, with quite a bit of research.

But I want a UI as well: I don’t want to have to teach new users how to install and run a command-line application if I can avoid it.

So I decided to spend some time researching Electron to see how hard it would be to make a basic Datasette desktop application a reality.

Progress so far

The code I’ve written so far can be found in the simonw/datasette.app repository on GitHub. The app so far does the following:

• Run a datasette server on localhost attached to an available port (found using portfinder) which terminates when the app quits.
• Open a desktop window showing that Datasette instance once the server has started.
• Allow additional windows onto the same instance to be opened using the “New Window” menu option or the Command+N keyboard shortcut.
• Provides an “Open Database...” menu option (and Command+O shortcut) which brings up a file picker to allow the user to select a SQLite database file to open—once selected, this is attached to the Datasette instance and any windows showing the Datasette homepage are reloaded.

Here’s a video demo showing these features in action:

It’s very much an MVP, but I’m encouraged by the progress so far. I think this is enough of a proof of concept to be worth turning this into an actual usable product.

How this all works

There are two components to the application.

The first is a thin Electron shell, responsible for launching the Python server, managing windows and configuring the various desktop menu options used to configure it. The code for that lives in main.js.

The second is a custom Datasette plugin that adds extra functionality needed by the application. Currently this consists of a tiny bit of extra CSS to make the footer stick to the bottom of the window, and a custom API endpoint at /-/open-database-file which is called by the menu option for opening a new database.

Initial impressions of Electron

I know it’s cool to knock Electron, but in this case it feels like exactly the right tool for the job. Datasette is already a web application—what I need is a way to hide the configuration of that web application behind an icon, and re-present the interface in a way that feels more like a desktop application.

This is my first time building anything with Electron—here are some of my initial impressions.

• The initial getting started workflow is really good. I started out with their Quick Start and was up and running with a barebones application that I could start making changes to in just a few minutes.
• The documentation is pretty good, but it leans more towards being an API reference. I found myself googling for examples of different things I wanted to do pretty often.
• The automated testing situation isn’t great. I’m using Spectron and Mocha for my initial (very thin) tests—I got them up and running in GitHub Actions, but I’ve already run into some limitations:
  • For some reason each time I run the tests an Electron window (and datasette Python process) is left running. I can’t figure out why this is.
  • There doesn’t appear to be a way for tests to trigger menu items, which is frustrating because most of the logic I’ve written so far deals with menu items! There is an open issue for this dating back to May 2016.
• I haven’t yet managed to package my app. This is clearly going to be the biggest challenge.

Up next: packaging the app

I was hoping to get to this before writing up my progress in these weeknotes, but it looks like it’s going to be quite a challenge.

In order to produce an installable macOS app (I’ll dive into Windows later) I need to do the following:

• Build a standalone Datasette executable, complete with the custom plugin, using PyInstaller
• Sign that binary with an Apple developer certificate
• Build an Electron application that bundles a copy of that datasette binary
• Sign the resulting Electron application

I’m expecting figuring this out to be a long-winded and frustrating experience, which is more the fault of Apple than of Electron. I’m tracking my progress on this in issue #7.

Datasette 0.59a2

I pushed out a new alpha of Datasette earlier this week, partly driven by work I was doing on datasette.app.

The biggest new feature in this release is a new plugin hook: register_commands()—which lets plugins add additional commands to Datasette, e.g. datasette verify name-of-file.db.

I released a new plugin that exercises this hook called datasette-verify. Past experience has shown me that it’s crucial to ship an example plugin alongside a new hook, to help confirm that the hook design is fit for purpose.

It turns out I didn’t need this for datasette.app after all, but it’s still a great capability to have!

sqlite-utils 3.17

Quoting the release notes in full:

• The sqlite-utils memory command has a new --analyze option, which runs the equivalent of the analyze-tables command directly against the in-memory database created from the incoming CSV or JSON data. (#320)
• sqlite-utils insert-files now has the ability to insert file contents in to TEXT columns in addition to the default BLOB. Pass the --text option or use content_text as a column specifier. (#319)

evernote-to-sqlite 0.3.2

As a follow-up to last week’s work on my personal Dogsheep, I decided to re-import my Evernote notes... and found out that Evernote has changed their export mechanism in ways that broke my tool. Most concerningly their exported XML is even less well-formed than it used to be. This new release works around that.

TIL this week

• Searching all columns of a table in Datasette

Releases this week

• datasette-verify: 0.1—2021-08-28
  Verify that files can be opened by Datasette
• datasette: 0.59a2—(97 releases total)—2021-08-28
  An open source multi-tool for exploring and publishing data
• evernote-to-sqlite: 0.3.2—(5 releases total)—2021-08-26
  Tools for converting Evernote content to SQLite
• sqlite-utils: 3.17—(86 releases total)—2021-08-24
  Python CLI utility and library for manipulating SQLite databases